查询语句 34道练习题

9.取得薪水最高的前五名

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 |
+-------+---------+

10.取得薪水最高的第六到第十名员工

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 |
| MARTIN | 1250.00 |
+--------+---------+

11.取得最后入职的5名员工

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 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
+--------+------------+

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

先找出每个员工的薪资等级
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

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

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

左外连接

select  a.ename '员工', b.ename '领导' from emp a  left join emp b on a.mgr=b.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 |
+--------+-------+

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

emp a 员工表
emp b 领导表
条件 ： a的领导编号等于b的员工编号 说明a的直接领导是b
条件2 a的入职日期大于b的入职日期
a.mgr=b.empno and a.hiredate <b.hiredate


select 
a.ename, a.hiredate, b.ename, b.hiredate, d.dname
from 
	emp a
join 
 	emp b
 on 
 	a.mgr=b.empno
 join 
 	 dept d
	on
 a.deptno = d.deptno
 where
 a.hiredate <b.hiredate;
+-------+------------+-------+------------+------------+
| ename | hiredate   | ename | hiredate   | dname      |
+-------+------------+-------+------------+------------+
| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
+-------+------------+-------+------------+------------+

16.列出部门名称和这些部门的员工信息，同时列出没有员工的部门

左外连接
 select 
      e.*, d.* 
 from 
  	emp e
 left join
  	dept d
 on
  	e.deptno=d.deptno;
  	
  	
  	    ->   e.deptno=d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

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

先找出部门员工人数：
select deptno, count(*) from emp   group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
 
 
 再把上面的表当作一个临时表找出大于五的部门
 select t.deptno, t.count from(select deptno, count(*) as count from emp   group by deptno) t where t.count>=5;
+--------+-------+
| deptno | count |
+--------+-------+
|     20 |     5 |
|     30 |     6 |
+--------+-------+

18列出比smith 薪资多的所有员工

找出Smith的薪资

找出的薪资当作条件查找比smith高的人

select ename, sal from emp where sal>(select  sal from emp where ename='smith');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

19.列出所有‘clerk’的姓名及部门名称部门人数

先找出所有的办事员（clerk）
select 
	e.ename, e.job, d.dname, d.deptno
from 
	emp e
join 
	dept d
on
 	e.deptno=d.deptno
 where 
 	e.job='clerk';
 	
 +--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+

再找出部门人数

select 
 deptno, count(*)
 from
 emp
 group by
 deptno;
 +--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
两个表拼接

select 
t1.*, t2.*
from
(select 
	e.ename, e.job, d.dname, d.deptno
from 
	emp e
join 
	dept d
on
 	e.deptno=d.deptno
 where 
 	e.job='clerk') t1
 join
 (select 
 deptno, count(*)
 from
 emp
 group by
 deptno) t2
 on
 t1.deptno=t2.deptno;
 
 +--------+-------+------------+--------+--------+----------+
| ename  | job   | dname      | deptno | deptno | count(*) |
+--------+-------+------------+--------+--------+----------+
| MILLER | CLERK | ACCOUNTING |     10 |     10 |        3 |
| SMITH  | CLERK | RESEARCH   |     20 |     20 |        5 |
| ADAMS  | CLERK | RESEARCH   |     20 |     20 |        5 |
| JAMES  | CLERK | SALES      |     30 |     30 |        6 |
+--------+-------+------------+--------+--------+----------+