mysql练习题合集

表结构：

1、部门表：部门编号，部门名称，位置

create table dept(
		deptno int(10) primary key,
		dname varchar(14),
		loc varchar(13)
);

2、薪资表：薪资等级、当前等级的最低薪资、当前等级的最高薪资

create table salgrade(
		grade int(11),
		losal int(11),
		hisal int(11)
);

3、员工表：员工编号、名称、工作岗位、上级编号、录用日期、工资、奖金、部门编号

create table emp(
		empno int(4) primary key,
		ename varchar(10),
		job varchar(9),
		mgr int(4),
		hiredate date,
		sal double(7,2),
		comm double(7,2),
		deptno int(2)
);

---

1、取得每个部门最高薪水的人员名称

先取得每个部门的最高薪水，组成临时表，然后与员工表连接得到名称

select e.ename, t.maxSal from emp e
join (select deptno, max(sal) maxSal from emp group by deptno) t
on e.deptno = t.deptno and e.sal = t.maxSal;

2、哪些人的薪水在部门的平均薪水之上

先取得每个部门的平均薪水组成临时表，然后与员工表连接得到，注意连接条件

select e.ename, e.sal, t.avgSal, t.deptno from emp e
join (select deptno, avg(sal) avgSal from emp group by deptno) t
on e.deptno = t.deptno and e.sal > t.avgSal;

3、取得每个薪水等级有多少员工

先取得每个员工的薪水等级组成临时表，然后再按薪水等级分组统计即可。

select t.grade, count(*) from 
(select e.ename, s.grade from emp e
join salgrade s
on e.sal BETWEEN s.losal and s.hisal) t 
GROUP BY t.grade;

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

先取得所有"CLERK"(办事员)的姓名及其部门名称

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

再取得每个部门的人数

select deptno,count(*) num from emp
GROUP BY deptno;

将两张表通过depno连接，获得全部信息：

select m.ename, m.dname, n.num
from (select e.ename, d.dname, e.deptno from emp e 
join dept d
on d.deptno=e.deptno
where e.job='CLERK') m
join (select deptno,count(*) num from emp
GROUP BY deptno) n
on m.deptno=n.deptno;

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

先求公司的平均薪水，再列出薪水高于平均薪水的所有员工

select e.ename from emp e
where e.sal > (select avg(sal) from emp);

由于需要知道所有员工的其他信息，所以左连接部门表获得部门名称，左连接员工表获得领导名称，左连接薪水表获得薪水等级

select e.ename, d.dname, e1.ename leaderName, s.grade from emp e
left join dept d
on d.deptno=e.deptno
left join emp e1
on e.mgr=e1.empno
left join salgrade s
on e.sal BETWEEN s.losal and s.hisal
where e.sal > (select avg(sal) from emp);