关于oracle样例数据库emp、dept、salgrade的mysql脚本复杂查询分析-白红宇

关于oracle样例数据库emp、dept、salgrade的mysql脚本复杂查询分析

阅读量：4691 次

发布时间：2019-06-09

本文共 8879 字，大约阅读时间需要 29 分钟。

大家可以自行网上找资源（网上资源比较多，不建议下载我的），也可以在我：

1.取得每个部门最高薪水的人员名称：正确一共有4个单位，要进行左外连接其中一个单位没得员工

SELECT dept.dname, emp.ename, emp.salFROM empRIGHT JOIN dept ON emp.deptno = dept.deptnoRIGHT JOIN (	SELECT dept.dname, MAX(emp.sal) AS 'max_sal'	FROM emp	RIGHT JOIN dept ON emp.deptno = dept.deptno	GROUP BY dept.dname)AS ttON tt.dname = dept.dname AND tt.max_sal = emp.salGROUP BY dname;

2.哪些人的薪水在部门平均薪水之上：不确定

SELECT dept.dname, emp.ename, emp.salFROM empINNER JOIN dept ON emp.deptno = dept.deptnoINNER JOIN (	SELECT dept.dname, AVG(emp.sal) AS 'avg_sal'	FROM emp	INNER JOIN dept ON emp.deptno = dept.deptno	GROUP BY dname)AS ttON tt.dname = dept.dname AND tt.avg_sal <= emp.sal;

3.取得部门中所有人的平均薪水等级

SELECT tt.dname, salgrade.gradeFROM (	SELECT dept.dname, AVG(emp.sal) AS 'avg_sal'	FROM emp	RIGHT JOIN dept ON emp.deptno = dept.deptno	GROUP BY dname) AS tt, salgradeWHERE tt.avg_sal >= salgrade.losal AND tt.avg_sal <= hisal;

4.不准使用组函数（MAX），取得最高薪水

SELECT emp.ename, emp.salFROM empORDER BY sal DESC -- 由高到底， asc 由低到高LIMIT 0, 1;

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

SELECT deptnoFROM (	SELECT dept.deptno, AVG(emp.sal) AS 'avg_sal'	FROM emp	INNER JOIN dept ON emp.deptno = dept.deptno	GROUP BY dname	ORDER BY avg_sal DESC	LIMIT 0, 1)AS tt;

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

SELECT dnameFROM (	SELECT dept.dname, AVG(emp.sal) AS 'avg_sal'	FROM emp	INNER JOIN dept ON emp.deptno = dept.deptno	GROUP BY dname	ORDER BY avg_sal DESC	LIMIT 0, 1)AS tt;

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

SELECT dnameFROM (	SELECT t1.dname , SALGRADE.`GRADE` 	FROM 	(		SELECT dept.dname, AVG(emp.sal) AS 'avg_sal'		FROM emp		INNER JOIN dept ON emp.deptno = dept.deptno		GROUP BY dname	) AS t1	, SALGRADE	WHERE t1.avg_sal >= SALGRADE.`LOSAL` AND t1.avg_sal <= SALGRADE.`HISAL`)AS ttWHERE tt.grade = (	SELECT MIN(SALGRADE.`GRADE`) 	FROM 	(		SELECT dept.dname, AVG(emp.sal) AS 'avg_sal'		FROM emp		INNER JOIN dept ON emp.deptno = dept.deptno		GROUP BY dname	) AS t1	, SALGRADE	WHERE t1.avg_sal >= SALGRADE.`LOSAL` AND t1.avg_sal <= SALGRADE.`HISAL`);

8.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水

SELECT emp.enameFROM empWHERE emp.mgr IS NULL AND sal >(	SELECT MAX(sal) FROM emp WHERE mgr IS NOT NULL);

9.取得薪水最高的5名员工

SELECT emp.`EMPNO` AS '编号', emp.`ENAME` AS '姓名'-- , emp.`SAL` as '薪资'FROM emp ORDER BY emp.`SAL` DESCLIMIT 0, 5;

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

SELECT emp.`EMPNO` AS '编号', emp.`ENAME` AS '姓名'-- , emp.`SAL`FROM empORDER BY emp.`SAL` DESCLIMIT 5, 5;

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

SELECT tt.no AS '编号', tt.name AS '姓名'-- , tt.daysFROM (	SELECT emp.`EMPNO` AS 'no', emp.`ENAME` AS 'name', TO_DAYS(HIREDATE) AS 'days'	FROM emp) AS ttORDER BY days DESCLIMIT 0, 5;

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

