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

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

1.使用两种方式查询所有员工（EMP）信息。
2.查询（EMP）员工编号、员工姓名、员工职位、员工月薪、工作部门编号。

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

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

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分），要求显示格式为：XXX的第一年总收入为XXX。
2.查询员工表中一共有哪几种岗位类型。
1.分别选择员工表、部门表、薪资等级表中的所有数据
2.分别查看员工表、部门表、薪资等级表结构。

第3章 限制数据和对数据排序

1.查询职位为SALESMAN的员工编号、职位、入职日期。
2.查询1985年12月31日之前入职的员工姓名及入职日期。
3.查询部门标号不在10部门的员工姓名、部门编号。

1.查询入职日期造82年至85年员工姓名，入职日期。
2.查询月薪在3000到5000的员工姓名，月薪。
3.查询部门编号为10或者20的员工姓名，部门编号。
4.查询经理编号为7709，7566，7788的员工姓名，经理编号。

1.查询员工姓名以W开头的员工姓名。
2.查询员工姓名倒数第2个字符为T的员工姓名。
3.查询奖金为空的员工姓名，奖金。

1.查询工资超过2000并且职位是WANAGER，或者职位是SALESMAN的员工姓名、职位、工资。
2 .查询工资超过2000并且职位是WANAGER或SALESMAN的员工姓名、职位、工资。
3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。
2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
2.查询员工姓名的第三个字母是a的员工姓名。
3.查询除了10、20号部门以外的员工姓名、部门编号。
4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
5.查询没有上级的员工（经理号为空）的员工姓名。
6.查询工资大于等于4500并且部门为10或者20的员工的姓名，工资，部门编号。

第4章 单行函数

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

1.查询员工姓名中包含大写或小写字母A的员工姓名。
2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度。（提示：要求使用INSTR函数）
3.查询每个职工的编号，姓名，工资
-要求将查询到的数据按照一定的格式合并成一个字符串
-前10位：编号，不足部分用 填充，左对齐
-中间10位：姓名，不足部分用 填充，左对齐
-后10位：工资，不足部分用*填充，右对齐

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

