oracle期末考试参考答案_检索scott.emp表中职位是salesman(销售员)的记录,并且分行编写sql语句。

刚刚考完，参考答案，欢迎斧正！

A,B相同的准备工作

准备工作: 开两个cmd
其中一个: sqlplus sys/123456 as sysdba;
                  alter user scott account unlock;
另一个:
                  sqlplus scott/tiger;
 

A卷题解

1.请完成以下sqlplus的设置并按要求保存代码和截图(20分，每小题4分)

scott用户下
(1)设置sql的提示符为你的姓名，如张三>>

set sqlprompt 张三>>;

(2)设置每行100个字符，每页18行

set linesize 100
set pagesize 18

(3)查看scott用户的dept表，并将该内容存入my_1.sql;

select * from dept;
save my_1

默认在主目录下，不用写路径

(4)执行my_1.sql文件

@my_1

(5)查看emp表对象的结构信息

desc emp;

2.请完成以下基本操作，并按要求保存代码和截图(此题50分，前4小题每小题5分，第5小题30分)

--sys用户窗口下  方便分辨sys还是普通用户，下面管理员的SQL>提示就不改了
(1)创建一个普通用户，要求以自己的姓名的全拼命名，如:zhangsan,密码为123

create user zhangsan identified by 123;

(2)创建角色student_role,并使之具备创建表、创建视图、创建存储过程、创建触发器的权限

create role student_role;
grant create table,create view,create procedure,create trigger to student_role;

(3)将用户角色student_role和创建会话权限授权给刚建立的用户zhangsan,将查询scott用户下的emp权限授给zhangsan

grant student_role,create session to zhangsan;
grant select on scott.emp to zhangsan;

(4)修改zhangsan用户，让其默认表空间为users,并且表空间配额20m

alter user zhangsan
    default tablespace users
    quota 20m on users;

(5)连接到zhangsan用户，并解锁。并在该用户下完成:

conn zhangsan/123;

①建立数据表stu_emp,stu_dept,要求该表为scott用户下的emp表、dept表的备份
--先要授予对dept表的select权限

grant select on scott.dept to zhangsan;

--复制表 回到zhangsan用户窗口

create table stu_emp as select * from scott.emp;
create table stu_dept as select * from scott.dept;

②查询每位员工的实际工资(工资+津贴)

select ename 姓名,sal+nvl(comm,0) 实际工资 from stu_emp;

③查询比部门号为10号的平均工资高的人数

select count(empno) from stu_emp where sal>(select avg(sal) from stu_emp where deptno=10);

④创建视图view_detaliled，用于查看每个员工的编号，姓名以及所在城市

create view view_detaliled as 
	select empno,ename,loc from stu_emp,stu_dept 
	where stu_emp.deptno=stu_dept.deptno;

⑤对视图view_detaliled插入一条数据(1010,jack,北京).若能成功请查询相关数据，若不能成功，说明原因。

insert into view_detaliled values(1010,'jack','北京');

不能插入成功，因为视图一般由多个基表组成的逻辑结构，一般不允许直接进行dml(update,insert,delete)操作。
因为对视图的操作其实是对组成视图的好多个基本表的操作，直接dml操作视图可能破坏表与表之间的完整性约束等。
比如上面的例子，其实是分别向stu_emp插入(1010)和向表stu_dept插入('jack','北京')。很明显，dept表的新插入
记录缺少主键数据，不可能让你插入成功的。
此时想要直接对视图进行dml操作，需要写一个关于该视图的替换触发器，在触发器内实现对各个基本表的dml操作

3.创建一个存储过程stu_proc并执行。要求:以员工编号为参数，返回该员工的姓名和工资，若工资低于3000，则报错，
说明工资低于国家最低工资水平

create procedure stu_proc(
        var_empno in stu_emp.empno%type,
        var_ename out stu_emp.ename%type,
        var_sal out stu_emp.sal%type
    ) as
begin
    select ename,sal into var_ename,var_sal from stu_emp where empno=var_empno;
    if var_sal<3000 then
        dbms_output.put_line('工资低于国家最低工资水平');
    end if;
end;
/

--执行

set serveroutput on;
declare
    var_empno stu_emp.empno%type;
    var_ename stu_emp.ename%type;
    var_sal stu_emp.sal%type;
begin
    var_empno:=&no;
    stu_proc(var_empno,var_ename,var_sal);
    dbms_output.put_line('姓名:'||var_ename||' 工资:'||var_sal);
end;
/

4.声明一个带输入参数(部门号)的显式游标cur_sel,根据参数传递来查询每位员工的姓名和所在城市

declare
	cursor cur_sel(var_deptno stu_dept.deptno%type) is
		select ename,loc from stu_dept,stu_emp 
		where stu_dept.deptno=stu_emp.deptno and stu_dept.deptno=var_deptno;
	var_no stu_dept.deptno%type;
begin
	var_no:=&no;
	dbms_output.put_line(var_no||'号员工的相关信息如下');
	for emp_record in cur_sel(var_no) loop
		dbms_output.put_line(emp_record.ename||'住在'||emp_record.loc);
	end loop;
