Sub-query and table joins
①. Query the name of the person who earns the most money
SELECT ename,sal from EMP
WHERE sal= (SELECT MAX (SAL) from EMP);
②. Find out who is on the average salary of everyone
SELECT ename,sal from EMP
WHERE sal > (SELECT AVG (SAL) from EMP);
③. Ask who is the highest in the department:
SELECT ename,sal from EMP
JOIN (SELECT MAX (SAL) Max_sal,deptno from emp GROUP by Deptno) t
On Emp.sal =t.max_sal and emp.deptno = T.deptno;
④. Find out the employee's name and his manager's name
SELECT E1.ename, E2.ename from
EMP E1 JOIN EMP E2
on e1.mgr = E2.empno;
⑤. Find out the salary level for everyone
SELECT Ename,grade from emp e JOIN salgrade s
On (E.sal between S.losal and S.hisal);
⑥ find out each person's department name and salary level
SELECT Ename,dname,grade from
EMP e JOIN Dept D on (E.deptno =d.deptno)
JOIN Salgrade s on (e.sal between S.losal and S.hisal)
⑦ (left outer connection) find out the employee's name and his manager's name
SELECT E1.ename, E2.ename from
EMP E1 left JOIN EMP E2
on e1.mgr = E2.empno;
⑧ (right outer connection) find out the department name of all employees
SELECT ename, dname from
EMP e right JOIN dept D
on e.deptno = D.deptno;
⑨ (full outer connection) to find out the department name of all employees
SELECT ename, dname from
EMP e full JOIN Dept D
on e.deptno = D.deptno;
⑩ the grade of the department average salary
SELECT Deptno,avg_sal,grade from
(SELECT deptno,avg (SAL) avg_sal from emp GROUP by Deptno) t
JOIN Salgrade S
On t.avg_sal between S.losal and S.hisal;
--to find the average salary level of the Department
SELECT Deptno,avg (grade) from
(SELECT deptno,ename,grade from emp JOIN salgrade s on
Emp.sal between S.losal and S.hisal) t
GROUP by Deptno;
--Ask the employee who is the manager
SELECT ename from EMP
WHERE empno in (SELECT DISTINCT Mgr from EMP);
--Don't use group functions to find the highest value of salary
SELECT sal from emp WHERE Sal isn't in
(SELECT DISTINCT e1.sal from EMP E1 JOIN EMP E2
on e1.sal< e2.sal);
-the department number of the department with the highest average salary
SELECT Deptno, avg_sal from
(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno);
WHERE avg_sal =
(SELECT MAX (avg_sal) from
(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno));
-the department name of the department with the highest average salary
SELECT dname from dept WHERE deptno =
(SELECT Deptno from
(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno)
WHERE avg_sal =
(SELECT MAX (avg_sal) from
(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno)
)
);
-the department name of the department with the lowest average salary level
SELECT Dept.dname,t1.deptno,grade,avg_sal from
(
SELECT Deptno,grade,avg_sal from
(SELECT AVG (SAL) avg_sal, deptno from emp GROUP by Deptno) T JOIN salgrade s
On t.avg_sal between S.losal and S.hisal
) T1
JOIN Dept On (t1.deptno = Dept.deptno)
WHERE T1.grade =
(SELECT MIN (grade) from
(SELECT deptno,grade,avg_sal from
(SELECT AVG (SAL) avg_sal, deptno from emp GROUP by Deptno) T JOIN salgrade s
On t.avg_sal between S.losal and S.hisal))
--The department name of the department manager who has the lowest average salary
-A manager's name that is higher than the average employee's salary
SELECT ename from EMP WHERE
Empno in (SELECT DISTINCT Mgr from EMP WHERE Mgr are not NULL)
> Sal
(SELECT Max (SAL) from EMP WHERE empno not in
(SELECT DISTINCT Mgr from EMP WHERE Mgr are not NULL))
-The top 5 employees with the highest salary
SELECT Ename,sal
From (
SELECT ename,sal from emp ORDER by Sal DESC
)
WHERE ROWNUM <=5;
-The 6th to 10th employees with the highest pay
SELECT Ename,sal from (
SELECT Ename,sal, ROWNUM R from (
SELECT ename,sal from emp ORDER by Sal DESC)
)
WHERE r>=6 and r<=10;
NOTE: The query statement is to watch the horse soldier's video Knock Out
ORCLA Database Review 2--subqueries and table joins
