山东大学软件学院数据库实验二

2-1  找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生

1． 找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_01
Create or replace view test2_01 as select ……
然后就可以点击实验二的题目1的【交卷验证】，验证正确性，正确后就有得分。

create or replace view test2_01 as
select sid,name
-- 将pub.student关系起个别名s
from pub.student s
--关系模型中的减运算在Oracle语句中变为not in
where s.sid not in(select sid from pub.student_course)

2-2  找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。

2．找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_02
Create or replace view test2_02 as select ……
然后就可以点击实验二的题目2的【交卷验证】，验证正确性，正确后就有得分。
以下各题操作类似。

create or replace view test2_02 as
select distinct s.sid,s.name
from pub.student_course sc,pub.student s
where 
-- !!!一定要做这个sc.sid=s.sid的判断，否则查询出来的结果会多很多，就变成纯笛卡尔积了好多关系都对应不上
sc.sid = s.sid
and sc.cid in
(select cid
from pub.student_course
where sid=200900130417)
-- not like,！=,<>均可表示不等于
and sc.sid not like 200900130417

//第一次创建的表如果有误，利用drop再删除
drop view test2_02

2-3   找出至少选修了一门其先行课程号为"300002"号课程的学生的学号、姓名。

3．找出至少选修了一门其先行课程号为"300002"号课程的学生的学号、姓名。

create or replace view test2_03 as
select s.sid,s.name
from pub.student_course sc,pub.student s
where sc.sid=s.sid
and sc.cid in(
  select cid
  from pub.course c
  where c.fcid=300002
)

2-4  找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。

4．找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。

create or replace view test2_04 as
select distinct s.sid,s.name as name-- 剔除重复
  from pub.student s
-- 需要课程名列，该列在pub.course c表中，但是c表和s表无共同属性，只能通过sc建立连接
join pub.student_course sc on s.sid=sc.sid-- 通过外键连接join 外表 on 外键
join pub.course c on c.cid=sc.cid
where c.name like '数据结构'
and s.sid in-- 这里要进行两次交运算，交运算选中一个可供判断的属性即可
(select distinct s.sid
  from pub.student s
join pub.student_course sc on s.sid=sc.sid
join pub.course c on c.cid=sc.cid
where c.name like '操作系统')-- 必须是单引号
and s.sid not in-- 不在这个select关系中
(select distinct s.sid
  from pub.student s
join pub.student_course sc on s.sid=sc.sid
join pub.course c on c.cid=sc.cid
where c.name like '程序设计语言')

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

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

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

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

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

create or replace view test2_06 as
select distinct s.sid,s.name,sc.score
from pub.student s
join pub.student_course sc on s.sid=sc.sid
join pub.department d on s.dname=d.dname
join pub.course c on c.cid=sc.cid
where d.dname like '计算机科学与技术学院'
and c.name like '操作系统'
and s.class like '2010'

2-7 查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

7．查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

create or replace view test2_07 as
select sid,name
from pub.student
where name not like '张%'
and name not like '李%'-- 不等于什么什么的可以直接and连接
and name not like '王%'

2-8 找出有间接先行课的所有课程的课程号、课程名称。

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

!!!一定要看清题目，是间接先行课（这个题卡了我老长时间，后来又回实验报告里看了一遍题目才反应过来是间接先行课，即先行课的先行课不为空）

create or replace view test2_08 as
select distinct c.cid,c.name 
from pub.course c,pub.course c1
where c.fcid=c1.cid and c1.fcid is not null

2-9 查询选修了300003号课程的学生的sid、name、score

9．查询选修了300003号课程的学生的sid、name、score

create or replace view test2_09 as
select s.sid,s.name,sc.score
from pub.student s,pub.student_course sc 
where sc.cid like 300003

2-10  找出选修了所有课程的学生的学号、姓名

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

lh老师的ppt,讲的很清楚，建议将这个除法运算的减法原理重新巩固理解，内化为自己的知识，写出SQL语句 

create or replace view test2_10 as
select sid,name 
from pub.student s 
where sid not in -- 等价于代数关系中的减运算
(select sid
  from pub.student s,pub.course c 
  where (sid,cid) not in-- 将S*C笛卡尔积的所有可能的学生选课关系列出来，再减去实际上的SC关系表中
-- 的，就是将每一个学生和他/她没有选的课一一对应，一个学生有没选的课，那么就不符合题目要求的选出选了
-- 所有课程的学生学号，于是就用开头的sid not in(即所有的sid,减去没有选全课的学生学号，即可得选了所有课的
 (
  select sid,cid
  from pub.student_course sc 
 ) 
  )