SQL练习: 多表查询

/*
1. 查出至少有一个员工的部门, 显示部门编号, 部门名称, 部门位置, 部门人数.
列: d.deptno , d.dname , d.loc, 部门人数
表: dept d, emp e 
条件: e.deptno = d.deptno
*/

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


/*
2. 列出所有员工的姓名及其直接上级的姓名
列: 员工姓名, 上级名称
表: emp e, emp m
条件: 员工的 mgr = 上级的empno
*/
SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno;


SELECT e.ename, IFNULL(m.ename,'BOSS') 领导 
FROM emp e
LEFT JOIN emp m
ON e.mgr = m.empno


/*
3. 列出受雇日期早于上级的所有员工的编号, 姓名, 部门名称
列: e.empno, e.ename, d.dname
表: emp e, emp m,  dept d 
条件: e.hiredate < m.hiredate
思路: 
1. 先不查 部门名称, 只查部门编号
列: e.empno, e.ename, e.deptno 
表: emp e, emp m 
条件: e.mgr = m.empno, e.hiredate = m.hiredate 
*/

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d 
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate AND e.deptno = d.deptno



/*
4. 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
列: 部门名称, 员工信息
表: d.dname , emp.*
条件:
*/

SELECT d.dname, e.* 
FROM dept d LEFT JOIN emp e
ON d.deptno = e.deptno

/*
5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数.
列: job, count(*)
表: emp e
条件: min(sal) > 15000
分组: job 
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job 
HAVING MIN(sal) > 15000



/*
6. 列出在销售部工作员工的姓名, 假定不知道销售部的部门编号
列: e.ename
表: emp
条件: e.deptno = (SELECT deptno FROM dept WHERE dname='销售部')
*/

SELECT *
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname ='销售部')



/*
7. 列出与庞统从事相同工作的所有员工和部门名称
列: e.*, d.dname 
表: emp e , dept d
条件: job = (查询出庞统的工作)
*/

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统')



/*
8. 列出薪金高于公司平均薪金的所有员工信息, 所在部门信息, 上级领导, 工资等级
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM
	emp e LEFT JOIN dept d ON e.deptno = d.deptno
				LEFT JOIN emp m ON e.mgr = m.empno
				LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp )






/*
9. 查出年份, 利润, 年度增长比
*/
SELECT * y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100,'%'),'0%') 增长比
FROM tb_year y1 LEFT JOIN tb_year y2
ON y1.year=y2.year+1;