SQL练习题（员工表）

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 );             --最高薪资

1、查询每个员工的上级领导 要求显示员工名和领导名

±-------±-------+
| 员工 | 领导 |
±-------±-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±-------±-------+

select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;

2、列出所有员工的姓名、部门名称和工资。

±-------±-----------±--------+
| ename | dname | sal |
±-------±-----------±--------+
| SMITH | RESEARCH | 800.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| JONES | RESEARCH | 2975.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| CLARK | ACCOUNTING | 2450.00 |
| SCOTT | RESEARCH | 3000.00 |
| KING | ACCOUNTING | 5000.00 |
| TURNER | SALES | 1500.00 |
| ADAMS | RESEARCH | 1100.00 |
| JAMES | SALES | 950.00 |
| FORD | RESEARCH | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
±-------±-----------±--------+

select distinct e.ename,d.dname,e.sal, from emp e,dept d,salgrage g,emp s where (e.deptno = d.deptno) and (e.sal between g.losal and g.hisal);

3、列出所有部门的详细信息和人数

±-------±-----------±---------±---------------+
| DEPTNO | DNAME | LOC | count(e.ename) |
±-------±-----------±---------±---------------+
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 10 | ACCOUNTING | NEW YORK | 3 |
| 40 | OPERATIONS | BOSTON | 0 |
±-------±-----------±---------±---------------+

select * from dept d left join(select deptno,count(*) from emp group by deptno) b on d.deptno = b.deptno;

4、取得每个部门最高薪水的人员名称
±------±-------±--------+
| ename | deptno | maxsal |
±------±-------±--------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
±------±-------±--------+

select e.ename,e.deptno,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;

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

±------±-------±--------±------------+
| ename | deptno | sal | avgsal |
±------±-------±--------±------------+
| ALLEN | 30 | 1600.00 | 1566.666667 |
| JONES | 20 | 2975.00 | 2175.000000 |
| BLAKE | 30 | 2850.00 | 1566.666667 |
| SCOTT | 20 | 3000.00 | 2175.000000 |
| KING | 10 | 5000.00 | 2916.666667 |
| FORD | 20 | 3000.00 | 2175.000000 |
±------±-------±--------±------------+

select e.ename,e.deptno,e.sal,t.avgsql 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;

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

±-------±-------------+
| deptno | avg(s.grade) |
±-------±-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------------+

select t.deptno,s.grade as avg(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 hisal;

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

±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+

select ename,sal from emp order by sal desc limit 0,5;

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

±------±---------------+
| grade | count(s.grade) |
±------±---------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
±------±---------------+

select grade,count(empno) from emp join salgrade on sal between losal and hisal group by grade;

9、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

±-----------±------±-------±----------±-----±-----------±--------±--------±-------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±-----------±------±-------±----------±-----±-----------±--------±--------±-------+
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
±-----------±------±-------±----------±-----±-----------±--------±--------±-------+

select d.dname,e.empno,e.name,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from dept left join emp e on d.deptno = e.deptno;

10、列出薪金比"SMITH"多的所有员工信息.

±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+

select * from emp where sal > (select sal from emp where ename = 'SIMTH');

11、列出各个部门的MANAGER(领导)的最低薪金

±-------±---------+
| deptno | min(sal) |
±-------±---------+
| 20 | 2975.00 |
| 30 | 2850.00 |
| 10 | 2450.00 |
±-------±---------+

select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;

12、求出员工领导的薪水超过3000的员工名称与领导名称

±------±-----+
| 员工 | 领导 |
±------±-----+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
±------±-----+

select a.ename,b.ename from emp a left join emp b on a.mgr = b.empno where b.sal > 3000;

13、找出名字中含有o的员工

±------+
| ename |
±------+
| JONES |
| SCOTT |
| FORD |
±------+

select ename from emp where ename like'%\o%' escape '\';

14、找出名字中第三个是’A’的员工

±------+
| ename |
±------+
| BLAKE |
| CLARK |
| ADAMS |
±------+

select ename from emp where ename like '__A%';

15、找出名字中最后一个是’T’的员工

±------+
| ename |
±------+
| SCOTT |
±------+

select ename from emp where ename like '%T';