-- 1. 取得每个部门最高薪水的人员名称
SELECT empno'员工号',ename'姓名',deptno'部门',sal '薪水'
FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno)
ORDER BY deptno;
-- 2. 哪些人的薪水在部门平均薪水之上
SELECT empno'员工号',ename'姓名',a.deptno'部门',emp.sal'工资',a.avgsal'平均工资'
FROM emp INNER JOIN(SELECT deptno,TRUNCATE(AVG(sal),2) avgsal
FROM emp
GROUP BY deptno) AS a
ON a.deptno=emp.deptno
WHERE emp.sal>a.avgsal;
-- 3. 取得部门中（所有人的）平均薪水等级
SELECT a.deptno,grade
FROM (SELECT deptno,TRUNCATE(AVG(sal),2) avgsal FROM emp GROUP BY deptno)AS a
INNER JOIN salgrade
WHERE a.avgsal BETWEEN losal AND hisal
-- 4. 不准用组函数（MAX），取得最高薪水
SELECT sal FROM emp ORDER BY sal DESC LIMIT 0,1;
-- 5. 取得平均薪水最高的部门的部门编号
SELECT a.deptno,MAX(a.avgsal)
FROM (SELECT deptno,TRUNCATE(AVG(sal),2) avgsal
FROM emp
GROUP BY deptno) AS a;
-- 6. 取得平均薪水最高的部门的部门名称
SELECT a.deptno,dname
FROM (SELECT deptno,TRUNCATE(AVG(sal),2) avgsal
FROM emp
GROUP BY deptno) AS a
INNER JOIN dept
ON dept.deptno=a.deptno
ORDER BY avgsal DESC
LIMIT 0,1;
-- 7. 求平均薪水的等级最低的部门的部门名称
SELECT a.deptno,dname
FROM salgrade,(SELECT deptno,TRUNCATE(AVG(sal),2) avgsal
FROM emp
GROUP BY deptno) AS a
INNER JOIN dept
ON dept.deptno=a.deptno
WHERE avgsal BETWEEN losal AND hisal
GROUP BY deptno
ORDER BY grade
LIMIT 0,1;
-- 8. 取得比普通员工（员工代码没有在 mgr 上出现的）的最高薪水还要高的经理人姓名
SELECT empno,ename
FROM emp
INNER JOIN (SELECT MAX(sal) maxsal
FROM emp
WHERE empno NOT IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL)) AS a
WHERE job='manager' AND sal>maxsal;
-- 9. 取得薪水最高的前五名员工
SELECT empno,ename,sal FROM emp ORDER BY sal DESC LIMIT 0,5;
-- 10. 取得薪水最高的第六到第十名员工
SELECT empno,ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5;
-- 11. 取得最后入职的 5 名员工
SELECT * FROM emp ORDER BY hiredate DESC LIMIT 0,5;
-- 12. 取得每个薪水等级有多少员工
-- 方法一：太复杂
SELECT
(CASE WHEN sal BETWEEN 700 AND 1200 THEN '1'
WHEN sal BETWEEN 1201 AND 1400 THEN '2'
WHEN sal BETWEEN 1401 AND 2000 THEN '3'
WHEN sal BETWEEN 2001 AND 3000 THEN '4'
WHEN sal BETWEEN 3001 AND 9999 THEN '5'
END) LEVEL,COUNT(*)
FROM emp
GROUP BY
CASE WHEN sal BETWEEN 700 AND 1200 THEN '1'
WHEN sal BETWEEN 1201 AND 1400 THEN '2'
WHEN sal BETWEEN 1401 AND 2000 THEN '3'
WHEN sal BETWEEN 2001 AND 3000 THEN '4'
WHEN sal BETWEEN 3001 AND 9999 THEN '5'
END;
-- 方法二
SELECT grade,COUNT(*)
FROM salgrade,emp
WHERE sal BETWEEN losal AND hisal
GROUP BY grade
-- 13. 列出所有员工及领导的名字
SELECT a.ename'员工名',b.ename '领导名'
FROM emp a
INNER JOIN emp b
WHERE a.mgr = b.empno;
-- 14. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
SELECT a.empno'员工名',a.ename '姓名',dname'部门'
FROM emp a
INNER JOIN emp b
INNER JOIN dept
ON a.mgr = b.empno AND a.deptno = dept.deptno
WHERE a.hiredate<b.hiredate;
-- 15. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dept.*,emp.* FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno;
-- 16. 列出至少有 5 个员工的所有部门
SELECT emp.deptno,dname,COUNT(*)
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
GROUP BY deptno
HAVING COUNT(empno) >= 5;
-- 17. 列出薪水比“SMITH”多的所有员工信息
SELECT * FROM emp,
(SELECT sal FROM emp WHERE ename = 'SMITH') AS a
WHERE emp.sal > a.sal;
-- 18. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
SELECT ename'员工名',dname'所属部门名',countnum'部门人数'
FROM emp
INNER JOIN dept
INNER JOIN (SELECT deptno,COUNT(empno) countnum FROM emp GROUP BY deptno) AS a
ON emp.deptno = dept.deptno AND emp.deptno = a.deptno
WHERE job = 'CLERK';
-- 19. 列出最低薪水大于 1500 的各种工作及从事此工作的全部雇员人数
SELECT job,MIN(sal),COUNT(*) FROM emp GROUP BY job HAVING MIN(sal)>1500;
-- 20. 列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号
SELECT ename FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE dname = 'SALES';
-- 21. 列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
-- 方法一
SELECT empno,deptno,mgr,grade
FROM emp,salgrade,(SELECT TRUNCATE(AVG(sal),2) avgsal FROM emp) AS a
WHERE sal > a.avgsal AND sal BETWEEN losal AND hisal
-- 方法二
SELECT empno,deptno,mgr,sal,
(CASE WHEN sal BETWEEN 700 AND 1200 THEN '1'
WHEN sal BETWEEN 1201 AND 1400 THEN '2'
WHEN sal BETWEEN 1401 AND 2000 THEN '3'
WHEN sal BETWEEN 2001 AND 3000 THEN '4'
WHEN sal BETWEEN 3001 AND 9999 THEN '5'
END) LEVEL
FROM emp,(SELECT AVG(sal) avgsal FROM emp) AS a
WHERE sal > a.avgsal AND
CASE WHEN sal BETWEEN 700 AND 1200 THEN '1'
WHEN sal BETWEEN 1201 AND 1400 THEN '2'
WHEN sal BETWEEN 1401 AND 2000 THEN '3'
WHEN sal BETWEEN 2001 AND 3000 THEN '4'
WHEN sal BETWEEN 3001 AND 9999 THEN '5'
END;
-- 22. 列出与“SCOTT”从事相同工作的所有员工及部门名称
SELECT ename'员工名',dname'部门名称',a.job'工作'
FROM emp
INNER JOIN dept
INNER JOIN(SELECT job FROM emp WHERE ename = 'SCOTT') AS a
ON emp.job = a.job AND emp.deptno = dept.deptno;
-- 23. 列出薪金等于部门 30 中员工的薪金的其它员工的姓名和薪金
SELECT ename,sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30) AND deptno!=30;
-- 24. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称
SELECT ename,sal,dname
FROM (SELECT MAX(sal) maxsal FROM emp WHERE deptno= 30) AS a,
emp INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE emp.sal > a.maxsal
-- 25. 列出在每个部门工作的员工数量、平均工资和平均服务期限
SELECT deptno'部门号',COUNT(*)'部门人数',TRUNCATE(AVG(sal),2)'平均工资',
FLOOR(AVG(TO_DAYS(NOW()) - TO_DAYS(hiredate)))'平均服务期限（以天为单位）'
FROM emp
GROUP BY deptno;
-- 26. 列出所有员工的姓名、部门名称和工资
SELECT ename,dname,sal
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno;
-- 27. 列出所有部门的详细信息和人数
SELECT dept.deptno,dname,loc,COUNT(empno)'部门人数'
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
GROUP BY deptno;
-- 28. 列出各种工作的最低工资及从事此工作的雇员姓名
SELECT
ename,job,sal
FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY job);
-- 29. 列出各个部门 MANAGER 的最低薪金
SELECT deptno,MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno;
-- 30. 列出所有员工的年工资，按年薪从低到高排序
SELECT ename,(sal+IFNULL(comm,0))*12 FROM emp ORDER BY sal*12 ASC;
-- 31. 求出员工领导的薪水超过 3000 的员工名称和领导名称
SELECT DISTINCT a.ename'员工名称',b.ename'领导名称'
FROM emp a
INNER JOIN emp b
ON a.mgr = b.empno
WHERE b.sal > 3000;
-- 32. 求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT SUM(sal)'工资合计',COUNT(*)'部门人数'
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE dname LIKE '%s%'
GROUP BY emp.deptno;
-- 33. 给任职日期超过 30 年的员工加薪 10%
SELECT ename,sal '原始工资',sal*1.1 '加薪后工资',a.worktime '工作时长'
FROM emp
INNER JOIN (SELECT empno,YEAR(NOW()) - YEAR(hiredate) worktime FROM emp ORDER BY empno) AS a
ON emp.empno = a.empno
WHERE a.worktime > 30
ORDER BY emp.empno;
评论1
最新资源