数据库作业五

找出销售部门中年纪最大的员工
 

SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售') AND age =
   (SELECT MAX(age) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售')) ;

求财务部门最低工资的员工姓名

SELECT a.name FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务')) a
 WHERE a.incoming = (SELECT MIN(incoming) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务'));

列出每个部门收入总和高于9000的部门名称

SELECT d.dept_name FROM dept d,
(SELECT dept2 FROM emp GROUP BY dept2 HAVING SUM(incoming) > 9000) e
WHERE d.dept1 = e.dept2;

求工资在7500到8500元之间，年龄最大的人的姓名及部门
 

SELECT a.name,d.dept_name FROM dept d,
  (SELECT e.NAME,e.dept2 FROM 
  (SELECT * FROM emp WHERE incoming BETWEEN 7500 AND 8500) e WHERE age = (SELECT MAX(age) FROM emp WHERE incoming BETWEEN 7500 AND 8500)) a
 WHERE d.dept1 = a.dept2 ;

找出销售部门收入最低的员工入职时间

SELECT worktime_start FROM 
(SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售')) a
WHERE a.incoming = (SELECT MIN(incoming) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售'));

财务部门收入超过2000元的员工姓名

SELECT NAME FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务')) a
WHERE a.incoming > 2000;

列出每个部门的平均收入及部门名称

SELECT d.dept_name,a.c FROM dept d,
(select dept2,AVG(incoming) AS c FROM emp GROUP BY dept2) a
WHERE d.dept1 = a.dept2;

IT技术部入职员工的员工号

SELECT a.sid FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = 'IT技术')) a;

财务部门的收入总和；

SELECT SUM(incoming) FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务')) a;

找出哪个部门还没有员工入职；这题不会

SELECT d.dept_name FROM dept d,
(SELECT dept2 FROM emp GROUP BY dept2 HAVING COUNT(1) != 0) a 
WHERE d.dept1 != a.dept2;

列出部门员工收入大于7000的部门编号，部门名称；

SELECT d.dept1,d.dept_name FROM dept d,
(SELECT distinct dept2 FROM emp WHERE incoming > 7000) a
WHERE d.dept1 = a.dept2l;

列出每一个部门的员工总收入及部门名称；

SELECT a.c,d.dept_name FROM dept d,
(SELECT dept2,SUM(incoming) AS c FROM emp GROUP BY dept2)a
WHERE a.dept2 = d.dept1;

列出每一个部门中年纪最大的员工姓名，部门名称；

SELECT m.name,d.dept_name FROM dept d,
(SELECT e.name,e.dept2,e.age FROM emp e,
(SELECT dept2 AS b,MAX(age) AS c FROM emp GROUP BY dept2) a
WHERE e.dept2 = a.b AND e.age = a.c) m
WHERE d.dept1 = m.dept2;

求李四的收入及部门名称

SELECT a.incoming,d.dept_name FROM dept d,
(SELECT dept2,incoming FROM emp WHERE NAME = '李四') a
WHERE d.dept1 = a.dept2;

列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序

SELECT m.name,d.dept_name,m.incoming FROM dept d,
(SELECT e.NAME,e.incoming,e.dept2 FROM emp e,
(SELECT dept2 AS b,MAX(incoming) AS c FROM emp GROUP BY dept2)a
WHERE e.dept2 = a.b AND e.incoming = a.c) m
WHERE d.dept1 = m.dept2
ORDER BY m.incoming DESC;