山东大学数据库实验六

1.
create view test6_01 as select sid,name,dname
from pub.student
where age <20 and dname='物理学院'
order by sid asc
2.
create table test6_021 as select pub.student.sid,name,score,class,dname
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid

create table test6_022 as select sid,sum(score)as sum_score
from test6_021
where class=2009 and dname='软件学院'
group by sid
create table test6_023 as select test6_022.sid,name,sum_score
from test6_021,test6_022
where test6_021.sid=test6_022.sid

create view test6_02 as select sid, name,sum_score
from test6_023
3.
create view test6_03 as select student.sid,https://www.wendangku.net/doc/7013843190.html,,student_course.score
from pub.student ,pub.student_course,pub.course
where student.dname='计算机科学与技术学院'
and https://www.wendangku.net/doc/7013843190.html,='操作系统'
and student.sid=student_course.sid
and student_course.cid=course.cid
and student.class=2010
4.
create view test6_04 as select student.sid,https://www.wendangku.net/doc/7013843190.html,
from pub.student ,pub.student_course,pub.course
where student.sid=student_course.sid
and student_course.cid=course.cid
and https://www.wendangku.net/doc/7013843190.html,='数据库系统'
and score>90
5.
create view test6_05 as select student.sid,course.cid,https://www.wendangku.net/doc/7013843190.html,,score
from pub.student ,pub.student_course,pub.course
where student.sid=student_course.sid
and student_course.cid=course.cid
and https://www.wendangku.net/doc/7013843190.html,='李龙'
6.
create table test6_614 as select sid,count(*) as csum
from pub.student, pub.course
group by sid

create table test6_615 as select sid,count(*) as csum
from pub.student_course
group by sid

create table test6_616 as select test6_614.sid
from test6_614,test6_615
where test6_614.sid=test6_615.sid
and test6_614.csum=test6_615.csum
create view test6_06 as select sid
from test6_616
7.
create view test6_07 as select sid,name
from pub.student
where student.sid in(
select sid
from pub.student_course
where cid is not null
)
8.create table test6_617 as select fcid
from pub.course
where fcid is not null

create table test6_618 as select cid
from pub.course,test6_617
where test6_617.fcid=cid and credit=2

create table test6_619 as select cid,name
from pub.course
where fcid in(select cid
from test6_618)

create view test6_08

as select * from test6_619

9.
create table test6_001 as select student.sid,name,credit
FROM pub.student ,
pub.student_course,
pub.course
WHERE student.dname= '化学与化工学院'
AND student.class=2010
and score>=60
AND student.sid=student_course.sid
AND student_course.cid = course.cid
group by student.sid


create view test6_09 as select sid,name,sum(credit） sum_credit
from test6_001
group by sid,name

10.
create view test6_10 as select cid,name
from pub.course c1
where c1.fcid in(
select cid
from pub.course c2
where c2.fcid is not null
)