oracle操作题

一、建表，修改表

CREATE TABLE XSB (

XH char(6) NOT NULL PRIMARY KEY, XM char(8) NOT NULL, XB char(2) DEFAULT '1' NOT NULL, CSSJ date NOT NULL, ZY char(12) NULL, ZXF number(2) NULL, BZ varchar2(200) NULL );

CREATE TABLE KCB ( KCH char(3) NOT NULL PRIMARY KEY, KCM char(16) NOT NULL, KKXQ number(1) NULL, XS number(2) NULL, XF number(1) NOT NULL );

CREATE TABLE CJB (

XH char(6) NOT NULL, KCH char(3) NOT NULL, CJ number(2) NULL, PRIMARY KEY(XH, KCH) );

二、查询

1、列出XSB表中所有记录的XM、XB和ZY列。 2、显示所有的专业名称即不重复的专业列。

3、显示CJB表中成绩在60到80之间的所有记录。 4、显示CJB表中成绩为85，86或88的记录。

5、显示XSB表中“计算机”专业或性别为“女”的同学记录。 6、显示“计算机”专业的学生人数。

7、显示CJB表中的最高分的学生学号和课程号。 8、显示“212”号课程的平均分。 9、*显示CJB表中至少有5名学生选修的且课程号以“2”开头的课程的平均分数。 10、*显示最低分大于70，最高分小于90 的XH。 11、显示所有学生的 XM、 KCH和CJ列。 12、显示所有学生的 XM、 KCM和CJ列。 13、列出“计算机”专业所选课程的平均分。 14、显示成绩高于学号为“101101”、课程号为“101”的成绩的所有成绩记录。

15、显示出和学号为“101101”的同学同年出生的所有学生的 XH、XM和 CSSJ列。 16、显示“软件工程”课程的学生成绩。 17、*显示选修人数多于5人的课程名称。 18、*显示存在有85分以上成绩的课程号。

19、*显示 “101”课程成绩高于“102”课程成绩的同学的学号、姓名和成绩。 select xsb.xh, xsb.xm , t1.cj

from ( select * from cjb where kch = 101 ) t1, (select * from cjb where kch = 102 ) t2, xsb where t1.cj > t2.cj and t1.xh = t2.xh and t1.xh = xsb.xh ;

Select cno sno,degree from score Where cno=‘3-105’ and degree> ALL (select degree from score where cno=‘3-245’) ;

select cno dno,degreefrom score where cno=’ 101’

and degree >(select max(degree) from score

where cno=’102 ’);

20、*列出没有学生选修的课程的基本信息。

select *from kcb where not exists (

select *from xsb Where xh in

(select xh From cjb Where xsb.xh=cjb.xh) );

21、*检索所有学生都选修的课程名称。

select * from kcb where not exists ( select * from xsb where not EXISTS ( select * from cjb where xh = xsb.xh and kch = kcb.kch));

22、*检索所选课程包含学生“101101”所选课程的学生学号。 select distinct xh from cjb t1 where not exists

( select * from cjb t2

where t2.xh = 101101 and not exists

(select * from cjb t3 where t3.xh = t1.xh and t3.kch = t2.kch ) );

三、数据增删改

1、往kcb表中添加一门课程信息（105，数据结构，2，80，5） insert into kcb(kch,kcm,kkxq,xs,xf)values(105，数据结构，2，80，5); 2、每个学生都要选修数据结构（105），将选课信息加入到cjb表。 insert into cjb(xh,kch,cj) select xh,105,null from xsb;

3、将“计算机”专业全体学生的 102号成绩修改为空值。

update cjb set cj=null where xh in(select xh from xsb where zy='计算机') and kch='102'; 4、删除“通信工程”专业所有学生的选课记录。

delete from cjb where xh in(select xh from xsb where zy='通信工程') 四、函数、存储过程和触发器

1、在scott方案下创建函数 cj_avg，要求输入课程编号作为参数，函数返回相应课程的平均成绩。

2、在scott中创建存储过程xs_avg，该存储过程包括一个输入参数 ，用于获取学号，一个输出参数用于返回指定学号的平均成绩。

3、 在cjb表上创建一个触发器，当删除记录的时候，如果被删除的课程成绩低于60则不能删除该选课记录。

Create or replace trigger del_cj before delete on cjb for each row when(old.cj<60) begin

raise_application_error(-20000,”被删除的学生成绩低于60分”)； end;

4、在学生表(xsb)中创建触发器，要求新插入的学生记录的年龄不能超过现有学生的最大年龄。

Create or replace trigger ins_age Before insert on xsb on each row Declare Time date;

Begin

Select min(ccsj) into time from xsb;

if(:new.ccsj

raise_applicartion_error(-20001,”新插入的学生记录的年龄不能超过现有的最大年纪”); End; 5、在cjb中创建触发器，当修改学生成绩时，成绩的变动不能超过10分，否则不允许修改。 create or replace trigger upda_cj after update of cj on cjb for each row when(old.cj>new.cj+10 or old.cj

6、在xsb表中创建触发器，当删除学生记录的时候，如果该生有不及格选课记录，则不允许删除。

create or replace trigger del_xs before delete on xsb for each row

declare num number; begin select count(xh) INTO num from xsb,cjb where xsb.XH = cjb.XH and cj<60; group by xh; if num = 0 then raise_application_error (-20000,'不允许删除'); end if; end;

7、 在xsb表上创建触发器，禁止修改学生的学号。 create or replace trigger upda_xh before update of XH on xsb for each row begin

raise_application_error(-20001,'不能修改学生学号'); end;

8、在cjb表上创建触发器，限制每个学生选课记录不能超过3. create or replace trigger ins_cjb before insert on cjb for each row declare num number; begin select count(*) into num

from cjb group by xh; if num>3 then

raise_application_error (-20001,'每个学生选课记录不能超过3个'); end if; end;

四、游标

DECLARE v_xh char(6); v_zxf number(2); CURSOR XS_CUR3 IS SELECT XH,ZXF FROM XSB WHERE ZY= '计算机'; BEGIN

OPEN XS_CUR3; FETCH XS_CUR3 INTO v_xh, v_zxf; WHILE XS_CUR3%FOUND LOOP DBMS_OUTPUT.PUT_LINE (v_xh+v_zxf); FETCH XS_CUR3 INTO v_xh, v_zxf; END LOOP; CLOSE XS_CUR3; END;

五、用户与权限管理

1、使用sql语句创建用户 2、给用户解锁 3、给用户授权

本文来源：https://www.bwwdw.com/article/euih.html