Oracle数据库PL SQL开发、Oracle-SQL开发习题答案

最新推荐文章于 2021-12-23 11:00:00 发布

蓝白咖啡

最新推荐文章于 2021-12-23 11:00:00 发布

阅读量1.8k

点赞数

分类专栏：数据库

原文链接：https://blog.csdn.net/qq_37906822/article/details/81381918

版权

数据库专栏收录该内容

4 篇文章 1 订阅

订阅专栏

第2章编写简单的查询语句
练习1

1.使用两种方式查询所有员工（EMP）信息。

   SELECT *

   FROM emp;

  

   SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno

    FROM emp;

2.查询（EMP）员工编号、员工姓名、员工职位、员工月薪、工作部门编号。

   SELECT empno,ename,job,sal,deptno

    FROM emp;

练习2

1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。

   SELECT ename,sal*(1+0.2)

    FROM emp;

2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分，年薪的试用期6个月的月薪+转正后6个月的月薪）

   SELECT empno,sal*(1+0.2)*6+sal*6

    FROM emp;

练习3

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作一年的所有收入（需考虑奖金部分），要求显示列标题为员工姓名，工资收入，奖金收入，总收入。

   SELECT ename 员工姓名,sal*(1+0.2)*6+sal*6 工资收入,comm 奖金收       入,sal*(1+0.2)*6+sal*6+nvl(comm*12,0) 总收入

    FROM emp;

练习4

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分），要求显示格式为：XXX的第一年总收入为XXX。

   SELECT ename || '的第一年总收入为' || (sal*(1+0.2)*6+sal*6+nvl(comm*12,0))

    FROM emp;

2.查询员工表中一共有哪几种岗位类型。

   SELECT DISTINCT deptno

    FROM emp;

课后作业

1.分别选择员工表、部门表、薪资等级表中的所有数据。

   SELECT *

    FROM emp;



   SELECT *

    FROM dept;



   SELECT *

    FROM salgrade;

2.分别查看员工表、部门表、薪资等级表结构。

   DESC emp;

   DESC dept;

   DESC salgrade;

第3章限制数据和对数据排序
练习1

1.查询职位为SALESMAN的员工编号、职位、入职日期。

   SELECT empno,job,hiredate

    FROM emp

   WHERE job='SALESMAN';

2.查询1985年12月31日之前入职的员工姓名及入职日期。

   SELECT empno,hiredate

    FROM emp

   WHERE hiredate<'31-12月1985';

3.查询部门标号不在10部门的员工姓名、部门编号。

   SELECT ename,deptno

    FROM emp

  WHERE deptno<>10;

练习2

1.查询入职日期造82年至85年员工姓名，入职日期。

   SELECT ename,hiredate

   FROM emp

   WHERE hiredate BETWEEN '01-1月-82' AND '01-1月-85';



  SELECT ename,hiredate

    FROM emp

   WHERE hiredate BETWEEN TO_DATE('1982-01-01','YYYY-MM-DD')

         AND TO_DATE('1985-01-01','YYYY-MM-DD');

2.查询月薪在3000到5000的员工姓名，月薪。

   SELECT ename,sal

  FROM emp

  WHERE sal BETWEEN 3000 AND 5000;

3.查询部门编号为10或者20的员工姓名，部门编号。

   SELECT ename,deptno

  FROM emp

  WHERE deptno=10 OR deptno=20;

4.查询经理编号为7709，7566，7788的员工姓名，经理编号。

   SELECT ename,mgr

   FROM emp

  WHERE mgr IN(7709,7756,778);

练习3

1.查询员工姓名以W开头的员工姓名。

   SELECT ename

  FROM emp

  WHERE ename LIKE 'W%';



   SELECT ename

  FROM emp

  WHERE INSTR(ename,'W')=1;

2.查询员工姓名倒数第2个字符为T的员工姓名。

   SELECT ename

  FROM emp

  WHERE ename LIKE '%T_';

SELECT ename

FROM emp

WHERE SUBSTR(ename,-2,1)=‘T’;

3.查询奖金为空的员工姓名，奖金。

   SELECT ename,comm

  FROM emp

  WHERE comm IS NULL;

练习4

1.查询工资超过2000并且职位是WANAGER，或者职位是SALESMAN的员工姓名、职位、工资。

   SELECT ename,job,sal

  FROM emp

  WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';

2 .查询工资超过2000并且职位是WANAGER或SALESMAN的员工姓名、职位、工资。

   SELECT ename,job,sal

  FROM emp

  WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';

3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。

   SELECT ename,deptno,sal

  FROM emp

   WHERE (deptno=10 OR deptno=20) AND sal BETWEEN 3000 AND 5000;

4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。

   SELECT ename,hiredate,job

  FROM emp

  WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81') AND job NOT LIKE 'SALES%';



   SELECT ename,hiredate,job

   FROM emp

  WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81')

        AND  SUBSTR(job,0,5)<>'SALES';

