MySQL练习题（三）

17、列出至少有 5 个员工的所有部门

select 
    d.deptno,d.dname,count(e.ename) '人数'
from 
    emp e
join 
    dept d
on 
    e.deptno = d.deptno
group by
    e.deptno
having
    count(e.ename) >=5;

18、列出薪金比"SMITH" 多的所有员工信息

select 
    *
from 
    emp e
where
    sal > (select sal from emp where ename = "SMITH");

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

（1）找出所有CLERK的人员

select 
    *
from
    emp
where
    job = 'CLERK';

（2）找上面结果对应的部门名称

select 
    e.ename,e.job,d.dname,d.deptno
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    job = 'CLERK';

（3）查询每个部门的人数

select 
    deptno,count(*)
from 
    emp e
group by
    deptno;

（4）将这两个结果合并

    select 
        t1.dname,t1.job,t1.deptno,t2.num
    from(
        select 
            e.ename ename,e.job job,d.dname dname,d.deptno deptno
        from
            emp e
        join
            dept d
        on
            e.deptno = d.deptno
        where
            job = 'CLERK'
        ) t1
    join(
        select 
            deptno,count(*) num
        from 
            emp e
        group by
            deptno
        ) t2
    on
        t1.deptno = t2.deptno;

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select 
    job,count(*) '人数'
from 
    emp
group by
    job
having
    min(sal) > 1500;

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.(*)

select 
    ename,job
from 
    emp
where
    deptno in(select deptno from dept where dname = 'SALES');

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

（1）查询公司平均薪资

select avg(sal) from emp;

（2）查询高于上面结果的人员信息

select a.ename,a.deptno,b.ename,a.sal 
from emp a 
left join emp b 
on a.mgr = b.empno 
where a.sal > (select avg(sal) from emp);

（3）将上面结果与dept表进行内连接

select 
    t.ename,t.deptno,t.sal,d.dname,t.mgr
from
    (select a.ename ename,a.deptno deptno,b.ename mgr,a.sal sal
     from emp a 
     left join emp b 
     on a.mgr = b.empno 
     where a.sal > (select avg(sal) from emp)) t
join 
    dept d
on
    t.deptno = d.deptno;

（4）将上面结果与salgrade表进行内连接

select 
    t.ename,t.deptno,t.sal,d.dname,t.mgr '领导',s.grade
from
    (select a.ename ename,a.deptno deptno,b.ename mgr,a.sal sal
     from emp a 
     left join emp b 
     on a.mgr = b.empno 
     where a.sal > (select avg(sal) from emp)) t
join 
    dept d
on
    t.deptno = d.deptno
join
    salgrade s
on 
    t.sal between s.losal and s.hisal;

23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称

select
    e.ename,e.job,d.dname
from
    emp e
join 
    dept d
on
    e.deptno = d.deptno
where
    e.job = (select job from emp where ename = 'SCOTT') and e.ename <> 'SCOTT';

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

select 
    ename,sal
from
    emp
where
    sal in(select distinct sal from emp where deptno = 30) and deptno <> 30;

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

select 
    e.ename,e.sal,d.dname
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.sal > (select  max(sal) from emp e where e.deptno = 30);