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

《山东大学数据库系统SQL上机实验代码test2——test8(2014最新版)》由会员分享，可在线阅读，更多相关《山东大学数据库系统SQL上机实验代码test2——test8(2014最新版)（6页珍藏版）》请在装配图网上搜索。

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)(2) 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=20090013041

2、7)(3) create 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.ci

3、d=course.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.s

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

5、 like (李%) or name like (王%)(8) 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

6、=300003(10) create table 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,)0（2） create table test3_02 as select * from pub.Student_31 delete from test3_02

7、where age2012-extract(year from 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)（4） create table test3_04 as select * from pub.Student_31 delete from test3_04 where dname is null or len

8、gth(dname)4 （6） create table test3_06 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

9、from test3_06 where dname is null or 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

10、_32delete from test3_08 where (cid,tid) 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 te

11、st3_10 where sid not in (select sid from 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

12、 as select * from pub.student_41alter 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) upd

13、ate test4_02 set avg_score = (select 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

14、_031 set credit=0 where score60update 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 (se

15、lect dname from pub.department)(5)create 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 (sc

16、ore) from pub.student_course where test4_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 a

17、1 as select * from pub.departmentinsert 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_42upd

18、ate test4_07 set sex =replace(sex,性,)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=2

19、012-extract(year from birthday) where 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=replac

20、e(class,级,)update test4_10 set class=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),inse

21、rt into test5_10values (结果4,86),insert 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,dna

22、me from pub.student where age90 and pub.student_course.cid=(select cid from pub.course where pub.course.name=数据库系统)(5) create view test6_05 as select pub.student_course.sid,pub.student_course.cid,score,pub.course.name from pub.course,pub.student_course,pub.student where pub.course.cid=pub.student_co

23、urse.cid and pub.student_course.sid=pub.student.sid and pub.student.name=李龙(6) 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 )(7) create view test6_07 as select sid,name from pu

24、b.student where sid in (select sid from pub.student_course where score=60 group by sid having count(*) =(select count(*) from pub.course )(8) create view test6_08 as select a1.cid,a1.name from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.credit=2(9) create view test6_09 as select pub.stu

25、dent.sid, pub.student.name,sum(credit) sum_credit from pub.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, pub.student.name(10) create view test6_10

26、 as select a1.cid,a1.name from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.fcid is not nullTest7(1) create 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_name(2) Union和Union All的区别之一在于对重复

27、结果的处理。UNION会自动排序。UNION ALL不会对结果自动进行排序。create table t7_2 as (select (substr(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 letter(3) create table test7

28、_03 (dname varchar(30),class varchar(10),p_count1 int,p_count2 int, p_count 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.cou

29、rse,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 update test7_03 set p_count1=(select count(sid) from t7_31 where sum_credit= 10 and test7_03.dname =t7_31.dname and te

30、st7_03.class=t7_31.class group by dname,class) update test7_03 set p_count1= 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(si

31、d) from pub.student where dname is not null group by dname,class create table 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

32、 is not null group by dname,class,pub.student.sid create table t7_42 as select 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

33、.class=t7_42.class group by dname,class) update test7_04 set p_count1= 0 where 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 (数

34、据结构,操作系统) group by sid,namecreate table t8_2 as select dname,(round(avg(M_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 whe

35、re t8_1.sid=pub.student.sid and t8_1.name=操作系统 and dname is not null group by dnamecreate table test8_01 as select * from (select * from t8_2) natural full outer join (select * from t8_3)(2)create table t8_11 as select sid,max(score) ds_score from pub.student_course,pub.course where pub.student_cour

36、se.cid = pub.course.cid and name =数据结构 group by sid create table t8_12 as select sid,max(score) os_score from pub.student_course,pub.course where pub.student_course.cid = pub.course.cid and name =操作系统 group by sid create table test8_02 as select pub.student.sid,pub.student.name,dname,ds_score,os_sco

37、re from pub.student,t8_11,t8_12 where pub.student.sid=t8_11.sid and pub.student.sid=t8_12.sid and dname=计算机科学与技术学院(3)create table t8_13 as select * from t8_11 natural full join t8_12create table test8_03 as select pub.student.sid,pub.student.name,dname,ds_score,os_score from pub.student,t8_13 where

38、pub.student.sid=t8_13.sid and dname=计算机科学与技术学院(4)create table t8_15 as select * from t8_11 natural full outer join t8_12create table t8_14 as select sid,name,dname from pub.student where dname=计算机科学与技术学院create table test8_04 as select sid,name,dname,ds_score,os_score from t8_14 natural left outer join t8_15