Oracle练习

SQL92的语法

注意： 92语法里面没有全连接，相比于99语法，92更加难读

select e.*,d.* from emp e,dept d where e.deptno = d.deptno
-- 右外连接
select e.*,d.* from emp e,dept d where e.deptno(+)=d.deptno
-- 左外连接
select e.*,d.* from emp e,dept d where e.deptno=d.deptno(+)

92版的SQL查询练习

-- 查询出员工姓名及所在部门名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+)
-- 查询出部门编号，部门名，及该部门下所有员工的姓名
select d.deptno,d.dname,e.ename from emp e,dept d where e.deptno=d.deptno
-- 查询出每个员工的姓名，所属部门名称，月薪等级及其领导的姓名，所属部门名称
select e.ename, d.dname, g.grade, m.ename, m.deptno
  from emp e, salgrade g, dept d, emp m, dept p
 where e.sal >= g.losal(+)
   and e.sal <= g.hisal(+)
   and e.deptno = d.deptno(+)
   and m.mgr = e.empno(+)
   and m.deptno = p.deptno(+)

-- 查询出每个部门月薪最低的员工信息
select * from emp a inner join (select e.job,min(nvl(sal,0)+nvl(comm,0)) sal from emp e group by job) b on a.job = b.job where a.sal < b.sal

-- 查询出每个部门的编号，员工数量，平均月薪
select deptno,count(*) ecount,round(avg(nvl(sal,0)+nvl(comm,0))) avg_sal from emp group by deptno

-- 查询出每个部门的编号，名称，员工数量，平均月薪
select d.deptno, d.dname, a.ecount, a.avg_sal
  from dept d
 inner join (select deptno,
                    count(*) ecount,
                    round(avg(nvl(sal, 0) + nvl(comm, 0))) avg_sal
               from emp
              group by deptno) a
    on d.deptno = a.deptno

连表查询练习

-- 多表联查练习题

1. 列出至少有一个员工的部门名。
select d.dname from dept d inner join (select deptno from emp group by deptno having count(empno)>0) a on d.deptno=a.deptno

2. 列出月薪比“SMITH”多的所有员工。
select * from emp where round(nvl(sal,0)+nvl(comm,0)) > (select round(nvl(sal,0)+nvl(comm,0)) from emp where ename = 'SMITH')

3. 列出所有员工的姓名及其直接上级的姓名。
select e.ename,m.ename from emp e,emp m where e.empno = m.mgr(+)

4. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。
select e.empno,e.ename,d.dname from emp e,emp m,dept d where e.empno = m.empno and (e.hiredate - m.hiredate) > 0 and e.deptno = d.deptno

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select * from (select d.dname,e.* from dept d,emp e where d.deptno = e.deptno) a,
(select deptno from emp group by deptno having count(empno)=0) b

6. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数。
select * from (select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno and e.job='CLERK') a,
(select e.deptno,count(e.empno) from emp e group by e.deptno) b where a.deptno = b.deptno

7. 列出最低月薪大于1500的各种工作及从事此工作的全部雇员人数。
select a.* from (select job,count(*) man_num from emp group by job) a,
(select * from (select job,min(round(nvl(sal,0),nvl(comm,0))) min_sal from emp group by job) a where a.min_sal > 1500) b where a.job(+) = b.job

8. 列出在部门“SALES”（销售部）工作的员工的姓名。
select * from dept
select e.ename from emp e,dept d where e.deptno=d.deptno and d.dname='SALES'

9. 列出月薪高于公司平均月薪的所有员工，所在部门，上级领导，工资等级。
select round(sum(sal)/12) avg_sal from emp
select * from salgrade
-- 下面是成品，@TODO ???
select a.ename, d.dname, m.ename manager, s.grade
  from (select e.*
          from emp e
         where nvl(e.sal, 0) >
               (select round(sum(sal) / 12) avg_sal from emp)) a,
       dept d,
       emp m,
       salgrade s
 where a.deptno = d.deptno
   and a.empno = m.mgr
   and a.sal >= s.losal
   and a.sal <= s.grade

10. 列出与“SCOTT”从事相同工作的所有员工及部门名称。
select e.ename,d.dname from emp e,dept d where e.job = (select job from emp where ename = 'SCOTT') and e.deptno=d.deptno


11. 列出月薪等于部门30中员工的月薪的所有员工的姓名和月薪。
-- 月薪
round(nvl(sal,0)+nvl(comm,0))
select e.ename,round(nvl(e.sal,0)+nvl(e.comm,0)) from emp e where e.sal in (select round(nvl(sal,0)+nvl(comm,0)) month_sal from emp where deptno = 30)

12. 列出月薪高于在部门30工作的所有员工的月薪的员工的姓名、月薪、部门名称。
select max(a.month_sal) from (select round(nvl(sal, 0) + nvl(comm, 0)) month_sal from emp where deptno = 30) a
-- 使用多层嵌套
select e.ename, round(nvl(e.sal, 0) + nvl(e.comm, 0)) month_sal, d.dname
  from emp e, dept d
 where e.sal > (select max(a.month_sal) from (select round(nvl(sal, 0) + nvl(comm, 0)) month_sal from emp where deptno = 30) a)
   and e.deptno = d.deptno

13. 列出每个部门工作的员工的数量、平均工资和平均服务期限。
select count(e.empno),round(avg(nvl(sal,0)+nvl(comm,0))),round(avg(sysdate-hiredate)) from emp e group by e.deptno

14. 列出所有员工的姓名、部门名称和部门平均工资。
select e.ename,d.dname,c.avg_sal from emp e,dept d,(select deptno,round(avg(nvl(sal,0)+nvl(comm,0))) avg_sal from emp group by deptno) c where e.deptno=d.deptno and e.deptno=c.deptno

15. 列出所有部门的详细信息和部门人数。
select d.*,m.dept_num from dept d,(select deptno,count(empno) dept_num from emp group by deptno) m where d.deptno=m.deptno

16. 列出各种工作的最低工资及员工姓名。
-- 怎么使用左连接
select a.min_sal,e.ename from
(select job,min(round(nvl(sal,0)+nvl(comm,0))) min_sal from emp group by job) a,emp e where a.job = e.job and a.min_sal=round(nvl(e.sal,0)+nvl(e.comm,0))

17. 列出各个部门的MANAGER(经理)的最低月薪。-- ???
select a.deptno,min(a.sal) from (select e.* from emp e where e.empno in (select distinct(nvl(mgr,0)) from emp)) a group by a.deptno

18. 列出所有员工的年总收入，按年总收入从低到高排序。
select * from (select round(nvl(sal,0)+nvl(comm,0))*12 year_sal from emp) a order by a.year_sal

19. 列出员工的上级主管信息，并要求这些主管的月薪超过3000。
select distinct(m.ename) from emp e,emp m where e.mgr=m.empno and round(nvl(m.sal,0)+nvl(m.comm,0)) > 3000

20. 列出部门名称中带“S”字符的部门员工的月薪合计、部门人数。
select sum(round(nvl(b.sal, 0) + nvl(b.comm, 0))), count(b.empno)
  from (select e.*
          from emp e
         where deptno in
               (select a.deptno
                  from (select * from dept where dname like 'S') a)) b
 group by b.deptno


21. 求出部门平均月薪最高的部门名和平均月薪。
select max(a.dept_avg) 最高平均月薪部门 from (select deptno,round(avg(nvl(sal,0)+nvl(comm,0))) dept_avg from emp group by deptno) a