练习题目
- 每个部门薪水最高的员工
mysql> select e.ename, t.*
-> 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 |
+-------+--------+---------+
4 rows in set (0.00 sec)
- 哪些人的工资在平均工资上
mysql> select e.ename, e.sal, t.*
-> from emp e
-> join (select deptno, avg(sal) as avgsal from emp group by deptno) t
-> on e.sal > avgsal and e.deptno = t.deptno;
+-------+---------+--------+-------------+
| ename | sal | deptno | avgsal |
+-------+---------+--------+-------------+
| 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 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)
- 每个部门平均的工资等级
mysql> select e.deptno, avg(grade)
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> group by e.deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+------------+
3 rows in set (0.01 sec)
- 查找工资最高的员工
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
- 平均工资最高的部门
mysql> select d.dname, t.*
-> from dept d
-> join (select deptno, avg(sal) as avgsal from emp group by deptno) t
-> on d.deptno = t.deptno
-> order by avgsal desc
-> limit 1;
+------------+--------+-------------+
| dname | deptno | avgsal |
+------------+--------+-------------+
| ACCOUNTING | 10 | 2916.666667 |
+------------+--------+-------------+
1 row in set (0.00 sec)
- 平均工资等级最低的部门
mysql> select d.dname, t.*
-> from dept d
-> join (select e.deptno, avg(s.grade) as avggrade from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno order by avggrade limit 1) t
-> on d.deptno = t.deptno;
+-------+--------+----------+
| dname | deptno | avggrade |
+-------+--------+----------+
| SALES | 30 | 2.5000 |
+-------+--------+----------+
1 row in set (0.00 sec)
- 取得比普通员工(在mgr字段没有出现)的最高薪水还要高的领导
// 比普通员工最高工资还要高的一定是领导
mysql> select empno, 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));
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)
- 取得工资最高的前五名
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
- 取得工资最高的第6到第10名员工
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 5, 5;
+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)
- 查找最后入职的五名员工
mysql> select ename, hiredate
-> from emp
-> order by hiredate desc
-> limit 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
- 取得每个工资等级的员工数
mysql> select s.grade, count(*)
-> from emp e
-> join salgrade s
-> on e.sal >s.losal and e.sal <s.hisal
-> group by s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 3 |
| 3 | 2 |
| 2 | 3 |
| 4 | 3 |
| 5 | 1 |
+-------+----------+
5 rows in set (0.00 sec)
- 列出所有员工和领导的名字
mysql> select e1.ename as '员工', e2.ename as '领导'
-> from emp e1
-> left join emp e2
-> on e1.mgr = e2.empno;
+--------+-------+
| 员工 | 领导 |
+--------+-------+
| 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 |
+--------+-------+
14 rows in set (0.00 sec)
- 列出受雇日期早于上级的员工编号、姓名和部门名称
mysql> select e1.empno as '员工',e1.hiredate, e2.ename as '领导',e2.hiredate, e2.deptno, d.dname
-> from emp e1
-> join emp e2
-> on e1.mgr = e2.empno
-> join dept d
-> on e1.deptno = d.deptno
-> where e1.hiredate < e2.hiredate;
+-------+------------+-------+------------+--------+------------+
| 员工 | hiredate | 领导 | hiredate | deptno | dname |
+-------+------------+-------+------------+--------+------------+
| 7369 | 1980-12-17 | FORD | 1981-12-03 | 20 | RESEARCH |
| 7499 | 1981-02-20 | BLAKE | 1981-05-01 | 30 | SALES |
| 7521 | 1981-02-22 | BLAKE | 1981-05-01 | 30 | SALES |
| 7566 | 1981-04-02 | KING | 1981-11-17 | 10 | RESEARCH |
| 7698 | 1981-05-01 | KING | 1981-11-17 | 10 | SALES |
| 7782 | 1981-06-09 | KING | 1981-11-17 | 10 | ACCOUNTING |
+-------+------------+-------+------------+--------+------------+
6 rows in set (0.00 sec)
- 列出部门名称和部门员工的信息,同时列出没有员工的部门
mysql> select d.dname, e.*
-> from dept d
-> left join emp e
-> on d.deptno = e.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)
- 列出至少有5个员工的部门
mysql> select deptno
-> from emp
-> group by deptno
-> having count(*) >= 5;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
+--------+
2 rows in set (0.00 sec)
- 列出所有岗位为“clerk”的的员工,以及部门名称和部门人数
mysql> select e.ename, e.deptno, d.dname, t.countdept from emp e
-> join dept d
-> on e.deptno = d.deptno
-> join (select deptno, count(*) as countdept from emp group by deptno) t
-> on t.deptno = e.deptno
-> where e.job = 'clerk';
+--------+--------+------------+-----------+
| ename | deptno | dname | countdept |
+--------+--------+------------+-----------+
| SMITH | 20 | RESEARCH | 5 |
| ADAMS | 20 | RESEARCH | 5 |
| JAMES | 30 | SALES | 6 |
| MILLER | 10 | ACCOUNTING | 3 |
+--------+--------+------------+-----------+
4 rows in set (0.00 sec)
- 列出最低工资大于1500的岗位,以及岗位的人数
mysql> select job, count(*) as '人数'
-> from emp
-> group by job
-> having min(sal) > 1500;
+-----------+------+
| job | 人数 |
+-----------+------+
| MANAGER | 3 |
| ANALYST | 2 |
| PRESIDENT | 1 |
+-----------+------+
3 rows in set (0.00 sec)
- 列出岗位为“sales”的员工
mysql> select ename
-> from emp
-> where deptno = (select deptno from dept where dname = 'sales');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.00 sec)
- 列出工资高于平均工资的员工,以及所在部门、上级领导和工资等级
mysql> select e.ename as '员工', ee.ename as '领导', e.sal, s.grade, e.deptno
-> from emp e
-> left join emp ee
-> on e.mgr = ee.empno
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> where e.sal > (select avg(sal) from emp);
+-------+-------+---------+-------+--------+
| 员工 | 领导 | sal | grade | deptno |
+-------+-------+---------+-------+--------+
| FORD | JONES | 3000.00 | 4 | 20 |
| SCOTT | JONES | 3000.00 | 4 | 20 |
| CLARK | KING | 2450.00 | 4 | 10 |
| BLAKE | KING | 2850.00 | 4 | 30 |
| JONES | KING | 2975.00 | 4 | 20 |
| KING | NULL | 5000.00 | 5 | 10 |
+-------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
- 列出与“scott”从事相同工作的员工及部门名称
mysql> select e.ename, d.dname
-> from emp e
-> join dept d
-> on e.deptno = d.deptno
-> where job = (select job from emp where ename = 'scott') and e.ename <> 'scott';
+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)
- 列出与部门30的员工的工资相等的其它部门的员工和工资
mysql> select ename, sal
-> from emp
-> where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
Empty set (0.00 sec)
- 列出工资高于部门30的所有员工的工资的员工姓名、工资和部门名称
mysql> 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);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
- 列出每个部门的员工数量和平均工资
// 部门40没有人,采用count(*)结果为1,采用字段进行统计,忽略null值,结果为0
mysql> select d.deptno, count(e.ename) as '员工数量', ifnull(avg(sal),0) as '平均工资'
-> from emp e
-> right join dept d
-> on e.deptno = d.deptno
-> group by d.deptno;
+--------+----------+-------------+
| deptno | 员工数量 | 平均工资 |
+--------+----------+-------------+
| 10 | 3 | 2916.666667 |
| 20 | 5 | 2175.000000 |
| 30 | 6 | 1566.666667 |
| 40 | 0 | 0.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)
- 列出每个岗位的最低工资和最低工资的员工
mysql> select e.ename, t.*
-> from emp e
-> join (select job, min(sal) as minsal from emp group by job) t
-> on e.sal = minsal and e.job = t.job;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)
- 列出各个部门领导(岗位为manager)的最低工资
mysql> select deptno, min(sal)
-> from emp
-> where job = 'manager' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 20 | 2975.00 |
| 30 | 2850.00 |
| 10 | 2450.00 |
+--------+----------+
3 rows in set (0.00 sec)
- 列出员工给年薪,并从高到低进行排序
mysql> select ename, ((sal + ifnull(comm, 0)) * 12) as yearsal
-> from emp
-> order by yearsal desc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| MARTIN | 31800.00 |
| CLARK | 29400.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| TURNER | 18000.00 |
| MILLER | 15600.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+--------+----------+
14 rows in set (0.00 sec)
- 列出员工领导的工资高于3000的员工和员工领导领导
mysql> select e1.ename as '员工', e2.ename as '领导', e2.sal
-> from emp e1
-> join emp e2
-> on e1.mgr = e2.empno
-> where e2.sal > 3000;
+-------+------+---------+
| 员工 | 领导 | sal |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
3 rows in set (0.00 sec)
- 列出部门名称中带“s”的部门员工工资总和和部门人数
mysql> select t.*, count(e.deptno), sum(ifnull(sal,0))
-> from emp e
-> right join (select deptno, dname from dept where dname like '%s%') t
-> on e.deptno = t.deptno
-> group by t.deptno;
+--------+------------+-----------------+--------------------+
| deptno | dname | count(e.deptno) | sum(ifnull(sal,0)) |
+--------+------------+-----------------+--------------------+
| 20 | RESEARCH | 5 | 10875.00 |
| 30 | SALES | 6 | 9400.00 |
| 40 | OPERATIONS | 0 | 0.00 |
+--------+------------+-----------------+--------------------+
3 rows in set (0.00 sec)