mysql练习

表结构

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );

员工表

部门表

薪资等级表

试题

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

select deptno,max(sal) as maxsal from emp group by deptno;

select e.ename,t.deptno,t.maxsal 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; 
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

select deptno,avg(sal) as avgsal from emp group by deptno;

select e.ename,t.* 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 order by deptno;
+-------+--------+-------------+
| ename | deptno | avgsal      |
+-------+--------+-------------+
| KING  |     10 | 2916.666667 |
| JONES |     20 | 2175.000000 |
| SCOTT |     20 | 2175.000000 |
| FORD  |     20 | 2175.000000 |
| BLAKE |     30 | 1566.666667 |
| ALLEN |     30 | 1566.666667 |
+-------+--------+-------------+

取得部门中（所有人的）平均薪水等级

select deptno,avg(sal) as avgsal from emp group by deptno;

select s.grade,t.* from salgrade s join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal order by s.grade;
+-------+--------+-------------+
| grade | deptno | avgsal      |
+-------+--------+-------------+
|     3 |     30 | 1566.666667 |
|     4 |     10 | 2916.666667 |
|     4 |     20 | 2175.000000 |
+-------+--------+-------------+

不准用组函数（MAX），取得最高薪水（给出两种解决方案）

/* 方法一 */
select e1.sal from emp e1 join emp e2 on e1.sal < e2.sal;

select e.ename,e.sal from emp e where e.sal not in (select e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
/* 方法二 */
select e.ename,e.sal from emp e order by e.sal desc limit 0,1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

取得平均薪水最高的部门的部门编号

select deptno,avg(sal)  as avgsal from emp group by deptno;

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 avg(sal) = (select max(t.avgsal) from (select deptno,avg(sal)  as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

取得平均薪水最高的部门的部门名称

select deptno,avg(sal)  as avgsal from emp group by deptno;

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 avg(sal) = (select max(t.avgsal) from (select deptno,avg(sal)  as avgsal from emp group by deptno) t);

select d.deptno,d.dname,tt.avgsal 
from dept d join (select deptno,avg(sal)  as avgsal from emp group by deptno having avg(sal) = (select max(t.avgsal) from (select deptno,avg(sal)  as avgsal from emp group by deptno) t)) tt on d.deptno = tt.deptno;
+--------+------------+-------------+
| deptno | dname      | avgsal      |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+

求平均薪水的等级最低的部门的部门名称

select deptno,avg(sal) as avgsal from emp group by deptno;

select t.deptno, t.avgsal,s.grade from salgrade s join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal; 

select 
	t.deptno,s.grade
from 
	salgrade s
join
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
on t.avgsal between s.losal and s.hisal

where s.grade = 
(select min(ss.grade) from salgrade ss
join
(select deptno,avg(sal) as avgsal from emp group by deptno) tt
on tt.avgsal between ss.losal and ss.hisal)

select d.dname from dept d join 
(select 
	t.deptno,s.grade
from 
	salgrade s
join
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
on t.avgsal between s.losal and s.hisal
where s.grade = 
(select min(ss.grade) from salgrade ss
join
(select deptno,avg(sal) as avgsal from emp group by deptno) tt
on tt.avgsal between ss.losal and ss.hisal) )ttt
on ttt.deptno = d.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+

取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名

select ename from emp where sal = (select min(sal) from emp where mgr not in (select mgr from emp where mgr <> null))
+-------+
| ename |
+-------+
| SMITH |
+-------+

取得薪水最高的前五名员工

select ename from emp order by sal desc limit 0,5;
+-------+
| ename |
+-------+
| KING  |
| FORD  |
| SCOTT |
| JONES |
| BLAKE |
+-------+

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

select count(*),s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+----------+-------+
| count(*) | grade |
+----------+-------+
|        3 |     1 |
|        3 |     2 |
|        2 |     3 |
|        5 |     4 |
|        1 |     5 |
+----------+-------+

列出所有员工及领导的名字

select e.ename,l.ename from emp e left join emp l on e.mgr=l.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称

select t.empno,t.ename,d.dname
from dept d
join
(select e.empno,e.ename,e.deptno
from emp e
join emp l
on e.mgr = l.empno
where e.hiredate < l.hiredate) t
on
	t.deptno = d.deptno
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+

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

select d.dname from dept d join 
(select deptno,count(*) as counts from emp group by deptno having counts >5) t on t.deptno = d.deptno
+-------+
| dname |
+-------+
| SALES |
+-------+