练习题

#1.取得每个部门最高薪水的人员名称
select deptno,max(sal) as maxsal from emp group by deptno #取得每个部门的最高薪水（按照部门编号分组，取最大值）
#将以上查询结果看做一张临时表t
select 
    e.ename,t.*
from
    emp e
join(select deptno,max(sal) as maxsal from emp group by deptno) t
on
    t.deptno = e.DEPTNO and e.sal = t.maxsal;
#2.哪些人的薪水在部门的平均薪水之上
select deptno,avg(sal) as avgsal from emp group by deptno; #找出每个部门的平均薪水
# 将以上查询结果当做t表，t和emp表连接
#条件:部门编号相同，并且emp的sal大于avgsal
select 
    e.ename,e.sal,t.*
from 
    emp e
join (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    t.deptno = e.deptno and e.sal > t.avgsal;
#3.取得部门中所有人的平均的薪水等级
select avg(sal) as avgsal from emp;
#第一步：找出每个人的薪水等级
#emp e和salgrade s表连接
#连接条件：e.sal between s.losal and s.hisal
select 
    e.ename,e.deptno,e.sal,s.grade
from 
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
order by
    deptno ;
#第二步：基于以上结果继续按照deptno分组，求grade的平均值
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;
#4.不准用组函数（Max），取得最高薪水（给出两种解决方案）
#第一种方案：降序 limit1
select 
    ename,sal 
from 
    emp
order by 
    sal desc
limit 1;        
#第二种方案：表的自连接
/*
    a和b是同一张表  拿来做大于小于的比对的话  除了最大的那个值  其他的值是肯定会打印出来的
    最大工资不会小于b表的任何一个数据   所以去重之后他就不在表里了
    此时采用not in 输出的正好是最大的值
*/
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
select
    distinct    a.sal
from 
    emp a
join 
    emp b
on
    a.sal < b.sal
#5.取得平均薪资最高的部门编号
#第一种方案：
#第一步：找出每个部门的平均薪水
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) as avgsal from (select 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) as avgsal from (select avg(sal) as avgsal from emp group by deptno)t);
#6.取得平均薪水最高的部门名称
select 
    d.dname,avg(e.sal) as avgsal
from
    emp e
join 
    dept d
on
    d.DEPTNO = e.DEPTNO
group by
    d.dname
order by
    avgsal desc
limit 
    1;
#7.求平均薪水的等级最低的部门的部门名称
#第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
#第二步：找出每个部门的平均薪水的等级
select
    t.* ,s.GRADE
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
join
    salgrade s
on
    t.avgsal between s.LOSAL and s.HISAL
group by
    deptno;
#找出最低等级
#薪水最低，等级一定最低，但是薪水不是最低，等级也有可能是最低的
select avg(sal) as avgsal from emp group by deptno order by avgsal    limit 1;
select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal    limit 1) between LOSAL and hisal;
select
    t.* ,s.GRADE
from 
    (select d.dname,avg(sal) as avgsal from emp e join dept d on d.DEPTNO = e.DEPTNO group by dname) t
join
    salgrade s
on
    t.avgsal between s.LOSAL and s.HISAL
where
    s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal    limit 1) between LOSAL and hisal);
#8.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
select distinct mgr from emp where mgr is not null;
#员工编号没有出现在以上范围内的都是普通员工
#第一步：找出普通员工的最高薪水
#not in在使用的时候，后面的小括号中记得排除null
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
#第二步：找出高于1600的
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
#9.取得薪水最高的前五名
select sal from emp order by sal desc limit 5; 
#10.取得薪水最高的第六到第十名员工
select 
    sal
from
    emp
order by
    sal desc
limit
    5,5;
#11.取得最后入职的5名员工
select hiredate from emp order by hiredate desc;
select
    ename,hiredate
from
    emp
order by
    hiredate desc
limit
5;
#12.取得每个薪水等级有多少个员工
#第一步：找出每个员工的薪水等级
select 
    e.ename,e.sal,s.grade 
from 
    emp e 
join 
    salgrade s 
on
    e.sal between s.LOSAL and s.HISAL;
#第二步，继续按照grade分组统计数量
select 
    s.grade,count(*)
from
    emp e
join
    salgrade s
on
    e.sal BETWEEN s.LOSAL and s.HISAL
group by
    s.grade;
/*
13.有三个表 S（学生表） C（课程表） SC（学生选课表）
S(SNO,SNAME)  代表（学号，姓名）
C（CNO，CNAME，CTEACHER） 代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE） 代表 （学号，课号，成绩）
问题：
1.找出没选过“黎明”老师的所有学生姓名
2.列出2门以上（含两门）不及格学生姓名及平均成绩
3.既学过一号课程又学过2号课程的所有学生的姓名
*/
#14.列出所有员工及领导的姓名
SELECT
    a.ename '员工',b.ename'领导'
