Oracle数据库技术上机测试题总集+答案解析(请以自己的姓名创建表空间，用sql语句创建如下表，将该表保存到以自己姓名命名的表空间中)…………

ONE：创建表空间：

1、要求：请以自己的姓名创建表空间，用sql语句创建如下表，将该表保存到以自己姓名命名的表空间中。（创建表空间4分，建表每个约束项2分， 该题共20分）

答案：

create tablespace mytable datafile 'D:\ text.dbf' size 10M autoextend on next 50M maxsize 1G extent management local autoalllocate; 

-- extent management（扩展的本地管理）
-- autoalllocate（自动管理）


create table t_student(
	no varchar2(11) primary key,
	name varchar2(10) ,
	sex char(2) default '男'
) tablespace mytable;

create table t_score(
	no varchar2(11),
	cno varchar2(10),
	score numberic(3,1) check(score>=0),
	constraint pk_no_cno primary key(no,cno),
	constraint fk_no foreign key(no) references t_student(no)
) tablespace mytable;

2、要求：请以自己的姓名创建表空间，用sql语句创建如下表，将该表保存到以自己姓名命名的表空间中。（创建表空间4分，建表每个约束项2分，该题共20分）

create tablespace mytable datafile 'D:\text.dbf' size 100M autoextend on next 50M maxsize 1G;

create table goods(
	goodsId varchar2(8) primary key,
	goodsName varchar2(10) not null,
	unitPrice number(6,2) check(unitPrice>0)
) tablespace mytable;


create table purchase(
	goodId varchar2(8),
	customerId varchar2(8),
	nums number(5) check(nums between 1 and 30),
	constraint unit_pk_goodcust primary key(goodId,customerId),
	constraint fk_goodid foreign key(goodId) references goods(goodsId);
) tablespace mytable;

TWO：用户管理以及权限管理

1、创建2个用户testA和testB，密码为testA、testB，然后创建一个角色demo，并将建立连接、建表、建视图及对scott用户的emp表的select等权限赋给该角色，然后将该角色赋给testA和testB。写出以上语句。

create user c##textA identified by textA;
create user c##textB identified by textB;
create role c##demo;
grant create session,create table,create view to c##demo;
grant select on c##scott.emp to c##demo;
grant c##demo to c##textA,c##textB;

2、用户管理：按照次序依次做下列题目，将要完成的命令代码复制到记事本文件中，题号为二。（该题20分）

2.1 创建用户，用户名为你的姓名+学号的最后两位数（如姓名张三，学号201022023，用户名为“张三23”），口令为你姓名的全拼（如zhangsan），默认表空间为users。

create user c##zhangsan23 identified by zhangsan default tablespace users;

2.2 向用户授予连接数据库系统权限

grant create session to c##zhangsan23;

2.3 向用户授予emp表的select权限

grant select on c##scott.emp to c##zhangsan23;

2.4以新建用户身份查询emp表

conn c##zhangsan23/zhangsan 
select * from c##scott.emp;

2.5 撤销向该用户授予的系统权限，向用户授予connect角色

revoke create session from c##zhangsan23;

2.6锁定该用户。

alter user zhangsan23 account onlock;
--解锁该用户的话：
--alter user zhangsan23 account unlock;

2.7 删除该新建用户。

drop user zhangsan23;

2.8 查看scott用户所拥有的角色。

select * from dba_role_privs;

THREE：sql语句编程

--1．从emp表中检索雇员姓名。
select ename from emp;

--2．列出员工表中每个部门的员工数，和部门no
select deptno no,count(*) from emp group by deptno;

--3．从emp表中检索工资超过1000的雇员信息。
select * from emp where sal>1000;

--4．显示雇员名，雇员工资及所在部门的名字，并按部门排序
select ename,sal,dname from emp e,dept d where e.deptno=d.deptno order by deptno;

--5．显示与 SMITH 同部门的所有员工
select ename,job from emp where job=(select job from emp where ename='SMITH');

--6．显示高于自己部门平均工资的员工的信息
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
select e.* from emp e,(select deptno,avg(sal) avgsalfrom emp group by deptno) e2 where e.deptno=e2.deptno and sal>avgsal;

--7．显示每个员工的上级领导的姓名。
select e.ename 员工,e2.ename 领导 from emp e join emp e2 on e.mgr=e2.empno;

--8．求平均工资大于2000的工作岗位
select job,avg(sal) from emp group by job having avg(sal)>2000;

--9．显示 1980 年入职的所有员工
select ename,hiredate from emp where to_char(hiredate,'yyyy')='1980';
select ename,hiredate from emp where extract(year from hiredate)=1980;

--10. 找出工资比JONES多的员工
select ename,sal from emp where sal>(select sal from emp where ename='JONES');

