Beginner SQL Statement Exercise 2

--Sub-query

--One-line subquery
--Find out all the information about employees who have a higher salary than employees named "CLARK"
SELECT * from emp where SAL > (SELECT SAL from emp where ename = ' CLARK ');

--inquire about employee names and wages above average wages.
SELECT Ename,sal from EMP
WHERE SAL > (SELECT ROUND (AVG (SAL)) from EMP);

-Query the names and wages of employees who are in the same department as SCOTT and who are lower than his salary.
SELECT Ename,sal from EMP
where DEPTNO = (SELECT DEPTNO from EMP WHERE ename = ' SCOTT ')
and Sal < (SELECT sal from EMP WHERE ename = ' SCOTT ');

--Search for employee information similar to Scott's, earlier than Scott hired
SELECT * from EMP
where JOB = (SELECT job from EMP WHERE ename = ' SCOTT ')
and HireDate < (SELECT hiredate from EMP WHERE ename = ' SCOTT ');

--inquire about the numbers and names of employees whose wages are higher than Scott or who have hired them earlier than Scott
SELECT Empno,ename from EMP
where SAL > (SELECT sal from EMP WHERE ename = ' SCOTT ')
OR HireDate < (SELECT hiredate from EMP WHERE ename = ' SCOTT ');

--Multi-row subquery
-->all () is a comparison of all values that match a subquery result
-->=<any () represents any one of the values in the matching subquery result
--in () represents any one of the values in the matching subquery result
--when any () is added with the = sign then the same meaning as in ()

--Inquire about employee information for wages below any one job that is "clerk".
--1. Obtaining employee salaries for work clerk
SELECT * from emp where SAL < All (select SAL from emp where JOB = ' clerk ');

--inquire about the number, name and salary of employees whose wages are higher than all "salesman".
--1. Getting salesman's wages
--1) Double-click the SQL line F8 execute
--2) Press the Home key shift+end Select single line F8 Execute
--1250 1600
Select Empno,ename,sal from emp where Sal >all (SELECT sal from emp where JOB = ' salesman ');

--To inquire about employee information in department 20 in the same position as 10 employees in the department.
--1. Access to 10th Department employees
SELECT * from emp where JOB =any (select Job from emp where DEPTNO = ten) and DEPTNO = 20;

--Query who is the leader of the employee
--1. Employee number of the leader
SELECT * from EMP where EMPNO in (the Select DISTINCT MGR from EMP where MGR are not NULL);

--Find the highest-paid staff in all employees with department number 20
--1. Get the maximum wage for all employees in department 20th
SELECT * from emp where SAL = (SELECT MAX (SAL) from emp where DEPTNO =) and DEPTNO = 20;

--Check the average salary level for each department
--You can use a subquery as a table to implement a multi-table association query
--sql statement Execution process: From-->where-->group by--->having--->select--->order by

--select ROUND (AVG (SAL)) Avg_sal from EMP WHERE avg_sal=2073; --Error

--1. Get the average salary per department
SELECT T.deptno,s.grade
From (SELECT deptno,round (AVG. SAL) Avg_sal from EMP GROUP by DEPTNO) T,salgrade S
WHERE t.avg_sal between S.losal and S.hisal;

--Query Department for details and department average salary and grade
--1. Get departmental Average wages
SELECT D.*,t2. Avg_sal,t2. GRADE
From DEPT D,
(SELECT t.deptno,s.grade,t.avg_sal
From (SELECT deptno,round (AVG. SAL) Avg_sal from EMP GROUP by DEPTNO) T,salgrade S
WHERE t.avg_sal between S.losal and S.hisal) T2
WHERE D.deptno = T2. DEPTNO (+);

Beginner SQL Statement Exercise 2