34道练习题（内含bjpowernode.sql数据）
* 本博客涉及到的数据为bjpowernode.sql，数据见文末。
* 该数据有三张表：部门表dept（deptno部门编号、dname部门名称、loc位置）、
员工表emp（empno工号、ename员工姓名、job职位、mgr直属领导工号、
hiredate入职日期、sal月薪、comm补贴、deptno部门编号）、
薪水等级表salgrade（grade等级、losal区间下限、hisal区间上限）
1、 取得每个部门最高薪水人员的名称
mysql> select t.deptno,e.ename,t.msal from emp e
-> join (selectdeptno,max(sal) msal from emp group by deptno) t
-> on e.sal =t.msal;
+--------+-------+---------+
| deptno | ename | msal |
+--------+-------+---------+
| 30 | BLAKE |2850.00 |
| 20 | SCOTT |3000.00 |
| 10 | KING | 5000.00 |
| 20 | FORD | 3000.00 |
+--------+-------+---------+
2、 哪些人的薪水在部门平均薪水之上？
思路：先求平均薪水构成表a2；联立emp e表，和a2表
mysql>select a.ename,a.sal,a.deptno
-> from emp a
-> join (select deptno,avg(sal) asalfrom emp group by deptno) b
-> on a.sal > b.asal and a.deptno =b.deptno;
mysql>create table a2 as select deptno,avg(sal) asal from emp group by deptno;
mysql>select e.ename,e.sal,e.deptno,a2.asal from emp e,a2 where e.sal > a2.asaland e.deptno = a2.deptno;
+-------+---------+--------+-------------+
| ename | sal |deptno | asal |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 | 30 | 1566.666667 |
| JONES | 2975.00 | 20 | 2175.000000 |
| BLAKE | 2850.00 | 30 | 1566.666667 |
| SCOTT | 3000.00 | 20 | 2175.000000 |
| KING | 5000.00 | 10 | 2916.666667 |
| FORD | 3000.00 | 20 | 2175.000000 |
+-------+---------+--------+-------------+
3、 取得部门中（所有人的）平均的薪水等级
思路：计算每个人的薪水等级；然后根据部门编号分组
select e.ename,e.deptno,s.gradefrom emp e
join salgrades on e.sal between s.losal and s.hisal
group by e.deptno；
mysql>select e.deptno,avg(s.grade) agrade from emp e
-> join salgrade s on e.sal betweens.losal and s.hisal
-> group by e.deptno;
+--------+--------+
| deptno |agrade |
+--------+--------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------+
4、 求最高薪水（给出两种解决方案）
法一：mysql>select ename, max(sal) from emp;
+--------+----------+
| ename | max(sal) |
+--------+----------+
| SIMITH | 5000.00 |
+--------+----------+
法二：mysql>select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
法三：mysql>select ename,sal from emp where sal not in(select distinct a.sal from emp ajoin emp b on a.sal < b.sal);
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
5、 取得平均薪水最高的部门和部门编号（至少给出两种解决方案）
mysql>create table a5 as select deptno,avg(sal) asal from emp group by deptno;
法一：mysql> select deptno,max(asal) masal from a5;
+--------+-------------+
| deptno | masal |
+--------+-------------+
| 10| 2916.666667 |
+--------+-------------+
select deptno,avg(sal) asal from emp group by deptno havingavg(sal) = (select max(t.asal) from (select deptno,avg(sal) asal from emp groupby deptno having avg(sal)) t);
法二：mysql>select deptno, asal from a5 order by asal desc limit 1;
+--------+-------------+
| deptno | asal |
+--------+-------------+
| 10| 2916.666667 |
+--------+-------------+
select deptno,avg(sal) asal from emp group by deptno having avg(sal) =(select avg(sal) from emp group by deptno order by avg(sal) desc limit 1);
6、 取得平均薪水最高的部门和部门名称
mysql>create table a6 as select deptno,avg(sal) asal from emp group by deptno;
mysql>select d.dname, max(a6.asal) from dept d,a6 where d.deptno = a6.deptno;
+------------+--------------+
|dname | max(a6.asal) |
+------------+--------------+
|ACCOUNTING | 2916.666667 |
+------------+--------------+
select d.dname,avg(e.sal)asal from emp e join dept d on e.deptno = d.deptno group by d.dname havingavg(e.sal) = (select avg(sal) from emp group by deptno order by avg(sal) desclimit 1);
7、 求平均薪水等级最低的部门的部门名称
mysql>select d.dname, avg(s.grade) agrade from emp e join dept d on e.deptno =d.deptno join salgrade s on e.sal between s.losal and s.hisal group by d.dnameorder by agrade asc limit 1;
+-------+--------+
| dname |agrade |
+-------+--------+
| SALES |2.5000 |
+-------+--------+
8、 取得比普通员工（院代码没有在mgr字段出现的）的最高薪水还要高的领导人姓名
mysql>select * from emp where empno not in(select distinct mgr from emp);
Empty set(0.00 sec)
无法查询到结果，原因是not in语句不会自动忽略空值，需要程序员手动排除NULL，即在语句后添加‘where mgr is not null’（这里KING没有mgr），但是in可以自动忽略空值
mysql>select * from emp where empno not in(select distinct mgr from emp where mgr isnot null);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno |ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SIMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALEDMAN | 7698 | 1981-09-28| 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08| 1500.00 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 |1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03| 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK |7782 | 1982-01-23 | 1300.00 | NULL| 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
mysql>select * from emp where empno in(select distinct mgr from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno |ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 |3000.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST
最低0.47元/天 解锁文章
7349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