from
    emp a
left join
    emp b
on
    a.mgr = b.empno;
#15.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select 
    a.ename '员工',a.hiredate,b.ename'领导',b.hiredate,d.dname
from 
    emp a
left join
    emp b
on
     a.mgr = b.empno
join
    dept d
on
    a.DEPTNO = d.deptno
where
    a.HIREDATE < b.HIREDATE;
#16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select 
    e.*,d.dname
from
    emp e
join
    dept d
on
    e.DEPTNO = d.DEPTNO;
#17.列出至少有五个员工的所有部门
#按照部门编号分组，计数，筛选出 >= 5
select
    deptno
from
    emp
group by
    deptno
having
    count(*) >= 5;
#18.列出薪金比“SMITH”多的所有员工信息
select sal from emp where ename = 'SMITH';
select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
#19.列出所有“CLERK”（办事员）的姓名及部门名称，部门人数
select ename,job from emp where job='CLERK';
select 
    e.ename,e.job,d.dname,d.deptno
from
    emp e
join
    dept d
on
    e.DEPTNO = d.DEPTNO
where
    e.job = 'CLERK';
#每个部门的人数
select deptno,count(*) as deptcount from emp group by deptno; 
select
    t1.*,t2.deptcount
from
    (select e.ename,e.job,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 deptno from dept where dname = 'SALES';
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
#22.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
SELECT
    e.ename '员工',d.dname,l.ename '领导',s.grade
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
LEFT JOIN
    emp l
ON
    e.mgr = l.empno
JOIN
    salgrade s
ON
    e.sal BETWEEN s.LOSAL and s.HISAL
WHERE    
    e.sal > (SELECT avg(sal) FROM emp);
#23.列出与“SCOTT”从事相同工作的所有员工及部门名称
SELECT job FROM emp WHERE ename = '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 DISTINCT sal FROM emp WHERE deptno = 30;
SELECT
    ename,sal
FROM
    emp 
WHERE
    sal IN(SELECT DISTINCT sal FROM emp WHERE deptno = 30)
AND deptno <> 30;
#25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
SELECT max(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);
#26.列出在每个部门工作的员工数量，平均工资和平均服务期限
#timestampdiff(时间间隔，前一个日期，后一个日期)
SELECT
    d.*,count(e.ename) ecount, ifnull(avg(e.sal),0) as avgsal,ifnull(avg(TIMESTAMPDIFF(YEAR,hiredate,now())),0) as avgyservicetime
FROM
    emp e
RIGHT JOIN
    dept d 
ON
    e.deptno = d.deptno
GROUP BY
    d.deptno,d.dname,d.loc;
#列出所有员工的姓名、部门名称和工资 
SELECT
    e.ename,d.dname,e.sal
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno;
#28.列出所有部门的详细信息和人数
SELECT
    d.deptno,d.dname,d.loc,count(e.ename)
FROM
    emp e
RIGHT JOIN
    dept d
ON
    e.deptno = d.deptno
GROUP BY
    d.deptno,d.dname,d.loc;
#29.列出各种工作的最低工资以及从事此工作的雇员姓名
SELECT
    job,min(sal) as minsal
FROM
    emp
GROUP  BY
    job;
SELECT
    e.ename,t.*
FROM
    emp e
JOIN
    (SELECT job,min(sal) as minsal FROM emp GROUP BY job)t
ON
    e.job = t.job  and e.sal = t.minsal;
#30.列出各个部门的MANAGER的最低薪金
SELECT
    deptno,min(sal) as minsal 
FROM 
    emp
WHERE 
    job = 'MANAGER' 
GROUP BY 
    deptno;
#31.列出所有员工的年薪，按年薪从低到高排序
SELECT
    ename,(sal + IFNULL(comm,0)) * 12 as yearsal
FROM
    emp
order by
    yearsal asc;
#32.求出员工领导的薪水超过3000的员工名称与领导
SELECT
    a.ename '员工',b.ename '领导'
FROM
    emp a
JOIN
    emp b
ON
    a.mgr = b.empno
WHERE
    b.sal > 3000;
#求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数
SELECT
    d.deptno,d.dname,d.loc,count(e.ename),IFNULL(sum(e.sal),0) as sumsal
FROM
    emp e
RIGHT JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE    
    d.dname like '%S%'
GROUP BY
    d.deptno,d.dname,d.loc;
#34.给任职日期超过30年的员工加薪百分之10
update emp set sal = sal * 1.1 where TIMESTAMPDIFF(YEAR,hiredate,now()) > 30;
select * from emp;