oracle 复杂查询（1）

--69 查询emp和dept表,产生笛卡尔积(多表行相乘) select * from emp, dept; --70 加where条件过滤查询emp和dept表产生的笛卡尔积 select * from emp, dept where emp.deptno = dept.deptno; --71 查询emp和dept表,产生笛卡尔积(多表行相乘),并为表取别名 select * from emp e, dept d; --72 查询雇员姓名,所在部门编号和名称 select e.ename, e.deptno, d.dname from emp e, dept d where e.deptno = d.deptno; --73 查询所有雇员姓名,工作,领导的姓名 select e1.ename, e1.job, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno; --74 查询雇员姓名,工作,领导姓名及部门名称 select e1.ename, e1.job, e2.ename, d.dname from (emp e1 left join emp e2 on e1.mgr = e2.empno) join dept d on e1.deptno = d.deptno; --75 查询雇员姓名,工作,工资及工资等级 select e.ename, e.job, e.sal, case when e.sal >= 700 and e.sal <= 1200 then '1' when e.sal >= 1201 and e.sal <= 1400 then '2' when e.sal >= 1401 and e.sal <= 2000 then '3' when e.sal >= 2001 and e.sal <= 3000 then '4' else '5' end as grade from emp e; -- 76 查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E select e.ename, e.job, e.sal, case g.grade when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'D' when 5 then 'E' else '0' end as grade FROM emp e, salgrade g where e.sal BETWEEN g.losal AND g.hisal; --77 查询雇员姓名,年薪（薪水+奖金）,按年薪从高到低排序 select ename, (sal + nvl(comm, 0)) * 12 total from emp order by total desc; --78 查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序，工资相同的情况下按姓名排升序 select e.ename, e.job, e.sal, d.dname from emp e left join dept d on e.deptno = d.deptno(+) and e.sal in (select max(sal) from emp group by deptno) order by sal desc, ename; -- 79 查询每个部门的部门编号和雇员数量 select d.deptno, count(e.empno) from emp e, dept d where e.deptno(+) = d.deptno group by d.deptno; --80 求出每个部门的部门名和平均工资(保留2位小数，截断) select dept.dname, nvl(trunc(avg(emp.sal), 2), 0) from dept left outer join emp on emp.deptno = dept.deptno group by dept.dname; -- 81 按部门分组，并显示部门的名称，以及每个部门的员工数 select dept.dname, count(emp.empno) from dept left outer join emp on emp.deptno = dept.deptno group by dept.dname; -- 82 要求显示平均工资大于2000的部门编号和平均工资(保留2位小数，截断) select deptno, trunc(avg(sal), 2) from emp group by deptno having avg(sal) > 2000;