sql重点题型

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

select e.* , e.ename, d.dname,s.grade
from emp e ,dept d ,emp m,salgrade s
where s.sal >  (select AVG(sal) from emp) AND e.deptno = d.deptno AND  e.sal BETWEEN s.losal AND s.hisal

使用下面的表连接的形式:

select e.* , e.ename, d.dname,s.grade
from emp e 
        LEFT OUTER JOIN dept d ON e.deptno = d.deptno
        LEFT OUTER JOIN emp m ON e.mgr = m.empno
        LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
where s.sal >  (select AVG(sal) from emp)

2.受雇日期 高于 领导的员工信息

select e.empno , e.ename,e.deptno
from emp e,emp m
where e.mgr = m.empno AND e.hiredate < m.hiredate AND e.deptno = d.deptno

3.列出所有别的员工姓名及其直接上级姓名

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

4.查询出年份,利润,年度增长比

SELECT  y1.*, IFNULL(CONCAT(y1.zz - y2.zz)/y2.zz*100,'0%')增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year = y2.year + 1;