Summary of Oracle interview questions-3

<Span style = "font-size: 18px;"> SQL> -- 09. query the sales staff ('slesmance') whose salaries are higher than the average (the average salary includes all employees ') <br/> SQL> select * from EMP where job = 'slesmance' and Sal> (select AVG (SAL) from EMP); <br/> SQL> -- 10. show the names and salaries of all employees <br/> SQL> select ename, dname, Sal from EMP E, DEPT d Where E. deptno = D. deptno; <br/> SQL> -- 11. query the number, name, department, and location of an employee in the Research Department ('resarch'). <br/> SQL> select empno, ename, dname, LOC from EMP e, dept d Where E. d Eptno = D. deptno <br/> and dname = 'search'; <br/> SQL> -- 12. query the names and numbers of departments <br/> SQL> select * from (select deptno, count (*) from EMP group by deptno) e inner join dept D on E. deptno = D. deptno; </P> <p> SQL> -- 13. query the number of employees in each position whose salary is greater than the average salary (including all employees) and their positions <br/> SQL> select count (*), job from EMP where SAL> (select AVG (SAL) from EMP) group by job; </P> <p> SQL> -- 14. query the salaries and names of employees with the same salary <br/> SQL> select * from EMP e where (Select count (*) from EMP where sal = E. sal group by SAL)> 1; <br/> SQL> -- 15. query the information of the three employees with the highest salary <br/> SQL> select * from (select * from EMP order by Sal DESC) Where rownum <= 3; <br/> SQL> -- 16. rank by salary. The rank starts from 1 and the same rank is the same (if two people are in the same place, there is no second place, and the rank starts from the third place) <br/> SQL> select e. *, (select count (*) from EMP where SAL> E. sal) + 1 rank from EMP e order by rank; </P> <p> SQL> -- 17. employees with the same entry date (the same year, month, and day) <br/> SQL> select * from EMP e where (Select count (*) from EMP where E. hiredate = hiredate)> 1; </P> <p> SQL> -- 18. query the maximum salary of each department </P> <p> SQL> select deptno, max (SAL) maxsal from EMP group by deptno order by deptno; </P> <p> SQL> -- 19. query the highest salary of each department and position <br/> SQL> select deptno, job, max (SAL) from EMP group by deptno, job order by deptno, job; </P> <p> SQL> -- 20. query the information-level salary and level of each employee <br/> SQL> select * from (select e. *, rownum m from (select * from EMP order By Sal DESC) e where rownum <= 10) Where </P> <p> m> 5; </P> <p> SQL> -- 22. query Information about employees with the highest salaries in each department </P> <p> SQL> select * from EMP e where E. sal = (select max (SAL) from EMP where (deptno = E. deptno); </P> <p> SQL> -- 23. query the first two employees with the highest salaries in each department <br/> SQL> select * from EMP e where (select count (*) from EMP where SAL> E. sal and E. deptno = deptno) <2 <br/> order by deptno, Sal DESC; </P> <p> SQL> -- 24. query the employee information of more than three subordinates <br/> SQL> select * from EMP e where (select count (*) from EMP where E. empno = MGR)> 2; </P> <p> SQL> -- 25. Query the information of all employees who are higher than the average salary of the department. <br/> SQL> select * from EMP e where SAL> (select AVG (SAL) from EMP where (deptno = E. deptno) Order by deptno; <br/> SQL> -- 26. query the department information with the highest average salary <br/> SQL> select AVG (SAL) avgsal, deptno from EMP group by deptno; </P> <p> SQL> -- 27. query the department information that is greater than the average of the total salary of each department <br/> SQL> -- 1. Calculate the total salary of each department <br/> SQL> select sum (SAL) sumsal, deptno from EMP group by deptno; <br/> SQL> -- 2. Average value of each department <br/> SQL> select AVG (sum (SAL) from EMP group by deptno; <br/> SQL> -- 3. Obtain the department information that is greater than the average total salary. Connection procedure <br/> SQL> select D. *, sumsal from Dept D, (select sum (SAL) sumsal, deptno from EMP group by deptno) se where sumsal> </P> <p> (select AVG (sum (SAL) from EMP group by deptno) and SE. deptno = D. deptno; </P> <p> SQL> -- 29. query the information of a department without employees <br/> SQL> select D. * From dept d left join EMP e on (E. deptno = D. deptno) Where empno is null; <br/> </span>