山东大学数据库系统实验
山东大学数据库实验
2-1
create or replace view test2_01 as
(select sid,name from pub.student where sid not in
(select sid from pub.student_course)
)
2-2
create or replace view test2_02 as
select pub.student.sid ,pub.student.name from
pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid and
pub.student_course.cid in (select cid from pub.student_course where sid=200900130417)
and pub.student.sid!=200900130417
group by pub.student.sid,pub.student.name
2-3
create or replace view test2_03 as
select pub.student.sid,pub.student.name
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid and
pub.student_course.cid in(select cid from pub.course where fcid=300002)
2-4
create or replace view test2_04 as
select sid,name from pub.student
where sid in
(select sid from pub.student_course where cid=300002) and
sid in(select sid from pub.student_course where cid=300005)```
2-5
create or replace view test2_05 as
select pub.student.sid,pub.student.name,
round(avg(pub.student_course.score))avg_score,
sum(pub.student_course.score)sum_score
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid
and pub.student.age=20
group by pub.student.sid,pub.student.name
2-6
create or replace view test2_06 as
select cid,name,max(score)max_score,count(score)max_score_count
from (select cid,sid,score from pub.student_course where (cid,score) in
(select cid,max(score)themax from pub.student_course group by cid) group by cid,sid,score) natural left outer join pub.course group by cid,name
2-7
create or replace view test2_07 as select sid,name from pub.student where
substr(name,0,1) not like '王%' and
substr(name,0,1) not like '李%' and
substr(name,0,1) not like '张%‘
2-8
create or replace view test2_08 as select substr(name,0,1) second_name ,count(substr(name,0,1)) p_count
from pub.student
group by substr(name,0,1)
3-1
create table test3_01(
sid char(12),name varchar(10),sex varchar(10),age number(22),birthday date,dname varchar(30),class varchar(10))
insert into test3_01 select * from pub.student_31
delete from test3_01 where regexp_like (sid,'[^0-9]')
3-2
delete from test3_02 where 2012-extract(year from birthday ) != age
3-3
delete from test3_03 where sex not like '男'
and sex not like '女' and sex is not null
3-4
delete from test3_04 where regexp_like (dname,' ')
or dname is null or length(dname) <3
3-5
delete from test3_05 where class not in (select class from pub.student)
3-6
3-7
delete from test3_07 where sid not in(select sid from pub.student)
3-8
delete from test3_08 where
(tid,cid) not in (select tid,cid from pub.teacher_course)
3-9
delete from test3_09 where
score <0 or score >100
3-10
4-1
create table test4_01 as
select * from pub.student_41
alter table test4_01 add sum_score int
update test4_01 set sum_score=(select sum(score)
from pub.student_course s where s.sid=test4_01.sid)
4-2
alter table test4_02 add avg_score number(3,1)
update test4_02 set avg_score=(select avg(score)
from pub.student_course s where s.sid=test4_02.sid)
4-3
update
test4_03 S
set
sum_credit=(
select
sum(credit)
from
(select sid,cid,credit from
pub.course natural join pub.student_course where score>=60 group by cid,sid,credit) T
where
T.sid=S.sid
)
4-4
update
test4_04 S
set
dname=(
select
did
from
pub.department T where
T.dname=S.dname
)where S.dname in (select dname from pub.department)
4-5
4-6
update test4_06 set name=translate(name,'\ ','\')
4-7
update test4_07 set sex=tranlate(sex,'\ ','\')
update test4_07 set sex='男' where sex='男性'
update test4_07 set sex='女' where sex='女性'
4-8
update test4_08 set class=translate(class,'\级','\')
4-9
update test4_09 set age=2012-extract(year from birthday) where age is null
4-10
5-1
insert into test5_01 (first_name,frequency) select substr(name,2,2) ,count(*) from pub.student group by substr(name,2,2)
5-2
insert into test5_02 (letter,frequency) select name2,count(*) from ((select substr(name,2,1) name2 from pub.student) union all (select substr(name,3,1) name2 from pub.student)) group by name2
delete from test5_02 where letter is null
5-3
//建表
create table test5_03(dname varchar(30),class varchar(10),P_count1 int,p_count2 int,p_count int)
insert into test5_04 (dname,class) select dname,class from pub.student where dname is not null group by dname,class
update test5_03 S set P_count=(select count(*) from test5_0333 T where T.dname=S.dname and T.class=S.class)
create table test5_033 as select sid,sum(credit) credit from(select sid,cid,credit from pub.course natural join pub.student_course where score>=60 group by sid,cid,credit)group by sid
create table test5_0333 as select * from (test5_033 natural join pub.student)
update test5_03 S set P_count1=(select count(*) from test5_0333 T where credit>=10 and T.dname=S.dname and T.class=S.class)
update test5_03 S set P_count2=p_count- p_count1
5-4
create table test5_04(dname varchar(30),class varchar(10),P_count1 int,p_count2 int ,p_count int )
insert into test5_04 (dname,class) select dname,class from pub.student where dname is not null group by dname,class
update test5_04 S set P_count=(select count(*) from pub.student T where T.dname=S.dname and T.class=S.class)
create table test5_033 as select sid,sum(credit) credit from(select sid,cid,credit from pub.course natural join pub.student_course where score>=60 group by sid,cid,credit)group by sid
create table test5_0333 as select * from (test5_033 natural join pub.student)
update test5_04 S set P_count1=(select count(*) from test5_0333 T where (credit>=10 or (credit >=8 and class<='2008')) and T.dname=S.dname and T.class=S.class )
update test5_04 set P_count2=P_count -P_count1
5-5
create table test5_05(dname varchar(30),avg_ds_score number(3,1),avg_os_score number(3,1))
insert into test5_05 (dname )(select dname from pub.student where dname is not null group by dname)
create table test5_055 as select sid,cid,max(score)score from pub.student_course where (cid='300002' or cid='300005')group by sid,cid
create table test5_0555 as select * from test5_055 natural join pub.student
update test5_05 S set avg_ds_score=(select avg(score) from test5_0555 T where S.dname=T.dname and T.cid='300002')
update test5_05 S set avg_os_score=(select avg(score) from test5_0555 T where S.dname=T.dname and T.cid='300005')
5-6
create table test5_06 (sid char(12),name varchar(10),dname varchar(30),ds_score number,os_score number)
insert into test5_06 (sid,name,dname) (select sid,name,dname from test5_0555 where sid in (select sid from test5_0555 where cid='300002') and sid in (select sid from test5_0555 where cid='300005') and dname='计算机科学与技术学院' group by sid,name,dname )
update test5_06 S set ds_score=(select score from test5_0555 T where T.sid=S.sid and T.cid='300002')
update test5_06 S set os_score=(select score from test5_0555 T where T.sid=S.sid and T.cid='300005')
5-7
create table test5_07 (sid char(12),name varchar(10),dname varchar(30),ds_score number,os_score number)
insert into test5_07 (sid,name,dname) (select sid,name,dname from test5_0555 where (sid in (select sid from test5_0555 where cid='300002') or sid in (select sid from test5_0555 where cid='300005')) and dname='计算机科学与技术学院' group by sid,name,dname )
update test5_07 S set ds_score=(select score from test5_0555 T where T.sid=S.sid and T.cid='300002')
update test5_07 S set os_score=(select score from test5_0555 T where T.sid=S.sid and T.cid='300005')
5-8
create table test5_08 as select * from test5_07
insert into test5_08(sid,dname,name) select sid,dname,name from pub.student where sid not in test5_07 and dname='计算机科学与技术学院'
6-1
create or replace view test6_01 as select sid，name，dname from pub.student where age<20 and dname like '物理学院' order by sid
6-2
create table test6_022 as
select
S.sid,S.name,sum(T.score) sum_score
from
pub.student S,pub.student_course T
where
S.dname='软件学院' and S.class='2009'
group by S.sid,S.name
update test6_022 set sum_score = null
update test6_022 S set sum_score = (select sum(score) from pub.student_course T where T.sid=S.sid)
create view test6_02 as select * from test6_022
6-3
create or replace view test6_03 as
select
sid,name,score
from
pub.student natural join pub.student_course
where
dname='计算机科学与技术学院' and cid='300005' and class='2010'
6-4
create or replace view test6_04 as
select
sid,name
from
pub.student natural join pub.student_course
where
cid='300003' and score>90
6-5
create or replace view test6_05 as
select
A.sid,A.cid,B.name, A.score
from
(select * from pub.student natural join pub.student_course)A,pub.course B
where
A.name='李龙' and A.cid=B.cid
6-6
create or replace view test6_06 as select sid,name from (select sid from (select sid,count(cid) cnt from pub.student_course group by sid )where cnt>=140) natural join pub.student
6-8
create or replace view test6_07 as select sid,name from (select sid from (select sid,count(cid) cnt from pub.student_course where score>=60 group by sid )where cnt>=140) natural join pub.student
6-9
create or replace view test6_09 as
select
A.sid,A.name,sum(credit) sum_credit
from
pub.student A, pub.student_course B,pub.course C
where A.dname='化学与化工学院' and A.class='2010' and B.sid=A.sid and C.cid=B.cid
and B.score>=60 group by A.sid,A.name
6-10
create or replace view test6_10 as select A.cid,A.name from pub.course A,pub.course B where A.fcid=B.cid and B.fcid is not null
7-04
create view test7_04 as select * from
(select sid,name,birthday,
(select count(*) from pub.student
where birthday>=trunc(t1.birthday,'mm') and birthday<=last_day(t1.birthday)
) sameyearmonth,
(select count(*) from pub.student
where birthday>=trunc(t1.birthday,'yyyy') and birthday<=last_day(add_months(trunc(t1.birthday,'y'),11)
) )sameyear
from pub.student_testindex t1) where sameyearmonth=35
7-05
create view test7_05 as select * from
(select sid,name,birthday,
(select count(*) from pub.student
where birthday=t1.birthday+1
) nextbirthday
from pub.student_testindex t1) where nextbirthday=7
9_01
create table test9_01 as select * from pub.student_11_1
delete from test9_01
create index index_sid on test9_01(sid)
insert into test9_01 select * from pub.student where sex='女'
insert into test9_01 select * from pub.student_11_1 where sex='女' and sid not in(select sid from test9_01)
insert into test9_01 select * from pub.student_11_2 where sex='女' and sid not in(select sid from test9_01)
9_02
create table test9_02 as select * from pub.student_11_1
delete from test9_02
insert into test9_02 select * from pub.student where sex='女' and sid in(select sid from pub.student natural join pub.student_course T where T.score<60)
insert into test9_02 select * from pub.student_11_1 where sex='女' and sid in(select sid from pub.student natural join pub.student_course T where T.score<60) and sid not in(select sid from test9_02)
insert into test9_02 select * from pub.student_11_2 where sex='女' and sid in(select sid from pub.student natural join pub.student_course T where T.score<60) and sid not in(select sid from test9_02)
