ORACLE query exercise

ORACLE query exercise emp employee table (empno employee No./ename employee name/job work/mgr superior No./hiredate employment date/sal salary/comm Commission/deptno Department No) dept Department table (deptno Department No./dname Department name/loc location) salary = salary + Commission 1. list all departments with at least one employee. 2. List all employees with higher salaries than SMITH. 3. List the names of all employees and their immediate superiors. 4. list all employees whose employment date is earlier than their direct superiors. 5. List department names and employee information of these departments, and list departments that do not have employees 6. List the names of all "CLERK" (Clerks) and their department names. 7. List jobs with a minimum salary of more than 1500. 8. List the names of employees who work in the SALES Department. Assume they do not know the Department Number of the SALES Department. 9. list all employees whose salaries are higher than the company's average salaries. 10. list all employees engaged in the same job as SCOTT. 11. List the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30. 12. List the names and salaries of employees whose salaries are higher than the salaries of all employees who work in the Department 30. 13. List the number of employees, average salaries, and average service life of each department. 14. List the names, department names, and salaries of all employees. 15. list detailed information about all departments and the number of departments. 16. List the minimum wage for various jobs. 17. List the minimum salaries of managers in each department. 18. List the annual salary of all employees in descending order of annual salary. -------- 1 ---------- select dname from dept where deptno in (select deptno from emp); -------- 2 ---------- select * from emp where sal> (select sal from emp where ename = 'Smith '); -------- 3 ---------- select. ename, (select ename from emp B where B. empno =. mgr) as bossname from emp a; -------- 4 ---------- select. ename from emp a where. hiredate <(select hiredate from emp B where B. empno =. mgr); -------- 5 ------- --- Select. dname, B. empno, B. ename, B. job, B. mgr, B. hiredate, B. sal, B. comm, B. deptnofrom dept a left join emp B on. deptno = B. deptno; -------- 6 ---------- select. ename, B. dname from emp a join dept bon. deptno = B. deptno and. job = 'cler'; -------- 7 ---------- select distinct job as HighSalJob from emp group by job having min (sal)> 1500; -------- 8 ---------- select ename from emp where deptno = (select deptno from Dept where dname = 'sales'); -------- 9 ---------- select ename from emp where sal> (select avg (sal) from emp ); -------- 10 --------- select ename from emp where job = (select job from emp where ename = 'Scott '); -------- 11 --------- select. ename,. sal from emp a where. sal in (select B. sal from emp B where B. deptno = 30) and. deptno <> 30; -------- 12 --------- select ename, sal from emp where sal> (select max (sal) from Emp where deptno = 30); -------- 13 --------- select (select B. dname from dept B where. deptno = B. deptno) as deptname, count (deptno) as deptcount, avg (sal) as deptavgsalfrom emp a group by deptno; -------- 14 --------- selecta. ename, (select B. dname from dept B where B. deptno =. deptno) as deptname, salfrom emp a; -------- 15 --------- selecta. deptno,. dname,. loc, (select count (deptno) from emp B where B. deptno =. d Eptno group by B. deptno) as deptcountfrom dept a; -------- 16 --------- select job, avg (sal) from emp group by job; -------- 17 --------- select deptno, min (sal) from emp where job = 'manager' group by deptno; -------- 18 --------- select ename, (sal + nvl (comm, 0) * 12 as salpersal from emp order by salpersal; ORACLE clause query and grouping. same table subquery as Condition. SELECT name FROM bbcWHERE population> (SELECT population FRO) M bbcWHERE name = 'Russia ') B. SELECT * FROM bbcWHERE region IN (SELECT region FROM bbcWHERE name IN ('India ', 'irance') c. given European countries whose GDP per capita exceeds 'United Kingdom '(UK. SELECT name FROM bbcWHERE region = 'Europe' AND gdp/population> (SELECT gdp/population FROM bbcWHERE name = 'United Kingdom ') d. this query is actually equivalent to the following: select e1.ename from emp e1, (select empno from emp where ename = 'King') E2 where e1.mgr = e2.empno; you can use EXISTS to write the same query. You only need to move the external query column to a subquery environment like the following: select ename from emp ewhere exists (select 0 from emp where e. mgr = empno and ename = 'King'); when you write EXISTS in a WHERE clause, it is equal to conveying this information to the optimization, that is, you want to run the external query first, and use each value to get a value from the internal query (assuming: EXISTS = from outside. B. an exception table subquery is used as a condition. select * from studentExam where studentid = (select studentid from student where name = 'Lili '); B. select * from studentexam where studentid in (select studentid from student) order by studentid; c. select * from student where studentid in (select studentid from studentexam where mark> 80); 3. select studentexam. mark, studentexam. studentid as seid, student. studentid, student. name from studentexam, student where student. studentid = studentexam. studentid; filter group: the order is grouping, then filtering, and finally counting (actual value ). select studentid, count (*) as highpasses from studentexamwhere mark> 70 group by studentid; if we do not want to use the actual value in the data table, we filter the query results through the results of the aggregate function. select studentid, avg (mark) as averagemarkfrom studentexamwhere avg (mark) <50 oravg (mark)> 70 group by studentid; (this sentence is incorrect, where sentences cannot use aggregate functions as conditions. At this time, having is used. select studentid, avg (mark) from studentexam group by studentid having avg (mark)> 70 or avg (mark) <50; select studentid, avg (mark) from studentexam where studentid in (,) group bystudentid having avg (mark)> 70; (grouping, filtering, having aggregation, and finally counting ). select studentid, avg (mark) as averagemarkfrom studentexamwhere examid in (5, 8, 11) group by studentidhaving avg (mark) <50 or avg (mark)> 70; returns a query with a limited number of rows: select name from student where rownum <= 10; The rownum keyword is used in oracle, but this keyword must be specified with a comparison operator in the where clause, it cannot be used together with order by because rownum maintains the original row number. if you need to use groupby \ order by, you can use the clause to query the table method: select studentid, averagemark from (select studentid, avg (mark) as partition by studentid order by averagemark desc) where rownum <= 10;