Oracle Learning Article Six: Sub-query

--1. Query for employees with a higher than 7654 salary

SELECT * from emp where sal> (select Sal from EMP where empno=7654);

---2. Find employee information for minimum wage
SELECT * from emp where sal= (select min (sal) from EMP);

------------The name of the department, the number of department employees, the average salary of the department, the name of the person with the lowest income, and the person with the highest income
Select D.dname,t1.c,t1.avgsal,t1.maxsal,t1.minsal from Dept D, (select Deptno,count (empno) c,avg (SAL) Avgsal,max (SAL) Maxsal,min (SAL) minsal from EMP Group by DEPTNO) T1
where D.deptno=t1.deptno


----Any function, =any is the same as in
SELECT * from emp where Sal in (select min (sal) from EMP Group by DEPTNO);

--> any is larger than the smallest value
SELECT * from emp where Sal>any (select min (sal) from EMP Group by DEPTNO);

--< Any is smaller than the maximum value.
SELECT * from emp where Sal < any (select min (sal) from EMP Group by DEPTNO);

SELECT * from emp where Sal < any (select min (sal) from EMP Group by DEPTNO);

---< All is smaller than the smallest
SELECT * from emp where sal > All (select Max (SAL) from EMP Group by DEPTNO);

---> All is bigger than the biggest
SELECT * from emp where Sal < All (select Max (SAL) from EMP Group by DEPTNO);

Oracle Learning Article Six: Sub-query