一、实验时间
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