Mysql_习题解答（-应改为--）

– 1. 取得每个部门最高薪水的人员名称
/* 将*改为 emp.ENAME,emp.DEPTNO */
SELECT * FROM emp JOIN
(SELECT MAX(emp.SAL),emp.DEPTNO DEPTNO FROM emp GROUP BY emp.DEPTNO) temp /部门最高薪水/
ON emp.DEPTNO = temp.DEPTNO

– 2. 哪些人的薪水在部门平均薪水之上
SELECT * FROM emp JOIN
(SELECT AVG( emp.SAL) avg,emp.DEPTNO DEPTNO FROM emp GROUP BY emp.DEPTNO) temp /平均薪水/
ON emp.SAL > temp.avg and emp.DEPTNO = temp.DEPTNO

– 3. 取得部门中（所有人的）平均薪水等级
SELECT * FROM salgrade JOIN
(SELECT AVG( emp.SAL) avg,emp.DEPTNO DEPTNO FROM emp GROUP BY emp.DEPTNO) temp /平均薪水/
ON temp.avg between salgrade.LOSAL AND salgrade.HISAL /平均薪水等级/

– 4. 不准用组函数（MAX），取得最高薪水（给出两种方案）
–(1)
SELECT * FROM emp ORDER BY emp.SAL DESC LIMIT 1 /排序/
–(2)

SELECT * FROM emp WHERE emp.SAL NOT IN
(SELECT e1.SAL SAL FROM emp e1 , emp e2 WHERE e1.SAL < e2.SAL) /自连接/

– 5. 取得平均薪水最高的部门的部门编号
SELECT * FROM dept JOIN
(SELECT MAX(emp.SAL) max ,emp.DEPTNO DEPTNO FROM emp ) temp /平均薪水最高/
ON dept.DEPTNO = temp.DEPTNO

– 6. 取得平均薪水最高的部门的名称
/* 将*改为dept.DNAME */
SELECT * FROM dept JOIN
(SELECT MAX(emp.SAL) max ,emp.DEPTNO DEPTNO FROM emp ) temp /平均薪水最高/
ON dept.DEPTNO = temp.DEPTNO

– 7. 求平均薪水的等级最低的部门的部门名称
SELECT * FROM dept INNER JOIN
(SELECT MIN(temp.avg),temp.deptno num FROM (SELECT AVG( emp.SAL) avg ,emp.DEPTNO deptno FROM emp GROUP BY emp.DEPTNO) temp )temp1
ON dept.DEPTNO = temp1.num

– 8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
SELECT * FROM emp JOIN
(SELECT max(emp.SAL) max1 FROM emp WHERE emp.EMPNO NOT IN (SELECT DISTINCT emp.MGR FROM emp WHERE emp.MGR IS NOT NULL)) temp
ON emp.SAL > temp.max1

– 9. 取得薪水最高的前五名员工
SELECT * FROM emp ORDER BY emp.SAL DESC LIMIT 5

– 10. 取得薪水最高的第六到第十的员工
SELECT * FROM emp ORDER BY emp.SAL DESC LIMIT 6,5

– 11. 取得最后入职的5名员工
SELECT * FROM emp ORDER BY emp.HIREDATE DESC LIMIT 5

– 12. 取得每个薪水等级有多少员工
SELECT COUNT(temp.GRADE) count ,GRADE FROM
(SELECT salgrade.GRADE FROM salgrade JOIN emp ON emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL) temp GROUP BY GRADE

– 13.1 找出没选过“黎明”老师的所有学生姓名
SELECT s.sname FROM s WHERE s.sno NOT IN
(SELECT sc.sno sno FROM sc WHERE sc.cno = 5)

– 13.2 列出2门以上（含两门）不及格学生姓名及平均成绩
SELECT sname,AVG( scgrade) FROM s,sc ,
(SELECT COUNT(sno) count FROM sc WHERE sc.scgrade < 60 GROUP BY sno) temp1 WHERE temp1.count >=2 AND s.sno = sc.sno

– 13.3 即学过1号课程又学过2号课程所有学生的姓名
SELECT sname FROM s ,
(SELECT DISTINCT sno FROM sc WHERE cno = 1 OR cno = 2)tep
WHERE tep.sno = s.sno

– 14. 列出所有员工及领导的名字
SELECT a.ENAME,(SELECT ENAME FROM emp b WHERE a.MGR = b.EMPNO)MGR FROM emp a
– SELECT b.ENAME FROM emp a,emp b WHERE a.MGR = b.EMPNO

– 15. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
SELECT DISTINCT EMPNO,emp.ENAME,DEPTNO FROM emp JOIN
(SELECT b.ENAME,b.HIREDATE HIREDATE FROM emp a,emp b WHERE a.MGR = b.EMPNO) c
ON emp.HIREDATE < c.HIREDATE

– 16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT DNAME ,emp1.ENAME FROM dept LEFT JOIN
(SELECT DEPTNO,GROUP_CONCAT(ENAME) ENAME FROM emp GROUP BY emp.DEPTNO) emp1
ON dept.DEPTNO = emp1.DEPTNO

– 17. 列出至少有5个员工的所有部门
SELECT DNAME ,tep1.count FROM dept ,
(SELECT COUNT(DEPTNO) count,DEPTNO FROM emp GROUP BY DEPTNO) tep1
WHERE tep1.count >= 5 AND dept.DEPTNO = tep1.DEPTNO

– 18. 列出薪水比’SMITH’多的所有员工信息
SELECT * FROM emp ,
(SELECT SAL AS SMITHS FROM emp WHERE ENAME = ‘SMITH’) tep /’SMITH’的薪水/
WHERE tep.SMITHS < emp.SAL

– 19. 列出所有’CLERK’（办事员）的姓名及其部门名称，部门人数

