数据更新与视图

一、实验目的

1．掌握数据插入、修改和删除语句的语法格式和使用方法。

2．通过对视图的操作掌握视图虚表的本质，学会视图的定义方法。

3．理解基于视图的查询和更新的操作过程，知道视图更新的限制，在Oracle中学会使用user_updatable_columns系统表查询视图可否更新。

二、实验内容

1．在实验一创建的student,course,teacher,sc,tc表中用SQL语句完成以下操作：

（1）学生“刘家顺”要退学，请删除该学生的所有记录（包括选课记录和学生记录）。

delete from sc where sno in(select sno from student where sname = '刘家顺');
delete from student where sname = '刘家顺';

（2）田小亮同学要转专业，他的学号为103465，将该同学的专业由原来的“软件工程”改为“计算机科学与技术”，班级由原来的“软工3班”修改为“计算机1班”。

update student set smajor = '计算机科学与技术', sclass='计算机1班' where sno='103465';

（3）删除选修了“嵌入式系统与应用”但没有及格的选课记录。

delete from sc where sno in(
select sno from course natural join sc where course.cname='嵌入式系统与应用' and sc.grade<60);

（4）将元组（1203076，吕振华，男，1985/11/8，讲师，体育部）插入教师关系。

insert into teacher values('1203076','吕振华','男',to_date('1985/11/8','yyyy/dd/mm'),'讲师','体育部');

（5）在course表中插入以下两门课程信息：

课程号：3094217，课程名：图与网络，课程学分：2，课程性质：选修，课程学时：32，开课学期：6

课程号：3094215，课程名：智能搜索与推荐技术，课程学分：2，课程性质：选修，课程学时：32，开课学期：7

insert into course values('3094217','图与网络','2','选修','32','6');
insert into course values('3094215','智能搜索与推荐技术','2','选修','32','7');

（6）创建名为xg_view的视图，在视图中显示所有信息工程学院教师的授课信息，包括教师姓名，职称，所授课程名称，授课班级，授课学期。

create view xg_view 
as 
select teacher.tname ,teacher.ttitle,tc.sclass,tc.semester 
from tc natural join teacher;

（7）创建每个学生已修课程总学分的视图，视图名为xf_view，要求在视图中显示学生学号、姓名和总学分。

create or replace view xf_view as
select student.sno,student.sname ,sum(course.credit) credit
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and sc.grade>=60
group by student.sname,student.sno;

（8）在创建的视图中查询大数据2班每位学生已修的总学分。

select student.sname,xf_view.credit 
from student,xf_view where student.sno=xf_view.sno and student.sclass='大数据2班';

（9）在系统表user_updatable_columns中查看视图xf_view是否可以更新，如果不能请说明原因。

select * from user_updatable_columns  where table_name='XF_VIEW';

2．在实验一创建的s，p，j和spj表中用SQL语句完成以下操作：

（1）现有一供应商，代码为s9、姓名为英特尔、所在城市西安，供应情况如下：供应零件p5给工程j7数量为600，供应零件p4给工程j4数量为500，请将此供应商的信息和供应信息插入数据库。

 

insert into s values('s9','英特尔','西安',null);
insert into spj values('s9','p5','j7','600');
insert into spj values('s9','p4','j4','500');

（2）请将北京供应商的供应数量加150。

update spj set qty=qty+150 where sno in(select sno from s where city='北京');

（3）把全部红色零件的颜色改为黑色。

update p set color='黑' where color in (select color from p where color='红');

（4）由s5供给j4的零件p6改为由s1供应。

update spj set sno='s1' where sno in 
(
       select sno from p where sno='s5' and pno='p6' and jno='j4'
);

（5）请将（s2,j7,p4,510）插入供应情况关系。

insert into spj values('s2','p4','j7','510');

（6）零件p1已经停产，请将p1的相关信息从数据库中删除。

delete from p natural join spj where p.pno='p1';
select * from p natural join spj where pno='p1';

（7）创建零件名为螺丝刀的供应情况的视图sd_view，视图中显示供应商名（Sname），零件名（Pname），零件重量（Weight），工程项目代码（Jno），供应数量（QTY）。

create or replace view sd_view 
as
select s.sname,p.pname,p.weight,spj.jno,spj.qty from s,p,spj
where s.sno=spj.sno and p.pno=spj.pno and p.pname='螺丝刀';

（8）在视图sd_view中查询供应数量在400到600之间的供应商姓名。

select sname from sd_view where qty between 400 and 600;

（9）请为三建工程项目建立一个供应情况的视图SJ_View，包括供应商编号，零件编号，供应数量。

create or replace view sj_view 
as
select sno,pno,qty from spj;