习题

#取得每个部门最高薪水的人员名称
select e.ENAME,t.* from EMP e 
JOIN
(select DEPTNO,MAX(SAL) as maxsal from EMP GROUP BY DEPTNO) t
ON e.DEPTNO=t.DEPTNO and e.SAL = t.maxsal;
select DEPTNO,AVG(SAL) from EMP GROUP BY DEPTNO;
#哪些人的薪水在部门的平均薪水之上
select e.ENAME,e.SAL from EMP e 
JOIN
(select DEPTNO,AVG(SAL) as avgsal from EMP GROUP BY DEPTNO) t
ON e.DEPTNO=t.DEPTNO AND e.SAL > t.avgsal;
#取得部门中（所有人的）平均的薪水等级
select e.DEPTNO, avg(s.GRADE)  from EMP e
JOIN
SALGRADE s
ON e.SAL BETWEEN s.LOSAL and s.HISAL
GROUP BY e.DEPTNO
#不准用组函数（Max），取得最高薪水。至少给出两种解决方案。
#第一种以降序方式，limit 1
select SAL as maxsal from EMP ORDER BY SAL desc limit 1
#表的自连接
select SAL as maxsal from EMP where SAL not in(
select DISTINCT a.SAL 
from 
EMP a 
JOIN
EMP b
on a.SAL < b.SAL
)
#取得平均薪水最高的部门的部门编号
#第一种方案：降序取第一个。
## 第一步：找出每个部门的平均薪水
select DEPTNO,AVG(sal) as avgsal from EMP group by DEPTNO;
## 第二步：降序选择第一个。
select DEPTNO, AVG(SAL) as avgsal from EMP GROUP BY DEPTNO ORDER BY avgsal desc limit 1;
## 第二种方案：max
select max(t.avgsal) from (select DEPTNO ,avg(SAL) as avgsal from EMP GROUP BY DEPTNO ) t;
select DEPTNO,avg(sal) as avgsal from EMP GROUP BY DEPTNO HAVING avgsal=(select max(t.avgsal) from (select DEPTNO ,avg(SAL) as avgsal from EMP GROUP BY DEPTNO ) t)
#取得平均薪水最高的部门的部门名称
select DNAME from DEPT as d 
JOIN
(select DEPTNO,AVG(sal) as avgsal from EMP group by DEPTNO ORDER BY avgsal desc limit 1) t
ON d.DEPTNO=t.DEPTNO;
#求平均薪水的等级最低的部门的部门名称
#第一步：按照部门名称分组，找出每个部门的平均薪水。
select avg(SAL) from EMP GROUP BY
#9、取得薪水最高的前五名员工
select ename,sal from EMP ORDER BY SAL DESC LIMIT 0, 5;
#取得薪水最高的第六到第十名员工
select ename,sal from EMP ORDER BY SAL DESC LIMIT 5,5;
#11、取得最后入职的5名员工
select ename,hiredate from EMP  ORDER BY hiredate DESC LIMIT 5 ;
#12、取得每个薪水等级有多少员工
select  
s.GRADE,count(*) 
from 
    EMP e 
join
    SALGRADE s 
on 
e.SAL between s.LOSAL and s.HISAL
GROUP  BY s.GRADE
#14、列出所有员工及领导的姓名
select a.ENAME as '员工', b.ENAME as '领导'
from 
    EMP a 
left join
    EMP b
on 
a.MGR=b.EMPNO;
#15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select 
  e.EMPNO,e.ENAME,d.DNAME
from 
  EMP e
join 
  EMP b
on
  e.MGR = b.EMPNO
join 
  DEPT d
on e.DEPTNO =d.DEPTNO
where  e.HIREDATE < b.HIREDATE
#16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select 
  d.DNAME,e.*
from 
    EMP e 
right JOIN
    DEPT d
on
    e.DEPTNO=d.DEPTNO
#17、列出至少有5个员工的所有部门
select 
    d.DNAME,count(*)
from 
    EMP e
