《山东大学数据库系统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=200900130417
2、)(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=
3、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.stu
4、dent_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 lik
5、e (李%) 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=3000
6、03(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 where a
7、ge4 （6（create table test3_06 as select * from pub.Student_31 delete from test3_06 where length(translate(sid,0123456789,)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 lengt
8、h(dname)4 delete from test3_06 where name like % % or length(name)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 from pub.student)delete from test3_10 where cid not in (select cid from pub.cours
9、e)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 table test4_01 add sum_score number update test4_01 set sum_score = (select sum
10、 (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 avg (score) from pub.student_course where test4_02.sid= pub.student_course.sid)(
11、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 score90 and pub.student_course.cid=(select cid from pub.course where pub.course.name=数据库系统)(5
12、)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_course.cid and pub.student_course.sid=pub.student.sid and pub.student.name=李龙(6)create view test6_06 as select sid,name
13、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 pub.student where sid in (select sid from pub.student_course where score=60 group by sid having count(*) =(select count(*)
14、 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.student.sid, pub.student.name,sum(credit) sum_credit from pub.student, pub.student_course,pub.course where pub.student.sid =
15、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 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 tabl
16、e 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的区别之一在于对重复结果的 处理。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 lett
