杜老师SQL面试题

1、取得每个部门最高薪水的人员名称

SELECT 
	empno,ename,MAX(sal) AS maxsal,deptno
FROM 
	emp 
GROUP BY 
	deptno

结果中maxsal=5000的员工可能有多个

select
	emp.ename
from
	emp
join 
	(select deptno,max(sal) as maxsal from emp group by deptno) a
on
	a.deptno = emp.deptno and a.maxsal = emp.sal

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

SELECT
	emp.ename,emp.sal
FROM
	emp
JOIN 
	(SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno) a
ON 
	a.deptno = emp.deptno AND emp.sal > a.avgsal

3、取得每个部门中薪水等级的平均值

SELECT
	deptno,AVG(grade)
FROM
	emp
JOIN
	salgrade sg
ON
	emp.sal BETWEEN sg.losal AND sg.hisal
GROUP BY 
	emp.deptno

4、不用组函数（Max），取得最高薪水

SELECT
	ename, sal
FROM
	emp
ORDER BY 
	sal DESC
LIMIT 1

5、取得平均薪水最高的部门的部门编号

SELECT 
	deptno,AVG(sal) AS avgsal 
FROM 
	emp 
GROUP BY 
	deptno
ORDER BY 
	avgsal DESC
LIMIT
	1

//或者
SELECT
	MAX(a.avgsal),deptno
FROM
	(SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno) a

6、取得平均薪水最高的部门的部门名称

SELECT
	dept.DNAME
FROM
	dept
JOIN
	(SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1) a
ON
	dept.DEPTNO = a.DEPTNO

7、求平均薪水的等级最低的部门的部门名称

where中不能用分组函数找最小等级min(grade)，等级最低的部门可能有多个，所以不能升序取第一个（可能升序第二个也是最低等级）

平均薪水的最低等级不是等级表中的最低等级，平均薪水的最低等级通过最低平均薪水在等级表中找

SELECT
	DNAME
FROM
	dept
WHERE
	DEPTNO IN (SELECT
								DEPTNO
							FROM
								salgrade
							JOIN
								(SELECT
										AVG(SAL) AS avgsal,DEPTNO
									FROM
										emp
									GROUP BY
										DEPTNO) a
							ON
								a.avgsal BETWEEN salgrade.LOSAL AND salgrade.HISAL
							WHERE
								salgrade.GRADE = (SELECT
																		GRADE
																	FROM
																		salgrade
																	JOIN
																		(SELECT 
																				AVG(SAL) AS avgsal 
																			FROM 
																				emp 
																			GROUP BY 
																				DEPTNO 
																			ORDER BY 
																				avgsal 
																			LIMIT 1) b
																	ON 
																		b.avgsal BETWEEN salgrade.LOSAL AND salgrade.HISAL))

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

SELECT
	EMPNO,ENAME
FROM
	emp
WHERE
	EMPNO IN (SELECT
							DISTINCT mgr
						FROM
							emp
						WHERE	
							mgr IS NOT NULL)
	AND
		SAL >	(SELECT 
							SAL
						FROM
							emp
						WHERE
							EMPNO NOT IN (SELECT
															DISTINCT mgr
														FROM
															emp
														WHERE	
															mgr IS NOT NULL)
						ORDER BY
							SAL DESC
						LIMIT 1)

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

SELECT
	ENAME,SAL
FROM
	emp
ORDER BY
	SAL DESC
LIMIT 5

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

SELECT
	ENAME
FROM
	emp
ORDER BY	
	SAL
LIMIT 5, 5

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

==日期排序，降序：从现在到过去；升序：从过去到现在

SELECT
	ENAME,HIREDATE
FROM
	emp
ORDER BY
	HIREDATE DESC
LIMIT 5

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

SELECT
	salgrade.GRADE,COUNT(emp.ENAME)
FROM
	emp
JOIN
	salgrade
ON
	emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL
GROUP BY
	salgrade.GRADE

14、列出所有员工及其领导的姓名

表的自连接

SELECT
	a.ENAME '员工名称',CASE WHEN b.ENAME IS NULL THEN '没有领导' ELSE b.ENAME END '领导名称'
FROM
	emp a
LEFT JOIN
	emp b
ON
	a.MGR = b.EMPNO

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

SELECT
	c.EMPNO,c.ENAME,dept.DNAME
FROM
	dept
JOIN
	( SELECT
			a.ENAME,a.DEPTNO,a.EMPNO
		FROM
			emp a
		left JOIN
			emp b
		ON
			a.MGR = b.EMPNO
		WHERE
			a.HIREDATE < b.HIREDATE) c
ON
	dept.DEPTNO = c.DEPTNO

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

为什么group by后，每组只有一条数据

SELECT
	DEPTNO
FROM
	emp
GROUP BY
	deptno

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

SELECT
	DNAME
FROM
	dept
