山东大学数据库系统SQL上机实验代码test2-test8(2014最新版)

1、Test2(1)create table test2_01 as select sid,name From pub.student pwhere not exists (select cid from pub.student_course where sid=p.sid)create table test2_02 as select sid,name From pub.student natural join pub.student_course Where cid in (select cid from pub.student_course where sid=200900130417)cr

2、eate table test2_03 as select sid,name From pub.student natural join pub.student_course Where cid in (select cid from pub.course where fcid=300002)(4)create table test2_04 as select sid,name From pub.student Where sid in(select sid from pub.course ,pub .student_course where student_course.cid=course

3、.cid and name=操作系统 )Andsid in(select sid from pub.course ,pub .student_course where student_course.cid=course.cid and name=数据结构 )(5)create table test2_05 as select student.sid,name,cast(avg(score) as numeric(5,0) avg_score, sum (score) sum_score from pub.student_course,pub.studentWhere pub.student_c

4、ourse.sid=pub.student.sid and age=20group by student.sid,namecreate table test2_06 as select cid,max(score)max_scoreFrom pub.student_courseGroup by cidcreate table test2_07 as select sid,nameFrom pub.student Where name not in (select name from pub.student where name like (张%) or name like (李%) or na

5、me like (王%)create table test2_08 as select substr(name,1,1) second_name,count (*) p_countFrom pub.studentGroup by substr(name,1,1)(9)create table test2_09 as select pub.student.sid,name,scoreFrom pub.student,pub.student_course Where pub.student.sid=pub.student_course.sid and cid=300003create table

6、test2_10 as select sid,cidFrom pub.student_course Where score is not nullTest3（1）create table test3_01 as select * from pub.Student_31 delete from test3_01 where length(translate(sid,0123456789,)0create table test3_02 as select * from pub.Student_31 delete from test3_02 where age2012-extract(year fr

7、om birthday)（3）create table test3_03 as select * from pub.Student_31 delete from test3_03 where sex not in (select sex from test3_03 where sex=男 or sex=女 or sex=null)create table test3_04 as select * from pub.Student_31 delete from test3_04 where dname is null or length(dname)4 create table test3_06

8、 as select * from pub.Student_31 delete from test3_06 where length(translate(sid,0123456789,)12 Delete from test3_06 where age2012-extract(year from birthday) Delete from test3_06 where sex not in (select sex from test3_03 where sex=男 or sex=女 or sex=null)Delete from test3_06 where dname is null or

9、length(dname)4 delete from test3_06 where name like % % or length(name)2 (7)create table test3_07 as select * from pub.Student_course_32delete from test3_07 where sid not in (select sid from pub.student)(8)create table test3_08 as select * from pub.Student_course_32delete from test3_08 where (cid,ti

10、d) not in (select cid,tid from pub.teacher_course)(9)create table test3_09 as select * from pub.Student_course_32delete from test3_09 where score 100(10)create table test3_10 as select * from pub.Student_course_32delete from test3_10 where score 100delete from test3_10 where sid not in (select sid f

11、rom pub.student)delete from test3_10 where cid not in (select cid from pub.course)delete from test3_10 where tid not in (select tid from pub.teacher)delete from test3_10 where (cid,tid) not in (select cid,tid from pub.teacher_course)Test 4(1)create table test4_01 as select * from pub.student_41alter

12、 table test4_01 add sum_score number update test4_01 set sum_score = (select sum (score) from pub.student_course where test4_01.sid= pub.student_course.sid)(2)create table test4_02 as select * from pub.student_41alter table test4_02 add avg_score numeric(5,1) update test4_02 set avg_score = (select

13、avg (score) from pub.student_course where test4_02.sid= pub.student_course.sid)(3)create table test4_03 as select * from pub.student_41alter table test4_03 add sum_credit intcreate table t4_031 as select * from pub.course natural join pub.student_course update t4_031 set credit=0 where score60update

14、 test4_03 set sum_credit = (select sum(credit) from t4_031 where test4_03.sid=t4_031.sid)(4)create table test4_04 as select * from pub.student_41update test4_04 set dname=(select did from pub.department where pub.department.dname=test4_04.dname)where dname in (select dname from pub.department)(5)cre

15、ate table test4_05 as select * from pub.student_41alter table test4_05 add sum_score numberalter table test4_05 add avg_score numeric(5,1) alter table test4_05 add sum_credit intalter table test4_05 add did varchar(2)update test4_05 set sum_score =(select sum (score) from pub.student_course where te

16、st4_05.sid= pub.student_course.sid)update test4_05 set avg_score = (select avg (score) from pub.student_course where test4_05.sid= pub.student_course.sid)update test4_05 set sum_credit = (select sum(credit) from t4_031 where test4_05.sid=t4_031.sid)create table a1 as select * from pub.departmentinse

17、rt into a1 select * from pub.department_41 where dname not in (select distinct dname from pub.department )（6）create table test4_06 as select * from pub.student_42update test4_06 set name =replace(name, ,)（7）create table test4_07 as select * from pub.student_42update test4_07 set sex =replace(sex,性,)

18、update test4_07 set sex =replace(sex, ,)(8)create table test4_08 as select * from pub.student_42update test4_08 set class=replace(class,级,)update test4_08 set class=replace(class, ,)(9)create table test4_09 as select * from pub.student_42update test4_09 set age=2012-extract(year from birthday) where

19、 age is null(10)create table test4_10 as select * from pub.student_42update test4_10 set name=replace(name, ,)update test4_10 set dname=replace(dname, ,)update test4_10 set sex=replace(sex,性,)update test4_10 set sex=replace(sex, ,)update test4_10 set class=replace(class,级,)update test4_10 set class=

20、replace(class, ,)update test4_10 set age=2012-extract(year from birthday) where age is nullTest5create table test5_10 (test varchar(20),age numeric (3)insert into test5_10values (结果1,88),insert into test5_10values (结果2,90),insert into test5_10values (结果3,90),insert into test5_10values (结果4,86),inser

21、t into test5_10values (结果5,90),insert into test5_10values (结果6,90),insert into test5_10values (结果7,86),insert into test5_10values (结果8,86),insert into test5_10values (结果9,76),insert into test5_10values (结果10,86)Test6(1) create view test6_01 as select sid,name,dname from pub.student where age90 and p

22、ub.student_course.cid=(select cid from pub.course where =数据库系统)create view test6_05 as select pub.student_course.sid,pub.student_course.cid,score, from pub.course,pub.student_course,pub.student where pub.course.cid=pub.student_course.cid and pub.student_course.sid=pub.s

23、tudent.sid and =李龙create view test6_06 as select sid,name from pub.student where sid in (select sid from pub.student_course group by sid having count(*) =(select count(*) from pub.course )create view test6_07 as select sid,name from pub.student where sid in (select sid from pub.stude

24、nt_course where score=60 group by sid having count(*) =(select count(*) from pub.course )create view test6_08 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.credit=2create view test6_09 as select pub.student.sid, ,sum(credit) sum_credit from pu

25、b.student, pub.student_course,pub.course where pub.student.sid = pub.student_course.sid and pub.student_course.cid=pub.course.cid and class=2010 and dname=化学与化工学院 and score=60 group by pub.student.sid, create view test6_10 as select a1.cid, from pub.course a1,pub.course a2 whe

26、re a1.fcid =a2.cid and a2.fcid is not nullTest7create table a as select (substr(name,2) first_name from pub.student create table test7_01 as select first_name,(count(*) frequency from a group by first_nameUnion和Union All的区别之一在于对重复结果的处理。UNION会自动排序。UNION ALL不会对结果自动进行排序。create table t7_2 as (select (su

27、bstr(name,2,1) letter from pub.student union all select (substr(name,3,1) letter from pub.student) create table test7_02 as select letter,(count(*) frequency from t7_2 where letter is not null group by lettercreate table test7_03 (dname varchar(30),class varchar(10),p_count1 int,p_count2 int, p_coun

28、t int)insert into test7_03 select dname,class,0,0,count(sid) from pub.student where dname is not null group by dname,class create table t7_31 as select pub.student.sid,dname,class,sum(credit)sum_credit from pub.student,pub.course,pub.student_course where pub.student.sid=pub.student_course.sid and pu

29、b.student_course.cid=pub.course.cid and score=60 and dname is not null group by dname,class,pub.student.sid update test7_03 set p_count1=(select count(sid) from t7_31 where sum_credit= 10 and test7_03.dname =t7_31.dname and test7_03.class=t7_31.class group by dname,class) update test7_03 set p_count

30、1= 0 where p_count1 is null update test7_03 set p_count2=p_count-p_count1(4)create table test7_04 (dname varchar(30),class varchar(10),p_count1 int,p_count2 int, p_count int)insert into test7_04 select dname,class,0,0,count(sid) from pub.student where dname is not null group by dname,class create ta

31、ble t7_41 as select pub.student.sid,dname,class,sum(credit)sum_credit from pub.student,pub.course,pub.student_course where pub.student.sid=pub.student_course.sid and pub.student_course.cid=pub.course.cid and score=60 and dname is not null group by dname,class,pub.student.sid create table t7_42 as se

32、lect sid,dname,class from t7_41 where class =8 union select sid,dname,class from t7_41 where class 2008 and sum_credit=10update test7_04 set p_count1=(select count(sid) from t7_42 where test7_04.dname =t7_42.dname and test7_04.class=t7_42.class group by dname,class) update test7_04 set p_count1= 0 w

33、here p_count1 is null update test7_04 set p_count2=p_count-p_count1Test8(1)create table t8_1 as select sid,name,max(score) M_score from pub.student_course,pub.course where pub.student_course.cid = pub.course.cid and name in (数据结构,操作系统) group by sid,namecreate table t8_2 as select dname,(round(avg(M_

34、score),0) avg_ds_score from t8_1,pub.student where t8_1.sid=pub.student.sid and t8_1.name=数据结构 and dname is not null group by dnamecreate table t8_3 as select dname,(round(avg(M_score),0) avg_os_score from t8_1,pub.student where t8_1.sid=pub.student.sid and t8_1.name=操作系统 and dname is not null group