--3.1显示每个雇员的雇员名，雇员工资及所在部门的名字
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;

--3.2显示部门号为 10 的工资大于2000的员工的部门名、员工名和工资。
select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and deptno=10 and sal>2000;

--3.3显示雇员名，雇员工资及所在部门的名字，并按部门排序
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by deptno;

--3.4列出所有员工的姓名和其上级的姓名。
select e.ename 员工,e2.ename 上级 from emp e,emp e2 where e.mgr=e2.empno; 

--3.5查询emp表中在部门20工作的雇员的姓名及其上级的姓名 
select e.ename 员工,e2.ename 上级 from emp e,emp e2 where e.mgr=e2.empno and e.empno=20;

--3.6查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号
select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);

--3.7显示与 SMITH 同部门的所有员工
select ename,deptno from emp where deptno=(select deptno from emp where ename='SMITH');

--3.8以职位分组，找出每种职位的平均工资。
select job,avg(sal) from emp group by job;

--3.9找到平均工资大于2000的工作职种
select job,avg(sal) from emp group by job having avg(sal)>2000;

--3.10找出工资比Jones多的员工
select ename,sal from emp where sal>(select sal from emp where ename='Jones');

FOUR：综合编程题

1：创建一个存储过程，根据提供的雇员姓名（作为过程的参数），将该雇员的工资改为2000；

create or replace procedure mydure(vname emp.ename%type)
is
begin
	update emp set sal=2000 where ename=vname;
end;


declare
	name emp.ename%type:='&ename';
begin
	mydure(name);
end;

2：创建一个存储过程，根据提供的雇员姓名，查询该雇员的上级领导人的姓名，并返回。

create or replace procedure mydure(vname emp.ename%type,sname out emp.ename%type)
is
begin
	select ename into sname from emp where empno=(select mgr from emp where ename=vname);
end;

declare
	name1 emp.ename%type := &a;
	name2 emp.ename%type;
begin
	mydure(vname=>name1,sname=>name2);
	dbms_output.put_line(name1||'的上级领导人为：'||name2);
end;

3：创建一个存储过程，输入部门号，返回该部门所有雇员的姓名，工资和佣金。

create or replace procedure mydure(dno emp.deptno%type)
is
	cursor mycur is select ename,sal,comm from emp where deptno=dno;
begin
	for my in mycur loop
	dbms_output.put_line(my.ename || '    ' || my.sal || '    ' || my.comm);
	end loop;
end;


declare
	d_no emp.deptno%type := &dno;
begin
	mydure(d_no);
end;

4：编写一个过程，输入部门号，返回该部门所有雇员信息。

create or replace procedure mydure(dno emp.deptno%type)
is
	cursor mycur is select * from emp where deptno=dno;
begin
	for my in mycur loop
	dbms_output.put_line(my.empno|| '   ' || my.ename || '   ' || my.job || '   ' || my.sal|| '   ' my.deptno);
	end loop;
end;


declare
	d_no emp.deptno%type:=&a;
begin
	mydure(d_no);
end;

5：创建一个存储过程，根据提供的雇员编号（作为过程的参数），将该雇员的佣金改为500；

create or replace procedure mydure(eno emp.empno%type)
is
begin
	update emp set comm=500 where empno=eno;
end;

declare
	e_no emp.empno%type := &empno;
begin
	mydure(e_no);
end;

6：创建一个存储过程，根据提供的雇员编号，查询该雇员的上级领导人的姓名，并返回。

create or replace procedure mydure(vname emp.ename%type,sname out emp.ename%type)
is
begin
	select ename into sname from emp where empno=(select mgr from emp where ename=vname);
end;

declare
	name1 emp.ename%type := &a;
	name2 emp.ename%type;
begin
	mydure(vname=>name1,sname=>name2);
	dbms_output.put_line(name1||'的上级领导人为：'||name2);
end;

7：创建一个存储过程，输入一个工资数，返回emp表中员工工资高于该工资的员工姓名、部门编号、工资和佣金。

create or replace procedure mydure(vsal emp.sal%type)
is
	cursor mycur is select ename,deptno,sal,comm from emp where sal>vsal;
begin
	for my in mycur loop
	dbms_output.put_line(my.ename || '   ' || my.deptno || '  ' || my.sal || '  ' || my.comm);
	end loop;
end;

declare
	esal emp.sal%type := &sal;
begin
	mydure(esal);
end;

8：写一个函数，传入员工编号，返回所在部门名称

create or replace function myfunc(eno emp.empno%type)
return dept.dname%type
is
	d_name dept.dname%type;
begin
	select dname into d_name from dept where deptno=(select deptno from emp where empno=eno);
	return d_name;
end;

declare
	e_no emp.empno%type:=&empno;
begin
	dbms_output.put_line(myfunc(e_no));
end;