Oracle Complex Query

1: List the names, department names, and salaries of all employees. Select a1.ename, a1.sal, a2.dname from EMP A1, DEPT A2 where a1.deptno = a2.deptno; 2: list all Department details and department count select a2.deptno, a2.dname, a2.loc, count (a1.empno) from EMP A1, DEPT A2 where a1.deptno (+) = a2.deptno group by a2.deptno, a2.dname, a2.loc; 3: List the annual salary of all employees. The Department name is listed in ascending order of annual salary. Select a1.sal * 12, a2.dname from EMP A1, dept A2 where a1.deptno = a2.deptno order by a1.sal * 12; 4: Find the supervisor and department name of each employee, and ask the supervisor to earn a salary of more than 3000 select employee. ename, boss. ename, a1.dname from EMP employee, EMP boss, DEPT A1 where employee. mgr = boss. empno and boss. deptno = a1.deptno and boss. SAL> 3000; 5: calculate the total salary of the Department employees with the 's' character in the department name. Select D. deptno, nvl (sum (SAL), 0), count (empno) from emp e, DEPT d Where E. deptno (+) = D. deptno and D. dname like '% S %' group by D. deptno; 6: List department names and employee information (quantity, average salary) of these departments, and list select D for departments without employees. dname, AVG (E. sal), count (E. empno) from emp e, DEPT d Where E. deptno (+) = D. deptno group by D. dname; 7: list the names of employees who work in the Department sales, basic salary, employment date, and department name. If you do not know the Department Number of the sales department, select a1.ename, a1.sal, a1.hiredate, a2.dname from EMP A1, DEPT A2 where a1.deptno = a2.deptno and a2.dname = 'sales'; 8: list the number of employees of each wage level in the company. The average wage is select grade, count (*), AVG (SAL) from EMP, salgrade where Sal between losal and hisal group by grade; 9: Name and salary of all employees whose salaries are higher than 30 in the department, department name select a1.ename, a1.sal, a2.dname from EMP A1, DEPT A2 where a1.deptno = a2.deptno and Sal> All (select Sal from EMP where deptno = 30); 10: list the number, name, Department name, Department location, and number of employees who have been employed before the direct superior date. Select E. empno, E. ename, D. dname, D. loc, temp. countfrom emp e, emp m, dept d, (select deptno DNO, count (empno) count from EMP group by deptno) tempwhere E. mgr = m. empno (+) and E. hiredate <m. hiredateand E. deptno = D. deptnoand E. deptno = temp. DNO; 11: list the names of all "clerk" and their department names, department count, and salary grade select e. ename, D. dname, temp. count, S. gradefrom emp e, dept d, (select deptno DNO, count (empno) count from EMP group by deptno) temp, salgrade swhere job = 'cler' and E. deptno = D. deptnoand D. deptno = temp. dnoand E. sal between S. losal and S. hisal; 12: List jobs with a minimum salary of more than 1500, total number of employees engaged in the job, and department name, location, and average wage select T. job, T. count, D. dname, E. ename, Reg. avgfrom dept D, (select e. job, count (E. empno) count from EMP e group by E. job having min (E. sal)> 1500) T, emp e, (select deptno DNO, AVG (SAL) AVG from EMP group by deptno) regwhere E. deptno = D. deptno and E. job = T. joband E. deptno = reg. DNO; 13: list all employees whose salaries are higher than the company's average salaries, their respective departments, superior leaders, and the company's salary levels select a1.ename, a2.dname, a1.mgr, a3.grade from EMP A1, DEPT A2, salgrade A3, (select AVG (SAL) mysal from EMP) A4 where a1.deptno = a2.deptno and Sal between a3.losal and a3.hisal and a1.sal> a4.mysal; 14: list all employees and department names of the same job as Scott. Select E. empno, E. ename, E. job, D. dname, temp. count from emp e, dept d, (select deptno DNO, count (empno) count from EMP group by deptno) tempwhere E. job = (select job from EMP where ename = 'Scott ') and E. ename <> 'Scott 'and E. deptno = D. deptno and temp. DNO = E. deptno; 15: list the number of employees, average salary, and average service life of each department. Select D. dname, count (E. empno), AVG (E. sal), round (AVG (sysdate-e.hiredate)/365) from emp e, DEPT d Where E. deptno (+) = D. deptno group by D. dname; 16: list the minimum wage for various jobs and the employee name select a1.ename, a1.job, a1.sal from EMP A1, (select job, min (SAL) min_sal from EMP group by job) a2 where a1.job = a2.job and a1.sal = a2.min _ Sal; 17: list the minimum salaries, names, department names, and department count of managers in each department. Select E. sal, E. ename, D. dname, count from (select job, min (SAL) Sal, ename, deptno from EMP where job = 'manager' group by job, ename, deptno) E, (select D. deptno, D. dname, count (E. empno) Count fromemp E, dept d wheree. deptno = D. deptno group by D. deptno, D. dname) dwhere E. deptno = D. deptno; Oracle paging (based on rownum paging) Select * from (select A1. *, rownum rn from (select ename, Sal from EMP order by SAL) A1 where rownum <= 10) where rn> = 6;