MySQL多表查询练习（内/外连接_子连接）

• 查询至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数

SELECT d.*, cnt
FROM dept d, (SELECT DEPTNO,COUNT(*) cnt FROM emp GROUP BY DEPTNO) z1
WHERE d.DEPTNO=z1.DEPTNO;

• 列出所有员工的姓名及其上级的姓名

SELECT e1.ENAME PERSON,IFNULL(e2.ENAME,'BOSS') 领导
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.MGR=e2.EMPNO;	

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

SELECT a, b, d.DNAME
FROM dept d, (SELECT e1.EMPNO a, e1.ENAME b, e1.DEPTNO c FROM emp e1, emp e2 WHERE (e1.MGR=e2.EMPNO) AND (e1.HIREDATE<e2.HIREDATE)) m
WHERE m.c=d.DEPTNO;

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

SELECT e.*, d.DNAME
FROM emp e RIGHT OUTER JOIN dept d
ON e.DEPTNO=d.DEPTNO;

• 列出最低薪金大于1500的各种工作及从事此工作的员工人数

SELECT z.j, z.c, z.m
FROM (SELECT job j, MIN(e.SAL) m, count(*) c FROM emp e GROUP BY e.JOB) z
WHERE z.m>1500;

• 列出在销售部工作的员工姓名， 假设不知道销售部的部门编号

SELECT e.ENAME 姓名
FROM emp e, (SELECT deptno dn FROM dept d WHERE d.DNAME='SALES') d
WHERE e.DEPTNO=d.dn;

• 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级

SELECT b.*, c.dna 部门名称, d.boss 上级领导, e.gra 工资等级
FROM  
emp b, 
(SELECT AVG(e.SAL) av FROM emp e) a, 
(SELECT emp.EMPNO cempno,dept.DNAME dna FROM emp, dept WHERE emp.DEPTNO=dept.DEPTNO) c, -- 部门名称
(SELECT e1.EMPNO dempno, IFNULL(e2.ENAME, 'BOSS') boss FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.MGR=e2.EMPNO) d, -- 上级领导
(SELECT emp.EMPNO eempno, salgrade.GRADE gra FROM emp, salgrade WHERE (emp.SAL <= salgrade.HISAL) AND (emp.SAL >= salgrade.LOSAL)) e -- 工资等级
WHERE (b.SAL>av) AND (b.EMPNO=c.cempno) AND (b.EMPNO=d.dempno) AND (b.EMPNO=e.eempno);

• 列出与WARD从事相同工作的所有员工及部门名称

SELECT e.ENAME, d.DNAME
FROM emp e, dept d
WHERE (d.DEPTNO=e.DEPTNO) AND ((SELECT emp.JOB FROM emp WHERE emp.ENAME='WARD')=e.JOB); 

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

SELECT e.ENAME, d.DNAME, e.SAL
FROM emp e, dept d
WHERE (e.DEPTNO=d.DEPTNO) AND (e.SAL>(SELECT MAX(emp.SAL) FROM emp GROUP BY emp.DEPTNO HAVING (emp.DEPTNO='30')));