6、mysql练习题（分组、多表、子查询）

分组查询

-- 2. 查询公司员工工资的最大值，最小值，平均值，总和
select max(sal), min(sal), avg(sal), sum(sal) from emp;
-- 3. 查询每个部门中各个职位的最高薪水。
select max(sal),deptno from emp group by deptno;
select avg(sal),deptno from emp group by deptno;
-- 4. 选择具有各个 job 的员工人数（提示：对 job 进行分组）
select count(ename), job from emp group by job;
-- 5. 查询员工最高工资和最低工资的差距，列名为 DIFFERENCE
select max(sal) - min(sal) DIFFERENCE from emp;
-- 6. 查询各个管理者属下员工的最低工资，其中最低工资不能低于 2000，没有管理者的员工不计算在内
select min(sal), mgr from emp where mgr is not null group by mgr having min(sal) >= 2000;
-- 7. 查询各个部门中工资大于1500的员工人数
select deptno, count(*) from emp where sal > 1500 group by deptno;
-- 8. 查询各部门的平均绩效，如果绩效为null，则按数值0进行统计
select avg(ifnull(comm,0)), deptno from emp group by deptno;
-- 11. 查询每个班级中每个科目的最高成绩
select classid, subject, max(score) from students group by classid,subject;
-- 12. 查询出每门课都大于80分的学生姓名。
select name from student_score group by name having min(score) > 80; 

多表查询

– 1. 查询员工的姓名及其所在部门的名字和城市 - 隐式内连接 inner join - 显式内连接
select ename, dname, loc from emp e, dept d where e.deptno = d.deptno;
– 2. 查询员工的姓名和他的管理者的姓名
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

– 1. 查询员工的编号、姓名、部门编码、部门名称以及部门所在城市。要求：把没有部门的员工也查出来
select empno, ename, e.deptno, dname, loc
from emp e left join dept d on e.deptno = d.deptno;
– 2. 查询员工的信息及其所在部门的信息。要求：把没有员工的部门也查出来
select empno, ename, d.deptno, dname, loc
from emp e right join dept d on e.deptno = d.deptno;
– 3. 查询员工的信息及其所在部门的信息。要求：只查询没有员工的部门
select empno, ename, d.deptno, dname, loc
from emp e right join dept d on e.deptno = d.deptno
where empno is null;
– 4. 查询并显示SALES部门的职位
select distinct job
from emp e right join dept d on e.deptno = d.deptno
where dname = ‘SALES’;
– 5. 查询所有部门的名称、所在地、员工数量以及平均工资
select dname, loc, count(ename), avg(sal)
from emp e join dept d on e.deptno = d.deptno
group by dname, loc;

– 9.假设员工表中，并且，上级管理者相同的员工，他们属于同一个部门。
– 找出EMP中那些工资高于他们所在部门的管理者工资的员工。
select e.ename, e.sal, m.sal
from emp e, emp m
where e.mgr = m.empno
and e.sal > m.sal;

– 10. 先关联, 再分组
select account, count(account) users, sum(rent) sum, sum(fee01), sum(fee02), sum(fee03), sum(fee04)
from userlist u left join chaege c
on u.telephone = c.telephone
group by account;

– 11.
select s.sno, sname
from student s join sc on s.sno = sc.sno
join course c on c.cno = sc.cno
where cname = ‘计算机原理’;

select cname
from student s join sc on s.sno = sc.sno
join course c on c.cno = sc.cno
where sname = ‘周星驰’;

– 12. 见第9题
– 13.
select cityNo, CityName, c.stateNo, stateName
from city c left join state s on c.stateNo = s.stateNo;

子查询

– 1. 查询哪个部门的平均工资是最高的，列出部门编码，平均工资。
select avg(sal) s_avg, deptno from emp group by deptno;
select max(s.s_avg)
from (select avg(sal) s_avg, deptno from emp group by deptno) s;
select deptno, s.s_avg
from (select avg(sal) s_avg, deptno from emp group by deptno) s
where s.s_avg = (select max(s.s_avg)
from (select avg(sal) s_avg, deptno from emp group by deptno) s);

