Oracle Group Query subquery statistics query from plus subquery temporary table query above average wage sample code

---average wageSELECT AVG(SAL) fromEMP;-----------greater than average salarySELECTE.ename,e.job,e.sal fromEMP EWHEREE.sal>(SELECT AVG(SAL) fromEMP)----------e leader Number =m employee number--EMP Employee table, Dept Department tableSELECTE.ename Employee name, e.job employee position, e.sal employee salary, M.ename leadership name, m.job leadership position, D.dname department name fromEMP e,emp m,dept DWHEREE.sal>(SELECT AVG(SAL) fromEMP) andE.mgr=M.empno (+) andE.deptno=D.deptno-----------Plus from subquery statement, number of statisticiansSELECTE.ename Employee name, e.job employee position, e.sal employee salary, M.ename leadership name, m.job leadership position, D.dname department name, Dtemp.CountNumber of Departments fromEMP e,emp m,dept D, (SELECTDeptno DNO,COUNT(empno)Count fromEMPGROUP  bydeptno) DtempWHEREE.sal>(SELECT AVG(SAL) fromEMP) andE.mgr=M.empno (+) andE.deptno=D.deptno andDtemp.dno (+)=D.deptno----Add a salary scale. Salary LevelSELECTE.ename Employee name, e.job employee position, e.sal employee salary, M.ename leadership name, m.job leadership position, D.dname department name, Dtemp.Countnumber of departments, S.grade wage level fromEMP e,emp m,dept D, (SELECTDeptno DNO,COUNT(empno)Count fromEMPGROUP  bydeptno) Dtemp,salgrade sWHEREE.sal>(SELECT AVG(SAL) fromEMP) andE.mgr=M.empno (+) andE.deptno=D.deptno andDtemp.dno (+)=D.deptno andE.salbetweenS.losal andS.hisal--61m-----Number of people using this wage levelSELECTS1.grade SG,COUNT(E1.empno)Count fromEMP e1,salgrade S1WHEREE1.salbetweenS1.losal andS1.hisalGROUP  byS1.grade--Seventh-Number of people embedding wage levels, grouped by S1.grade----SELECTS1.grade SG,COUNT(E1.empno)Count fromEMP e1,salgrade S1WHEREE1.salbetweenS1.losal andS1.hisalGROUP  byS1.grade;----Complete bodySELECTE.ename Employee name, e.job employee position, e.sal employee salary, M.ename leadership name, m.job leadership position, D.dname department name, Dtemp.Countnumber of departments, S.grade wage level, stemp.CountRank number--m leader, fromEMP e,emp m,dept D, (SELECTDeptno DNO,COUNT(empno)Count fromEMPGROUP  bydeptno) Dtemp,--number of DTEMP departmentsSalgrade S, (SELECTS1.grade SG,COUNT(E1.empno)Count fromEMP e1,salgrade S1WHEREE1.salbetweenS1.losal andS1.hisalGROUP  bys1.grade) Stemp--stemp wage level numberWHEREE.sal>(SELECT AVG(SAL) fromEMP) andE.mgr=M.empno (+) andE.deptno=D.deptno andDtemp.dno (+)=D.deptno andE.salbetweenS.losal andS.hisal andS.grade=stemp.sg; 

Oracle Group Query subquery statistics query from plus subquery temporary table query above average wage sample code