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

Test2

(1)create table test2_01 as select sid,name

From pub.student p

where 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=’200900130417’)

(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.cid=course.cid and name='操作系统' )

And

sid 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.student

Where pub.student_course.sid=pub.student.sid and age='20'

group by student.sid,name

(6)create table test2_06 as select cid,max(score)max_score

From pub.student_course

Group by cid

(7)create table test2_07 as select sid,name

From pub.student

Where name not in (select name from pub.student where name like (‘张%’) or name like (‘李%’) or name like (‘王%’)

(8)create table test2_08 as select substr(name,1,1) second_name,count (*) p_count

From pub.student

Group by substr(name,1,1)

(9)create table test2_09 as select pub.student.sid,name,score

From pub.student,pub.student_course

Where pub.student.sid=pub.student_course.sid and cid='300003'

(10)create table test2_10 as select sid,cid

From pub.student_course

Where score is not null

Test3

（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 where age<>2012-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 length(dname)<3 or dname like '% %'

（5）create table test3_05 as select * from pub.Student_31

delete from test3_05 where length(class)>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 age<>2012-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 length(dname)<3 or dname like '% %'

delete from test3_06 where length(class)>4

delete from test3_06 where name like '% %' or length(name)<2

(7)create table test3_07 as select * from pub.Student_course_32

delete from test3_07 where sid not in (select sid from pub.student)

(8)create table test3_08 as select * from pub.Student_course_32

delete 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_32

delete from test3_09 where score <0 or score >100

(10)create table test3_10 as select * from pub.Student_course_32

delete from test3_10 where score <0 or score >100

delete from test3_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 as select * from pub.student_41

alter 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_41

alter table test4_02 add avg_score numeric(5,1)

update 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_41

alter table test4_03 add sum_credit int

create table t4_031 as select * from pub.course natural join pub.student_course

update t4_031 set credit=0 where score<60

update 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_41

update test4_04 set dname=(select did from pub.department where pub.department.dname=test4_04.dname)

where dname in (select dname from pub.department)