山东大学软件学院数据库系统实验六

一、实验时间

2021年5月11日星期二，第11周

二、实验题目

1.例如：找出年龄小于20岁的所有学生的学号、姓名、年龄
正确执行:create view test6_00 as select sid，name，age from pub.student where age>20
Oracle扩展后方便写法：
create or replace view test6_00 as select sid，name，age from pub.student where age>20
直行select count(*) from test6_00 检查是否能够5分钟内查询出全部结果，如果超时说明可能有错误，这种情况下严禁执行"update dbtest set test=6"进行交卷。
找出年龄小于20岁且是"物理学院"的学生的学号、姓名、院系名称,按学号排序。

create or replace view test6_01 as
select s.sid,s.name,s.dname
from pub.student S
where s.age < 20 and s.dname = '物理学院'
order by s.sid

2． 查询统计2009级、软件学院每个学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）。

create or replace view test6_02 as
select s.sid,s.name,sum(sc.score) sum_score
from pub.student s left outer join pub.student_course sc
on s.sid = sc.sid
where s.dname = '软件学院' and s.class = 2009
group by s.sid,s.name

3． 查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。

create or replace view test6_03 as
select s.sid,s.name,sc.score
from pub.student s left outer join pub.student_course sc
on s.sid = sc.sid
where s.dname = '计算机科学与技术学院' and s.class = 2010 and sc.cid = 300005

4． 找出选修"数据库系统"课程，且成绩大于90的学生的学号、姓名

create or replace view test6_04 as
select s.sid,s.name
from pub.student s left outer join pub.student_course sc
on s.sid = sc.sid
where sc.cid = 300003 and sc.score > 90

5． 找出姓名叫"李龙"的学生的学号及其选修全部课程的课程号、课程名和成绩。

create or replace view test6_05 as
select s.sid,sc.cid,c.name,sc.score
from pub.student s, pub.student_course sc, pub.course c
where s.name = '李龙' and s.sid = sc.sid and sc.cid = c.cid

6． 找出选修了所有课程的学生的学号、姓名

create or replace view test6_06 as
select s.sid,s.name
from pub.student s
where not exists(
select *
from pub.course c
where not exists(
select * from pub.student_course sc
where sc.sid = s.sid and sc.cid = c.cid
))

7． 找出选修了所有课程并且所有课程全部通过的学生的学号、姓名

create or replace view test6_07 as
select s.sid,s.name
from pub.student s
where not exists(
select *
from pub.course c
where not exists(
select * from pub.student_course sc
where sc.sid = s.sid and sc.cid = c.cid and sc.score >= 60
))

8． 检索先行课的学分为2的课程号、课程名。

create or replace view test6_08 as
select c1.cid,c1.name
from pub.course c1,pub.course c2
where c1.fcid = c2.cid and c2.credit = 2

9． 查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit

create or replace view test6_09 as
select s.sid,s.name,sum(c.credit) sum_credit
from pub.student s,pub.student_course sc,pub.course c
where s.sid = sc.sid and s.class = 2010 and s.dname = '化学与化工学院' and sc.cid = c.cid and sc.score >= 60
group by s.sid,s.name

10． 找出有间接先行课的所有课程的课程号、课程名称

create or replace view test6_10 as
select c1.cid,c1.name
from pub.course c1,pub.course c2,pub.course c3
where c1.fcid = c2.cid and c2.fcid = c3.cid