5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

   SELECT ename,job,deptno

    FROM emp

  WHERE (job='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND               ename LIKE '%A%';



   SELECT ename,job,deptno

  FROM emp

  WHERE (job='SALESMAN' OR job='MANAGER')

        AND (deptno=10 OR deptno=20)

        AND INSTR(ename,'A')>0;

练习5

1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。

   SELECT ename,deptno,sal

   FROM emp

WHERE deptno=20 OR deptno=30

  ORDER BY sal;

2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。

   SELECT ename,deptno, sal

  FROM emp

  WHERE (sal BETWEEN 2000 AND 3000) AND deptno<>10

  ORDER BY deptno,sal DESC;

3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

–3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

   SELECT ename,hiredate,job

  FROM emp

   WHERE (hiredate BETWEEN '01-1月-82' AND '01-1月-83')

       AND (job LIKE 'SALES%'  OR  job LIKE 'MAN%')

  ORDER BY hiredate DESC;

课后作业

1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

   SELECT ename,hiredate,job

  FROM emp

  WHERE hiredate>'09-7月-1982' AND job<>'SALESMAN';

2.查询员工姓名的第三个字母是a的员工姓名。

   SELECT ename

   FROM emp

  WHERE SUBSTR(ename,3,1)='a';



  SELECT ename

  FROM emp

  WHERE ename LIKE '__a%';

3.查询除了10、20号部门以外的员工姓名、部门编号。

   SELECT ename,deptno

   FROM emp

  WHERE deptno NOT IN(10,20);

4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。

   SELECT *

   FROM emp

  WHERE deptno=30

  ORDER BY sal DESC,ename;

5.查询没有上级的员工（经理号为空）的员工姓名。

   SELECT ename

   FROM emp

  WHERE mgr IS NULL;

6.查询工资大于等于4500并且部门为10或者20的员工的姓名，工资，部门编号。

   SELECT ename,sal,deptno

   FROM emp

  WHERE sal>=4500 AND deptno IN(10,20);

第4章单行函数
练习1

1.写一个查询，用首字母大写，其他字母小写显示雇员的ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A或M的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）

   SELECT INITCAP(ename) 雇员姓名,LENGTH(ename) 姓名长度

   FROM emp

   WHERE ename LIKE 'J%' OR  ename LIKE 'A%' OR  ename LIKE 'M%'

  ORDER BY ename;

练习2

1.查询员工姓名中包含大写或小写字母A的员工姓名。

SELECT ename

FROM emp

WHERE SUBSTR(ename,1,1)=‘A’ OR SUBSTR(ename,1,1)=‘a’;

2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度。（提示：要求使用INSTR函数）

SELECT ename 员工姓名,LENGTH(ename) 姓名长度

FROM emp

WHERE (deptno=10 OR deptno=20)

  AND hiredate>'01-5月-81'

  AND SUBSTR(ename,1,1)='A';

3.查询每个职工的编号，姓名，工资

-要求将查询到的数据按照一定的格式合并成一个字符串

-前10位：编号，不足部分用*填充，左对齐

-中间10位：姓名，不足部分用*填充，左对齐

-后10位：工资，不足部分用*填充，右对齐

   SELECT CONCAT(CONCAT(LPAD(empno,10,'*'),LPAD(ename,10,'*')),RPAD(sal,10,'*'))

      FROM emp;

练习3

1.写一个查询，分别计算100.456四舍五入到小数点后第2位，第1位，整数位的值。

   SELECT ROUND(100.456,2)

  FROM dual;



   SELECT ROUND(100.456,1)

      FROM dual;



   SELECT ROUND(100.456,-1)

      FROM dual;

2.写一个查询，分别计算100.456从小数点后第2位，第1位，整数位截断的值。

   SELECT TRUNC(100.456,2)

      FROM dual;



   SELECT TRUNC(100.456,1)

      FROM dual;



   SELECT TRUNC(100.456,-1)

   FROM dual;

练习4

1.查询每个员工截止到现在一共入职多少天？

   SELECT empno,SYSDATE-hiredate 入职天数

      FROM emp

2.当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？

   2001-01-01

当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？
```
1995-01-01
```

4.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？

   2001-01-01

5.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？

   1995-01-01

6.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-01，该日期实际所代表的日期为？

   1901-01-01

7.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-95，该日期实际所代表的日期为？

   1995-01-01

练习5

1.查询服务器当前时间。

SELECT SYSDATE

FROM dual;

2.查询部门10，20的员工截止到2000年1月1日，工作了多少个月，入职的月份。（使用months_between, extract）

   SELECT MONTHS_BETWEEN('01-1月-2000',hiredate) 工作月份,

                  EXTRACT(MONTH FROM DATE '2000-01-01') 入职月份

     FROM emp

  WHERE deptno=10 OR deptno=20;

3.如果员工使用期6个月，查询职位不是MANAGER员工姓名，入职日期，转正日期，入职日期后的第一个星期一，入职当前月的最后一天日期。（使用add_months, next_day, last_day）

   SELECT ename,hiredate,

                  ADD_MONTHS(hiredate,6) 转正日期,

                  NEXT_DAY(hiredate,'星期一')  入职日期后的第一个星期一, 

                  LAST_DAY(hiredate) 入职当前月的最后一天日期

    FROM emp

WHERE job<>'MANAGER';

练习6

1.显示服务器系统当前时间，格式为2007-10-12 17:11:11。（用to_char函数）

   SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

     FROM dual;

2.显示ename，hiredate和雇员开始工作日是星期几，列标签DAY。（用to_char函数）

   SELECT ename,hiredate,

                  to_char(hiredate,'DAY') DAY

     FROM emp;

3.查询员工姓名，工资，格式化的工资（￥999,999.99）。（用to_char函数）

   SELECT  ename,sal,

           to_char(sal,'$999,999.99') 格式化的工资

   FROM emp;

4.把字符串2015-3月-18 13:13:13转换成日期格式，并计算和系统当前时间间隔多少天。（用to_char函数）

   SELECT TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS'),

                  SYSDATE-TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS')

   FROM dual;

课后作业

1.计算2000年1月1日到现在又多少月，多少周（四舍五入）。

   SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-1月-2000')),

        ROUND(ROUND(SYSDATE-TO_DATE('2000-01-01','YYYY-MM-DD'))/7)

   FROM dual;

2.查询员工ename的第三个字母是A的员工信息（使用两个函数）。

   SELECT *

   FROM emp

   WHERE SUBSTR(ename,3,1)='A';



  SELECT *

  FROM emp

 WHERE INSTR(ename,'A',3)=3;

3.使用trim函数将字符串‘hello’，‘Hello’，‘bllb’，‘hello’分别处理得以下字符串ello、Hello、ll、hello。

   SELECT TRIM('h' FROM 'hello'),

       TRIM(' ' FROM 'Hello '),

        TRIM('b' FROM 'bllb'),

       TRIM(' ' FROM 'hello  ')

 FROM dual;

4.将员工工资按如下格式显示：123,234.00 RMB。

   SELECT empno,to_char(sal,'999,999.99') || 'RMB'

  FROM emp;

5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

   SELECT ename,NVL(to_char(mgr),'No Manager')

   FROM emp;

6.将员工的参加工作日期按照如此啊格式显示：月份/年份

   SELECT empno,to_char(hiredate,'MM/YYYY')

   FROM emp;

7.在员工表中查询除员工的工资，并计算应交税款：如果工资小于1000，税率为0，如果工资大于等于1000并小于2000，税率为10%，如果工资大于等于2000并小于3000，税率为15%，如果工资大于等于3000，税率为20%。

   SELECT empno,sal,

        (CASE 

           WHEN sal>=0 AND sal<1000 THEN sal*0

               WHEN sal>=1000 AND sal<2000 THEN sal*0.1

                   WHEN sal>=2000 AND sal<3000 THEN sal*0.15

                      WHEN sal>=3000 THEN sal*0.1

                          ELSE 0 END) 税率

  FROM emp;

8.创建一个查询显示所有雇员的ename和sal。格式化sal围殴15个字符长度，用$左填充，列标签SALARY。

   SELECT ename,LPAD(sal,15,'$') SALARY

   FROM emp;

第5章多表连接
练习1

1.写一个查询，显示所有员工姓名，部门编号，部门姓名。

   SELECT ename,emp.deptno,dname

   FROM emp,dept

  WHERE emp.deptno=dept.deptno;

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金。

   SELECT ename,loc,comm

   FROM emp,dept

  WHERE emp.deptno=dept.deptno AND comm IS NOT NULL;

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

   SELECT ename,loc

   FROM emp,dept

  WHERE emp.deptno=dept.deptno AND ename LIKE '%A%';

练习2

1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

   SELECT e.deptno,ename,sal,grade,loc

   FROM emp e,dept d,salgrade s

  WHERE e.deptno=d.deptno

  AND (e.sal BETWEEN s.losal AND s.hisal)

  ORDER BY grade;

练习3.

1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

   SELECT e.ename 员工姓名,e.empno 员工编号,

       m.ename 经理姓名,m.empno 经理编号

  FROM emp e,emp m

WHERE e.mgr=m.empno;

2.在上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

   SELECT e.ename 员工姓名,e.empno 员工编号,

       m.ename 经理姓名,m.empno 经理编号

 FROM emp e,emp m

WHERE e.mgr=m.empno(+)

ORDER BY e.empno;

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

   SELECT emp.empno,ename,dname

   FROM emp,dept

  WHERE emp.deptno=dept.deptno(+);

练习4

使用SQL-99写法，完成如下练习

1.创建一个员工表和部门表的交叉连接。

   SELECT *

   FROM emp

   CROSS JOIN dept;

2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期。

   SELECT ename,dname,hiredate

  FROM emp

NATURAL JOIN dept

  WHERE hiredate>'01-5月-80';

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点。

   SELECT ename,dname,loc

  FROM emp

   JOIN dept USING(deptno)

 WHERE loc='CHICAGO';

4.使用ON子句，显示工作在CHICAGO的员工姓名，本门名称，工作地点，薪资等级。

   SELECT ename,dname,loc,grade

  FROM emp e

    JOIN dept d

    ON e.deptno=d.deptno AND loc='CHICAGO'

   JOIN salgrade s

   ON sal BETWEEN losal AND hisal;

5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

   SELECT e.ename 员工姓名,m.ename 经理姓名

  FROM emp e

  OUTER JOIN emp m

   ON e.mgr=m.empno;

6.使用右外连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

   SELECT e.ename 员工姓名,m.ename 经理姓名

   FROM emp m

 RIGHT OUTER JOIN emp e

   ON e.mgr=m.empno;

课后作业

1.显示员工SMITH的姓名，部门名称，直接上级名称。

   SELECT e.ename,dname,m.ename 直接上级名称

  FROM emp e,emp m,dept d

  WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.ename='SMITH';

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

   SELECT ename,dname,sal,grade

  FROM emp e,dept d,salgrade s

   WHERE e.deptno=d.deptno

     AND (sal BETWEEN losal AND hisal)

     AND grade>4;

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

   SELECT e.ename 员工姓名,m.ename 经理姓名

   FROM emp e,emp m

   WHERE e.mgr=m.empno

     AND m.ename IN('KING','FORD');

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

   SELECT e.ename,e.hiredate,m.ename,m.hiredate

   FROM emp e,emp m

  WHERE e.mgr=m.empno AND e.hiredate>m.hiredate;

第6章分组函数
练习1

1.查询部门20的员工，每月的工资总和及平均工资。

   SELECT SUM(sal),AVG(sal)

  FROM emp

  WHERE deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

   SELECT COUNT(*),MIN(sal),MAX(sal)

   FROM emp e,dept d

   WHERE e.deptno=d.deptno AND loc='CHICAGO';

3.查询员工表中一共有几种岗位类型。

   SELECT COUNT(DISTINCT job)

   FROM emp;

练习2

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

   SELECT d.deptno,dname,COUNT(e.deptno),MIN(sal),MAX(sal),SUM(sal),AVG(sal)

   FROM emp e,dept d

  WHERE e.deptno(+)=d.deptno

  GROUP BY d.deptno,dname,loc;

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

   SELECT d.deptno,dname,job,COUNT(e.empno),MAX(sal),MIN(sal),SUM(sal),AVG(sal)

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  GROUP BY d.deptno,dname,e.job;

3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

   SELECT COUNT(e.empno),m.empno,m.ename

   FROM emp e,emp m

  WHERE e.mgr=m.empno(+)

   GROUP BY m.empno,m.ename;

练习3

1.查询部门人数大于2的部门编号，部门名称，部门人数。

   SELECT d.deptno,dname,COUNT(empno)

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

   GROUP BY d.deptno,dname

HAVING COUNT(empno)>2;

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，

部门平均工资，并按照部门人数升序排序。

   SELECT d.deptno,dname,COUNT(empno),AVG(sal)

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  GROUP BY d.deptno,dname

HAVING AVG(sal)>2000 AND COUNT(empno)>2

  ORDER BY COUNT(empno);

课后作业

1.查询部门平均工资在2500元以上的部门名称及平均工资。

   SELECT dname,AVG(sal)

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  GROUP BY dname

HAVING AVG(sal)>2500;

2.查询员工岗位中不是以“SA”开头并且平均工资在2500员以上的岗位及平均工资，并按平均工资降序排序。

   SELECT job,AVG(sal)

   FROM emp

  WHERE job NOT LIKE 'SA%'

  GROUP BY job

HAVING AVG(sal)>2500

  ORDER BY AVG(sal) DESC;

3.查询部门人数在2人以上的部门名称，最低工资，最高工资，并对要求的工资进行四舍五入到整数位。

   SELECT dname,ROUND(MIN(sal),-1) 最低工资,

        ROUND(MAX(sal),-1) 最高工资

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  GROUP BY dname

HAVING COUNT(empno)>2;

4.查询岗位不为SALESMSMAN，工资和大于等于2500的岗位及每种岗位的工资和。

   SELECT job,SUM(sal)

  FROM emp

  WHERE job<>'SALESMSMAN'

  GROUP BY job

HAVING SUM(sal)>2500;

5.显示经理编号和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

   SELECT m.empno,m.ename,MIN(e.sal)

   FROM emp e,emp m

  WHERE e.mgr=m.empno(+)

  GROUP BY m.empno,m.ename

HAVING MIN(e.sal)>=3000

  ORDER BY MIN(e.sal) DESC;

6.写一个查询，显示每个部门最高工资和最低工资的差额。

   SELECT deptno,MAX(sal)-MIN(sal)

   FROM emp

  GROUP BY deptno;

第7章子查询
练习1

1.查询入职日期最早的员工姓名，入职日期。

   --最早的入职日期

   SELECT MIN(hiredate)

   FROM emp;

              

   SELECT ename,hiredate

    FROM emp

  WHERE hiredate=

                   (SELECT MIN(hiredate)

                  FROM emp);

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称。

   --SMITH的工资

   SELECT sal

   FROM emp

  WHERE ename='SMITH';

        

   SELECT ename,sal,dname

   FROM emp e,dept d

  WHERE sal>

            (SELECT sal

           FROM emp

          WHERE ename='SMITH')

  AND loc='CHICAGO';

3.查询入职日期比20部门入职日期最早的员工还早的员工姓名，入职日期。

   --20部门员工的最早入职日期

   SELECT MIN(hiredate)

   FROM emp

  WHERE deptno=20;

             

   SELECT ename,hiredate

   FROM emp

  WHERE hiredate<

                (SELECT MIN(hiredate)

                 FROM emp

                WHERE deptno=20);

4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数。

   --所有部门平均人数

   SELECT AVG(COUNT(empno))

  FROM emp

   GROUP BY deptno;

            

   SELECT d.deptno,dname,COUNT(empno)

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  GROUP BY d.deptno,dname

HAVING COUNT(empno)>

                      (SELECT AVG(COUNT(empno))

                        FROM emp

                       GROUP BY deptno);

练习2

1.查询入职日期比10部门任意一个员工晚的员工姓名，入职日期，不包括10部门员工。

   --10部门员工的最早入职日期

   SELECT MIN(hiredate)

   FROM emp

  WHERE deptno=10;



   SELECT ename,hiredate

   FROM emp

  WHERE hiredate>

                    (SELECT MIN(hiredate)

                  FROM emp

                 WHERE deptno=10)

  AND deptno<>10;

2.查询入职日期比10部门所有员工晚的员工姓名，入职日期，不包括10部门员工。

   --10部门最晚的员工入职日期

   SELECT MAX(hiredate)

   FROM emp

  WHERE deptno=10;



   SELECT ename,hiredate

   FROM emp

  WHERE hiredate>

                    (SELECT MAX(hiredate)

                   FROM emp

                  WHERE deptno=10)

  AND deptno<>10;

3.查询职位和10部门人员一个员工职位相同的员工姓名，职位，不包括10部门员工。

   --10部门员工职位

   SELECT job

   FROM emp

  WHERE deptno=10;



   SELECT ename,job

   FROM emp

  WHERE job IN

                (SELECT job

              FROM emp

              WHERE deptno=10)

   AND deptno<>10;

练习3

1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工。

   --10部门的全部员工职位及经理

   SELECT job,mgr

   FROM emp

   WHERE deptno=10;



   SELECT ename,job

  FROM emp

    WHERE (job,mgr) IN

                    (SELECT job,mgr

                      FROM emp

                      WHERE deptno=10)

    AND deptno<>10;

2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工。

   SELECT ename,job

  FROM emp

  WHERE (job IN

               (SELECT job

              FROM emp

             WHERE deptno=10)

   AND deptno<>10)

   OR (mgr IN

             (SELECT mgr

              FROM emp

             WHERE deptno=10)

 AND deptno<>10);

练习4

1.查询比自己职位平均工资高的员姓名，职位，部门名称，职位平均工资。

   --查询每个职位的平均工资

   SELECT job,AVG(sal) avgsal

   FROM emp

  GROUP BY job;



   SELECT ename,e.job,dname,a.avgsal

   FROM emp e,dept d,

             (SELECT job,AVG(sal) avgsal

         FROM emp

       GROUP BY job) a

WHERE e.deptno=d.deptno

 AND e.job=a.job

 AND e.sal>a.avgsal;

2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名，职位，不包括SCOTT和BLAKE本人。

   --员工SCOTT或BLAKE的职位和经理

   SELECT job,mgr

   FROM emp

  WHERE ename='SCOTT'

    OR ename='BLAKE';



   SELECT ename,job

   FROM emp

    WHERE (job,mgr) IN

                   (SELECT job,mgr

                    FROM emp

                   WHERE ename='SCOTT'

                       OR ename='BLAKE');

3.查询不是经理的员工姓名。

  SELECT ename

   FROM emp

  WHERE job<>'MANAGER';

练习5

1.查询入职日期最早的前5名员姓名，入职日期。

   SELECT ROWNUM,ename,hiredate

   FROM emp

  WHERE ROWNUM<=5

   ORDER BY hiredate;

2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。

   SELECT ROWNUM,ename,hiredate

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

   AND loc='CHICAGO'

  AND ROWNUM<=2

  ORDER BY hiredate;

练习6

1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名，入职日期，部门名称。

   SELECT ROWNUM rn,ename,hiredate,dname

   FROM emp e,dept d

  WHERE e.deptno=d.deptno



   --第一页

   SELECT a.*

 FROM (SELECT ROWNUM rn,ename,hiredate,dname

         FROM emp e,dept d

        WHERE e.deptno=d.deptno) a

WHERE rn<=5*1 AND rn>5*0;



   --第二页

   SELECT a.*

   FROM (SELECT ROWNUM rn,ename,hiredate,dname

         FROM emp e,dept d

        WHERE e.deptno=d.deptno) a

WHERE rn<=5*2 AND rn>5*1;



   --第三页

   SELECT a.*

   FROM (SELECT ROWNUM rn,ename,hiredate,dname

          FROM emp e,dept d

       WHERE e.deptno=d.deptno) a

WHERE rn<=5*3 AND rn>5*2;

练习7

1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名，入职日期，部门名称，工资。

   SELECT ROWNUM rn,ename,hiredate,dname,sal

   FROM emp e,dept d

  WHERE e.deptno=d.deptno

  ORDER BY sal DESC



   --第一页

   SELECT a.*

   FROM (SELECT ROWNUM rn,ename,hiredate,dname,sal

           FROM emp e,dept d

          WHERE e.deptno=d.deptno

          ORDER BY sal DESC) a

          WHERE rn<=5*1 AND rn>5*0;

    

   --第二页

   SELECT a.*

   FROM (SELECT ROWNUM rn,ename,hiredate,dname,sal

           FROM emp e,dept d

          WHERE e.deptno=d.deptno

          ORDER BY sal DESC) a

          WHERE rn<=5*2 AND rn>5*1;

    

   --第三页

   SELECT a.*

   FROM (SELECT ROWNUM rn,ename,hiredate,dname,sal

        FROM emp e,dept d

       WHERE e.deptno=d.deptno

       ORDER BY sal DESC) a

       WHERE rn<=5*3 AND rn>5*2;

课后作业

1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号，姓名及工资。

   --编号为7782的员工工资

   SELECT sal

   FROM emp

  WHERE empno='7782';



   --7369号员工从事的工作

   SELECT job

   FROM emp

  WHERE empno='7369';



   SELECT empno,ename,sal

   FROM emp

  WHERE sal>

           (SELECT sal

           FROM emp

          WHERE empno='7782')

   AND job=

            (SELECT job

            FROM emp

           WHERE empno='7369');

2.查询工资最高的员工姓名和工资。

   SELECT ename,sal

   FROM emp

    WHERE sal=

                (SELECT MAX(sal)

             FROM emp);

查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

--10号部门最低工资

SELECT MIN(sal)

FROM emp

WHERE deptno=10;



SELECT e.deptno,dname,MIN(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno,dname

HAVING MIN(sal)<

                (SELECT MIN(sal)

                FROM emp

             WHERE deptno=10);

4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

–部门最低工资的员工

SELECT deptno,MIN(sal)

FROM emp

GROUP BY deptno;

SELECT empno,ename,sal

FROM emp e,(SELECT deptno,MIN(sal) minsal

           FROM emp

          GROUP BY deptno) d

WHERE e.deptno=d.deptno

AND sal=minsal;

5.显示经理是KING的员工姓名，工资。

SELECT ename,sal

FROM emp

WHERE mgr=

        (SELECT empno

        FROM emp

        WHERE ename='KING');

6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

SELECT ename,sal,hiredate

FROM emp

WHERE hiredate>

             (SELECT hiredate

              FROM emp

             WHERE ename='SMITH');

7.使用子查询的方式查询哪些职员在NEW YORK工作。

SELECT *

FROM emp

WHERE deptno=

            (SELECT deptno

             FROM dept

            WHERE loc='NEW YORK');

8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

SELECT ename,hiredate

FROM emp

WHERE deptno=

            (SELECT deptno

             FROM emp

            WHERE ename='SMITH')

AND ename<>‘SMITH’;

9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

SELECT empno,ename

FROM emp

WHERE sal>

        (SELECT AVG(sal)

         FROM emp);

10.写一个查询显示其上级领导是King的员工姓名、工资。

SELECT ename,sal

FROM emp

WHERE mgr=

         (SELECT empno

          FROM emp

         WHERE ename='KING');

11.显示所有工作在RESEARCH部门的员工姓名，职位。

SELECT ename,job

FROM emp

WHERE deptno=

             (SELECT deptno

             FROM dept

            WHERE dname='RESEARCH');

12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal)>

              (SELECT AVG(sal)

               FROM emp

              WHERE deptno=20);

13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。

SELECT ename,sal,avgsal,sal-avgsal

FROM emp e,(SELECT deptno,AVG(sal) avgsal

           FROM emp

          GROUP BY deptno) d

WHERE e.deptno=d.deptno AND sal>avgsal;

14.列出至少有一个雇员的所有部门。

SELECT *

FROM dept

WHERE deptno IN

              (SELECT deptno

               FROM emp

              GROUP BY deptno

             HAVING COUNT(*)>0);

15.列出薪金比"SMITH"多的所有雇员。

SELECT *

FROM emp

WHERE sal>

         (SELECT sal

          FROM emp

         WHERE ename='SMITH');

16.列出入职日期早于其直接上级的所有雇员。

SELECT *

FROM emp e

WHERE hiredate<

              (SELECT m.hiredate

               FROM emp m

              WHERE e.mgr=m.empno);

SELECT DISTINCT *

FROM emp a,(SELECT e.mgr mno,m.hiredate mdate

            FROM emp e,emp m

           WHERE e.mgr=m.empno) b

WHERE a.mgr=b.mno

AND hiredate<b.mdate;

17.找员工姓名和直接上级的名字。

SELECT e.ename ,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+);

