7.子查询

1.查询工资比7566高雇员信息

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

2.查询雇员的编号、姓名、部门名称

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

3.查询工资比7654高，同时又与7900从事相同工作的雇员信息

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

4.查询工资最低的雇员的姓名、工作、工资

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

5.查询工资高于公司平均工资的雇员信息

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

6.查询每个部门的编号和最低工资，要求最低工资大于等于部门30的最低工资

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

7.查询部门的名称、部门的员工数、部门的平均工资、部门的最低收入雇员的姓名

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.查询平均工资最低的工作及平均工资

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.查询所在部门编号大于等于20的雇员信息

select * from emp
where deptno>=20;

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

10.查询工资与部门20中的任意员工相同的雇员信息

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

11.在所在从事销售工作的雇员中找出工资大于1500的员工

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