7. Subquery

1. Check the wage ratio 7566 High employee information

select * 
from emp
where sal>(select sal from emp where empno=7566);

2. Query the employee's number 、 full name 、 Department name

select empno,ename,(select dname from dept where deptno=e.deptno)
from emp e;

3. Check the wage ratio 7654 high , At the same time with 7900 Information about employees engaged in the same work

select *
from emp
where sal>(
     select sal from emp where empno=7654
) and job=(
     select job from emp where empno=7900
);

4. Check the name of the lowest paid employee 、 Work 、 Wages

select ename,job,sal 
from emp 
where sal=(select min(sal) from emp);

5. Query the information of employees whose salary is higher than the average salary of the company

select * 
from emp
where sal>(select avg(sal) from emp);

6. Check the number and minimum wage of each department , The minimum wage is required to be greater than or equal to the Department 30 The minimum wage

select deptno,min(sal)
from emp
group by deptno
having min(sal)>=(
    select min(sal) from emp where deptno=30
);

7. Query the name of the Department 、 The number of employees in the Department 、 The average wage of the Department 、 The name of the lowest paid employee in the Department

select
    (select dname from dept where deptno=e.deptno) dname,
    count(empno),
    avg(sal),
    (select ename from emp where sal=min(e.sal)) ename
from emp e
group by deptno;

8. Check the lowest average wage job and average wage

select job,avg(sal)
from emp
group by job
having avg(sal)=(
    select min(t.avg) from (select avg(sal) avg from emp group by job) t
);

9. Query the department number is greater than or equal to 20 Of employees

select * from emp
where deptno>=20;

select * from emp
where deptno in (
    select deptno from dept where deptno>=20
);

10. Query salary and department 20 Any employee in the same employee information

select * from emp 
where sal in (
    select sal from emp where deptno=20
);

11. Find out that the salary of employees engaged in sales is greater than 1500 The employees'

select *
from (select * from emp where job='saleman') t
where t.sal>1500;