18.显示部门名称和人数。

SELECT dname,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY dname;

19.显示每个部门的最高工资的员工。

SELECT *

FROM emp

WHERE (deptno,sal) IN

                  (SELECT deptno,MAX(sal)

                   FROM emp

                  GROUP BY deptno);

20.显示出和员工号7369部门相同的员工姓名，工资。

SELECT ename,sal

FROM emp

WHERE deptno=

            (SELECT deptno

             FROM emp

            WHERE empno='7369');

21.显示出和姓名中包含"W"的员工相同部门的员工姓名。

SELECT ename

FROM emp

WHERE deptno=

            (SELECT deptno

             FROM emp

            WHERE ename LIKE '%W%');

22.显示出工资大于平均工资的员工姓名，工资。

SELECT ename,sal

FROM emp

WHERE sal>

        (SELECT AVG(sal)

          FROM emp);

23.显示出工资大于本部门平均工资的员工姓名，工资。

SELECT ename,sal

FROM emp e,(SELECT deptno,AVG(sal) avgsal

            FROM emp

           GROUP BY deptno) a

WHERE e.deptno=a.deptno

AND sal>avgsal;

24.显示每位经理管理员工的最低工资，及最低工资者的姓名。

SELECT sal,ename

FROM emp

WHERE (mgr,sal) IN

               (SELECT mgr,min(sal)

                FROM emp

               GROUP BY mgr);

25.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间

SELECT ename,hiredate

FROM emp

WHERE hiredate>

             (SELECT hiredate

               FROM emp

              WHERE sal=

                         (SELECT MAX(sal)

                           FROM emp));

26.显示出平均工资最高的的部门平均工资及部门名称

SELECT *

FROM (SELECT AVG(sal),dname

      FROM emp e,dept d

     WHERE e.deptno=d.deptno

     GROUP BY dname

     ORDER BY AVG(sal) DESC)

WHERE ROWNUM<=1;

第8章集合运算
练习1

1.分别使用联合运算及完全联合运算完成，按照时间升序顺序，查询员工7839的工作岗位列表。

   --集合运算

   SELECT hiredate,job

   FROM emp

  WHERE empno= 7839

   UNION

   SELECT begindate,job

   FROM emp_jobhistory

  WHERE empno =7839;

–完全联合运算

SELECT job,hiredate

FROM emp

WHERE empno=7839

UNION ALL

SELECT job,begindate

FROM emp_jobhistory

WHERE empno=7839

ORDER BY hiredate;

2.使用多表连接，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

SELECT d.deptno,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno;

3.使用联合运算，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

SELECT d.deptno,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.deptno

UNION

SELECT d.deptno,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno

HAVING COUNT(empno)=0;

4.使用联合运算，查询10号部门及20号部门的员工姓名，部门编号。

SELECT ename,deptno

FROM emp

WHERE deptno=10

UNION

SELECT ename,deptno

FROM emp

WHERE deptno=20;

使用集合运算，输出如下效果？

部门工作地点员工姓名入职日期

10 NEW YORK

10 CLARK 1981/6/9

10 KING 1981/11/17

10 MILLER 1982/1/23

20 DALLAS

20 ADAMS 1987/5/23

20 FORD 1981/12/3

20 JONES 1981/4/2

20 SCOTT 1987/4/19

20 SMITH 1980/12/17

30 CHICAGO

30 ALLEN 1981/2/20

30 BLAKE 1981/5/1

30 JAMES 1981/12/3

30 MARTIN 1981/9/28

30 TURNER 1981/9/8

30 WARD 1981/2/22

40 BOSTON

SELECT deptno 部门,NULL 工作地点,ename 员工姓名,hiredate 入职日期

FROM emp

UNION

SELECT deptno,loc,NULL,NULL

FROM dept

WHERE loc IN (‘NEW YORK’,‘DALLAS’,‘CHICAGO’,‘BOSTON’);

课后作业

1.用集合运算，列出不包含job为SALESMAN的部门的部门号。

SELECT deptno

FROM dept

MINUS

SELECT deptno

FROM emp

WHERE job=‘SALESMAN’;

2.写一个联合查询，列出下面的信息：

EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。

DEPT表中的所有部门编号和部门名称，不管他们是否有员工。

SELECT ename,deptno,NULL

FROM emp

UNION

SELECT NULL,deptno,dname

FROM dept

ORDER BY deptno,ename DESC;

3.用集合运算查询出职位为SALESMAN和部门编号为10的

人员编号、姓名、职位，不排除重复结果。

SELECT empno,ename,job

FROM emp

WHERE job=‘SALESMAN’

UNION ALL

SELECT empno,ename,job

FROM emp

WHERE deptno=10;

4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。

SELECT empno,ename,dname

FROM emp e,dept d

WHERE e.deptno=d.deptno AND e.deptno=10

UNION

SELECT empno,ename,job

FROM emp e,dept d

WHERE e.deptno=d.deptno AND e.deptno=20;

第9章高级子查询
练习1

如下练习，使用相关子查询完成

1.查询比所在职位平均工资高的员工姓名，职位

SELECT ename,job

FROM emp e

WHERE sal>

        (SELECT AVG(sal)

          FROM emp

         WHERE job=e.job);

2.查询工资为其部门最低工资的员工编号，姓名，工资。

SELECT empno,ename,sal

FROM emp e

WHERE sal=

        (SELECT MIN(sal)

          FROM emp

         WHERE deptno=e.deptno);

练习2

如下练习，用相关子查询完成

1.查询所有雇员编号，名字和部门名字。

SELECT empno,ename,

    (SELECT dname

      FROM dept

     WHERE e.deptno=deptno) 部门名字

FROM emp e;

2.查询哪些员工是经理？

SELECT *

FROM emp e

WHERE EXISTS

          (SELECT '1'

            FROM emp

           WHERE mgr=e.empno);

3.查询哪些员工不是经理？

SELECT *

FROM emp e

WHERE NOT EXISTS

              (SELECT '1'

                FROM emp

               WHERE mgr=e.empno);

4.查询每个部门工资最低的两个员工编号，姓名，工资。

SELECT empno,ename,sal

FROM emp e

WHERE 1>=

       (SELECT COUNT(empno)

         FROM emp

        WHERE deptno=e.deptno

          AND sal<e.sal)

练习3

如下练习，用exists或not exists完成

1.列出至少有一个雇员的所有部门名称。

SELECT dname

FROM dept d

WHERE EXISTS

          (SELECT '1'

            FROM emp

           WHERE deptno=d.deptno);

2.列出一个雇员都没有的所有部门名称。

SELECT dname

FROM dept d

WHERE NOT EXISTS

              (SELECT '1'

                FROM emp

               WHERE deptno=d.deptno);

课后作业：

如下练习，使用相关子查询完成。

1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。

SELECT ename,deptno

FROM emp e

WHERE sal>

        (SELECT AVG(sal)

          FROM emp

         WHERE job=e.job);

2.查询员工姓名和直接上级的名字。

SELECT ename,

    (SELECT ename

      FROM emp

     WHERE e.mgr=empno) 直接上级名字

FROM emp e;

3.查询每个部门工资最高的员工姓名，工资。

SELECT ename,sal

FROM emp e

WHERE sal=

       (SELECT MAX(sal)

           FROM emp

         WHERE deptno=e.deptno);

4.查询每个部门工资前两名高的员工姓名，工资。

SELECT empno,ename,sal

FROM emp e

WHERE 1>=

       (SELECT COUNT(empno)

         FROM emp

        WHERE deptno=e.deptno

          AND sal>e.sal)

第10章层次查询
课后作业

1.产生一个报告显示 BLAKE的所有下级（包括直接和间接下级）雇员的名字、薪水和部门号。

SELECT ename,sal,deptno

FROM emp

START WITH ename=‘BLAKE’

CONNECT BY PRIOR empno=mgr;

2.创建一个报告显示对于雇员 SMITH 经理的层次，包括级别和姓名，首先显示他的直接经理。

SELECT LEVEL,ename

FROM emp

WHERE ename<>‘SMITH’

START WITH ename=‘SMITH’

CONNECT BY PRIOR mgr=empno;

3.创建一个缩进报告显示经理层次，从名字为 KING的雇员开始，显示雇员的名字、经理ID和部门ID。

SELECT LPAD(ename, LENGTH(ename)+(LEVEL-1)2,’’) ename,mgr,deptno

FROM emp

START WITH ename=‘KING’

CONNECT BY PRIOR empno=mgr;

4.产生一个公司组织图表显示经理层次。从最顶级的人开始，排除所有job为CLERK的人，还要排除FORD和那些对FORD报告的雇员。

SELECT LPAD(ename, LENGTH(ename)+(LEVEL-1)2,’’) ename,mgr,deptno

FROM emp

WHERE job<>‘CLERK’

START WITH ename=‘KING’

CONNECT BY PRIOR empno=mgr

AND ename<>'FORD';

第11章数据操作与事务控制
练习1

1.向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

INSERT INTO dept

VALUES(50,‘HR’,‘SY’);

2.向部门表新增一个部门，部门编号为60，部门名称为MARKET。

INSERT INTO dept(deptno,dname)

VALUES(‘60’,‘MARKET’);

练习2

1.向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

INSERT INTO emp

VALUES(8888,‘BOB’,‘CLERK’,‘7788’,‘03-3月-1985’,3000,NULL,null);

练习3

1.使用CREATE TABLE emp_back as

  SELECT * FROM EMP WHERE 1=0，创建 emp_back表,拷贝下来即可。

CREATE TABLE emp_back

as

SELECT *

FROM EMP

WHERE 1=0;

2.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。

INSERT INTO emp_back

SELECT *

FROM emp

WHERE hiredate>=‘01-1月-82’;

练习4

1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

UPDATE emp

SET hiredate=hiredate+10

WHERE deptno=20

AND hiredate>‘31-12月-81’;

2.修改奖金为null的员工，奖金设置为0

UPDATE emp

SET comm=0

WHERE comm IS NULL;

3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE emp

SET sal=sal+500

WHERE deptno IN

            (SELECT deptno

               FROM  dept

              WHERE loc

                 IN ('CHICAGO','NEW YORK'));

练习5

1.重复做一下刚才的案例。

ALTER TABLE emp_back

ADD(dname varchar2(14));

UPDATE emp_back e

SET dname =

          (SELECT dname

             FROM dept d

            WHERE deptno=e.deptno);

练习6

1.删除经理编号为7566的员工记录

DELETE FROM emp

WHERE mgr=7566;

2.删除工作在NEW YORK的员工记录

DELETE FROM emp