select deptno, avg(sal) from emp
group by deptno
having avg(sal) >= all (select avg(sal) s_avg from emp group by deptno);

– 分页 - 不建议使用
select avg(sal) s_avg, deptno from emp group by deptno order by s_avg desc limit 0,1;

– 2. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
select max(sal),deptno from emp group by deptno;
select ename, e.deptno, sal from emp e
join (select max(sal) s_max,deptno from emp group by deptno) s
on s.deptno = e.deptno
where e.sal = s.s_max;

– 3. 查询管理者是“KING”的员工姓名（ename）和工资（sal）。
select empno from emp where ename = ‘KING’;
select ename, sal from emp
where mgr = (select empno from emp where ename = ‘KING’);

– 4. 查询部门所在地（loc）为“NEW YORK”的部门的员工姓名、部门名称和岗位名称。
select ename, (select dname from dept where dept.deptno = emp.deptno), job
from emp
where deptno = (select deptno from dept where loc = ‘NEW YORK’);

– 5. 查询工资比公司平均工资高的所有员工的员工号，姓名和工资。
select avg(sal) from emp;
select empno, ename, sal from emp
where sal > (select avg(sal) from emp);

– 6. 查询姓名中包含字母“u”的员工在相同部门的员工的员工号和姓名。
select deptno from emp where ename like ‘%U%’;
select empno, ename from emp
where deptno in (select deptno from emp where ename like ‘%U%’);

– 7. 查询哪些员工的薪水比本部门的平均薪水低。
select deptno, avg(sal) s_avg from emp group by deptno;
select ename, sal from emp e
join (select deptno, avg(sal) s_avg from emp group by deptno) s
on s.deptno = e.deptno
where e.sal < s.s_avg;

– 8. SALES部门有哪些职位？
select deptno from dept where dname = ‘SALES’;
select distinct job from emp
where deptno = (select deptno from dept where dname = ‘SALES’);

– 9. 哪些人不是别人的经理？
select distinct ifnull(mgr,0) from emp;
select empno, ename from emp
where empno not in (select distinct ifnull(mgr,0) from emp);

– 10. 谁的薪水比FORD高？如果有多个同名，比任何一个叫FORD的人高就行
select sal from emp where ename = ‘FORD’;
select ename, sal from emp
where sal > any(select sal from emp where ename = ‘FORD’);

– 11. 谁和FORD同部门？列出除了FORD之外的员工名字
select deptno from emp where ename = ‘FORD’;
select ename, deptno from emp
where deptno in (select deptno from emp where ename = ‘FORD’)
and ename <> ‘FORD’;

– 12. 哪个部门的人数比部门20的人数多
select count() from emp where deptno = 20;
select count(), deptno from emp
group by deptno having count() > (select count() from emp where deptno = 20);

– 13. 列出员工名字和职位，查询员工所在的部门平均薪水大于2000元的员工信息
select deptno from emp group by deptno having avg(sal) > 2000;
select ename, job from emp
where deptno in (select deptno from emp group by deptno having avg(sal) > 2000);

– 16. 找出EMP中那些工资高于他们所在部门普通员工（不包含管理者）平均工资的员工。
– 步骤a
select empno, ename, deptno, sal from emp
where empno not in (select distinct ifnull(mgr,0) from emp);
– 步骤b
select avg(s.sal), s.deptno
from (select empno, ename, deptno, sal from emp
where empno not in (select distinct ifnull(mgr,0) from emp)) S
group by s.deptno;

– 将 步骤a 和 步骤b 两张表关联, 筛选条件 a.sal > b.avg(s.sal)
select temp1.empno, temp1.ename, temp1.sal, temp1.deptno
from (select empno, ename, deptno, sal from emp
where empno not in (select distinct ifnull(mgr,0) from emp)) temp1
join (select avg(s.sal) t_sal, s.deptno
from (select empno, ename, deptno, sal from emp
where empno not in (select distinct ifnull(mgr,0) from emp)) S
group by s.deptno) temp2
on temp1.deptno = temp2.deptno
where temp1.sal > temp2.t_sal;