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

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) 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’) 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 create table test2_06 as select cid,max(score)max_score From pub.student_course Group by cid 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 (‘王%’) 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 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 create table test3_02 as select * from pub.Student_31 delete from test3_02 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=男