WHERE deptno IN

            (SELECT deptno

               FROM dept

              WHERE loc='NEW YORK');

3.删除工资大于所在部门平均工资的员工记录

DELETE FROM emp

WHERE sal>

       (SELECT AVG(sal)

          FROM emp

         WHERE deptno=e.deptno);

练习7

分析如下语句序列，哪些语句会结束事务？

INSERT…
UPDATE…
INSERT
ROLLBACK;
DELETE…
DELETE…
SELECT…
COMMIT…
INSERT…
INSERT…
DELETE…
GRANT…
INSERT…
SELECT;

练习8

1.test表为空表，分析如下语句操作后，最后test表的状态。

INSERT INTO test(id,name) values(1, ‘a’)；
INSERT INTO test(id,name) values(2, ‘b’)；
SAVEPOINT s1;
INSERT INTO test(id,name) values(3, ‘c’)；
INSERT INTO test(id,name) values(4, ‘d’)；
DELETE FROM test WHERE id in (1,3);
ROLLBACK TO s1; --回滚到保存点s1，后两条数据插入无效
DELETE FROM test WHERE id in (2,4);
COMMIT; --将所有修改写入数据库
ROLLBACK; --所有操作已经提交，不能回滚

课后作业

1.使用如下语句，创建学生表student和班级表class

create table student ( --学生表

                 xh char(4),--学号

                 xm varchar2(10),--姓名

                 sex char(2),--性别

                 birthday date,--出生日期

                 sal number(7,2), --奖学金

                 studentcid number(2) --学生班级号

)

Create table class ( --班级表

                 classid number(2), --班级编号

                 cname varchar2(20),--班级名称

               ccount  number(3) --班级人数

)

2.基于上述学生表和班级表，完成如下问题

（1）添加三个班级信息为：1，JAVA1班，null

                     2，JAVA2班，null

                     3，JAVA3班，null

INSERT INTO class(classid,cname,ccount)

VALUES(1,‘java1班’,NULL);

INSERT INTO class(classid,cname,ccount)

VALUES(2,‘java2班’,NULL);

INSERT INTO class(classid,cname,ccount)

VALUES(3,‘java3班’,NULL);

（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1

INSERT INTO student

VALUES(‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1);

（3）添加学生信息如下：‘A002’,‘MIKE’,‘男’,‘1905-05-06’,10

INSERT INTO student

VALUES(‘A002’,‘MIKE’,‘男’,TO_DATE(‘1905-05-06’,‘YYYY-MM-DD’),10,NULL);

（4）插入部分学生信息: ‘A003’,‘JOHN’,‘女’

INSERT INTO student(xh,xm,sex)

VALUES(‘A003’,‘JOHN’,‘女’);

（5）将A001学生性别修改为’女’

UPDATE student

SET sex='女'

WHERE xh=‘A001’;

（6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01

UPDATE student

SET sex='男',birthday=TO_DATE('1980-04-01','YYYY-MM-DD')

WHERE xh=‘A001’;

（7）将生日为空的学生班级修改为java3班

UPDATE student

SET studentcid=

              (SELECT classid

                 FROM class

                WHERE cname='java3班')

WHERE birthday IS NULL;

（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段

UPDATE CLASS c

SET ccount=

          (SELECT COUNT(*)

             FROM student s

            WHERE c.classid= s.studentcid);

3.使用如下语句，建立以下表

CREATE TABLE copy_emp (

empno number(4),

ename varchar2(20),

hiredate date default sysdate ,

deptno number(2),

sal number(8,2))

4.在第三题表的基础上，完成下列问题

