2023_10_10_MYSQL_DAY_02_课后题
#06章1--7题
#1. 查询10号部门所有员工的员工姓名，员工工资，工资级别。
SELECT e.ename 员工姓名,e.`sal` 员工工资,s.`grade`工资级别
FROM emp e ,salgrade s
WHERE e.`deptno`=10
AND e.`sal` BETWEEN s.`losal` AND s.`hisal` ;
#2. 查询20号部门员工的工资级别在3级以上（包括3级）员工姓名，员工工资，工资级别。
SELECT e.ename 员工姓名,e.`sal` 员工工资,s.`grade`工资级别
FROM emp e ,salgrade s
WHERE e.`deptno`=20
AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
AND s.`grade` >=3;
#3. 使用SQL99标准语法实现：查询20号部门员工的工资级别在3级以上（包括3级）员工姓名，员工工资，工资级别。
SELECT e.ename 员工姓名,e.`sal` 员工工资,s.`grade`工资级别
FROM emp e LEFT OUTER JOIN salgrade s ON
( e.`deptno`=20
AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
AND s.`grade` >=3);
#4. 显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
SELECT e.ename 员工姓名,d.dname 部门名称,e.`sal` 员工工资,s.`grade`工资级别
FROM emp e ,dept d,salgrade s
WHERE e.`deptno`=d.`deptno`
AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
AND s.`grade` >4;
#5. 显示员工KING和FORD管理的下属员工的员工姓名。
SELECT e.ename 员工姓名,e2.`ename` 领导姓名
FROM emp e ,emp e2
WHERE e.`mgr`=e2.`empno`
AND( e2.`ename`= 'KING' OR e2.`ename`= 'FORD');
#6. 显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。（选做）
SELECT e.ename 员工姓名,e.`hiredate` 参加工作时间,e2.`ename` 经理名,e2.`hiredate` 经理参加工作时间
FROM emp e ,emp e2
WHERE e.`mgr`=e2.`empno`
AND e.`hiredate`< e2.`hiredate`;
#7. 查询20号部门和30号部门的员工编号，员工姓名，部门编号，要求使用联合查询。（选做）
SELECT e.empno 员工编号,e.`ename` 员工姓名 ,e.`deptno` 部门编号
FROM emp e LEFT OUTER JOIN emp e2 ON(e.`deptno`=20 AND e2.`deptno`=30)
UNION
SELECT e.empno 员工编号,e.`ename` 员工姓名 ,e.`deptno` 部门编号
FROM emp e RIGHT OUTER JOIN emp e2 ON(e.`deptno`=20 AND e2.`deptno`=30);
#07章1--6题
#1. 查询各部门工资在2500元以上的员工的平均工资及部门编号。
SELECT e.`deptno` 部门编号,AVG(e.sal) 平均工资
FROM emp e
WHERE e.`sal`>2500
GROUP BY e.deptno ;
#2. 查询部门平均工资在2500元以上的部门编号及平均工资。
SELECT e.`deptno` 部门编号,AVG(e.sal) 平均工资
FROM emp e
GROUP BY e.deptno
HAVING AVG(e.sal) >2500;
#3. 查询部门平均工资在2500元以上的部门名称及平均工资。
SELECT d.`dname` 部门名称,AVG(e.sal) 平均工资
FROM emp e ,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY e.deptno
HAVING AVG(e.sal) >2500;
#4. 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
SELECT e.`job` 员工岗位,AVG(e.sal) 平均工资
FROM emp e
WHERE e.`job` NOT LIKE "SA%"
GROUP BY e.job
HAVING AVG(e.sal) >2500
ORDER BY AVG(e.sal) DESC;
#5. 查询部门人数在2人以上的部门名称、最低工资、最高工资。
SELECT e.`job` 员工岗位,AVG(e.sal) 平均工资
FROM emp e
WHERE e.`job` NOT LIKE "SA%"
GROUP BY e.job
HAVING AVG(e.sal) >2500
ORDER BY AVG(e.sal) DESC;
#6. 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
SELECT e.`job` 员工岗位,SUM(e.`sal`) 工资和
FROM emp e
WHERE e.`job` <> "SALESMAN"
GROUP BY e.job
HAVING SUM(e.`sal`) >=2500;
#08章1--2题
#1. 查询员工表中第3页员工信息（每页显示4条记录）。
SELECT *
FROM emp
LIMIT 8,4;
#2. 查询工资最高的前4名员工的员工编号，员工姓名，员工工资。（选做）
SELECT e.empno 员工编号,e.`ename` 员工姓名,e.`sal`
FROM emp e
ORDER BY e.`sal` DESC
LIMIT 0,4;
#09章1--6题
#1. 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
SELECT empno 员工的编号,ename 姓名,sal 工资
FROM emp
WHERE sal>(SELECT sal
FROM emp
WHERE empno = 7782
)
AND job=(SELECT job
FROM emp
WHERE empno = 7369
)
AND empno <> 7369;
#2. 查询工资最高的员工姓名和工资。
SELECT ename 员工姓名 ,sal 工资
FROM emp
WHERE sal = (
SELECT MAX(sal)
FROM emp)
#3. 查询高于20号部门最低工资的员工的编号和姓名及工资。
SELECT empno 员工编号, ename 员工姓名, sal 工资
FROM emp
WHERE sal > (
SELECT MIN(sal) 20部门的最低工资
FROM emp e , dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY d.`deptno`
HAVING d.`deptno`=20);
#4. 显示经理是KING的员工姓名，工资。
#方法一
SELECT ename 员工姓名,sal 工资
FROM emp
WHERE mgr=(SELECT empno
FROM emp
WHERE ename="KING");
#方法二
SELECT e.ename 员工姓名, e.sal 工资
FROM emp e, emp mgr
WHERE e.mgr = mgr.empno
AND mgr.ename = 'KING';
#5. 显示比10号部门所有员工入职时间都要晚的员工姓名，参加工作时间。
SELECT ename 员工姓名 , hiredate 参加工作时间
FROM emp
WHERE hiredate > ALL (SELECT hiredate
FROM emp
WHERE deptno = 10)
AND deptno <> 10 ;
#6. 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
SELECT d.`deptno` 部门的编号 ,d.`dname` 部门的名称 ,e.`sal` 最低工资
FROM dept d , emp e
WHERE e.`deptno`=d.`deptno`
AND d.`deptno`<>10
GROUP BY d.`deptno`
HAVING MIN(e.`sal`) > (SELECT MIN(sal)
FROM emp
WHERE deptno = 10);