文章目录
一、34道SQL综合练习二、测试使用的数据表三、创建测试表的SQL语句一、34道SQL综合练习
1.查询取得每个部门最高工资的人员信息。
select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal;
2.查询员工的工资在部门的平均工资以上。
select e.ename,e.sal,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.deptno=e.deptno and t.avgsal<e.sal;
3.查询部门中（所有人）的平均工资等级
select e.deptno,avg(s.grade) avggrade from emp e join salgrade s on e.sal between s.losal and hisal group by e.deptno order by e.deptno;
4.查询员工的最高工资（不使用max函数，使用两种以上解法）
解法一：使用limit和排序
select ename,sal from emp order by sal desc limit 1;
解法二：使用not in
select ename,sal from emp e where e.sal not in (select x.sal from emp x join emp y on x.sal<y.sal);
解法三：插询比第二高的员工工资
select ename,sal from emp where sal > (select sal from emp order by sal desc limit 1,1);
（还可以升序排序取最后一个，解法较多。）
5.查询平均工资最高的部门编号
解法一 ：使用order by 排序后limit
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
解法二：使用max
select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
6.查询平均工资最高的部门名称
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avgsal desc limit 1;
7.查询平均工资最低的部门的部门名称
select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade=(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
8.查询比普通员工（员工代码没有在mgr上出现的）的最高工资还高的领导姓名
select 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));
9.查询工资排在前五的员工信息
select ename,sal from emp order by sal desc limit 5;
10.查询工资排在第6-10名的员工信息
select ename,sal from emp order by sal desc limit 5,5;
11.查询最后入职的5名员工信息
select ename,hiredate from emp order by hiredate desc limit 5;
12.查询每个工资等级各有多少员工
select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and hisal group by s.grade;
13.查询工作地点在DALLAS的员工信息
select e.empno,e.ename,e.job from emp e where deptno = (select deptno from dept d where loc="DALLAS");
14.查询每个员工对对应的领导姓名
select a.ename as "员工",b.ename as "领导" from emp a left join emp b on a.mgr=b.empno;
15.查询入职日期早于其直接上级领导的员工编号、姓名、部门名称
select distinct a.ename as "员工",a.hiredate,b.ename as "领导",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;
16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select e.*,d.dname from emp e right join dept d on e.deptno=d.deptno;
17.查询至少有5个员工的所有部门
select deptno from emp group by deptno having count(*) >= 5;
18.查询工资比“smith”高的员工信息
select ename,sal from emp where sal > (select sal from emp where ename = "smith");
19.查询所有"CLERK"(办事员)的姓名及其部门名称，部门的人数
select t1.*,t2.deptcount from (select e.ename,d.dname,d.deptno from emp e join dept d on d.deptno=e.deptno where job="clerk") t1 join (select deptno ,count(*) as deptcount from emp group by deptno) t2 on t1.deptno=t2.deptno order by deptno;
20.查询最低工资大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal)>1500;
21.查询在部门"SALES"<销售部>工作的员工的姓名，假定不知道销售部的部门编号
select ename from emp where deptno = (select deptno from dept where dname="sales");
22.查询工资高于公司平均工资的所有员工，所在部门，上级领导，雇员的工资等级.
select e.ename "员工",d.dname,l.ename "领导",s.grade from emp e join dept d on e.deptno=d.deptno left join emp l on e.mgr = l.empno join salgrade s on e.sal between losal and hisal where e.sal > (select avg(sal) from emp);
23.查询与"scott"从事相同工作的所有员工及部门名称
select e.ename,e.job,d.dname from emp e join dept d on e.deptno=d.deptno where e.job=(select job from emp where ename="scott") and e.ename<>"scott";
24.查询工资等于部门30中员工的工资的其他员工的姓名和工资
select ename,sal from emp where sal in (select sal from emp where deptno=30) and deptno<>30;
25查询工资高于在部门30工作的所有员工的工资的员工姓名工资、部门名称
select e.ename,e.sal,d.dname from emp e join dept d on d.deptno=e.deptno where e.sal > (select max(sal) from emp where deptno=30);
26.查询在每个部门工作的员工数量，平均工资和平均服务期限
select d.deptno,count(e.ename) as ecount,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
27.查询所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
28.查询所有部门详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
29.查询各种工作的最低工资以及对应员工的姓名
select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job=t.job and e.sal=t.minsal;
30.查询各个部门“manager”的最低工资
select deptno,min(sal) from emp where job="manager" group by deptno;
31.查询所有员工的年工资，按年工资最高到低排序
select ename,(sal + ifnull(comm,0)) as yearsal from emp order by yearsal asc;
32.查询员工领导的工资超过3000的员工名称与领导
select a.ename as "员工",b.ename as "领导" from emp a join emp b on a.mgr=b.empno where b.sal > 3000;
33.查询部门名称中，带’s’字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like"%S%" group by d.deptno,d.dname,d.loc;
34.给任职日期超过30年的员工加新10%
update emp set sal = sal *1.1 where timestampdiff(YEAR,hiredate,now())>30;
二、测试使用的数据表
emp表（员工表）
mysql> select * from emp;+-------+--------+-----------+------+------------+---------+---------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL| COMM | DEPTNO |+-------+--------+-----------+------+------------+---------+---------+--------+| 7369 | SMITH | 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 || 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 |+-------+--------+-----------+------+------------+---------+---------+--------+
dept表（部门表）
mysql> select * from dept;+--------+------------+----------+| DEPTNO | DNAME| LOC|+--------+------------+----------+|10 | ACCOUNTING | NEW YORK ||20 | RESEARCH | DALLAS ||30 | SALES| CHICAGO ||40 | OPERATIONS | BOSTON |+--------+------------+----------+
salgrade表（工资等级表）
mysql> select * from salgrade;+-------+-------+-------+| GRADE | LOSAL | HISAL |+-------+-------+-------+|1 | 700 | 1200 ||2 | 1201 | 1400 ||3 | 1401 | 2000 ||4 | 2001 | 3000 ||5 | 3001 | 9999 |+-------+-------+-------+
三、创建测试表的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 );INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); commit;INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); commit;INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit;