1.查询每个员工截止到现在一共入职多少天？
2.当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？

  • 当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？
    4.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？
    5.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？
    6.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-01，该日期实际所代表的日期为？
    7.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-95，该日期实际所代表的日期为？
    • 1.查询服务器当前时间。
    2.查询部门10，20的员工截止到2000年1月1日，工作了多少个月，入职的月份。（使用months_between, extract）
    3.如果员工使用期6个月，查询职位不是MANAGER员工姓名，入职日期，转正日期，入职日期后的第一个星期一，入职当前月的最后一天日期。（使用add_months, next_day, last_day）

    1.显示服务器系统当前时间，格式为2007-10-12 17:11:11。（用to_char函数）
    2.显示ename，hiredate和雇员开始工作日是星期几，列标签DAY。（用to_char函数）
    3.查询员工姓名，工资，格式化的工资（￥999,999.99）。（用to_char函数）
    4.把字符串2015-3月-18 13:13:13转换成日期格式，并计算和系统当前时间间隔多少天。（用to_char函数）

    1.计算2000年1月1日到现在又多少月，多少周（四舍五入）。
    2.查询员工ename的第三个字母是A的员工信息（使用两个函数）。
    3.使用trim函数将字符串‘hello’，‘Hello’，‘bllb’，‘hello’分别处理得以下字符串ello、Hello、ll、hello。
    4.将员工工资按如下格式显示：123,234.00 RMB。
    5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
    6.将员工的参加工作日期按照如此啊格式显示：月份/年份
    7.在员工表中查询除员工的工资，并计算应交税款：如果工资小于1000，税率为0，如果工资大于等于1000并小于2000，税率为10%，如果工资大于等于2000并小于3000，税率为15%，如果工资大于等于3000，税率为20%。
    8.创建一个查询显示所有雇员的ename和sal。格式化sal围殴15个字符长度，用$左填充，列标签SALARY。

    第5章 多表连接

    1.写一个查询，显示所有员工姓名，部门编号，部门姓名。
    2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金。
    3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

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

    1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
    2.在上一题的基础上，添加没有经理的员工King，并按照员工编号排序。
    3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

    使用SQL-99写法，完成如下练习
    1.创建一个员工表和部门表的交叉连接。
    2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期。
    3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点。
    4.使用ON子句，显示工作在CHICAGO的员工姓名，本门名称，工作地点，薪资等级。
    5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
    6.使用右外连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

    1.显示员工SMITH的姓名，部门名称，直接上级名称。
    2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
    3.显示员工KING和FORD管理的员工姓名及其经理姓名。
    4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

    第6章 分组函数

    1.查询部门20的员工，每月的工资总和及平均工资。
    2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
    3.查询员工表中一共有几种岗位类型。

    1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
    2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
    3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

    1.查询部门人数大于2的部门编号，部门名称，部门人数。
    2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，
    部门平均工资，并按照部门人数升序排序。
    1.查询部门平均工资在2500元以上的部门名称及平均工资。
    2.查询员工岗位中不是以“SA”开头并且平均工资在2500员以上的岗位及平均工资，并按平均工资降序排序。
    3.查询部门人数在2人以上的部门名称，最低工资，最高工资，并对要求的工资进行四舍五入到整数位。
    4.查询岗位不为SALESMSMAN，工资和大于等于2500的岗位及每种岗位的工资和。
    5.显示经理编号和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
    6.写一个查询，显示每个部门最高工资和最低工资的差额。

    第7章 子查询

    1.查询入职日期最早的员工姓名，入职日期。
    2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称。
    3.查询入职日期比20部门入职日期最早的员工还早的员工姓名，入职日期。
    4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数。

    1.查询入职日期比10部门任意一个员工晚的员工姓名，入职日期，不包括10部门员工。
    2.查询入职日期比10部门所有员工晚的员工姓名，入职日期，不包括10部门员工。
    3.查询职位和10部门人员一个员工职位相同的员工姓名，职位，不包括10部门员工。

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

    1.查询比自己职位平均工资高的员姓名，职位，部门名称，职位平均工资。
    2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名，职位，不包括SCOTT和BLAKE本人。
    3.查询不是经理的员工姓名。

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

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

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

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

  • 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
    4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
    5.显示经理是KING的员工姓名，工资。
    6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
    7.使用子查询的方式查询哪些职员在NEW YORK工作。
    8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
    9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
    10.写一个查询显示其上级领导是King的员工姓名、工资。
    11.显示所有工作在RESEARCH部门的员工姓名，职位。
    12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
    13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
    14.列出至少有一个雇员的所有部门。
    15.列出薪金比"SMITH"多的所有雇员。
    16.列出入职日期早于其直接上级的所有雇员。
    17.找员工姓名和直接上级的名字。
    18.显示部门名称和人数。
    19.显示每个部门的最高工资的员工。
    20.显示出和员工号7369部门相同的员工姓名，工资。
    21.显示出和姓名中包含"W"的员工相同部门的员工姓名。
    22.显示出工资大于平均工资的员工姓名，工资。
    23.显示出工资大于本部门平均工资的员工姓名，工资。
    24.显示每位经理管理员工的最低工资，及最低工资者的姓名。
    25.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
    26.显示出平均工资最高的的部门平均工资及部门名称

    • 第8章 集合运算

    1.分别使用联合运算及完全联合运算完成，按照时间升序顺序，查询员工7839的工作岗位列表。
    2.使用多表连接，查询每个部门的部门编号，部门人数，没有人数的部门显示0。
    3.使用联合运算，查询每个部门的部门编号，部门人数，没有人数的部门显示0。
    4.使用联合运算，查询10号部门及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
    1.用集合运算，列出不包含job为SALESMAN的部门的部门号。
    2.写一个联合查询，列出下面的信息：
    EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。
    DEPT表中的所有部门编号和部门名称，不管他们是否有员工。
    3.用集合运算查询出职位为SALESMAN和部门编号为10的
    人员编号、姓名、职位，不排除重复结果。
    4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。

    • 第9章 高级子查询

    如下练习，使用相关子查询完成
    1.查询比所在职位平均工资高的员工姓名，职位
    2.查询工资为其部门最低工资的员工编号，姓名 ，工资。

    如下练习，用相关子查询完成
    1.查询所有雇员编号，名字和部门名字。
    2.查询哪些员工是经理？
    3.查询哪些员工不是经理？
    4.查询每个部门工资最低的两个员工编号，姓名，工资。

    如下练习，用exists或not exists完成
    1.列出至少有一个雇员的所有部门名称。
    2.列出一个雇员都没有的所有部门名称。

    课后作业 ：
    如下练习，使用相关子查询完成。
    1.查询薪水多于他所在部门平均薪水的雇员名字 ，部门号。
    2.查询员工姓名和直接上级的名字。
    3.查询每个部门工资最高的员工姓名，工资。
    4.查询每个部门工资前两名高的员工姓名，工资。

    第10章 层次查询

    1.产生一个报告显示 BLAKE的所有下级（包括直接和间接下级）雇员的名字、薪水和部门号。
    2.创建一个报告显示对于雇员 SMITH 经理的层次，包括级别和姓名，首先显示他的直接经理。
    3.创建一个缩进报告显示经理层次，从名字为 KING的雇员开始，显示雇员的名字、经理ID和部门ID。
    4.产生一个公司组织图表显示经理层次。从最顶级的人开始，排除所有job为CLERK的人，还要排除FORD和那些对FORD报告的雇员。

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

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

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

    1.使用CREATE TABLE emp_back as
    SELECT * FROM EMP WHERE 1=0，创建 emp_back表,拷贝下来即可。
    CREATE TABLE emp_back
    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';

    1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
    2.修改奖金为null的员工，奖金设置为0
    3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

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

    1.删除经理编号为7566的员工记录
    2.删除工作在NEW YORK的员工记录
    3.删除工资大于所在部门平均工资的员工记录

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

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

    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
    （2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月05’,100,1
    （3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10
    （4）插入部分学生信息： 'A003','JOHN','女’
    （5）将A001学生性别修改为'女‘
    （6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01
    （7）将生日为空的学生班级修改为Java3班
    （8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数
    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日，其他字段随意
    (2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门
    的员工信息插入
    (3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资
    (4)修改copy_emp表中sal为空的记录，工资修改为平均工资
    (5)把工资为平均工资的员工，工资修改为空
    (6)另外打开窗口2查看以上修改
    (7)执行commit，窗口2中再次查看以上信息
    (8)删除工资为空的员工信息
    (9)执行rollback

    第13章 创建和维护表

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

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

    1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”
    2.修改员工表中性别列的数据类型为char(4)
    3.修改员工表中性别列的默认值为“女”
    4.删除员工表中的性别列

    1.请分析按照以下要求都需要建立什么类型的字段?
    –(1)最大2000个字节定长字符串
    –(2)如果输入‘张三’ 后添空格6个
    –(3)性别输入'男'或'女’
    –(4)最大4000个字节变长字符串
    –(5)如果在数据库中输入'张三'则显示数据'张三’
    –(6)表示数字范围为- 10的125次方到10的126次方, 可
    以表示小数 也可以表示整数
    –(7)最大表示4位整数 -9999 到 9999
    –(8)表示5位有效数字 2位小数的 一个小数 -999.99
    到 999.99
    –(9)包含年月日和时分秒
    –(10)包含年月日和时分秒毫秒
    –(11)二进制大对象图像/声音
    2.创建表date_test,包含列d，类型为date型。试向
    date_test表中插入两条记录，一条当前系统日期记录，一
    条记录为“1998-08-18”。
    • 3.创建与dept表相同表结构的表dtest，将dept表中部门编
    号在40之前的信息插入该表。
    • 3.创建与emp表结构相同的表empl，并将其部门编号为前30
    号的员工信息复制到empl表。
    • 4.试为学生表student增加一列学生性别gender 默认值
    • 5.试修改学生姓名列数据类型为定长字符型10位。

    第14章 约束

    1.学校有一个选课系统，其中包括如下关系模式：
    系(系编号： 主键，
    系名称: 唯一键，
    系主任： 非空约束，
    系所在校去：取值范围只能在南湖校区和浑南校区)
    班级(班级编号： 主键，
    班级名称： 唯一键，
    所属系： 外键)
    2.创建学生表，包含如下属性:
    学号 定长字符型 10位 主键
    姓名 变长字符型 20位 非空
    性别 定长字符型 2位 取值范围只能为男或女
    出生日期 日期型
    1.简述5种约束的含义。
    2.创建学生关系sc，包括属性名：
    – 选课流水号 数值型 主键；
    – 学生编号 非空 外键
    – 课程编号 非空 外键；
    – 成绩 0-100之间；
    3.创建copy_emp，要求格式同emp表完全一样，不包含数据。
    4.创建copy_dept，要求格式同dept表完全一样，不包含数据。
    5.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能否成功,为什么？
    6.追加copy_dept表中主键deptno

    第15章 视图

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

    1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。
    2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。
    3.通过如上视图，查询每个部门工资最低的员工姓名及部门名称

    • 1.创建视图v_emp_20，包含20号部门的员工编号，姓名，
    年薪列(年薪=12*(工资+奖金）；
    • 2.从视图v_emp_20中查询年薪大于1万元员工的信息；
    • 3.请为工资大于2000的员工创建视图，要求显示员工的部
    门信息，职位信息，工作地点；
    • 4.针对以上视图执行insert,update,delete,语句能否成
    功，为什么

    第16章 序列、索引、同义词

    1.创建一个序列，该序列起始值从1开始，无最
    大值，增量是1，不循环。
    2.查询序列的当前值及下一个值
    3.使用第1题所建的序列，向部门表中插入两条记录，部门编号使用序列值，部门名称分别为：Education、Market，城市分别为：DALLAS、WASHTON

    1.使用子查询的方式，创建test表。
    2.快速复制test表中的数据，复制到100w条左右
    3.更新test表中的empno字段为rownum
    4.查询test中empno为800000的记录值，记录查询执行时间。
    5.在test表的empno字段上创建索引
    6.重新执行第4题，对比查询时间

    1.有如下关系模式，
    student(sno,sname,gender,birthday,email);--学生
    course(cno,cname,type,credit);--课程
    sc（sno,cno,grade);--选课
    试分析哪些列上适合创建索引？

    1.创建序列，起始位1，自增为1，最小值为1，最大值为9999
    • 2.创建序列，起始值为50，每次增加5；
    • 3.在表copy_dept中插入记录，其中部门号码采用上一步中创建的序列
    • 4.请为工资创建索引，比较<10000,>1000,与round（sal）>10000,哪
    个索引有效，哪个索引无效；
    • 5.创建表，采用“create table copy_emp_index as select * from
    emp”，生成500万条数据，把其中的“员工号”字段修改为唯一；