(1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意

INSERT INTO copy_emp

VALUES(1,‘张三’,‘01-1月-2000’,50,NULL);

(2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门

的员工信息插入

INSERT INTO copy_emp

SELECT empno,ename,hiredate,deptno,sal

FROM emp

WHERE deptno=10;

(3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资

UPDATE copy_emp

SET sal=sal*1.2

WHERE deptno=10;

(4)修改copy_emp表中sal为空的记录，工资修改为平均工资

UPDATE copy_emp

SET sal=

       (SELECT AVG(sal)

          FROM copy_emp)

WHERE sal IS NULL;

(5)把工资为平均工资的员工，工资修改为空

UPDATE copy_emp

SET sal=NULL,hiredate=default

WHERE sal=

       (SELECT AVG(sal)

          FROM copy_emp);

(6)另外打开窗口2查看以上修改

修改无效

(7)执行commit，窗口2中再次查看以上信息

COMMIT;

修改有效

(8)删除工资为空的员工信息

DELETE FROM copy_emp

WHERE sal IS NULL;

(9)执行rollback

ROLLBACK;

第13章创建和维护表
练习1

1.学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。

CREATE TABLE student(

   xh CHAR(4),

   xm VARCHAR(10),

   sex CHAR(2),

   birth DATE,

   classid BUMBER(2));

CREATE TABLE course(

   courseno NUMBER(4),

   coursename CHAR(30),

   teacher CHAR(10),);

练习2

1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。

CREATE TABLE dept10

AS

SELECT *

FROM emp

WHERE deptno=10;

练习3

1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”

ALTER TABLE emp

ADD gender CHAR(2)

DEFAULT ‘男’;

2.修改员工表中性别列的数据类型为char(4)

ALTER TABLE emp

MODIFY gender CHAR(4);

3.修改员工表中性别列的默认值为“女”

ALTER TABLE emp

MODIFY gender CHAR(4) DEFAULT’女’ ;

4.删除员工表中的性别列

ALTER TABLE emp

DROP (gender);

课后作业

1.请分析按照以下要求都需要建立什么类型的字段?

– (1)最大2000个字节定长字符串

CHAR(2000)

– (2)如果输入‘张三’ 后添空格6个

CHAR(10)

– (3)性别输入’男’或’女’

CHAR(2)

– (4)最大4000个字节变长字符串

VARCHAR2

– (5)如果在数据库中输入’张三’则显示数据’张三’

NVARCHAR2

– (6)表示数字范围为- 10的125次方到10的126次方, 可以表示小数也可以表示整数 NUMBER

– (7)最大表示4位整数 -9999 到 9999

NUMBER(4)

– (8)表示5位有效数字 2位小数的一个小数 -999.99 到 999.99

NUMBER(5,2)

– (9)包含年月日和时分秒

DATE(yyyymmddhhmiss)

– (10)包含年月日和时分秒毫秒

DATE(yyyymmddhhmissms)

– (11)二进制大对象图像/声音

BLOB

2.创建表date_test,包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。

CREATE TABLE date_test(

   d DATE

);

INSERT INTO date_test

VALUES(sysdate);

INSERT INTO date_test

VALUES(TO_DATE(‘1998-08-18’,‘yyyy-mm_dd’));

3.创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表

CREATE TABLE dtest

AS

SELECT *

FROM dept

WHERE deptno<40;

4.创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

CREATE TABLE emp1

AS

SELECT *

FROM emp

WHERE deptno<30;

5.试为学生表student增加一列学生性别gender 默认值 “女”。

ALTER TABLE student

ADD(gender CHAR(2) DEFAULT’女’);

6.试修改学生姓名列数据类型为定长字符型10位。

ALTER TABLE student

MODIFY(xm CHAR(10));

第14章约束
课后作业

1.简述5种约束的含义。

(1)NOT NULL约束：也叫非空约束，确保被约束列的所有行记录都不能为空值。

(2) UNIQUE约束：也叫唯一约束，用来确保表中的某一列或者某几列组合的所有行数据必须唯一，定义UNIQUE约束的列 (或列组合) 被称为唯一键。

(3) PRIMARY KEY约束：主键约束，用来确保表中的某一列或者某几列组合的所有行数据必须唯一，并且确保作为主键一部分的列不能包含空值；

(4)FOREIGN KEY，也叫外键约束，外键确保了相关联的两个字段的关系：

(5)CHECK约束，也叫检查性约束，确保某个列的所有行数据都必须满足的条件

2.创建学生关系sc，包括属性名：

– 选课流水号数值型主键；

– 学生编号非空外键

– 课程编号非空外键；

– 成绩 0-100之间；

CREATE TABLE sc(

   selectno NUMBER PRIMARY KEY,

   studentno VARCHAR(10) NOT NULL

   CONSTRAINT student_studentno_fk REFERENCES student(studentno),

   courseno VARCHAR(10) NOT NULL

   CONSTRAINT course_courseno_fk REFERENCES course(courseno),

   grade NUMBER

   CONSTRAINT sc_grade_ck CHECK(grade between 0 and 100));

3.创建copy_emp，要求格式同emp表完全一样，不包含数据。

CREATE TABLE copy_emp

AS

SELECT *

FROM emp

WHERE 1=2;

4.创建copy_dept，要求格式同dept表完全一样，不包含数据。

CREATE TABLE copy_dept

AS

SELECT *

FROM dept

WHERE 1=2;

5.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能否成功,为什么？

ALTER TABLE copy_emp

ADD CONSTRAINT pk_deptno PRIMARY KEY(deptno) REFERENCES copy_dept(deptno);

不能，因为已经关联了dept中的外键。

6.追加copy_dept表中主键deptno

ALTER TABLE copy_dept

ADD CONSTRAINT pk_depno PRIMARY KEY(deptno)

第15章视图
练习1

1.创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。

CREATE VIEW empv

AS

SELECT empno, ename, sal

FROM emp

WHERE sal BETWEEN 2000 AND 5000

AND ename LIKE ‘%A%’;

2.通过上述创建的视图查询数据

SELECT *

FROM empv;

练习2

1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。

CREATE VIEW ncemp

AS

SELECT empno,ename,e.deptno,hiredate

FROM emp e

JOIN dept d

ON e.deptno=d.deptno

AND loc IN(‘NEW YORK’,‘CHICAGO’);

2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。

CREATE VIEW empv2

AS

SELECT dname,min(e.sal) min_sal

FROM emp e

JOIN dept d

ON e.deptno=d.deptno

GROUP BY dname;

3.通过如上视图，查询每个部门工资最低的员工姓名及部门名称

SELECT e.ename, v.dname

FROM empv2 v

JOIN dept d

ON v.dname=d.dname

JOIN emp e

ON d.deptno=e.deptno

WHERE sal=v.min_sal;

课后作业

1.创建视图v_emp_20，包含20号部门的员工编号，姓名，年薪列(年薪=12*(工资+奖金）；

CREATE VIEW v_emp_20

AS

SELECT empno,ename,12*(sal+nvl(comm,0)) income

FROM emp;

2.从视图v_emp_20中查询年薪大于1万元员工的信息；

SELECT *

FROM v_emp_20

WHERE income>10000;

3.请为工资大于2000的员工创建视图，要求显示员工的部门信息，职位信息，工作地点；

CREATE VIEW empsal2000

AS

SELECT e.deptno,job,loc

FROM emp e

JOIN dept d

ON e.deptno=d.deptno

WHERE e.sal>2000;

4.针对以上视图执行insert,update,delete,语句能否成功，为什么？

在简单视图上可以执行 DML 操作；

(1)可以通过视图删除基表中数据，只要视图中不出现以下情况：

Group 函数；

   GROUP BY 子句；

DISTINCT 关键字；

(2)可以通过视图修改基表中数据，只要视图中不出现以下情况：

GROUP函数、GROUP BY子句，DISTINCT关键字；

使用表达式定义的列；

ROWNUM 伪列；

(3)可以通过视图向基表插入数据，只要视图中不出现以下情况：

 GROUP函数、GROUP BY子句，DISTINCT关键字；

 使用表达式定义的列；

 ROWNUM 伪列；

 基表中未在视图中选择的其它列定义为非空并且没有默认值；

第16章序列、索引、同义词
练习1

1.创建一个序列，该序列起始值从1开始，无最大值，增量是1，不循环。

CREATE SEQUENCE asd_seq;

2.查询序列的当前值及下一个值

SELECT asd_seq.currval

FROM DUAL;

SELECT asd_seq.nextval

FROM DUAL;

3.使用第1题所建的序列，向部门表中插入两条记录，部门编号使用序列值，部门名称分别为：Education、Market，城市分别为：DALLAS、WASHTON

INSERT INTO dept

VALUES(asd_seq.nextval,‘education’,‘dallas’);

INSERT INTO dept

VALUES(asd_seq.nextval,‘maeket’,‘washton’);

练习2

1.使用子查询的方式，创建test表。

CREATE TABLE test

AS

SELECT *

FROM emp;

2.快速复制test表中的数据，复制到100w条左右

INSERT INTO test

SELECT *

FROM emp;

3.更新test表中的empno字段为rownum

UPDATE test

SET empno=rownum;

4.查询test中empno为800000的记录值，记录查询执行时间。

SELECT *

FROM test

WHERE empno=800000;

5.在test表的empno字段上创建索引

CREATE INDEX IDX_TEST_EMPNO

ON test(empno);

6.重新执行第4题，对比查询时间

SELECT *

FROM TEST

WHERE empno=800000;

练习3

1.有如下关系模式，

– student(sno,sname,gender,birthday,email);–学生

– course(cno,cname,type,credit);–课程

– sc（sno,cno,grade);–选课

– 试分析哪些列上适合创建索引？

student:sno

course:cno

course:sno

课后作业

1.创建序列，起始位1，自增为1，最小值为1，最大值为9999

CREATE SEQUENCE test_seq2

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 9999

CYCLE

CACHE 10;

2.创建序列，起始值为50，每次增加5；

CREATE SEQUENCE test_seq3

 START WITH 50

INCREMENT BY 5

MINVALUE 50

NOCYCLE

CACHE 10;

3.在表copy_dept中插入记录，其中部门号码采用上一步中创建的序列生成；

INSERT INTO copy_dept(deptno,dname,loc)

VALUES(test_seq2.nextval,‘dd’,‘dd’);

4.请为工资创建索引，比较<10000,>1000,与round（sal）>10000,哪个索引有效，哪个索引无效；

CREATE INDEX indexsal

ON emp(sal);

SELECT sal

FROM emp

WHERE sal<10000; --速度较慢

SELECT sal

FROM emp

WHERE sal>1000; --速度较快

SELECT sal

FROM emp

WHERE round(sal)>10000; --有效

5.创建表，采用“create table copy_emp_index as select * from emp”，生成500万条数据，把其中的“员工号”字段修改为唯一；

CREATE TABLE copy_emp_index

AS

SELECT *

FROM emp;

ALTER TABLE copy_emp_index

ADD CONSTRAINT copy_emp_index_tb UNIQUE (empno);

6.查询表copy_emp_index表中员工号为200001的员工姓名，工资，记录执行时间；

SELECT ename,sal

FROM copy_emp_index

WHERE empno=200001;

7.在copy_emp_index表的empno字段上创建索引，再次执行第6题语句，记录执行时间并做对比；

CREATE SEQUENCE test_seq

 START WITH 1

INCREMENT BY 1

MAXVALUE 1000000000000

MINVALUE 1

 CYCLE

CACHE 100;

SELECT ename,sal

FROM copy_emp_index

WHERE empno=200001;

第1章 PLSQL基础知识
练习1

1.创建一个匿名块，在屏幕输出‘hello world’

DECLARE

v_hello varchar2(20) :='Hello World';

BEGIN

dbms_output.put_line(v_hello);

END;

练习2

1.写一个块，查询最大的部门编号，并在屏幕上输出该部门编号。

DECLARE

v_deptno dept.deptno%TYPE;

BEGIN

SELECT MAX(dept.deptno)

  INTO v_deptno

  FROM dept;

  dbms_output.put_line(v_deptno);

END;

练习3

1.写一个块，用来向10号部门入职一名新员工，员工编号为当前最大员工编号加1，员工姓名为JAMES，岗位为CLERK，入职日期为当前日期，工资为4000，上级为SMITH，奖金为null

DECLARE

v_empno emp.empno%TYPE;--最大员工编号

v_mgr emp.mgr%TYPE;--上级编号

BEGIN

SELECT MAX(emp.empno)

  INTO v_empno

  FROM emp;



SELECT empno

  INTO v_mgr

  FROM emp

 WHERE ename='SMITH';

 

          INSERT INTO emp

          VALUES(v_empno+1,'JAMES','CLERK',v_mgr,SYSDATE,4000,NULL,10);

COMMIT;

END;

课后作业

1、创建一个匿名块，查询emp表，显示雇员名是’SCOTT‘的薪水，通过DBMS_OUTPUT包来显示。

DECLARE

v_sal emp.sal%TYPE;

BEGIN

SELECT sal

  INTO v_sal

  FROM emp

 WHERE ename='SCOTT';

  dbms_output.put_line(v_sal);

END;

2、创建一个匿名块，使用 SQL*Plus的替代变量emp_num (雇员编号）,查询emp表，通过外部变量显示对应的雇员名

SELECT *

FROM emp

WHERE empno=&emp_num;

3、创建和emp表结构一样的test表,不要求有数据

CREATE TABLE test

AS

SELECT *

FROM emp

WHERE 1=2;

4、创建pl/sql块，将emp表中最高薪水员工的信息插入到test表中

BEGIN

          INSERT INTO test

          SELECT *

  FROM emp e

 WHERE 1>

         (SELECT COUNT(*)

            FROM emp

           WHERE sal>e.sal);

COMMIT;

END;

5、创建pl/sql块，将emp表中员工的平均薪水更新到test表中，并打印平均薪水

DECLARE

v_sal emp.sal%TYPE;

BEGIN

SELECT AVG(sal)

  INTO v_sal

  FROM emp;

  dbms_output.put_line(v_sal);



          UPDATE test

             SET sal=v_sal;

COMMIT;

END;

第2章编写控制结构
练习1

1.写SQL语句，向部门表中添加一个字段maxnumber 整型，表示部门编制人数。

ALTER TABLE dept

ADD(maxnumber INTEGER);

2.把10号部门的编制更新为5人。

UPDATE dept

SET maxnumber=5

WHERE deptno=10;

3.写一个块，用来向10号部门入职一名新员工，员工编号为当前最大员工编号加1，员工姓名为TOM，岗位为CLERK，其它字段都为null；当10号部门最大的人数不超过编制人数时，入职成功；当部门的人数超过编制人数时，提示入职失败。

DECLARE

v_empno emp.empno%TYPE;--最大员工编号

v_empnum NUMBER;--部门人数

v_maxnumber dept.maxnumber%TYPE;--编制人数

BEGIN

 SELECT MAX(emp.empno)

   INTO v_empno

  FROM emp;

             

    INSERT INTO emp(empno,ename,job,deptno)

    VALUES(v_empno+1,'TOM','CLERK',10);



 SELECT maxnumber

   INTO v_maxnumber

   FROM dept

  WHERE deptno=10;



  SELECT COUNT(empno)

    INTO v_empnum

   FROM emp

  WHERE deptno=10;

 

 IF v_empnum>v_maxnumber THEN

   dbms_output.put_line('入职失败');

   ROLLBACK;

 ELSE

   dbms_output.put_line('入职成功');

   COMMIT;

 END IF;

END;

练习2

1.使用简单循环，批量入职5名员工，员工编号分别为当前最大编号加1，部门为20号部门，姓名为zs1,zs2…，入职日期为当前日期，其它字段为null，暂时不判断人数是否超编。

DECLARE

v_empno emp.empno%TYPE;--最大员工编号

v_count NUMBER :=1;--计数器

BEGIN

 SELECT MAX(emp.empno)

   INTO v_empno

  FROM emp;

             

   LOOP

  INSERT INTO emp(empno,deptno,ename,hiredate)

  VALUES(v_empno+v_count,20,'ZS'||to_char(v_count),SYSDATE);

  v_count :=v_count+1;

  EXIT WHEN v_count>5;

END LOOP;

COMMIT;

END;

练习3

1.使用for循环，遍历员工信息，依次输出每个员工的姓名及部门名称。

BEGIN

FOR emp_record IN

                            (SELECT ename,dname

                           FROM emp e,dept d

                           WHERE e.deptno=d.deptno) LOOP

  dbms_output.put_line(emp_record.ename||'的部门是'||emp_record.dname); 

END LOOP;

END;

2.使用while循环，遍历员工信息，依次输出每个员工的姓名及部门名称。

DECLARE

v_ename emp.ename%TYPE;

v_dname dept.dname%TYPE;

CURSOR emp_cursor IS

SELECT ename,dname

 FROM emp e,dept d

WHERE e.deptno=d.deptno;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_dname;

WHILE emp_cursor%found LOOP

  dbms_output.put_line(v_ename||'的部门是'||v_dname); 

  FETCH emp_cursor INTO v_ename,v_dname;

END LOOP;

END;

课后作业

1.使用外部替代变量提供雇员的ID，传递该值到PL/SQL块，查询emp表的薪水，如果薪水小于2000的，显示‘挣的不多，需努力’；如果薪水在2000到5000的，显示‘收入还可以，还需努力’；薪水大于5000的显示’挣的挺多了，歇歇吧‘。

DECLARE

v_sal emp.sal%TYPE;

BEGIN

 SELECT sal

  INTO v_sal

  FROM emp

 WHERE empno=&ID;



IF v_sal<2000 THEN

  dbms_output.put_line('挣的不多，需努力');     

ELSIF v_sal BETWEEN 2000 AND 5000 THEN

  dbms_output.put_line('收入还可以，还需努力');     

ELSIF v_sal>5000 THEN

  dbms_output.put_line('挣的挺多了，歇歇吧');

END IF;

END;

2.屏幕上输出1到10（不包括6和8)

DECLARE

v_num NUMBER :=1;

BEGIN

WHILE v_num<=10 LOOP

  IF v_num NOT IN(6,8) THEN

    dbms_output.put_line(v_num);

  END IF;

  v_num :=v_num+1;

END LOOP;

END;

3.使用FOR循环和while循环，分别实现练习2的批量员工入职功能。

DECLARE

v_empno emp.empno%TYPE;--最大员工编号

v_count NUMBER :=1;--计数器

BEGIN

 SELECT MAX(emp.empno)

   INTO v_empno

  FROM emp;

             

   FOR v_count IN 1..5 LOOP

  INSERT INTO emp(empno,deptno,ename,hiredate)

  VALUES(v_empno+v_count,20,'ZS'||to_char(v_count),SYSDATE);

END LOOP;

COMMIT;

END;

DECLARE

v_empno emp.empno%TYPE;--最大员工编号

v_count NUMBER :=1;--计数器

BEGIN

 SELECT MAX(emp.empno)

   INTO v_empno

  FROM emp;

             

   FOR v_count <=5 LOOP

  INSERT INTO emp(empno,deptno,ename,hiredate)

  VALUES(v_empno+v_count,20,'ZS'||to_char(v_count),SYSDATE);

  v_count :=v_count+1;

END LOOP;

COMMIT;

END;

第3章游标
练习1

读程序，写结果：

1.假设10号部门有3名员工，20号部门有5名员工，30号部门有6名员工。如下代码段执行后，

Declare

v_count Number(2);

Begin

Select Count(empno) Into v_count

From emp Where deptno=10;

IF SQL%Rowcount>0  THEN

  Delete From emp Where deptno = 20;

ELSE

  Delete From emp Where deptno = 30;

END IF;

dbms_output.PUT_LINE(SQL%Rowcount);

End;

程序输出为：5

2.假设部门表中没有部门编号为60的记录，执行如下代码后，

Begin

   Insert Into dept(deptno,dname,loc)

          Values(60,’HR’,’SY’);

   If SQL%Rowcount>0 Then

          dbms_output.PUT_LINE(‘插入部门成功’);

   Else

    dbms_output.PUT_LINE(‘插入部门失败’);

   End If;

End

程序输出为：插入部门成功

BEGIN

DELETE FROM emp where deptno = 100;

dbms_output.PUT_LINE(‘游标所影响的行数：’||SQL%ROWCOUNT);

IF SQL%FOUND THEN

DBMS_output.PUT_LINE('Found为真');

ELSE

DBMS_output.PUT_LINE('Found为假');

END IF;

IF SQL%NOTFOUND then

DBMS_output.PUT_LINE('NotFound为真');

ELSE

DBMS_output.PUT_LINE('NotFound为假');

END IF;

IF SQL%ISOPEN THEN

DBMS_output.PUT_LINE('isOpen为真');

ELSE

DBMS_output.PUT_LINE('isOpen为假');

END IF;

END;

输出结果：

   游标所影响的行数：0

   Found为假

   NotFound为真

   isOpen为假

BEGIN

INSERT INTO dept VALUES(2,‘AA’,‘SY’);

dbms_output.PUT_LINE(‘游标所影响的行数：’||SQL%ROWCOUNT);

IF SQL%FOUND THEN

DBMS_output.PUT_LINE('Found为真');

ELSE

DBMS_output.PUT_LINE('Found为假');

END IF;

IF SQL%NOTFOUND then

DBMS_output.PUT_LINE('NotFound为真');

ELSE

DBMS_output.PUT_LINE('NotFound为假');

END IF;

IF SQL%ISOPEN THEN

DBMS_output.PUT_LINE('isOpen为真');

ELSE

DBMS_output.PUT_LINE('isOpen为假');

END IF;

END;

输出结果：

   游标所影响的行数：1

   Found为真

   NotFound为假

   isOpen为假

练习2

使用游标，完成遍历所有员工姓名和部门名称的操作。

DECLARE

v_ename emp.ename%TYPE;

v_dname dept.dname%TYPE;

v_count NUMBER;

CURSOR emp_cursor

     IS

SELECT ename,dname

 FROM emp e,dept d

WHERE e.deptno=d.deptno;

BEGIN

SELECT COUNT(empno)

  INTO v_count

 FROM emp e; 



OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_dname;

FOR i IN 1..v_count LOOP

  dbms_output.put_line(v_ename||'的部门是'||v_dname); 

  FETCH emp_cursor INTO v_ename,v_dname;

END LOOP;

END;

练习3

1.使用游标属性，完成遍历所有员工姓名和部门名称的操作。

DECLARE

v_ename emp.ename%TYPE;

v_dname dept.dname%TYPE;

CURSOR emp_cursor IS

SELECT ename,dname

 FROM emp e,dept d

WHERE e.deptno=d.deptno;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_dname;

WHILE emp_cursor%found LOOP

  dbms_output.put_line(v_ename||'的部门是'||v_dname);  

  FETCH emp_cursor INTO v_ename,v_dname;

END LOOP;

END;

练习4

1.使用游标和记录联合方式，完成遍历所有员工姓名和部门名称的操作。

DECLARE

CURSOR emp_cursor IS

SELECT ename,dname

             FROM emp e,dept d

            WHERE e.deptno=d.deptno;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO emp_record;

WHILE emp_cursor%found LOOP

  dbms_output.put_line(emp_record.ename||'的部门是'||emp_record.dname); 

  FETCH emp_cursor INTO emp_record;

END LOOP;

END;

2.使用游标式的FOR循环方式，完成遍历所有员工姓名和部门名称的操作。

DECLARE

v_ename emp.ename%TYPE;

v_dname dept.dname%TYPE;

v_count NUMBER;

CURSOR emp_cursor IS



SELECT ename,dname

 FROM emp e,dept d

WHERE e.deptno=d.deptno;

BEGIN

SELECT COUNT(empno)

  INTO v_count

 FROM emp e; 



OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_dname;

FOR i IN 1..v_count LOOP

  dbms_output.put_line(v_ename||'的部门是'||v_dname); 

  FETCH emp_cursor INTO v_ename,v_dname;

END LOOP;

END;

3.使用不需声明的游标方式，完成遍历所有员工姓名和部门名称的操作。

BEGIN

FOR emp_record IN

         (SELECT ename,dname

             FROM emp e,dept d

            WHERE e.deptno=d.deptno) LOOP

  dbms_output.put_line(emp_record.ename||'的部门是'||emp_record.dname); 

END LOOP;

END;

练习5

1.使用带参数的游标，分别遍历每个部门的员工姓名及入职日期，要求显示格式如下：

------X号部门的员工列表---------

姓名：xx，入职日期：xx

…………………….

小计：X号部门员工数为：xxx人

------X号号部门的员工列表---------

姓名：xx，入职日期：xx

…………………….

小计：x号部门员工数为：xxx人

------x号号部门的员工列表---------

姓名：xx，入职日期：xx

…………………….

小计：x号部门员工数为：xxx人

DECLARE

v_ename emp.ename%TYPE;

v_hiredate emp.hiredate%TYPE;

v_count NUMBER;



CURSOR emp_cursor1(p_deptno NUMBER) --员工信息

    IS

SELECT ename,hiredate

 FROM emp

WHERE deptno=p_deptno;

BEGIN

  dbms_output.put_line('------10号号部门的员工列表---------');  

  OPEN emp_cursor1(10);

  FETCH emp_cursor1 INTO v_ename,v_hiredate;

  WHILE emp_cursor1%found LOOP

    dbms_output.put_line('姓名：'||v_ename||',入职日期：'||v_hiredate); 

    FETCH emp_cursor1 INTO v_ename,v_hiredate;  

  END LOOP;

  CLOSE emp_cursor1;     

  SELECT COUNT(empno)

    INTO v_count

    FROM emp e

   WHERE deptno=10;

  dbms_output.put_line('…………………….');

  dbms_output.put_line('小计：10号部门员工数为：'||v_count||'人');

  dbms_output.put_line('');

 

  dbms_output.put_line('------30号号部门的员工列表---------'); 

  OPEN emp_cursor1(30);

  FETCH emp_cursor1 INTO v_ename,v_hiredate;

  WHILE emp_cursor1%found LOOP

    dbms_output.put_line('姓名：'||v_ename||',入职日期：'||v_hiredate); 

    FETCH emp_cursor1 INTO v_ename,v_hiredate;  

  END LOOP;

  CLOSE emp_cursor1;     

  SELECT COUNT(empno)

    INTO v_count

    FROM emp e

   WHERE deptno=30;

  dbms_output.put_line('…………………….');

  dbms_output.put_line('小计：30号部门员工数为：'||v_count||'人');

  dbms_output.put_line('');

 

  dbms_output.put_line('------没有号号部门的员工列表---------'); 

  SELECT ename,hiredate

    INTO v_ename,v_hiredate

    FROM emp

   WHERE deptno IS NULL;

  dbms_output.put_line('姓名：'||v_ename||',入职日期：'||v_hiredate); 

  SELECT COUNT(empno)

    INTO v_count

    FROM emp e

   WHERE deptno IS NULL;

  dbms_output.put_line('…………………….');

  dbms_output.put_line('小计：没有部门员工数为：'||v_count||'人');

  dbms_output.put_line('');

END;

DECLARE

v_ename emp.ename%TYPE;

v_hiredate emp.hiredate%TYPE;

v_count NUMBER;

p_deptno emp.deptno%TYPE;



CURSOR dept_cursor --部门

    IS

SELECT DISTINCT deptno

 FROM dept;



CURSOR emp_cursor(p_deptno dept.deptno%Type) --员工信息

    IS

SELECT ename,hiredate

 FROM emp

WHERE deptno=p_deptno;

BEGIN

FOR dept_record IN dept_cursor LOOP

   dbms_output.put_line('------'||dept_record.deptno||'号部门的员工列表---------');

  

   FOR emp_record IN emp_cursor(dept_record.deptno) LOOP

     dbms_output.put_line('姓名：'||emp_record.ename||',入职日期：'||emp_record.hiredate); 

   END LOOP;

  

   SELECT COUNT(*)

    INTO v_count

    FROM emp e

   WHERE deptno=dept_record.deptno;

   dbms_output.put_line('…………………….');

   dbms_output.put_line('小计：'||dept_record.deptno||'号部门员工数为：'||v_count||'人');    

   dbms_output.put_line('');

END LOOP;

END;

练习6

如下两个块代码执行后结果分别是？

DECLARE

CURSOR sal_cursor IS

  SELECT   sal FROM    emp WHERE    deptno = 30 FOR UPDATE OF sal NOWAIT;

BEGIN

FOR emp_record IN sal_cursor LOOP

  UPDATE  emp SET   sal = emp_record.sal * 1.10;

END LOOP;

END;

执行结果：所有员工的工资都变成了30号部门最后一名员工的工资的110%。

DECLARE

CURSOR sal_cursor IS

  SELECT   sal FROM    emp WHERE    deptno = 30 FOR UPDATE OF sal NOWAIT;

BEGIN

FOR emp_record IN sal_cursor LOOP

  UPDATE  emp SET   sal = emp_record.sal * 1.10 WHERE CURRENT OF sal_cursor;

END LOOP;

END;

执行结果： 30号部门的员工工资上升10%。

第4章异常处理
练习1

1.编写一个块，实现员工入职功能：其中员工编号为7839，姓名为张三，工资为3000，入职日期为系统当前日期，职位为CLERK，奖金为null，所在部门为SALES ，上级经理为JONES ，要求写出所有可能发生的异常处理程序。

DECLARE

v_mgrno emp.empno%Type;

v_deptno dept.deptno%Type;

BEGIN

SELECT empno

  INTO v_mgrno

  FROM emp

 WHERE ename='JONES';



SELECT deptno

  INTO v_deptno

  FROM dept

 WHERE dname='SALES';



INSERT INTO emp(empno,ename,job,hiredate,mgr,sal,comm,deptno)

VALUES(7839,'张三','CLERK',SYSDATE,v_mgrno,3000,NULL,v_deptno);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

    ROLLBACK;

    dbms_output.put_line('员工编号重复，不能办理入职');

END;

练习2

1.编写第4章异常处理一个匿名块，完成删除一个部门的功能，要有相应的异常处理程序。

DECLARE

e_haveempexception EXCEPTION;

PRAGMA EXCEPTION_INIT(e_haveempexception ,-02292);

BEGIN

DELETE FROM dept WHERE deptno=10;

COMMIT;

dbms_output.put_line('操作成功');

EXCEPTION

WHEN OTHERS THEN

    ROLLBACK;

    dbms_output.put_line('该部门下存在员工，不能删除部门');

END;

练习3

编写一个匿名块，分别对部门做一个查询、插入、修改、删除操作，验证这几个操作执行后的SQLCODE和SQLERRM结果。

DECLARE

v_dname dept.dname%TYPE;

BEGIN

SELECT dname

  INTO v_dname

  FROM dept

 WHERE deptno = 10;

dbms_output.put_line('查询成功的SQLCODE：'||SQLCODE);

dbms_output.put_line('查询成功的SQLerrm：'||SQLERRM);



SELECT dname

  INTO v_dname

  FROM dept

 WHERE deptno = 1; 



INSERT INTO dept(deptno,dname,loc)

VALUES(1,'a',NULL);

dbms_output.put_line('插入成功的SQLCODE：'||SQLCODE);

dbms_output.put_line('插入成功的SQLerrm：'||SQLERRM);



INSERT INTO dept(deptno,dname,loc)

VALUES(1,'b',NULL);



UPDATE dept

   SET dname = dname ;

dbms_output.put_line('更新成功的SQLCODE：'||SQLCODE);

dbms_output.put_line('更新成功的SQLerrm：'||SQLERRM);



DELETE dept SET deptno = 1 ;

dbms_output.put_line('删除成功的SQLCODE：'||SQLCODE);

dbms_output.put_line('删除成功的SQLerrm：'||SQLERRM);

EXCEPTION

WHEN no_data_found THEN

         dbms_output.put_line('查询失败的SQLCODE：'||SQLCODE);

         dbms_output.put_line('查询失败的SQLerrm：'||SQLERRM);

END;

练习4

修改员工入职的功能，当入职部门的人数超编时，提示用户部门以超编，不能再录入员工。

DECLARE

v_max dept.maxnumber%TYPE;

v_current v_max%TYPE;

v_empno emp.empno%TYPE;

e_havemaxnumber EXCEPTION;

BEGIN

--查询10号部门的编制

SELECT maxnumber

  INTO v_max

  FROM dept

 WHERE deptno = 10;



--查询10号部门的当前人数

SELECT COUNT(empno)

  INTO v_current

  FROM emp

 WHERE deptno = 10;



IF v_max-v_current > 0 THEN

  --说明有剩余编制，可以实现入职操作

  SELECT MAX(empno)

    INTO v_empno

    FROM emp;

   

  INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

  VALUES(v_empno+1,'TOM','CLERK',NULL,NULL,NULL,NULL,10);

  COMMIT;

  dbms_output.put_line('入职成功');                

ELSE

  RAISE e_havemaxnumber;      

END IF;

EXCEPTION

WHEN e_havemaxnumber THEN

  dbms_output.put_line('当前部门编制人数已满,不能继续入职操作');

END;

课后作业

1、创建表message(result varchar2(100)),存放状态信息

CREATE TABLE message(RESULT VARCHAR(100));

2、写PL/SQL块，传递不同的sal值到块中，根据传递的sal值进行查询：

   当emp表中没有该sal值时，引发异常，在异常部分将‘没有雇员挣sal的薪水’信息插入到message表中，并显示该信息；

   当emp表中有多于一个sal值时，引发异常，在异常部分将‘太多的雇员挣sal的薪水’信息插入到message表中，并显示该信息；

   当只有一个雇员具有该工资时，则输出雇员名和工资。

DECLARE

e_noequal EXCEPTION;   

e_toomanyupper EXCEPTION;

v_ename emp.ename%TYPE;

v_count NUMBER;

BEGIN

SELECT COUNT(empno)

  INTO v_count

  FROM emp

 WHERE sal = &sal;



IF v_count = 0 THEN

    RAISE e_noequal;

ELSIF v_count >1 THEN

    RAISE e_toomanyupper;

ELSE

    SELECT ename INTO v_ename FROM emp WHERE sal = &sal;

    dbms_output.put_line(v_ename ||','|| &sal);

END IF;

EXCEPTION

WHEN e_noequal THEN

    INSERT INTO message

    VALUES('没有雇员挣sal的薪水');

    COMMIT;

    dbms_output.put_line('没有雇员挣sal的薪水');

WHEN e_toomanyupper THEN

   INSERT INTO message

   VALUES('太多的雇员挣sal的薪水');

   COMMIT;

   dbms_output.put_line('太多的雇员挣sal的薪水');

END;

3、根据员工号，获得员工到目前为止参加工作年限（保留到整数），员工号不存在时提示“此员工号不存在”

DECLARE

v_year NUMBER(3);

BEGIN

SELECT ROUND(months_between(SYSDATE,hiredate)/12)

  INTO v_year

FROM emp WHERE empno = &empno;

dbms_output.put_line(v_year);

EXCEPTION

WHEN no_data_found THEN

    dbms_output.put_line('此员工号不存在') ;

END;

4、编写PL/SQL块，使用SELECT语句将管理者编号为空的员工的姓名及工作编号显示出来，如果符合条件的员工多于一人，则返回字符串“最高管理者人员过多！”字符串，如果找到没有符合条件的记录，则返回字符串“没有最高管理者，请指定”

DECLARE

v_ename emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

SELECT ename,job

  INTO v_ename,v_job

  FROM emp

 WHERE mgr IS NULL;

dbms_output.put_line(v_ename || ',' || v_job);

EXCEPTION

WHEN too_many_rows THEN

    dbms_output.put_line('最高管理者人员过多');

WHEN no_data_found THEN

    dbms_output.put_line('没有最高管理者，请指定');

END;

5、获得每个部门的平均工资，如果平均工资大于15000，视为用户定义的异常，提示“该部门的平均工资过高”

DECLARE

e_high_sal EXCEPTION;

BEGIN

FOR v_count IN

              (SELECT deptno,AVG(sal) avgsal

                 FROM emp

                GROUP BY deptno) LOOP

    IF v_count.avgsal>15000 THEN

       RAISE e_high_sal;

       dbms_output.put_line(v_count.deptno);

    END IF;                

END LOOP;

EXCEPTION

WHEN e_high_sal THEN

    dbms_output.put_line('该部门的平均工资过高');

END;

6、统计大于平均薪水的员工数量,如果数量大于5,触发e_too_many异常,显示’大于平均工资的人数不少’;如果数量小于等于5,触发e_too_low异常,显示’大于平均工资的人数太少

DECLARE

v_count_empno NUMBER;

v_avg_sal emp.sal%TYPE;

e_too_many EXCEPTION;

e_too_low EXCEPTION;

BEGIN

SELECT AVG(sal)

  INTO v_avg_sal

  FROM emp;

 

SELECT COUNT(empno)

  INTO v_count_empno

  FROM emp

  WHERE sal>v_avg_sal;

 

IF v_count_empno<=5 THEN

    RAISE e_too_low;

ELSIF v_count_empno>5 THEN

    RAISE e_too_many;

END IF;

EXCEPTION

WHEN e_too_many THEN

    dbms_output.put_line('大于平均工资的人数太少');

   

WHEN e_too_low THEN

    dbms_output.put_line('大于平均工资的人数不少');

END;

第5章存储过程
练习1

1.写一个存储过程getAllDept,遍历所有部门。

CREATE OR REPLACE PROCEDURE getAllDept

IS

    CURSOR dept_cursor

        IS

           SELECT deptno,dname ,loc

             FROM dept;

BEGIN

FOR dept_record IN dept_cursor LOOP

    dbms_output.put_line('编号为'||dept_record.deptno ||'部门的名称为：'||dept_record.dname);

END LOOP;

END;

–调用

BEGIN

getAllDept;

END;

练习2

1.写一个存储过程addDept，实现添加一个部门功能，部门编号为当前最大部门编号加1，其它信息通过in模式参数传入。

CREATE OR REPLACE PROCEDURE addDept(p_dname dept.dname%TYPE,

                                p_loc dept.loc%TYPE)

IS

    v_deptno dept.deptno%TYPE;

BEGIN

SELECT MAX(deptno)

  INTO v_deptno from dept;

INSERT INTO dept(deptno,dname,loc)

VALUES(v_deptno+1,p_dname,p_loc);



IF SQL%ROWCOUNT>0 THEN

    COMMIT;

    dbms_output.put_line('新建部门成功');

ELSE

    ROLLBACK;

    dbms_output.put_line('新建部门失败'||SQLERRM);

END IF;

END;

–调用

BEGIN

addDept('AAA','AAA');

END;

3.写一个存储过程updateDept，实现根据编号修改部门功能。

CREATE OR REPLACE PROCEDURE updateDept(p_deptno IN dept.deptno%TYPE,

                                   p_dname IN dept.dname%TYPE,

                                   p_loc IN dept.loc%TYPE)

IS

    v_count NUMBER(2);

BEGIN

SELECT COUNT(deptno)

  INTO v_count

  FROM dept

  WHERE deptno=p_deptno;

      

IF v_count>0 THEN

    UPDATE dept

       SET dname=p_dname,loc=p_loc

     WHERE deptno=p_deptno;       

IF SQL%ROWCOUNT>0 THEN

    COMMIT;

    dbms_output.put_line('修改部门成功');

ELSE

    ROLLBACK;

    dbms_output.put_line('修改部门失败'||SQLERRM);     

END IF;   

ELSE

    dbms_output.put_line(p_deptno||'是一个无效的部门编号');     

END IF;

END;

–调用

BEGIN

updateDept(81,'BBB','BBB');

END;

SELECT * FROM dept;

2.写一个存储过程getDeptByID，实现根据编号读取部门信息功能。

CREATE OR REPLACE PROCEDURE getDeptByID(p_deptno IN OUT dept.deptno%TYPE,

                                    p_dname OUT dept.dname%TYPE,

                                    p_loc OUT dept.loc%TYPE)

IS

BEGIN

SELECT dname,loc

  INTO p_dname,p_loc

  FROM dept

 WHERE deptno=p_deptno;

dbms_output.put_line('部门号：'||p_deptno||' 名称：'||p_dname||' 地点：'||p_loc);

END;

–调用

DECLARE

p_deptno dept.deptno%TYPE :=10;

p_dname dept.dname%TYPE;

p_loc dept.loc%TYPE;

BEGIN

getDeptByID(p_deptno,p_dname,p_loc);

END;

4.写一个存储过程delDept，实现根据部门编号删除部门功能，当部门中存在员工时，提示该部门不能删除。

CREATE OR REPLACE PROCEDURE delDept(p_deptno IN dept.deptno%TYPE)

IS

    v_count NUMBER(2);

BEGIN

SELECT COUNT(empno)

  INTO V_count

  FROM emp

 WHERE deptno=p_deptno;



IF v_count >0 THEN

    dbms_output.put_line('该部门下存在员工，不能删除');

ELSE

    DELETE FROM dept WHERE deptno=p_deptno;

    IF SQL%ROWCOUNT >0 THEN

        COMMIT;

        dbms_output.put_line('删除部门成功');

    ELSE

        ROLLBACK;

        dbms_output.put_line('删除部门失败'||SQLERRM);     

    END IF;   

END IF;

END;

–调用

BEGIN

delDept(10);

END;

5.写一个存储过程getAllEmpByID，实现根据部门编号查询部门所有员工信息功能,要求带分页功能。

CREATE OR REPLACE PROCEDURE getAllEmpByID(p_deptno IN dept.deptno%TYPE,

                                      p_pageNo IN NUMBER,

                                      p_pageCount IN NUMBER)

   CURSOR emp_cur

       IS

           SELECT b.*

             FROM (SELECT ROWNUM rn,ename,dname

                     FROM emp e,dept d

                    WHERE e.deptno=d.deptno

                      AND e.deptno=p_deptno

                      AND ROWNUM<=p_pageNo*p_pageCount) b

                    WHERE rn>(p_pageNo-1)*p_pageCOunt;

BEGIN

FOR emp_record IN emp_cur LOOP

    dbms_output.put_line(p_deptno||'号部门员工为：'||emp_record.ename);

END LOOP;

END;

–调用

BEGIN

getAllEmpByID(10,3,1);

END;

6.写一个块，分别调用上述过程。

BEGIN

addDept('AAA','AAA');

END;

BEGIN

updateDept(81,'BBB','BBB');

END;

DECLARE

p_deptno dept.deptno%TYPE :=10;

p_dname dept.dname%TYPE;

p_loc dept.loc%TYPE;

BEGIN

getDeptByID(p_deptno,p_dname,p_loc);

END;

BEGIN

delDept(10);

END;

BEGIN

getAllEmpByID(10,3,1);

END;

练习3

1.写一个存储过程changeDept，实现部门调转功能，传入参数：员工编号、调入部门编号；传出参数：调转是否成功，成功返回ture，否则返回flase。实现该功能时，需要判断传入的员工编号及部门编号是否是有效的；同时修改emp表的deptno字段，并且在员工历史岗位表中插入一条新记录。

CREATE OR REPLACE PROCEDURE chageDept(p_empno IN emp.empno%type,

                                  p_olddeptno IN emp.deptno%TYPE,

                                  p_newdeptno IN emp.deptno%TYPE,

                                  p_date IN emp_jobhistory.chagedate%TYPE,

                                  p_type IN emp_jobhistory.chagetype%TYPE,

                                  p_res IN emp_jobhistory.chagereason%TYPE,

                                  p_flag OUT BOOLEAN)

IS

    v_ecount NUMBER(2);

    v_dcount NUMBER(2);

    v_maxid emp_jobhistory.id%TYPE;

BEGIN

p_flag := FALSE;

--判断传入的员工编号和部门编号是否有效

SELECT COUNT(empno)

  INTO v_ecount

  FROM emp

  WHERE empno=p_empno AND deptno=p_olddeptno;

IF v_ecount<=0THEN

    dbms_output.put_line('员工编号或部门编号无效');

    RETURN;

END IF;

--判断新部门编号是否有效

SELECT COUNT(deptno)

  INTO v_dcount

  FROM dept

 WHERE deptno=p_newdeptno;

IF v_dcount<=0THEN

    dbms_output.put_line('要调入的部门编号无效');

    RETURN;

END IF;

--修改员工当前部门编号

UPDATE emp

   SET deptno = p_newdeptno

 WHERE empno = p_empno;

IF SQL%ROWCOUNT<=0 THEN

    ROLLBACK;

    dbms_output.put_line('调转失败');

    RETURN;

END IF;

 --读取最大ID

SELECT MAX(ID)

  INTO v_maxid

  FROM emp_jobhistory;

IF v_maxid IS NULL THEN

    v_maxid :=0;

END IF; 

--向历史岗位表中插入一条记录

INSERT INTO emp_jobhistory

VALUES(v_maxid+1,p_empno,p_olddeptno,p_newdeptno,p_date,p_type,p_res);



IF SQL%ROWCOUNT>0 THEN

    COMMIT;

    dbms_output.put_line('调转成功');

    p_flag :=TRUE;

ELSE

    ROLLBACK;

    dbms_output.put_line('调转失败');

END IF;

END;

2.写一个块，分别按照三种调用方式，调用上述过程。

DECLARE

p_flag BOOLEAN;

BEGIN

chageDept(7900,20,30,SYSDATE,‘被动调转’,‘薪资太高’,p_flag);

END;

SELECT * FROM emp_jobhistory;

SELECT * FROM emp;

CREATE TABLE emp_jobhistory(

id NUMBER(4) PRIMARY KEY,

empno NUMBER(4),

olddeptno NUMBER(2),

p_newdeptno NUMBER(2),

chagedate DATE,

chagetype VARCHAR2(100),

chagereason VARCHAR2(100));

课后作业

1.创建一个员工离职表:dimission,包括如下字段：

   流水号:数值型

   员工编号:数值型

   离职时所在部门编号：数值型

   离职日期:日期型

   离职原因:变长字符

   离职去向：变长字符

CREATE TABLE dimission(

id NUMBER(6) PRIMARY KEY,

empno NUMBER(4) ,

deptno NUMBER(2),

dimdate date,

dimres VARCHAR2(100),

dimto VARCHAR2(100));

2.修改员工表emp结构，添加员工状态字段status,整型，默认值为1，表示正常状态。

ALTER TABLE emp

ADD status NUMBER(2) DEFAULT 1;

3.写一个存储过程addDim，实现员工离职功能，办理成功返回ture，失败返回false，仔细分析传入及传出参数，

办理离职时，修改员工表的员工状态字段status为2，

并且向dimission表中插入1条记录。

CREATE OR REPLACE PROCEDURE addDim(p_empno IN emp.empno%TYPE,

                               p_deptno IN emp.deptno%TYPE,

                               p_date IN DATE,

                               p_res IN dimission.dimres%TYPE,

                               p_to IN dimission.dimto%TYPE,

                               p_flag OUT BOOLEAN)

IS

    v_maxid NUMBER(6);

BEGIN

p_flag :=FALSE;

--更新员工当前状态

UPDATE emp

   SET status=2

 WHERE empno=p_empno;   

IF SQL%ROWCOUNT<=0THEN

    ROLLBACK;

    dbms_output.put_line('离职失败 ：'||SQLERRM);

    RETURN ;

END IF;

--读取当前最大ID

SELECT MAX(ID)

  INTO v_maxid

  FROM dimission;

IF v_maxid IS NULL THEN

    v_maxid :=0;

END IF;

--向离职表中插入一条信息

INSERT INTO dimission(id,empno,deptno,dimdate,dimres,dimto)

VALUES(v_maxid+1,p_empno,p_deptno,p_date,p_res,p_to);

IF SQL%ROWCOUNT>0 THEN

    COMMIT;

    p_flag :=TRUE;

    dbms_output.put_line('办理离职成功');

ELSE

    ROLLBACK;

    dbms_output.put_line('办理离职失败'||SQLERRM);

END IF;

END;

4.写一个存储过程searchDim,实现遍历指定时间段办理入职的员工姓名。

CREATE OR REPLACE PROCEDURE searchDim(p_begindate DATE,

                                  p_enddate DATE)

IS

    CURSOR emp_cur IS

                       SELECT ename,dimdate from dimission d,emp e

                        WHERE d.empno=e.empno

                          AND dimdate>=p_begindate

                          AND dimdate<p_enddate;

BEGIN

FOR emp_rec IN emp_cur LOOP

    dbms_output.put_line(emp_rec.ename || ','||emp_rec.dimdate);

END LOOP;

END;

5.写一个块，调用3,4过程。

DECLARE

P_flag BOOLEAN;

BEGIN

addDim(8891,10,SYSDATE,'赚够钱咯','环游世界',P_flag);

END;

SELECT * FROM emp;

SELECT * FROM dimission;

BEGIN

searchDim('01-1月-18','01-1月-18');

END;

第6章函数
练习1

1.编写一个函数，计算员工应交个人所得税，1000元以下的员工，不交税，1000-2000元的按照5%缴纳，2000以上的10%缴纳。

CREATE OR REPLACE FUNCTION calculateTax(p_empno emp.empno%TYPE)

RETURN NUMBER

    IS

        v_tax NUMBER;

        v_sal emp.sal%TYPE;

BEGIN

SELECT sal

  INTO v_sal

  FROM emp

 WHERE empno=p_empno;



IF v_sal<1000 THEN

    v_tax :=0;

ELSIF v_sal BETWEEN 1000 AND 2000 THEN

    v_tax :=v_sal*0.05;

ELSE

    v_tax :=v_sal*0.1;

END IF;

RETURN v_tax;

END;

2.写一个块，调用上述函数。

DECLARE

CURSOR tax_cursor

    IS

        SELECT ename,sal,empno

          FROM emp;

v_tax emp.sal%TYPE;

BEGIN

FOR tax_record IN tax_cursor LOOP

 v_tax :=calculateTax(tax_record.empno);

 dbms_output.put_line('员工姓名'||tax_record.ename||' 工资:'||tax_record.sal ||' 应交税费:'||v_tax);

END LOOP;

END;

3.写一个SELECT语句，调用上述函数。

SELECT empno,ename,sal,calculateTax(empno)

FROM emp;

课后作业

1、创建函数，根据输入的参数（员工代码）值，返回对应的员工姓名

CREATE OR REPLACE FUNCTION getEname(p_empno emp.empno%TYPE)

RETURN emp.ename%TYPE

    IS

        v_ename emp.ename%TYPE;

BEGIN

SELECT ename

  INTO v_ename

  FROM emp

 WHERE empno = p_empno;

RETURN v_ename;

EXCEPTION

WHEN no_Data_found THEN

    RETURN NULL;

    dbms_output.put_line('指定编号员工不存在'); 

WHEN too_many_rows THEN

    RETURN NULL;

    dbms_output.put_line('指定编号的员工存在多条记录');

END;

–调用

SELECT empno,getEname(empno)

FROM emp;

2、创建一个函数，根据输入的参数（部门代码）值，返回对应的部门员工的最高薪水；

CREATE OR REPLACE FUNCTION getMaxSal(p_deptno emp.deptno%TYPE)

RETURN emp.sal%TYPE

    IS

        v_maxsal emp.sal%TYPE;

        v_count NUMBER(2) ;

BEGIN

SELECT COUNT(deptno)

  INTO v_count

  FROM dept

 WHERE deptno = p_deptno;

IF v_count<=0 THEN

    dbms_output.put_line('部门编号无效');

    RETURN -1;

END IF;   

SELECT MAX(sal)

  INTO v_maxsal

  FROM emp

 WHERE deptno=p_deptno;

RETURN v_maxsal;

END;

3、创建一个存储过程，查询dept表的所有部门信息，调用该函数，当函数返回的薪水大于4000时，产生异常，在异常部分显示’某某部门的薪水太高了’。

CREATE OR REPLACE PROCEDURE finAllDeptMaxSal

IS

    e_exec EXCEPTION;

    v_dname VARCHAR2(100);

    v_sal emp.sal%TYPE;

BEGIN

FOR dept_record IN

                  (SELECT *

                     FROM dept) LOOP

    v_sal :=getmaxsal(dept_record.deptno);



    IF v_sal >=4000 THEN

        IF v_dname IS NULL THEN

            v_dname :=dept_record.dname;

        ELSE

            v_dname :=v_dname||','||dept_record.dname;

        END IF; 

    END IF;

END LOOP;



IF v_dname IS NOT NULL THEN

    RAISE e_exec;

END IF;

EXCEPTION

WHEN e_exec THEN

    dbms_output.put_line(v_dname||'部门的薪水太高了');

END;

–调用

BEGIN

finAllDeptMaxSal;

END ;

第7章包
练习1

1.创建一个包，该包实现部门管理的功能，包括

1.1：函数：验证部门编号是否有效。

1.2：过程：查询当前所有部门信息

1.3：过程：新建一个部门，成功返回新建的部门编号，失败返回-1

1.4：过程：根据部门编号读取部门信息

1.5：过程：根据部门编号修改部门信息

1.6：过程：根据部门编号查询部门所有员工信息

1.7：过程：根据部门编号删除一个部门，之前要判断该部门下是否存在员工，如果存在员工，则提示用户“部门下已经存在员工，不能删除该部门”。

CREATE OR REPLACE PACKAGE dept_pak

 IS

    FUNCTION validDept(p_deptno IN dept.deptno%TYPE) RETURN BOOLEAN;

    PROCEDURE searchAllDept ;

    PROCEDURE addDept(p_dname dept.dname%TYPE,

                      p_loc dept.loc%TYPE,

                      p_deptno OUT dept.deptno%TYPE);

    PROCEDURE getDeptByID(p_deptno dept.deptno%TYPE,

                          p_dname OUT dept.dname%TYPE,

                          p_loc OUT dept.loc%TYPE);

    PROCEDURE updateDept(p_deptno dept.deptno%TYPE,

                         p_dname dept.dname%TYPE,

                         p_loc dept.loc%TYPE);

    PROCEDURE searchAllEmp(p_deptno dept.deptno%TYPE);

    PROCEDURE delDept(p_deptno dept.deptno%TYPE);

END;

CREATE OR REPLACE PACKAGE BODY dept_pak

 IS

    --1.1：函数：验证部门编号是否有效

    FUNCTION validDept(p_deptno IN dept.deptno%TYPE) RETURN BOOLEAN

          IS

             v_deptno dept.deptno%TYPE;

       BEGIN

           SELECT COUNT(deptno)

             INTO v_deptno

             FROM dept

            WHERE deptno=p_deptno;

           IF v_deptno>0 THEN

               RETURN TRUE;

           ELSE

               return FALSE;

           END IF;

      END;



    --1.2：过程：查询当前所有部门信息

    PROCEDURE searchAllDept

           IS

        BEGIN

            FOR dept_record IN

                               (SELECT *

                                FROM dept) LOOP

                dbms_output.put_line(dept_record.dname);

            END LOOP;

        END;



    --1.3：过程：新建一个部门，成功返回新建的部门编号，失败返回-1

    PROCEDURE addDept(p_dname dept.dname%TYPE,

                      p_loc dept.loc%TYPE,

                      p_deptno OUT dept.deptno%TYPE)

           IS

               v_deptno dept.deptno%TYPE;

        BEGIN

            SELECT MAX(deptno)

              INTO v_deptno

              FROM dept;

            INSERT INTO dept(deptno,dname,loc)

            VALUES(v_deptno+1,p_dname,p_loc);

            IF SQL%ROWCOUNT>0 THEN

                COMMIT;

                p_deptno :=v_deptno+1;

                dbms_output.put_line('新建部门成功');

            ELSE

                ROLLBACK;

                p_deptno :=-1;

                dbms_output.put_line('新建部门失败'||SQLERRM);

            END IF;

        END;



    --1.4：过程：根据部门编号读取部门信息

    PROCEDURE getDeptByID(p_deptno dept.deptno%TYPE,

                          p_dname OUT dept.dname%TYPE,

                          p_loc OUT dept.loc%TYPE)

           IS

        BEGIN

            SELECT DName,loc

              INTO p_dname,p_loc

              FROM dept

             WHERE deptno=p_deptno;

        END;



    --1.5：过程：根据部门编号修改部门信息

    PROCEDURE updateDept(p_deptno dept.deptno%TYPE,p_dname  dept.dname%TYPE,p_loc  dept.loc%TYPE)

           IS

        BEGIN

            --验证部门编号是否有效

            IF validDept(p_deptno) THEN

                UPDATE dept

                   SET dname=p_dname,loc = p_loc

                 WHERE deptno=p_deptno;

            IF SQL%ROWCOUNT>0 THEN

                COMMIT;

                dbms_output.put_line('修改部门成功');

            ELSE

                ROLLBACK;

                dbms_output.put_line('修改部门失败'||SQLERRM);

            END IF;

            ELSE

                dbms_output.put_line(p_deptno ||'是一个无效的部门编号');

            END IF;

        END;



    --1.6：过程：根据部门编号查询部门所有员工信息

    PROCEDURE searchAllEmp(p_deptno dept.deptno%TYPE)

           IS

               CURSOR emp_cur

                   IS

                       SELECT *

                         FROM emp

                        WHERE deptno=p_deptno;

        BEGIN

            FOR emp_record IN emp_cur LOOP

                dbms_output.put_line(p_deptno||'号部门员工为：'||emp_record.ename);

            END LOOP;

          END;



    --1.7：过程：根据部门编号删除一个部门，之前要判断该部门下是否存在员工，如果存在员工，则提示用户“部门下已经存在员工，不能删除该部门”。

    PROCEDURE delDept(p_deptno dept.deptno%TYPE)

           IS

               v_count NUMBER(2);

        BEGIN

            SELECT COUNT(empno)

              INTO V_count

              FROM emp

             WHERE deptno = p_deptno;

            IF v_count>0 THEN

                dbms_output.put_line('该部门下存在员工，不能删除');

            ELSE

                DELETE FROM dept

                 WHERE deptno=p_deptno;

                IF SQL%ROWCOUNT >0 THEN

                    COMMIT;

                    dbms_output.put_line('删除部门成功');

                ELSE

                    ROLLBACK;

                    dbms_output.put_line('删除部门失败'||SQLERRM);

                END IF;

            END IF;

        END;

END;

课后作业

1、创建表test,表定义如下：

empno number(2),

ename varchar2(10),

sal number(7,2)

empno是主键

CREATE TABLE test(

empno number(2) PRIMARY KEY,

ename varchar2(10),

sal number(7,2));

2、创建包的声明test_pak,内容包含add_user过程、del_user过程(根据empno值删除用户）、add_sal函数（根据empno值确定用户，增加员工工资，并返回该用户的工资）

CREATE OR REPLACE PACKAGE test_paK

IS

    PROCEDURE add_user(p_empno IN test.empno%TYPE,

                       p_ename IN test.ename%TYPE,

                       p_sal IN test.sal%TYPE);

    PROCEDURE del_user(p_empno IN test.empno%TYPE);

    FUNCTION add_sal(p_empno IN test.empno%TYPE,

                     p_sal IN test.sal%TYPE) RETURN NUMBER;

3、创建包体，实现上述定义

CREATE OR REPLACE PACKAGE BODY test_paK

IS

    PROCEDURE add_user(p_empno IN test.empno%TYPE,

                       p_ename IN test.ename%TYPE,

                       p_sal IN test.sal%TYPE)

           IS

               BEGIN

                   INSERT INTO test

                   VALUES(p_empno,p_ename,p_sal);

                   COMMIT;

               END;

              

    PROCEDURE del_user(p_empno IN test.empno%TYPE)

           IS

               BEGIN

                   DELETE FROM test

                    WHERE empno=p_empno;

                    commit;

               END;

              

    FUNCTION add_sal(p_empno IN test.empno%TYPE,

                     p_sal IN test.sal%TYPE) RETURN NUMBER

          IS

              v_sal NUMBER(10);

          BEGIN

              UPDATE test

                 SET sal=sal+p_sal

               WHERE empno=p_empno;

              COMMIT;

              SELECT sal

                INTO v_sal

                FROM test

               WHERE empno=p_empno;

              RETURN v_sal;

          END;

END;

4、调用上述的每部分,验证正确性

EXEC test_paK.add_user(2,‘A’,100);

DECLARE

v_sal test.sal%TYPE;

BEGIN

v_sal=test_pak.add_sal(2,300);

dbms_output.put_line(v_sal);

END;

EXEC test_pak.del_user(1);

第9章触发器
练习1

1.在EMP表上创建语句级别的触发器,

当用户在8:00点至17:00点以外插入数据时，系统提示‘只是在工作期间可以录入数据’;

当用户在8:00点至17:00点以外修改数据时，系统提示‘只是在工作期间可以修改数据’；

当用户在8:00点至17:00点以外删除数据时，系统提示‘只是在工作期间可以删除数据’。

CREATE OR REPLACE TRIGGER tri_emp_insertInfo

BEFORE INSERT ON emp

BEGIN

IF(to_char(SYSDATE,'HH24:MI') NOT BETWEEN '08:00'

                                      AND '17:00') THEN

    raise_application_error(-20001,'只是在工作期间可以录入数据');

END IF;

END;

CREATE OR REPLACE TRIGGER tri_emp_updateInfo

BEFORE DELETE ON emp

BEGIN

IF(to_char(SYSDATE,'HH24:MI') NOT BETWEEN '08:00'

                                      AND '17:00') THEN

    raise_application_error(-20001,'只是在工作期间可以修改数据');

END IF;

END;

CREATE OR REPLACE TRIGGER tri_emp_deleteInfo

BEFORE DELETE ON emp

BEGIN

IF(to_char(SYSDATE,'HH24:MI') NOT BETWEEN '08:00'

                                      AND '17:00') THEN

    raise_application_error(-20001,'只是在工作期间可以删除数据');

END IF;

END;

练习2

1.在员工部门调转时，当修改员工表中的部门编号时，请在该操作上建立触发器，同步实现员工历史岗位表中数据的操作。

CREATE OR REPLACE TRIGGER tri_emp_updatedeptno

AFTER UPDATE OF deptno ON emp

FOR EACH ROW

DECLARE

v_maxid NUMBER(6);

BEGIN

SELECT MAX(id)

  INTO v_maxid

  FROM emp_jobhistory;

INSERT INTO Emp_Jobhistory

VALUES(v_maxid+1,:old.empno,:old.deptno,:new.deptno,SYSDATE,NULL,NULL);

END;

课件及习题答案汇总word文档百度云地址：

链接: https://pan.baidu.com/s/1H9cw1OpKu4B96F2yBwOfZQ
提取码: 8phq

蓝白咖啡

关注

0
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库PL SQL开发、Oracle-SQL开发习题答案

第2章编写简单的查询语句练习11.使用两种方式查询所有员工（EMP）信息。 SELECT * FROM emp; SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;2.查询（EMP）员工编号、员工姓名、员工职位、员工月薪、工作部门编号。 SELECT empno,ename,job,sal,deptno FROM emp;练习21.员工转正后，月薪上调20%，请查询
复制链接

扫一扫