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            1600BLAKE            2850CLARK            2450KING             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月 -81WARD       SALESMAN  22-2月 -81JONES      MANAGER   02-4月 -81BLAKE      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        300TURNER                     1500          0MARTIN                     1250       1400WARD                       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 join10  (select dname,deptno from dept where deptno!=10) c11  on o.deptno=c.deptno12* order by o.deptnoSQL> /    DEPTNO ENAME                DNAME                        DNAME---------- -------------------- ---------------------------- --------        10 CLARK                ACCOUNTING        10 KING                 ACCOUNTING        10 MILLER               ACCOUNTING        20                                                   RESEARCH        30                                                   SALES