老杜MySQL入门34道题答案
1、取得每个部门最高薪水的人员名称
SELECT ENAME,SAL,T1.DEPTNO
FROM(SELECT MAX(SAL) MAXSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN EMP T2
ON T1.DEPTNO=T2.DEPTNO AND T1.MAXSAL=T2.SAL;
2、哪些人的薪水在部门的平均薪水之上
SELECT ENAME,SAL,AVGSAL,T1.DEPTNO
FROM (SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN EMP T2
ON T1.DEPTNO=T2.DEPTNO
WHERE SAL>AVGSAL;
3、取得部门中（所有人的）平均薪水的等级
SELECT DEPTNO,AVGSAL,GRADE
FROM (SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN SALGRADE T2
ON T1.AVGSAL BETWEEN T2.LOSAL AND T2.HISAL;
4、不准用组函数（Max），取得最高薪水
//降序，limit 1
SELECT SAL
FROM EMP
ORDER BY SAL DESC
LIMIT 1;
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
SELECT MAX(AVGSAL),DEPTNO
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1;
SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;
6、取得平均薪水最高的部门的部门名称
SELECT T1.DEPTNO,MAX(AVGSAL),DNAME
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO;
SELECT T1.DEPTNO,AVGSAL,DNAME
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;
7、求平均薪水的等级最低的部门的部门名称
//先求出部门平均薪资最低的 等级 --3
SELECT GRADE
FROM SALGRADE
WHERE(SELECT AVG(SAL) AVGSAL FROM EMP
GROUP BY DEPTNO ORDER BY AVGSAL LIMIT 1)
BETWEEN LOSAL AND HISAL;
//1
//再求出每个部门平均薪资的等级=3的部门是哪些
//最后用部门编号关联得出部门名称
SELECT T1.DEPTNO,DNAME
FROM(SELECT DEPTNO,AVGSAL,GRADE
FROM (SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN SALGRADE T2
ON T1.AVGSAL BETWEEN T2.LOSAL AND T2.HISAL
WHERE GRADE=(SELECT GRADE FROM SALGRADE
WHERE(SELECT AVG(SAL) AVGSAL FROM EMP
GROUP BY DEPTNO ORDER BY AVGSAL LIMIT 1)
BETWEEN LOSAL AND HISAL)) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO;
//2
SELECT DNAME,GRADE
FROM (SELECT DNAME,AVG(SAL) AVGSAL,D.DEPTNO FROM EMP E
JOIN DEPT D ON E.DEPTNO=D.DEPTNO GROUP BY DEPTNO)T1
JOIN SALGRADE S
ON T1.AVGSAL BETWEEN S.LOSAL AND S.HISAL
WHERE GRADE=(SELECT GRADE FROM SALGRADE
WHERE (SELECT AVG(SAL) AVGSAL FROM EMP
GROUP BY DEPTNO ORDER BY AVGSAL 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 0,5;
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 0,5;
12、取得每个薪水等级有多少员工
SELECT GRADE,COUNT(GRADE)
FROM EMP T1
JOIN SALGRADE T2
ON T1.SAL BETWEEN T2.LOSAL AND T2.HISAL
GROUP BY GRADE;
14、列出所有员工及领导的姓名
SELECT T1.ENAME'员工',T2.ENAME'领导'
FROM EMP T1
LEFT JOIN EMP T2
ON T1.MGR=T2.EMPNO;
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
SELECT T1.EMPNO,T1.ENAME,DNAME
FROM EMP T1
LEFT JOIN EMP T2
ON T1.MGR=T2.EMPNO
JOIN DEPT T3
ON T1.DEPTNO=T3.DEPTNO
WHERE T1.HIREDATE<T2.HIREDATE;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SELECT DNAME,T2.*
FROM DEPT T1
LEFT JOIN EMP T2
ON T1.DEPTNO=T2.DEPTNO;
17、列出至少有 5 个员工的所有部门
SELECT T1.DEPTNO,DNAME,T1.COUNTS
FROM(SELECT DEPTNO,COUNT(*) COUNTS
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=5) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO;
18、列出薪金比"SMITH"多的所有员工信息
SELECT * FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
SELECT ENAME,DNAME,COUNTS
FROM(SELECT ENAME,DEPTNO FROM EMP
WHERE JOB='CLERK') T1
JOIN (SELECT DEPTNO,COUNT(*) COUNTS FROM EMP GROUP BY DEPTNO) T2
ON T1.DEPTNO=T2.DEPTNO
JOIN DEPT T3
ON T3.DEPTNO=T2.DEPTNO;
SELECT T3.*,T4.COUNTS
FROM(SELECT ENAME,DNAME,T1.DEPTNO
FROM EMP T1 JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
WHERE JOB='CLERK') T3
JOIN (SELECT DEPTNO,COUNT(*) COUNTS FROM EMP GROUP BY DEPTNO) T4
ON T3.DEPTNO=T4.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 T1.ENAME '员工' ,T2.DNAME,T3.ENAME '领导',T4.GRADE
FROM EMP T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
LEFT JOIN EMP T3
ON T1.MGR=T3.EMPNO
JOIN SALGRADE T4
ON T1.SAL BETWEEN T4.LOSAL AND T4.HISAL
WHERE T1.SAL>(SELECT AVG(SAL) FROM EMP);
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
SELECT ENAME,DNAME
FROM EMP T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT')
AND ENAME<>'SCOTT';
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
SELECT ENAME,SAL
FROM (SELECT * FROM EMP WHERE DEPTNO NOT IN(30)) T1
WHERE SAL IN(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30);
SELECT ENAME,SAL
FROM EMP
WHERE SAL IN(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30)
AND DEPTNO<>30;
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
SELECT ENAME,SAL,ENAME
FROM EMP T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);
26、列出在每个部门工作的员工数量,平均工资和平均服务期限
SELECT T2.DEPTNO,COUNT(ENAME) COUNTS,IFNULL(AVG(SAL),0) AVGSAL,
IFNULL(AVG(TIMESTAMPDIFF(YEAR,HIREDATE,CURDATE())),0) AVGTIME
FROM EMP T1
RIGHT JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
GROUP BY T2.DEPTNO;
27、列出所有员工的姓名、部门名称和工资
SELECT ENAME,DNAME,SAL
FROM EMP T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO;
28、列出所有部门的详细信息和人数
SELECT T2.*,COUNT(ENAME) COUNTS
FROM EMP T1
RIGHT JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
GROUP BY DEPTNO;
29、列出各种工作的最低工资及从事此工作的雇员姓名
SELECT T1.*,T2.ENAME
FROM(SELECT JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T1
JOIN EMP T2
ON T1.JOB=T2.JOB AND T1.MINSAL=T2.SAL;
30、列出各个部门的 MANAGER(领导)的最低薪金
SELECT DEPTNO,ENAME,MIN(SAL)
FROM EMP
WHERE JOB='MANAGER'
GROUP BY DEPTNO;
31、列出所有员工的年工资,按年薪从低到高排序
SELECT ENAME,(SAL+IFNULL(COMM,0))*12 YEARSAL
FROM EMP
ORDER BY YEARSAL;
32、求出员工领导的薪水超过 3000 的员工名称与领导名称
SELECT T1.ENAME '员工',T2.ENAME '领导'
FROM EMP T1
JOIN EMP T2
ON T1.MGR=T2.EMPNO
WHERE T2.SAL>3000;
33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
SELECT T2.DEPTNO,DNAME,IFNULL(SUM(SAL),0) SUMSAL,COUNT(T1.ENAME) COUNTS
FROM EMP T1
RIGHT JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
WHERE DNAME LIKE '%S%'
GROUP BY T2.DEPTNO,T2.DNAME,T2.LOC;
34、给任职日期超过 30 年的员工加薪 10%.
UPDATE EMP SET SAL=SAL*1.1
WHERE TIMESTAMPDIFF(YEAR,HIREDATE,NOW())>30;