PL/SQL 练习题

1.部门10和30中工资超过1500的雇员名及工资

SQL> select ename,sal
  2  from scott.emp
  3  where sal>1500
  4  and deptno=any(10,30);

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
KING             5000

2. 在1981年2月1日~1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期的先后进行排序

SQL> select ename,job,hiredate from scott.emp
  2  where hiredate between to_date('1981-02-01','yyyy-mm-dd')
  3   AND to_date('1981-05-01','yyyy-mm-dd')
  4  ORDER BY hiredate;

ENAME      JOB       HIREDATE
---------- --------- --------------
ALLEN      SALESMAN  20-2月 -81
WARD       SALESMAN  22-2月 -81
JONES      MANAGER   02-4月 -81
BLAKE      MANAGER   01-5月 -81

3. 获得补助的所有雇员名、工资及补助额，并以工资和补助的降序排序

SQL> select ename,sal,comm from scott.emp
  2  where comm is NOT NULL
  3  ORDER BY sal desc,comm desc;

ENAME                       SAL       COMM
-------------------- ---------- ----------
ALLEN                      1600        300
TURNER                     1500          0
MARTIN                     1250       1400
WARD                       1250        500

4. 雇员总数，以及获得补助的雇员数

SQL> select count(empno),count(comm) from scott.emp;

COUNT(EMPNO) COUNT(COMM)
------------ -----------
          15           4

5. 每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资

  select a.deptno,a.job,a.deptno_job_avg,b.deptno_avg,c.job_avg from
  (select deptno,job,avg(sal) as deptno_job_avg from scott.emp group by deptno,job) a
  left join
  (select deptno,avg(sal) as deptno_avg from scott.emp group by deptno) b
  on a.deptno=b.deptno
  left join
  (select job,avg(sal) as job_avg from scott.emp group by job ) c
  on a.job=c.job
  order by deptno

6. 雇员SCOTT的管理者名

SQL> select b.ename
  2  from emp a,emp b
  3  where a.mgr=b.empno
  4  and a.ename='SCOTT';

7. 部门10的所有雇员名、部门名，以及其他部门名

 1  select distinct o.deptno,a.ename,a.dname,c.dname from
 2  (select dept.deptno,emp.ename,dept.dname from emp,dept
 3  where emp.deptno=dept.deptno) o
 4  left join
 5  (select dept.deptno,emp.ename,dept.dname from emp,dept
 6  where emp.deptno=dept.deptno
 7  and dept.deptno=10) a
 8  on o.deptno=a.deptno
 9  left join
10  (select dname,deptno from dept where deptno!=10) c
11  on o.deptno=c.deptno
12* order by o.deptno
SQL> /

    DEPTNO ENAME                DNAME                        DNAME
---------- -------------------- ---------------------------- --------
        10 CLARK                ACCOUNTING
        10 KING                 ACCOUNTING
        10 MILLER               ACCOUNTING
        20                                                   RESEARCH
        30                                                   SALES