Subquery four (using subqueries in the SELECT clause)

example One, query out the number of each department, name, location, number of departments, average wage

 select   D.deptno,d.dname,d.loc, ( select  count  (empno) from  emp where  Emp.deptno =  d.deptno group  by   Deptno) con, ( select  avg  (SAL) from  emp where  Emp.deptno=  d.deptno by   Deptno) avgsal  from  Dept D 

With clause

example Two, using the WITH clause to define the data in the EMP table as a temporary table

 with  as (SELECT* from EMP) SELECT *  from E;

example three , query each department's number, name, location, department average salary, number of people

--query Each department's number, name, location, department average salary, number of people withE as(  SELECTDeptno DNO,ROUND(AVG(SAL),2) Avgsal,COUNT(empno) con fromEMPGROUP  bydeptno)SELECTD.deptno,d.dname,d.loc,e.avgsal,e.con fromDept D,eWHERED.deptno=E.dno (+);

example Four, query the highest salary of each department employee number, name, position, entry date, salary, department number, department name, display results sorted by department number

 withE as(   SELECTDeptno DNO,MAX(SAL) maxsal fromEMPGROUP  bydeptno)SELECTEm.ename,em.job,em.hiredate,em.sal,d.deptno,d.dname fromEMP em,dept d,eWHEREEm.deptno=E.dno andEm.sal=E.maxsal andD.deptno=E.dnoORDER  byEm.deptno;

Subquery four (using subqueries in the SELECT clause)