数据结库原理实验报告Word文件下载.docx

1、create index xsno on agents （aid）create index xsno on products （pid）create index xsno on orders （ordan）（5）drop index customer.xsnodrop index agents.xsnodrop index products.xsnodrop index orders.xsno1.2 （1）select * from courses（2）select sid from choices（3）select cid from courses where hour400（5） sele

2、ct count （cid）from courses（6）select cid,count（sid）from choices group by cid（7）select sid from choices where score60 group by sid having count （cid）2（8）select sid,count（cid）,avg（score）from choices group by sid（9）select students.sid,sname from students,choices,courseswhere students.sid=choices.sid and

3、 choices.cid=courses.cid and ame=java（10）select choices.cid,choices.score from choices, students where sname=ssshtand choices.sid=students.sidselect cid,score from choices where sid in（select students.sid from students where sname=）（11）select ame from courses as c1,courses as c2 where c1.hourc2.hour

4、 and ame=c+（12）select sid,sname from students where sid in （select c1.sid from choices as c1,choices as c2 where c1.scorec2.score and c1.cid=c2.cidand c2.sid=（select sid from students where sname=znkooand c1.cid=（select cid from courses where cname=）（13）select sname from students where grade in（sele

5、ct grade from students where sid in （883794999,850955252（14）select sname from students where sid not in （select sid from choices where cid =（select cid from courses where cname=（15）select * from courses where hour=（select salary from teachers where teachers.tid=choices.tid）（17）select sid from choice

6、s where score=（select max（score）from choices where cid=（select cid from courses where cname=erp（18）select cname from courses where cid not in （select cid from choices）（19）select cname from courses where cid =some （select cid from choices where tid =some （select tid from courses,choices where cname =

7、umland courses.cid=choices.cid）（20）select sname from students where not exists（select *from choices as c1 where not exists（select * from choices as c2 where c2.sid=students.sid and c2.cid=c1.cid and c2.tid =200102901（21）select sid from choices,courses where courses.cid=choices.cid and ame=databaseun

8、ion select sid from choices,courses where courses.cid=choices.cid and ame=（22）select x.sid from choices as x,choices as y where （x.cid=（select cid from courses where cname=and y.cid=（select cid from courses where cname=）and x.sid=y.sid（23）and x.sid=y.sid and not （y.cid=（select cid from courses where

9、 cname=1.3（1）insert into students（sid,sname） values（800022222WangLaninsert into teachers values（200001000LXLs4zrck3024update teachers set salary=4000 where tid =200010493update teachers set salary=2500 where salary2500update choices set tid=（select tid from teachers where tname=rnupx）where tid=20001

10、6731（6）update students set grade=2001 where sid=800071780（7）delete from courses where cid not in （select cid from choices group by cid）delete from students where grade1998delete from students where sid not in （select sid from choices group by sid）delete from choices where scoreselect * from views wh

11、ere sname=uxjofselect sid ,score from viewc where cname=insert into s1 values（60000001Lily,2001）create view s2（sid,sname,grade）asselect sid,sname,grade from students where grade1998 with check option插入元组：insert into s2 values（,1997）结果讨论：加入了 WITH CHECK OPTION 子句后，使得所有的对视图的插入或更新操作都必须满足定义视图时所指明的条件，在本题就

12、是GRADE1998,题目中要插入的元组并不满足这个条件，GRADE=19971998），但是，由于基本表STUDENTS和表CHOICES之间存在引用完整性约束，而将GRADE=1999的元组删除将违反了它们之间的引用完整性约束，所以出现了上面的错误。update views set score=score+5 where sname=drop view viewc drop view views drop view s1 drop view s21.5 grant select on students to public（2） grant select,update on courses

13、to publicgrant select ,update（salary） on teachers to user1 with grant optiongrant select,update（score） on choices to user2create view TV as select tid,tname,email,salary from teachers grant select on TV to user2（6）以用户USER1身份登录数据库后，执行，grant select on teachers to user2 with no option（7） 以用户USER2身份登录数据

14、库后，执行，grant select on teachers to user3 with no option以用户USER3身份登录数据库后，执行，revoke select on teachers from user1 cascade操作不成功，取消授权操作存在级联效应。revoke select ,update on courses from user1,user21.6（1）select cid,hour*18 from coursesselect count （*）from choices where cid=（select cid from courses where cname=s

15、elect count （*）from choices where score=60 and cid=（select cid from courses where cname=select count （*）from choices where score=all（select grade from students）select count（*） from students,teachers where teachers.tid=students.sid实验22.1create table Class（ Class_id varchar（4）, name varchar（10）,Deparm

16、ent varchar（20） constraint PK_Class Primary key （Class_id）begin transaction T3 insert into class values（0000101CSCCSbegin transaction T4 insert into class values（commit transaction T4 commit transaction T3 由于T4中插入违法，T4失败，而且整个T3事物回滚，T3中的插入也不成功。2.2相应结果：数据库不允许删除students表以及course表中对应的元组。由于on delete rest

17、rict的约束，数据库不允许任何引用关系存在对应元组时进行删除操作。 约束on delete set NULL是将要删除的对应元组的外键置空值，如果cno以及sno不是SC表的主键，删除操作是可以完成的，但是由于主键不可以取空值，所以删除操作不可以进行。create table help（sid char（8）,sname varchar（20）,help_id char（8） not nullconstraint PK_help primary key （sid）alter table help add constraint FK_help foreign key （help_id） ref

18、erences help（help_id）create table leader（ sid char（9）,sname varchar（20）,myleader char（9） constraint PK_leader primary key（sid）create table monitor（sid char（9）,sname varchar（20）,mymonitor char（9） constraint PK_monitor primary key（sid）constraint FK_monitor foreign key（mymonitor） references leader（sid）

19、alter table leaderadd constraint FK_leader foreign key（myleader）references monitor（sid）2.3create table worker（Number char（5）,Name char（8） constraint U1 unique,Sex char（1）,Sage int constraint U2 check （Sage=0）;GO create rule rule_sage as value between 1 and 100go exec sp_bindrule rule_sage,worker.sag

20、e;2.4GO create trigger T4 on workerfor insert asif（select sage from inserted）=（select max（sage）from worker）begin printthe sage of couple must be more than the existed couple sage!rollback transaction endUSE SchoolGO create trigger T5 on workerfor update as if（select sage from inserted）=（select sage from deleted）the sage of new couple must be more than the sage of old couple !