SELECT salgrade.`GRADE` AS '薪资等级', COUNT(tt.`EMPNO`) AS '员工数'FROM salgrade, (	SELECT emp.`EMPNO`, emp.`SAL` 	FROM emp) AS ttWHERE tt.`SAL` >= salgrade.`LOSAL` AND tt.`SAL` <= salgrade.`HISAL`GROUP BY salgrade.`GRADE`;

13.列出所有员工及领导的名字

SELECT emp.`ENAME` AS '员工名称', tt.lname AS '领导名称'FROM emp LEFT JOIN (	SELECT emp.`EMPNO`, emp.`ENAME` AS 'lname'	FROM emp 	WHERE empno IN 	(		SELECT mgr FROM emp	) -- 找出所有是领导的员工no) AS ttON emp.`MGR` = tt.empno;

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

SELECT t2.eno AS '编号', t2.ename AS '姓名', dept.`DNAME` AS '部门名称', t2.ehiredate AS '员工受雇日期', t2.lhiredate AS '领导受雇日期'FROM deptINNER JOIN (	SELECT emp.`EMPNO` AS 'eno', emp.`ENAME`, emp.`HIREDATE` AS 'ehiredate', t1.lhiredate, emp.`DEPTNO`	FROM 	emp LEFT JOIN 	(		SELECT emp.`EMPNO` AS 'lno', emp.`HIREDATE` AS 'lhiredate'		FROM emp 		WHERE empno IN 		(			SELECT mgr FROM emp		) -- 找出所有是领导的员工no	) AS t1	ON emp.`MGR` = t1.lno) AS t2ON t2.deptno = dept.`DEPTNO`WHERE t2.ehiredate <  t2.lhiredate OR t2.lhiredate IS NULL;

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

SELECT dept.`DNAME` AS '部门名称', emp.`ENAME` AS '员工名称', emp.`JOB` AS '职位', emp.`HIREDATE` AS '入职时间', emp.`SAL` AS '薪水'FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`

16.列出至少有5个员工的所有部门

SELECT dept.`DNAME` AS '部门名', COUNT(emp.`EMPNO`) AS '员工个数'FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`GROUP BY dept.`DNAME` HAVING COUNT(emp.`EMPNO`) >= 5;

17.列出薪水比“SMITH”多的所有员工信息

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

18.列出所有“CLERK”办事员的姓名及其部门名称，部门人数

SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部门名称', tt.emp_count AS '部门人数'FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`INNER JOIN (	SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count'	FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`	GROUP BY dept.`DEPTNO` ) AS ttON emp.`DEPTNO` = tt.deptnoWHERE emp.`JOB` = 'CLERK';

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

SELECT emp.`JOB` AS '工作名称', COUNT(emp.`EMPNO`) AS '雇员人数'FROM emp WHERE emp.`JOB` IN (	SELECT emp.`JOB`	FROM emp	GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500)GROUP BY emp.`JOB`;

-- 20.列出在部门“SALES”<销售部>工作的员工姓名（假定不知道销售部门的部门编号）

SELECT emp.`ENAME` AS '销售部人员'FROM empWHERE emp.`DEPTNO` = (	SELECT dept.`DEPTNO`	FROM dept	WHERE dept.`DNAME` = 'sales');

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

SELECT t1.ename AS '员工名称', dept.`DNAME` AS '部门名称', t2.ename AS '上级名称', t1.grade AS '工资等级', t1.sal AS '工资'FROM (	SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE`	FROM emp, salgrade	WHERE emp.`SAL` > (		SELECT AVG(sal) AS 'avg_sal'		FROM emp	) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL`) AS t1LEFT JOIN (	SELECT emp.`EMPNO` AS 'mgr', emp.`ENAME` -- 注意这里是查出所有领导的empno，作为mgr编号	FROM emp 	WHERE emp.`EMPNO` IN 	(		SELECT DISTINCT(emp.`MGR`) -- 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积		FROM emp		WHERE mgr IS NOT NULL	)) AS t2ON t1.mgr = t2.mgrLEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;

22.列出“SCOTT”从事相同工作的所有员工及部门名称

SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', dept.`DNAME` AS '部门名称'-- , emp.`DEPTNO`FROM emp, deptWHERE emp.`JOB` = (	SELECT emp.`JOB` -- 查询出SCOTT从事的职业	FROM emp	WHERE emp.`ENAME` = 'SCOTT') AND emp.`ENAME` <> 'SCOTT'AND emp.`DEPTNO` = dept.`DEPTNO`;

23.列出薪水等于部门30中员工的薪水的其它员工的姓名和薪水

SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', emp.`SAL` AS '员工薪水'FROM empWHERE emp.`DEPTNO` <> 30AND emp.`SAL` IN(	SELECT DISTINCT emp.`SAL` -- 查询出部门为30的所有的员工的sal 不重复	FROM emp	WHERE emp.`DEPTNO` = 30);

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

SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', emp.`SAL` AS '员工薪水', dept.`DNAME` AS '部门名称'FROM emp, deptWHERE emp.`DEPTNO` <> 30AND emp.`SAL` >(	SELECT MAX(emp.`SAL`) -- 查询出部门为30的所有的员工的sal 不重复	FROM emp	WHERE emp.`DEPTNO` = 30)AND dept.`DEPTNO` = emp.`DEPTNO`;

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

SELECT dept.`DNAME` AS '部门名称', COUNT(emp.`EMPNO`) AS '员工数量', ROUND(AVG(emp.`SAL`), 2) AS '平均工资', t2.avg_year AS '平均服务年限'FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`LEFT JOIN (	SELECT t1.deptno, ROUND(AVG(t1.year), 0) AS 'avg_year' -- 四舍五入，保留0位小数	FROM 	(		SELECT emp.`DEPTNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'year'		FROM emp	) AS t1	GROUP BY t1.deptno) AS t2 ON dept.`DEPTNO` = t2.deptnoGROUP BY dept.`DEPTNO`;

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

SELECT emp.`ENAME` AS '员工名称', dept.`DNAME` AS '部门名称', emp.`SAL` AS '工资' FROM emp, deptWHERE dept.`DEPTNO` = emp.`DEPTNO`;

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

SELECT dept.`DEPTNO` AS '部门编号', dept.`DNAME` AS '部门名称', dept.`LOC` AS '部门地址', tt.num AS '部门人数'FROM dept LEFT JOIN (	SELECT emp.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'num'	FROM emp	GROUP BY emp.`DEPTNO`) AS ttON dept.`DEPTNO` = tt.`DEPTNO`;

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

SELECT emp.`SAL`, emp.`ENAME`FROM emp, (	SELECT emp.`JOB`, MIN(emp.`SAL`) AS 'min_sal'	FROM emp	GROUP BY emp.`JOB`)AS ttWHERE emp.`JOB` = tt.job AND emp.`SAL` = tt.min_sal;

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

SELECT dept.`DNAME` AS '部门名称', MIN(tt.sal) AS '最低薪资'FROM dept LEFT JOIN (	SELECT emp.`DEPTNO`, emp.`SAL`	FROM emp	WHERE emp.`JOB` = 'manager') AS tt ON dept.`DEPTNO` = tt.deptno GROUP BY dept.`DEPTNO`;

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

SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', (emp.`SAL`*12) AS '年薪' FROM empORDER BY 年薪 ASC;

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

SELECT tt.ename AS '领导名称', emp.`ENAME` AS '雇员名称' FROM emp RIGHT JOIN (	SELECT emp.`EMPNO`, emp.`ENAME`	FROM emp	WHERE emp.`EMPNO` IN	(		SELECT DISTINCT emp.`MGR`		FROM emp		WHERE emp.`MGR` IS NOT NULL	) AND emp.`SAL` >= 3000) AS ttON emp.`MGR` = tt.empno

32. 求部门名称中带“S”字符的部门员工的工资合计、部门人数

SELECT tt.dname AS '部门名称', SUM(emp.`SAL`) AS '工资合计', COUNT(emp.`EMPNO`) AS '部门人数'FROM emp RIGHT JOIN (	SELECT dept.`DEPTNO`, dept.`DNAME`	FROM dept	WHERE dept.`DNAME` LIKE '%s%') AS tt ON emp.`DEPTNO` = tt.deptnoGROUP BY emp.`DEPTNO`;

32+.查询名字中有s的员工的工资总和（按照部门进行分组）

SELECT dept.`DNAME` AS '部门名称', SUM(tt.`SAL`) AS '工资'FROM dept LEFT JOIN 	(	SELECT emp.`EMPNO`, emp.`ENAME`, emp.`SAL`, emp.`DEPTNO`	FROM emp	WHERE emp.`ENAME` LIKE '%s%') AS ttON dept.`DEPTNO` = tt.deptnoGROUP BY dept.`DEPTNO`;

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

UPDATE emp SET emp.`SAL` = emp.`SAL` + emp.`SAL`*10WHERE emp.`EMPNO` IN(		SELECT tt.`EMPNO` 	FROM (		SELECT emp.`EMPNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'work_year'		FROM emp	) AS tt	WHERE tt.work_year > 30);