Oracle基本查询语句练习（二）

本篇相对于上一篇，更多的是多表关联查询，稍微有难度，可以练手

1.查询emp和dept表,产生笛卡尔积(多表行相乘)
select * from emp,dept;
2.加where条件过滤查询emp和dept表产生的笛卡尔积
select * from emp,dept where emp.deptno = dept.deptno;
3.查询emp和dept表,产生笛卡尔积(多表行相乘),并为表取别名
select * from emp e,dept d;
4.查询雇员姓名,所在部门编号和名称
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
5.查询所有雇员姓名,工作,领导的姓名
select e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
6.查询雇员姓名,工作,领导姓名及部门名称
select e1.ename,e1.job,e2.ename,d.dname from emp e1,emp e2,dept d where e1.mgr= e2.empno(+) and e1.deptno = d.deptno;
7.查询雇员姓名,工作,工资及工资等级
select ename,job,sal,case 
    when e.sal >= 700 and e.sal <= 1200 then '1' 
    hen 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 
    from emp e;
8.查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E
select e.ename,e.job,e.sal,
case 
  when g.grade =1 then 'A' 
  when g.grade =2 then 'B' 
  when g.grade =3 then 'C' 
  when g.grade =4 then 'D' 
  when g.grade =5 then 'E'  
else '0'
end
FROM emp e,salgrade g where  e.sal BETWEEN g.losal AND g.hisal
9.查询雇员姓名,年薪（薪水+奖金）,按年薪从高到低排序
select ename,(sal+nvl(comm,0))*12 total from emp order by total desc;
10.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序，工资相同的情况下按姓名排升序
select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno(+) and e.sal in (select max(sal) from emp group by deptno) 
order by sal desc,ename;
11.查询每个部门的部门编号和雇员数量
select d.deptno,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno;
12.求出每个部门的部门名和平均工资(保留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;
13.按部门分组，并显示部门的名称，以及每个部门的员工数
select dept.dname,count(emp.empno) from dept left outer join emp on emp.deptno=dept.deptno group by dept.dname
14.要求显示平均工资大于2000的部门编号和平均工资(保留2位小数，截断)
select deptno,trunc(avg(sal),2) from emp group by deptno having avg(sal) > 2000;
15.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资大于$1500，输出结果按月工资的合计升序排列
SELECT job,sum(sal) sum FROM emp WHERE job!='SALESMAN' and sal>1500 GROUP BY job  ORDER BY sum ASC;
16.求出平均工资最高的部门名称
select d.dname from (select deptno,avg(sal) from emp group by deptno order by avg(sal) desc) e,dept d where d.deptno=e.deptno and rownum=1;
17.要求查询出比7654工资要高的全部雇员的信息
select e.* from emp e where e.sal > (select sal from emp where emp.empno = '7654');
18.要求查询工资比7654高，与7788从事相同工作的全部雇员信息
select e.* from emp e where (e.sal > (select sal from emp where empno = '7654' )) and (e.job = (select job from emp where emp.empno = '7788'));
19.查询出工资最低的雇员姓名，工作，工资
select ename,job,sal from emp where sal = (select min(sal) from  emp);
20.查询出各部门工资最低的雇员姓名，工作，工资
select e.ename,e.job,e.sal
from emp e, (select deptno, count(*), avg(sal), min(sal) min_sal, max(sal) max_sal from emp group by deptno) t 
where e.deptno = e.deptno
and (e.sal = min_sal);
21.要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名，要求显示所有部门名，
如果该部门没有任何员工，则员工书和平均工资需显示0，员工姓名显示null即可。
SELECT p.dname,p.count1,nvl(p.avg1,0),q.ename FROM 
(select e.deptno,avg(e.sal) as avg1,count(empno) as count1,d.dname FROM emp e RIGHT JOIN dept d ON d.deptno =e.deptno 
GROUP BY d.dname,e.deptno) p LEFT JOIN (select e.ename,e.deptno FROM emp e RIGHT JOIN (select deptno as dn,MIN(DISTINCT sal) salary 
FROM emp GROUP BY deptno) p on p.dn = e.deptno AND p.salary = e.sal) q on p.deptno = q.deptno