找出销售部门中年纪最大的员工
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;