内容发布更新时间 : 2024/5/3 17:36:24星期一 下面是文章的全部内容请认真阅读。

Oracle试卷 一、

使用scott/tiger用户下的emp表和dept表完成下列题目

(本题45分，每小题3分)

1. 查看emp和dept的表结构以及所有的记录. DESC emp; Select * from emp;

2. 找出佣金高于薪金的员工姓名

select * from emp where comm>sal;

3. 以首字母大写其余小写的方式显示所有员工的姓名

SQL>select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 4. 找出不收取佣金或收取的佣金低于100的员工

select * from emp where comm is null or comm<100;

5. 找出收取佣金的员工的不同工作

select distinct job from emp where comm is not null

6. 列出所有雇员的雇员名称、部门名称和工资

select ename,dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;

7. 显示所有名字中第三个字母是a的雇员的名字

Select ename from emp where ename LIKE ‘—a%;

8. 显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序

select ename,job,sal from emp order by job desc,sal;

9. 显示满10年服务年限的员工的姓名和受雇日期

Select ename,hiredate from emp where months_between(sysdate,hiredate)>=10*12;

10. 列出薪金高于在SALES部门工作的所有员工的薪金的员工姓名和薪金

select * from emp where sal>all (select sal from emp where deptno=30); 11. 列出与“MILLER”从事相同工作的所有员工

select * from emp where job=(select job from emp where ename='MILLER');

12. 列出所有员工的姓名及其直接上级的姓名

select a.ename,b.ename from emp a,emp b w select ename,(select ename from emp where empno=a.mhere a.mgr=b.empno(+);

13. 列出各种工作职位的最低工资，且显示最低工资大于1500的记录

select job,min(sal) from emp group by job having min(sal)>1500

14. 找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK)， 既不是经理又不是办事员但其薪金大于或等于20

select * from emp where (deptno=10 and job=upper('manager')) o sal>=2000)

job=upper('clerk ')) or (job<>upper(‘manager’) and job<>upp

二、使用system/manager用户/密码登陆，并完成以下操作

1.创建一个用户teacher，密码是math，默认表空间users，临时表空间temp无配额,帐户不锁定 CREAT USER teacher IDENTIFIED BY math DEFAULT TABLESPACE user TEMPORARY TABLESPACE temp ACCOUNT UNLOCK

2.创建一个口令有效期为7天，锁定前允许的最大失败登陆次数为3 的概要文件MYPROFILE，并分配给用户teacher CREAT PROFILE MYPROFILE LIMIT PASSWORD-LIFE-TIME 7 FAILED-LOGIN-ATTEMPTS 3;

ALTER USER teacher PROFILE lock-account;

3.创建一个角色，具有连接到数据库以及创建表的权限，并把这个角色赋给用户teacher CREAT ROLE X

GRANT CONNECT,CREAT TABLE TO X; GRANT X to teacher;

4.以用户teacher登陆，并建立下面Courses和Students两个表，并插入相应的数据 Courses表，要求其中CourseCode为主键，CourseName非空 CourseCode X001 X002 CourseName 数据库原理 计算机网络原理 Description 计算机系必修 计算机系选修

Students表，要求StudentCode为主键，CourseCode 为外键，score在0-100之间 StudentCode T08001 T08002 Name 赵磊 吴吉 BirthDate 1989-5-30 1987-12-3 CourseCode X001 X002 Score 89 95 CREAT TABLE Courses

(CourserCode number(4) primary key courseName varchar2(20) not null Description varchar2(20));

Insert into Courses(coursecode,coursename,descpiption)values(x001,’数据库原理’,’计算机必修’); Insert into Courses(coursecode,coursename,descpiption)values(x002,’计算机网络原理’,’计算机选修’);

CREAT TABLE Sutudents

(studentcode number(4)primary key, Name varchar2(20), Birthdate ,

Coursecode number(4) references courses(coursecode0, Score number(2) check(score between 0 and 100));

Insert into students(studentcode,name,birthdate,coursecode,score)values(t08001,’赵磊’,1989-5-30,x001,89); Insert into students(studentcode,name,birthdate,coursecode,score)values(t08002,’吴吉’,1987-12-3,x002,95); 三、程序填空题：(本题28分，每空2分)

1.创建一个以部门号为参数，返回该部门最高工资的函数。 得 分 CREATE OR REPLACE FUNCTION return_maxsal(p_deptno emp.deptno%TYPE)

评卷人 RETURN____emp.deptno%type__________

AS

v_maxsal emp.sal%TYPE; BEGIN

SELECT max(sal) INTO v_maxsal FROM emp WHERE__deptno=p_deptno_____________ RETURN____v_maxsal;________ EXCEPTION

WHEN____no_data_found ______________THEN DBMS_OUTPUT.PUT_LINE('The deptno is invalid!'); END return_maxsal;

2.计算scott.emp表各个员工年薪，如果小于30000，则涨工资20%，其他则涨15% DECLARE

v_sal NUMBER(7,2);

CURSOR emp_cur IS ___select _____sal from__emp;_____________ BEGIN

_____open emp-cur___;___________ LOOP

FETCH emp_cur INTO v_sal;

EXIT WHEN ____emp-cur%NOTFOUND_;__________________ IF v_sal<30000 THEN

UPDATE emp SET sal=sal*1.2 WHERE CURRENT OF emp_cur; ELSE

UPDATE emp SET sal=sal*1.15 WHERE CURRENT OF emp_cur; END IF; END LOOP;

CLOSE emp_cur; END;

3.在scott.dept表上创建一个触发器tr_update_dept，在修改的部门号时，同时更新scott.emp表中相应的员工的部门号。

create or replace trigger tr_update_dept

__after update of deptno on dept__________________________ for each row begin

update emp set deptno=____:new.deptno_______________ where deptno=_____:old.deptno;___________ end;

4. 创建一个序列，1-1000，每次加2，从100开始

CREATE SEQUENCE WORKER_ID

INCREMENT BY ____2__________MAXVALUE _1000_______START WITH ___100____________ 并使用以上创建的序列，在scott.dept表中插入一行

INSERT INTO EMP VALUES(_________worker-id nextral____________,'MARKETING ','HOUSTON');