-
查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列：d.deptno, d.dname, d.loc, 部门人数
表：dept d, emp e
条件：e.deptno=d.deptno
分析：- d.deptno, d.dname, d.loc 为dept表所有列
SELECT * FROM dept - 部门人数为emp表分组统计出来
SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno; - 将2个结果集合并起来并去除笛卡尔积，注意给结果集和count列取别名
SELECT dept.*, tb2.num FROM dept, (SELECT deptno, COUNT(empno) num FROM emp GROUP BY deptno) tb2 WHERE dept.deptno = tb2.deptno;
- d.deptno, d.dname, d.loc 为dept表所有列
列出所有员工的姓名及其直接上级的姓名
列：员工姓名、上级姓名
表：emp e, emp m
条件：员工的mgr = 上级的empno
SELECT e.ename, IFNULL(m.ename, 'BOSS') 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno
- 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：e.empno, e.ename, d.dname
表：emp e, emp m, dept d
条件：e.hiredate < m.hiredate
思路：
- 先不查部门名称，只查部门编号!
列：e.empno, e.ename, e.deptno
表：emp e, emp m
条件：e.mgr=m.empno, e.hiredate<m.hireadate
SELECT e.empno, e.ename
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate;
- 加入dept表
SELECT tb1.*, d.dname
FROM (SELECT e.empno, e.ename '本人', m.ename '上级', e.hiredate '本人入职', m.hiredate '上级入职' , e.deptno
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate) tb1 LEFT JOIN dept d
ON tb1.deptno = d.deptno;
思路2：先将emp和dept表 左连接， 将结果作为一张表，然后在这张表里面查出入职时间<上级入职时间的员工
SELECT tb1.empno, tb1.ename, tb1.hiredate, tb1.dname
FROM (SELECT emp.*, dept.dname
FROM emp LEFT JOIN dept
ON emp.deptno = dept.deptno) tb1, emp
WHERE tb1.hiredate < emp.hiredate AND tb1.mgr = emp.empno;
- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
- 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;
- 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
SELECT e.ename
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = '销售部'
- 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e
NATURAL LEFT JOIN dept d
LEFT JOIN emp m ON m.empno=e.mgr
LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);
8.列出与庞统从事相同工作的所有员工及部门名称。
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
9.列出薪金高于在部门30工作的所有员工的薪金 的员工姓名和薪金、部门名称。
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
10.列出在每个部门工作的员工数量、平均工资。
SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;
