山东大学数据库实验六2015最新版

1.create view test6_01 as select sid,name,dname from pub.STUDENT where age<20 and dname='物理学院'order by sid
select * from test6_01
2.create view test6_02(sid,name,sum_score) 
as 
(select pub.student.sid,name,sum(score)
from pub.STUDENT,pub.STUDENT_COURSE 
where pub.STUDENT.SID=pub.student_course.sid
and dname='软件学院' 
and class=2009
group by pub.STUDENT.SID,name)
select * from test6_02
3.create view test6_03 as(
select pub.STUDENT.SID,pub.STUDENT.name,score
from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE
where pub.STUDENT.SID=pub.student_course.sid
and pub.STUDENT_COURSE.CID=pub.course.cid
and class=2010
and dname='计算机科学与技术学院' 
and pub.COURSE.NAME='操作系统')
select * fromm test6_03
4.create view test6_04 as
(select pub.STUDENT.SID,pub.STUDENT.NAME
from pub.STUDENT_COURSE,pub.STUDENT,pub.COURSE
where pub.STUDENT_COURSE.SID=pub.student.sid
and pub.STUDENT_COURSE.cid=pub.course.cid
and pub.COURSE.NAME ='数据库系统'
and score>90)
select * from test6_04
5.create view test6_05 as
(select pub.STUDENT.SID,pub.STUDENT_COURSE.cid,pub.COURSE.NAME,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 pub.STUDENT.NAME='李龙'
)

6.create view test6_06 as(
select pub.STUDENT.SID,name
from pub.STUDENT
where not exists(
(select cid
from pub.COURSE)
minus(
select cid
from pub.STUDENT_COURSE
where pub.STUDENT_COURSE.SID=pub.student.sid)))
7.create view test6_07 as
(select SID,name
from pub.STUDENT
where sid in(
select sid
from pub.STUDENT_COURSE
where score>=60) 
and not exists((
select cid
from pub.COURSE)
minus(select cid
from pub.STUDENT_COURSE
))
)
create view test6_07 as
(select sid,name
from test6_06 where sid in
(select distinct sid
from pub.STUDENT_COURSE
where sid in(select sid
from test6_06) and score>60))//不知道前两个尤其是第二个为什么不行，第二个有15行数据错误，下面的才是正确答案
create view test6_07 as
select * from test6_06 where sid not in (
select distinct sid from pub.student_course where sid in(select sid from test6_06) and score<60)
8.
create view test6_08 as
(select cid,name
from pub.COURSE
where fcid in(
select cid
from pub.COURSE
where credit=2))
9.create view test6_09(sid,name,sum_credit)as
(select pub.STUDENT.sid,pub.STUDENT.name,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 class=2010
and dname='化学与化工学院'
and score>=60
group by pub.STUDENT.sid,pub.STUDENT.name)
10.create view test6_10 as
(select cid,name
from pub.COURSE
where fcid in(select cid
from pub.COURSE
where fcid is not null))