Using subqueries in the FROM clause

Using subqueries in the FROM clause

Example : Querying the number, name, location, number of departments, average wage for each department

First step : Check out the department number, the number of people, the average wage.

Select Deptno,count (empno), avg (SAL)

From EMP

Group BY Deptno;

The second step : The data returned above is multi-row and multi-column, as a data table for processing, as long as the from after the introduction of the Dept table

Select D.deptno,d.dname,d.loc,temp.count,temp.avg

From Dept D, (select Deptno dno,count (empno) count,avg (SAL) Avg

From EMP

GROUP BY Deptno) Temp

where D.deptno = Temp.dno (+);

main Purpose : the appearance of sub-query is mainly to solve the performance problem in multi-table query.

Secondary Purpose : Many times a subquery is used in the FROM clause because it is not possible to continue using the statistical function in an external query.

Example : Ask for an employee's name, position, salary, department name, leader name, leadership position, department number and departmental average salary, wage level, and number of employees at this level that are higher than the company's average salary.

Determine the data table to use:

EMP Table: Statistical Company's average salary

EMP Table: Employee name, position, salary

EMP table: Self-related, leader name, leadership position

Dept Table: Department Name

EMP Table: Statistics Department number, department average salary

Salgrade Table: Salary Level

EMP Table: Counting the number of people in a wage class

Determine the known associated fields:

Employees and leaders: Emp.mgr=m.empno

Employees and departments: Emp.deptno=dept.deptno

First step : Calculate the average salary of the company, return single row

Select AVG (SAL) from EMP;

Step two : Find the employee's name, position, and salary above this average wage

Select E.ename,e.job,e.sal

From EMP E

where E.sal > (select AVG (SAL) from EMP);

The third step : Leadership name, leadership position

Select E.ename,e.job,e.sal,m.ename,m.job

From EMP e,emp m

where E.sal > (select AVG (SAL) from EMP)

and e.mgr = M.empno (+);

Fourth Step : Join the Dept table to find the department name

Select E.ename,e.job,e.sal,m.ename,m.job,d.dname

From EMP e,emp m,dept D

where E.sal > (select AVG (SAL) from EMP)

and e.mgr = M.empno (+)

and E.deptno = D.deptno;

Fifth Step : Number of departments

Select Deptno Dno,count (empno) Count

From EMP

Group BY Deptno;

Select E.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count

From EMP e,emp m,dept D, (select Deptno dno,count (empno) Count

From EMP

Group by Deptno) dtemp

where E.sal > (select AVG (SAL) from EMP)

and e.mgr = M.empno (+)

and E.deptno = D.deptno

and D.deptno (+) = Dtemp.dno;

Sixth step : Salary level, add Salgrade table

Select E.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count,s.grade

From EMP e,emp m,dept D, (select Deptno dno,count (empno) Count

From EMP

Group by Deptno) Dtemp,salgrade s

where E.sal > (select AVG (SAL) from EMP)

and e.mgr = M.empno (+)

and E.deptno = D.deptno

and D.deptno (+) = Dtemp.dno

and e.sal between S.losal and S.hisal;

Seventh Step : Find out the number of employees in the wage level, still need to write a subquery for statistics

Select S1.grade Sg,count (e1.empno) Count

From EMP e1,salgrade s1

where e1.sal between S1.losal and S1.hisal

Group BY S1.grade;

Select E.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count,s.grade,stemp.count

From EMP e,emp m,dept D, (select Deptno dno,count (empno) Count

From EMP

Group by Deptno) Dtemp,salgrade s, (select S1.grade sg,count (e1.empno) Count

From EMP e1,salgrade s1

where e1.sal between S1.losal and S1.hisal

Group by S1.grade) stemp

where E.sal > (select AVG (SAL) from EMP)

and e.mgr = M.empno (+)

and E.deptno = D.deptno

and D.deptno (+) = Dtemp.dno

and e.sal between S.losal and S.hisal

and S.grade = stemp.sg;

This article from the "10281302" blog, reproduced please contact the author!

Using subqueries in the FROM clause