山东大学数据库实验答案.doc

数据库实验（一）1、create table test1_teacher( tid char(6) primary key,name varchar(10) not null, sex char(2),age int, dname varchar(10) ) create index index_table1 on test1_teacher(name); insert into test1_teacher values(100101,张老师,男,44,计算机学院); insert into test1_teacher values(100102,李老师,女,45,软件学院); insert into test1_teacher values(100103,马老师,男,46,计算机学院);2、create table test1_student( sid char(12) primary key, name varchar(10) not null,sex char(2), age int, birthday date, dname varchar(10),class varchar(10) create index index_table2 on test1_student(name); insert into test1_student values(200800020101,王欣,女,19, to_date(19940202,yyyymmdd),计算机学院,2010); insert into test1_student values(200800020102,李华,女,20, to_date(19950303,yyyymmdd),软件学院,2009); insert into test1_student values(200800020103,赵岩,男,18, to_date(19960404,yyyymmdd),软件学院,2009);3、create table test1_course( cid char(6) primary key, name varchar(10) not null, fcid char(6),credit numeric(2,1) ) create index index_table3 on test1_course(name); insert into test1_course values(300001,数据结构,2); insert into test1_course values(300002,数据库,300001,2.5); insert into test1_course values(300003,操作系统,300001,4);4、 create table test1_student_course( sid char(12) , cid char(6) , score numeric(5,1), tid char(6), primary key(sid,cid), FOREIGN KEY (sid) REFERENCES test1_student(sid), FOREIGN KEY (cid) REFERENCES test1_course(cid), FOREIGN KEY (tid) REFERENCES test1_teacher(tid) ) insert into test1_student_course values(200800020101,300001,91.5,100101);insert into test1_student_course values(200800020101,300002,92.6,100102);insert into test1_student_course values(200800020101,300003,93.7,100103);5、create table test1_teacher_course( tid char(6) , cid char(6) , primary key(tid,cid), FOREIGN KEY (tid) REFERENCES test1_teacher(tid), FOREIGN KEY (cid) REFERENCES test1_course(cid) ) insert into test1_teacher_course values(100101,300001); insert into test1_teacher_course values(100102,300002); insert into test1_teacher_course values(100103,300003);数据库实验（二） 检索查询1、 create table test2_01 as select sid ，name from pub.student where sid not in(select sid from pub.student_course)2、create table test2_02 as select distinct student.sid，name from pub.student, pub.student_course where student_course.sid = student.sid and student_course.cid in (select cid from pub.student_course where sid=200900130417)3、create table test2_03 as select distinct student.sid，name from pub.student, pub.student_course where student_course.sid = student.sid and student_course.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.student_course,pub.course where student_course.cid=course.cid and name =操作系统) and sid in (select sid from pub.student_course,pub.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,pub.student_course where student.sid = student_course.sid and age =20 group by student.sid，name使用CAST： CAST ( expression AS data_type ) 使用CONVERT： CONVERT (data_type(length), expression , style)6、create table test2_06 as select sid，max(score） max_score from pub.student_course group by cid7.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) SUBSTR(string,start,count)取子字符串，从start开始（如果start是负数，从尾部开始），取count个.上述就是PL/SQL函数的解释，从中可以看出，是1开始从左开始取数；如果是负值，那么就从右开始取数。9、create table test2_09 as select student.sid，，score from pub.student,pub.student_course where student.sid = student_course.sid and cid =30000310、create table test2_10 as select sid，cid from pub.student_course where score is not null 实验三1.create table test3_01 as select * from pub.student_31;delete from test3_01 where not regexp_like(sid,:digit:+$)2.create table test3_02 as select * from pub.student_31;delete from test3_02 where sid not in(select sid 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 sid not in(select sid from test3_03 where sex=男or sex=女or sex is null)4.create table test3_04 as select * from pub.student_31;delete from test3_04 where sid in(select sid 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 sid not in(select sid from test3_05 where class not like%级 and length(class)=4)6.7.create table test3_07 as select * from pub.Student_course_32;delete from test3_07 where sid not in(select pub.Student.sid from pub.student,pub.Student_course_32 where pub.student.sid=pub.Student_course_32.sid)8-.create table test3_08 as select * from pub.Student_course_32;delete from test3_08 where sid in(select pub.Student_course_32.sid from pub.teacher_course,pub.Student_course_32 where pub.teacher_course.tid=pub.Student_course_32.tid and pub.teacher_course.cid=pub.Student_course_32.cid)9.create table test3_09 as select * from pub.Student_course_32 where score between 0 and 10010.create table test3_10 as select sid,pub.Student_course_32.cid,score,pub.Student_course_32.tid from pub.Student_course_32,pub.teacher_course where pub.Student_course_32.cid = pub.teacher_course.cid and pub.Student_course_32.tid = pub.teacher_course.tid delete from test3_10 where sid not in(select sid from test3_10 where score between 0 and 100)delete from test3_10 where sid not in(select pub.student.sid from pub.student,pub.student_course_32 where pub.student.sid=pub.student_course_32.sid)delete from test3_10 where sid not in(select sid from pub.course,pub.student_course_32 where pub.course.cid=pub.student_course_32.cid)delete from test3_10 where sid not in(select sid from pub.teacher,pub.student_course_32 where pub.teacher.tid=pub.student_course_32.tid 实验4.1.create table test4_01 as select * from pub.student_41alter table test4_01 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2)update test4_01 set sum_score=(select sum(score) from pub.student_course where pub.student_course.sid=test4_01.sid group by pub.student_course.sid )2.create table test4_02 as select * from pub.student_41alter table test4_02 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2)update test4_02 set avg_score=(select avg(score) from pub.student_course where pub.student_course.sid=test4_02.sid group by pub.student_course.sid )3.create table test4_03 as select * from pub.student_41alter table test4_03 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2)update test4_03 set sum_credit=(select sum(credit) from pub.student_course,pub.course where pub.student_course.cid=pub.course.cid and pub.student_course.sid=test4_03.sid group by pub.student_course.sid)4.create table test4_04 as select * from pub.student_41alter table test4_04 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2)create table temp as select * from pub.department union select * from pub.department_41update test4_04 set did=(select did from temp where temp.dname=test4_04.dname)update test4_04 set did=00 where did is nulldrop table temp5.create table test4_05 as select * from pub.student_41alter table test4_05 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2)update test4_05 set sum_score=(select sum(score) from pub.student_course where pub.student_course.sid=test4_05.sid group by pub.student_course.sid )update test4_05 set avg_score=(select avg(score) from pub.student_course where pub.student_course.sid=test4_05.sid group by pub.student_course.sid )update test4_05 set sum_credit=(select sum(credit) from pub.student_course,pub.course where pub.student_course.cid=pub.course.cid and pub.student_course.sid=test4_05.sid group by pub.student_course.sid)create table temp as select * from pub.department union select * from pub.department_41update test4_05 set did=(select did from temp where temp.dname=test4_05.dname)update test4_05 set did=00 where did is nulldrop table temp6.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=substr(sex,1,1) where sex like_性update test4_07 set sex=replace(sex, ,)8.create table test4_08 as select * from pub.student_42update test4_08 set class=substr(class,1,4) where class like _级9.create table test4_09 as select * from pub.student_42update test4_09 set age=2012-extract(year from birthday) where age is null10.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=substr(sex,1,1) where sex like_性update test4_10 set sex=replace(sex, ,)update test4_10 set class=substr(class,1,4) where class like _级update test4_10 set age=2012-extract(year from birthday) where age is null实验六1.create view test6_01 as select sid，name，dname from pub.student where age905.create view test6_05 as select pub.student.sid,pub.course.cid,,score from pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and pub.student_course.cid=pub.course.cid and =李龙6.create view test6_06 as select sid from pub.student_course where pub.student_course.cid =all(select cid from pub.course) group by sid7.create view test6_07 as select pub.student.sid, from pub.student where pub.student.sid in(select sid from pub.student_course)8.create view test6_08 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.credit=29.create view test6_09 as select pub.student.sid,,sum(credit) sum_credit from pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and pub.student_course.cid=pub.course.cid and pub.student.class=2010 and pub.student.dname=化学与化工学院 and score=60 group by pub.student.sid,10. create view test6_10 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.fcid is not nul实验七.综合查询1.create view a as select SUBSTR(name,2,2) as First_name from pub.studentcreate view b as select First_name,count(First_name) as frequency from a group by First_namecreate table test7_01 (First_name varchar(4),frequency numeric(4)insert into test7_01(First_name,frequency) select * from b 2.create view a as select SUBSTR(name,2,1) as letter from pub.student union all (select SUBSTR(name,3,1) as letter from pub.student where SUBSTR(name,3,1) is not null)create view b as select letter,count(letter) as frequency from a group by letter create table test7_02(letter varchar(2),frequency numeric(4)insert into test7_02(letter,frequency) select * from b3.create table test7_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,classcreate table t as select dname,class,pub.student.sid,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.sidupdate test7_03 set p_count1=(select count(sid) num from t where sum_credit= 10 and test7_03.dname = t.dname and test7_03.class=t.class group by dname,class)update test7_03 set p_count1= 0 where p_count1 is nullupdate test7_03 set p_count2 = p_count-p_count14.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,classcreate table t as select dname,class,pub.student.sid,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.sidupdate test7_04 set p_count1= (select count(sid) num from t where sum_credit= 8 and test7_04.dname = t.dname and test7_04.class=t.class group by dname,class)where test7_04.class= 10 and test7_04.dname = t.dname and test7_04.class=t.class group by dname,class)where test7_04.class2008update test7_04 set p_count1= 0 where p_count1 is nullupdate test7_04 set p_count2 = p_count-p_count1 实验八.报表统计1.create table test8_01(Dname varchar(30),Avg_ds_score int,Avg_os_score int)insert into test8_01 select dname,round(avg(score),0),0 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 dname is not null and =数据结构 group by dnameupdate test8_01 set Avg_os_score=(select avg(score) 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 dname is not null and test8_01.dname=pub.student.dname and =操作系统 group by dname)2.select * from pub.studentcreate table test8_02(sid varchar(12),name varchar(10),dname varchar(30),ds_score int,os_score int)insert into test8_02 select pub.student.sid,,dname,0,0 from pub.student where dname=计算机科学与技术学院 and sid in (select sid from pub.student_course where cid=(select cid from pub.course where name=数据结构) intersect(select sid from pub.student_course where cid=(select cid from pub.course where name=操作系统)update test8_02 set ds_score=(select score from pub.student_course where cid=(select cid