使用scott/tiger用户下的emp表和dept表完成下列练习

Emp员工表

Dept表

1．列出至少有一个员工的所有部门。

2．列出薪金比“SMITH”多的所有员工。

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

4．列出受雇日期早于其直接上级的所有员工。

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

7．列出最低薪金大于1500的各种工作。

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

9．列出薪金高于公司平均薪金的所有员工。

10．列出与“SCOTT”从事相同工作的所有员工。

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

14．列出所有员工的姓名、部门名称和工资。

15．列出从事同一种工作但属于不同部门的员工的一种组合。

16．列出所有部门的详细信息和部门人数。

17．列出各种工作的最低工资。

18．列出各个部门的MANAGER（经理）的最低薪金。

19．列出所有员工的年工资,按年薪从低到高排序。

1.
select distinct dname
from dept
where deptno in
          (select distinct deptno
           from emp);

要查部门编号如果存在于emp 表中，说明这个部门有员工。

2.
select ename,job,mgr,hiredate,sal
from emp
where sal>
        (select sal
         from emp
         where ename='SMITH');

3.
select e.ename, e.mgr , w.ename
from emp e, emp w
where e.mgr=w.empno;

4.
select e.ename, e.mgr , e.hiredate,w.ename,w.hiredate
from emp e, emp w
where e.mgr=w.empno
and e.hiredate< w.hiredate;

5.
select e.ename,e.hiredate, d.dname,d.deptno
from emp e, dept d
where e.deptno (+)=d.deptno;

本例子使用的是外连接， 也可以用右连接。
select e.ename,e.hiredate, d.dname,d.deptno
from emp e right join dept d
on e.deptno=d.deptno;

6.
select e.ename,e.hiredate,e.job, d.dname,d.deptno
from emp e, dept d
where e.deptno =d.deptno
and e.job=upper('clerk');

7.
select job, min(sal)
from emp
group by job
having min(sal)>1500;

8.
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno
and d.dname=upper('sales');
或者：
select ename
from emp
where deptno =
      (select deptno
       from dept
       where dname=upper('SALES'));

9.
select ename, job, sal
from emp
where sal>
        (select avg(sal)
         from emp );

10.
select ename, job
from emp
where job =
       (select job
        from emp
        where ename=upper('scott'));
11.

select ename, sal,job
from emp
where sal in
       (select sal
        from emp
        where deptno=30);
12.
select ename, sal,job
from emp
where sal >all
       (select sal
        from emp
        where deptno=30);

或者
select ename ,sal
from emp
where sal>
       (select max(sal)
        from emp
        where deptno=30);

13.

select d.deptno,d.dname, count(e.ename), avg(e.sal), avg(months_between(sysdate,e.hiredate))
from emp e, dept d
where e.deptno(+)= d.deptno
group by d.deptno,dname;

或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。
select e.*,d.dname ，
from (select deptno, count(ename) ,avg(sal),avg(months_between(sysdate,hiredate))
      from emp
      group by deptno) e, dept d
where e.deptno =d.deptno;

14.

select d.dname, e.ename, sal+nvl(comm,0)
from emp e, dept d
where d.deptno=e.deptno;

15.
select e.ename,e.job, e.deptno ,d.job, d.deptno
from emp e, emp d
where e.job=d.job and e.deptno<>d.deptno;

16.
select count(e.ename),d.dname
from emp e, dept d
where e.deptno(+)= d.deptno
group by e.deptno,d.dname;

或者写成
select d.deptno,e.*,d.dname
from (select deptno,count(ename)
     from emp
     group by deptno) e,dept d
where e.deptno(+)= d.deptno;

17.

select job,min(sal+nvl(comm,0))from emp group by job;

18.
select deptno,min(sal) from emp where job=upper('manager')
group by deptno ;

19.
select ename , (sal+nvl(comm,0))*12   yearsal
from emp
order by yearsal ;