JOIN
	emp
ON
	emp.DEPTNO = dept.DEPTNO
GROUP BY
	DNAME
HAVING
	COUNT(ENAME) >= 5

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

SELECT
	*
FROM
	emp
WHERE
	SAL > (SELECT
					SAL
				FROM
					emp
				WHERE
					ENAME = 'SMITH'
				)

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

SELECT
	a.DNAME,a.depename,ENAME,JOB
FROM
	emp
JOIN
	( SELECT
			COUNT(emp.ENAME) depename, dept.DNAME ,dept.DEPTNO
		FROM
			emp
		LEFT JOIN
			dept
		ON
			dept.DEPTNO = emp.DEPTNO
		GROUP BY
			dept.DNAME) a
ON
	a.DEPTNO = emp.DEPTNO
WHERE
	JOB = 'clerk'

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.

SELECT
	COUNT(ENAME),JOB
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
	emp.ENAME,dept.DNAME,e.EMPNO 'leader',salgrade.GRADE
FROM
	emp
JOIN
	dept
ON
	dept.DEPTNO = emp.DEPTNO
JOIN
	emp e
ON
	emp.MGR = e.EMPNO
JOIN
	salgrade
ON
	emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL
WHERE
	emp.SAL > (SELECT AVG(SAL) FROM emp)

23、列出与"SCOTT"从事相同工作的所有员工及部门名称.

SELECT
	emp.ENAME,dept.DNAME
FROM
	emp
JOIN
	dept
ON
	dept.DEPTNO = emp.DEPTNO
WHERE
	JOB =(SELECT JOB FROM emp WHERE ENAME = 'SCOTT') 
AND
	ENAME != 'SCOTT'

24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

SELECT
	emp.ENAME
FROM
	emp
WHERE 
	SAL IN (SELECT DISTINCT SAL FROM emp WHERE DEPTNO = 30)
AND
	DEPTNO <> 30

25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.

SELECT
	emp.ENAME,emp.SAL,dept.DNAME
FROM
	emp
JOIN
	dept
ON
	dept.DEPTNO = emp.DEPTNO
WHERE
	SAL > (SELECT MAX(SAL) FROM emp WHERE DEPTNO = 30)

26、列出在每个部门工作的员工数量,平均工资和平均服务期限.

SELECT
	DNAME,COUNT(emp.ENAME),IFNULL(AVG(emp.SAL),0),IFNULL(AVG(TIMESTAMPDIFF(YEAR,emp.HIREDATE,NOW())), 0)
FROM
	emp
RIGHT JOIN
	dept
ON
	dept.DEPTNO = emp.DEPTNO
GROUP BY
	emp.DEPTNO

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

SELECT
	emp.ENAME,emp.SAL,dept.DNAME
FROM
	emp
LEFT JOIN
	dept
ON
	dept.DEPTNO = emp.DEPTNO

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

SELECT
	dept.*,COUNT(emp.ENAME)
FROM
	dept
LEFT JOIN
	emp
ON
	emp.DEPTNO = dept.DEPTNO
GROUP BY
	dept.DEPTNO,dept.DNAME,dept.LOC

29、列出各种工作的最低工资及从事此工作的雇员姓名

SELECT
	a.JOB,a.minsal,emp.ENAME
FROM
	emp
JOIN
	(SELECT JOB,MIN(SAL) minsal FROM emp GROUP BY JOB) a
ON
	a.minsal = emp.SAL AND a.JOB = emp.JOB

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

SELECT
	DEPTNO,MIN(SAL)
FROM
	emp
WHERE
	JOB = 'MANAGER'
GROUP BY
	DEPTNO

31、列出所有员工的年工资,按年薪从低到高排序

SELECT
	ENAME, (SAL + IFNULL(COMM,0))*12 yearsal
FROM
	emp
ORDER BY yearsal ASC

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

本题用表的自连接，emp ‘员工表’，emp ‘领导表’，将员工表中的‘领导’字段（编号）替换为与领导表中员工编号相同的人

SELECT
	a.ENAME, b.ENAME
FROM
	emp a
JOIN
	emp b
ON 
	a.MGR = b.EMPNO
WHERE
	b.SAL >	3000

33、求出各个部门中,带’S’字符的部门员工的工资合计、部门总人数.

多看看，先用where筛选出符合条件的员工

SELECT
	SUM(emp.SAL),a.cnt
FROM
	emp
RIGHT JOIN 
	(SELECT DEPTNO,COUNT(ENAME)  cnt FROM emp GROUP BY DEPTNO) a
ON
	a.DEPTNO = emp.DEPTNO
WHERE
	emp.ENAME LIKE '%S%'
GROUP BY
	emp.DEPTNO

34、给任职日期超过30年的员工加薪10%.

UPDATE emp SET SAL = SAL*1.1 WHERE TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) > 30