数据库作业

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 = '财务'));

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 = '销售'

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;

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)

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

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)