JOIN
  DEPT d
on e.DEPTNO = d.DEPTNO
GROUP  BY e.DEPTNO
HAVING count(*)>=5
#18、列出薪金比"SMITH"多的所有员工信息.
select * from EMP where SAL > (select SAL from EMP where ENAME = 'SMITH')
#19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
#查询部门人手
select DEPTNO, count(*) as deptcount from EMP GROUP BY DEPTNO;
#列出所有"CLERK"(办事员)的姓名及其部门名称
select 
    e.ENAME,d.DNAME,d.DEPTNO
from 
    EMP e
JOIN
  DEPT d
on e.DEPTNO = d.DEPTNO
where 
  e.JOB = 'CLERK';
#组合查询
select 
    t1.*,t2.deptcount
from
    (select 
        e.ENAME,d.DNAME,d.DEPTNO
    from 
        EMP e
    JOIN
        DEPT d
    on e.DEPTNO = d.DEPTNO
    where 
        e.JOB = 'CLERK') t1
JOIN
  (select DEPTNO, count(*) as deptcount from EMP 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 
    e.ENAME
from 
  EMP e
JOIN 
  (select DEPTNO from DEPT  where DNAME='SALES' ) t
on 
  e.DEPTNO= t.DEPTNO 
#22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
#平均工资
select avg(SAL) from EMP 
select 
  e.ENAME as '姓名',
    d.DNAME as '部门名称',
    b.ENAME as '上级领导',
    s.GRADE as '工资等级'
from 
    EMP e
JOIN
  DEPT d
on
  e.DEPTNO = d.DEPTNO
left JOIN
  EMP b
on
  e.MGR = b.EMPNO
JOIN
  SALGRADE s
on 
  e.SAL BETWEEN s.LOSAL AND s.HISAL
where 
 e.SAL >(select avg(SAL) from EMP);
#23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
select 
 EMP.ENAME,DEPT.DNAME
from 
    EMP,DEPT
WHERE
  JOB=(select JOB from EMP where ENAME = 'SCOTT') and ENAME != 'SCOTT' and EMP.DEPTNO = DEPT.DEPTNO
#24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
select 
    e.ENAME,e.SAL
from
  EMP e
where 
  e.SAL in (select DISTINCT SAL from EMP where DEPTNO=30) and e.DEPTNO <> 30;
#25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select DISTINCT SAL from EMP where DEPTNO=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 where DEPTNO=30 ) and e.DEPTNO <> 30;
#26、列出在每个部门工作的员工数量,平均工资和平均服务期限.    
#mysql当中怎么计算两个日期的“年差”,查了多少年？
#TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
#间隔类型 second 秒  MINUTE 分钟  HOUR 小时  DAY 天 WEEK 星期 MONTH 月 QUARTER季度 YEAR 年
select 
    count(e.ENAME) as '员工数量',
    IFNULL(avg(e.SAL),0) as '平均工资',
    IFNULL(avg(TIMESTAMPDIFF(YEAR,e.HIREDATE,NOW())),0) as '平均服务期限'
from 
  EMP e    
right JOIN
    DEPT d
on
  e.DEPTNO = d.DEPTNO
GROUP BY
  d.DEPTNO
#27、列出所有员工的姓名、部门名称和工资。
select 
  e.ENAME,d.DNAME,e.SAL
from 
    EMP e 
join
  DEPT d
on e.DEPTNO = d.DEPTNO;
#28、列出所有部门的详细信息和人数
select 
   d.*,count(e.ENAME)
from 
  EMP e
right JOIN
  DEPT d
on 
    e.DEPTNO = d.DEPTNO
GROUP BY DEPTNO
#29、列出各种工作的最低工资及从事此工作的雇员姓名
select
  ENAME,
    SAL
from 
  EMP
where sal in (select 
    min(SAL)
from
  EMP
GROUP BY
  JOB)
#30、列出各个部门的MANAGER(领导)的最低薪金
select DISTINCT MGR from EMP GROUP BY MGR