SELECT tep.ENAME,tep.JOB,DNAME,tep1.count from dept,
(SELECT * FROM emp WHERE JOB = ‘CLERK’)tep, /’CLERK’（办事员）/
(SELECT COUNT(DEPTNO) count,DEPTNO FROM emp GROUP BY DEPTNO) tep1 /部门人数/
WHERE dept.DEPTNO = tep.DEPTNO AND tep.DEPTNO = tep1.DEPTNO

– 20. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数 ,求出每种工作岗位的最低薪水
SELECT tep.JOB,tep1.count,tep.mSAL FROM
(SELECT JOB,MIN(SAL) mSAL FROM emp GROUP BY JOB)tep , /每种工作岗位的最低薪水/
(SELECT JOB,COUNT(JOB) count FROM emp GROUP BY JOB)tep1 /事此工作的全部雇员人数/
WHERE tep.mSAL > 1500 AND tep.JOB = tep1.JOB

– 21. 列出在部门’SALES’<销售部>工作的员工姓名，假定不知道销售部门的部门编号

SELECT ENAME,JOB FROM emp WHERE JOB = ‘SALESMAN’ /工作来区分/

– 22. 列出薪金高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级

SELECT tep2.ENAME,tep2.MGR,tep1.DEPTNO,tep1.GRADE ,emp.SAL,tep.avg FROM emp,
(SELECT AVG( SAL) avg FROM emp) tep, /平均薪水/
(SELECT DEPTNO,ENAME,GRADE FROM salgrade,emp WHERE emp.SAL BETWEEN LOSAL AND HISAL) tep1 , /工作等级/
(SELECT a.ENAME,(SELECT ENAME FROM emp b WHERE a.MGR = b.EMPNO)MGR FROM emp a) tep2 /员工及上级领导/
WHERE emp.SAL > tep.avg AND tep1.ENAME = tep2.ENAME AND emp.DEPTNO = tep1.DEPTNO AND tep2.ENAME = emp.ENAME

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

SELECT ENAME ,JOB ,DNAME FROM dept,
(SELECT * FROM emp WHERE JOB = ‘ANALYST’)tep /与’SCOTT’从事相同工作/
WHERE tep.DEPTNO = dept.DEPTNO

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

SELECT ENAME ,SAL FROM
(SELECT * FROM emp WHERE DEPTNO <> 30) tep /其他员工/
WHERE tep.SAL IN (SELECT DISTINCT SAL FROM emp WHERE DEPTNO = 30)
– (SELECT DISTINCT SAL FROM emp WHERE DEPTNO = 30 )tep /部门30中员工的薪金/

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

SELECT ENAME ,SAL ,DNAME FROM dept,
(SELECT * FROM emp WHERE DEPTNO <> 30) tep /其他员工/
WHERE tep.SAL > (SELECT MAX(SAL) FROM emp WHERE DEPTNO = 30) AND dept.DEPTNO = tep.DEPTNO

/* 26. 列出在每个部门工作的员工数量、平均工资和平均服务期限
to_days(日期类型) ->获取天数
获取数据库的系统当前时间的函数是：now()
*/
SELECT tep.DEPTNO,tep.avgSAL,tep.count,tep2.avgDays FROM
(SELECT DEPTNO,AVG( SAL) avgSAL,COUNT(DEPTNO) count FROM emp GROUP BY DEPTNO) tep, /每个部门工作的员工数量/
– (SELECT (TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) days, DEPTNO FROM emp) /获得天数/
(SELECT AVG( days) avgDays,tep1.DEPTNO DEPTNO FROM (SELECT (TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) days, DEPTNO FROM emp) tep1 GROUP BY tep1.DEPTNO) tep2 /获得平均天数/
WHERE tep.DEPTNO = tep2.DEPTNO

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

SELECT ENAME ,DNAME ,SAL FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO

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

SELECT dept.DEPTNO,DNAME,LOC,count FROM dept LEFT JOIN
(SELECT DEPTNO,COUNT(DEPTNO) count FROM emp GROUP BY DEPTNO) tep /每个部门工作的员工数量/
ON dept.DEPTNO = tep.DEPTNO

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

SELECT ENAME,MIN(SAL) ,JOB FROM emp GROUP BY JOB

– 30. 列出各个部门MANAGER的最低薪金

SELECT MIN(SAL) FROM emp WHERE JOB = ‘MANAGER’ GROUP BY DEPTNO

– 31. 列出所有员工的年工资，按年薪从低到高排序

SELECT SAL*12 SALS FROM emp ORDER BY SALS DESC

– 32. 求出员工领导的薪水超过3000的员工名称和领导名称

SELECT tep.ename ename,tep.mgr mgr,sal FROM emp,
(SELECT a.ENAME ENAME,(SELECT ENAME FROM emp b WHERE a.MGR = b.EMPNO)MGR FROM emp a) tep
WHERE emp.sal > 3000 AND emp.ename = tep.mgr /员工，领导，领导工资/

– 33. 求部门名称中带’S’字符的部门员工的工资合计、部门人数

SELECT tep1.dname,tep.sum,count FROM
(SELECT * FROM dept WHERE dname LIKE ‘%S%’) tep1 LEFT JOIN /部门名称中带’S’字符的部门/
(SELECT deptno,SUM(sal) sum,COUNT(deptno) count FROM emp GROUP BY deptno )tep /部门员工的工资合计、部门人数/
ON tep.deptno = tep1.deptno

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

– (SELECT (TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) days, DEPTNO FROM emp) /获得天数/
SELECT emp.ename,tep.days,sal*1.1 sals FROM emp ,
((SELECT (TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) days, ename FROM emp) ) tep /获得天数/
WHERE tep.days/365 >30 AND emp.ename = tep.ename