Subquery 3 (use subquery in the from clause), from clause

Subquery 3 (use subquery in the from clause), from clause

In the FROM clause, subqueries generally return multiple rows and multiple columns, which can be treated as a data table.

 

Example 1,Query the number, name, location, department count, and average salary of each department.

SELECT d.deptno,d.dname,d.loc,temp.con,temp.avgsalFROM dept d,(SELECT deptno dno,COUNT(empno) con,ROUND (AVG(sal),2) avgsal             FROM emp              GROUP BY deptno) tempWHERE d.deptno=temp.dno;

 

Example 2,Query the number, name, basic salary, bonus, position, employment date, maximum department and minimum wage of all employees who work in the SALES department.

SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,e.deptno,temp.maxsal,temp.minsalFROM emp e,(            SELECT deptno dno,MAX(sal) maxsal,MIN(sal) minsal            FROM emp            GROUP BY deptno            ) tempWHERE e.deptno=(SELECT deptno                 FROM dept                 WHERE dname='SALES')                 AND e.deptno=temp.dno;

 

Example 3,The number, name, basic salary, position, employment date, Department name, location, superior name, Company salary grade, department count of all employees whose salaries are higher than the company's average salary are found, average salary and average service life

SELECT e.empno,e.ename,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade,temp.avgsal,temp.avgyearFROM emp e,dept d,emp m,salgrade s,(                                   SELECT deptno dno,COUNT(empno) con,ROUND (AVG(sal),2) avgsal,ROUND(AVG(months_between(SYSDATE,hiredate)/12),2) avgyear                                   FROM emp                                   GROUP BY deptno                                   ) tempWHERE e.sal>(SELECT AVG(sal)              FROM emp)              AND e.deptno=d.deptno              AND e.mgr=m.empno(+)              AND e.sal BETWEEN s.losal AND s.hisal              AND e.deptno=temp.dno;

 

Example 4,Lists the numbers, names, basic salaries, department names, leadership names, and number of employees who have higher salaries than ALLEN or CLARK.

SELECT e.empno,e.ename,e.sal,d.dname,m.ename,temp.conFROM emp e,dept d,emp m,(                          SELECT deptno dno,COUNT(empno) con                          FROM emp                          GROUP BY deptno                          )tempWHERE e.sal>ANY(SELECT sal                 FROM emp                 WHERE ename IN('ALLEN','CLARK')                 )              AND e.ename NOT IN ('ALLEN','CLARK')              AND e.deptno=d.deptno              AND e.mgr=m.empno(+)              AND e.deptno=temp.dno;

 

Example 5,Lists the name, salary, Department name, department count, and average salary of the managers (only one department) of each department of the company.

 

-- List the names, salaries, department names, number of employees, and average salaries of managers in each department of the company. SELECT e. ename, e. sal, d. dname, temp. con, temp. avgsalFROM emp e, dept d, (SELECT deptno dno, COUNT (empno) con, ROUND (AVG (sal), 2) avgsal FROM emp group by deptno) tempWHERE e. job = 'manager' AND e. deptno = d. deptno (+) AND e. deptno = temp. dno;