end;
/

考试时不知道时间。。总感觉我们时间分配少了。。做这题时就剩下10min了，急的要死。。然后多写了一个cursor前create，卡住了，竟然当时方的不会改。。差点没完卷，考试提交版本如下:

declare
	cursor cur_sel(var_deptno stu_dept.deptno%type) is
		select ename,loc from stu_dept,stu_emp 
		where stu_dept.deptno=stu_emp.deptno and stu_dept.deptno=var_deptno;
begin
	for emp_record in cur_sel(10) loop
		dbms_output.put_line(emp_record.ename||'住在'||emp_record.loc);
	end loop;
end;
/

5.详细阐述oracle的体系结构(10分)
略
 

B卷题解

1.sqlplus环境设置(20分，每小题4分)
(1)设置每行120个字符，设置每页20行

set linesize 120
set pagesize 20

(2)设置sql的提示符为你的名字，如李四>>

set sqlprompt 李四>>

 

(3)查看scott用户下emp表信息

select * from emp;

 

(4)my.sql文件，实现在scott用户下emp表中查询工作是SALESMAN的所有职工的姓名
工资，要求姓名，工资字段分别格式化成a10,$999,999.00
在D盘根目录下新建文件my.sql 写下如下代码:
--my.sql

col ename for a10
col sal for $999,999.00
select ename,sal from emp where job='SALESMAN';

(5)执行my.sql

@ D:\my

2.请完成以下基本操作，并按要求保存代码和截图(此题50分，每小题5分)
--管理员窗口
(1)创建一个普通用户，要求以自己的姓名的全拼写命名，如:lisi,密码:abc

create user lisi identified by abc;

 

(2)创建角色myrole,并使之具备创建表，创建视图，创建触发器，存储过程的权限

create role myrole;
grant create table,create view,create trigger,create procedure to myrole;

 

(3)将用户角色myrole和连接权限授给刚建立的用户lisi

grant myrole,connect to lisi;

 

(4)将访问scott用户下的emp的权限授权给lisi

grant select on scott.emp to lisi;

 

(5)修改lisi用户，让其默认表空间为users,并且表空间配额5m

alter user lisi 
    default tablespace users
    quota 5m on users;

 

(6)连接到lisi用户，并且在该用户下完成
--普通用户窗口切换到 lisi用户

conn lisi/abc

①建立数据表my_emp/my_dept,分别为scott用户下的emp,dept表的备份
--先要授予对dept表的select权限  sys窗口

grant select on scott.dept to lisi;

 

--复制表 回到lisi用户窗口

create table my_emp as select * from scott.emp;
create table my_dept as select * from scott.dept;

②列出所有"CLERK"(办事员)的姓名及其部门名称

select ename,dname from my_emp,my_dept where my_emp.deptno=my_dept.deptno and job='CLERK';

③列出薪水比"SMITH"多的所有员工

select * from my_emp where sal>(select sal from my_emp where ename='SMITH');

④找出在(任何年份的)2月受聘的所有员工

select * from my_emp where to_char(hiredate,'mm')=2;

⑤查询各种工作的最低工资

select job 工作,min(sal) 最低工资 from my_emp group by job;

 

3.scott模式中使用隐式游标for循环，用于实现根据部门号查询员工姓名和工作(10分)

conn scott/tiger;

set serveroutput on;
declare
	var_deptno emp.deptno%type;
begin
	var_deptno:=&no;
	dbms_output.put_line(var_deptno||'号部门的相关信息如下');
	for emp_record in (select ename,job from emp where deptno=var_deptno) loop
		dbms_output.put_line('姓名:'||emp_record.ename||' 工作:'||emp_record.job);
	end loop;
end;
/

4.写一个pl/sql存储过程。根据scott模式下的emp表，写一个带参数的存储过程proc(deptno in number,sum_sal out number),
输入部门编号，输出该部门的总工资信息，并写一个pl/sql程序块，测试该存储过程(10分)

--过程

create or replace procedure proc(var_deptno in number,sum_sal out number) as
begin
	select sum(sal) into sum_sal from emp where deptno=var_deptno;
end;
/

--执行

declare
	var_deptno number;
	var_sumSal number;
begin
	var_deptno:=&no;
	proc(var_deptno,var_sumSal);
	dbms_output.put_line(var_deptno||'号部门的总工资为:'||var_sumSal);
end;
/

    

5.说明oracle逻辑结构和物理结构的构成
略

(安工程，依芸芸&李臣龙oracle班)

总的来说。。一如既往。。简单 。。就是有些题意可能有歧义，A卷存储过程那题"以员工编号为参数，返回该员工的姓名和工资，若工资低于3000，"姓名和工资要写成输出参数。。亲口问老师的。。

还有就是时间。。。考完前10分钟才知道总时长只有。。。。。

参考答案，欢迎斧正，给下届乘凉！

还有，以后打死不写学校考试有关的博客了。。。。后辈子弟也不要写了。。太无耻！