目录
练习题
题目一
题目二
题目三
题目四
题目五
题目六
题目七
题目八
题目九
题目十
练习题
题目一
找出年龄小于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关键点：
1、create or replace：可以创建或者替代table和view，方便调试错误
题目二
查询统计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
关键点：
1、left outer join 需要利用on来确定连接的原则
2、select结果集、view和table三者都是不同的。select结果集是一次性显示的在数据库中不占有任何内存；view在数据库中占有非常小的内存，因为它并不存储数据而是动态从table中获取；table是真实存储数据的，是占有内存的
题目三
查询所有课的最高成绩、最高成绩人数，test6_06有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次,需要考虑刷成绩情况，一个同学选了一个课程多次，两次都是最高分。如果结果错一行，可能就是没有考虑这种情况，这里是为了考核“去重复计数”知识点的）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零
提示：参考讲义关于标量子查询（只返回包含单个属性的单个元组）
create or replace view test6_03 as
select*
from
(
select C.cid,C.name,max(SC.max_score) max_score
from pub.course C left outer join
(select sid,cid,max(score) max_score
from pub.student_course
group by sid,cid
)SC
on C.cid=SC.cid
group by C.cid,C.name
)
natural full outer join
(
select SC.cid,count(sid) max_score_count
from (select sid,cid,max(score) max_score
from pub.student_course
group by sid,cid
)SC
group by SC.cid,SC.max_score
having (SC.max_score=(select max(score)
from pub.student_course temp
where SC.cid=temp.cid)
)
)关键点：
1、多个select结果处理：每一个sql程序只能有一个最终的select，而不能重复显示select多次的结果（结果会覆盖从而报错）；如果select结果要按行合并则用union（删除重复行）/union all（不删除重复行）；如果select结果要按列合并，则可以用join、natural full outer join
2、select结果是临时的不是表也不是视图，所以要将select结果合并需要把select语句放在from中当成一个临时关系来处理
3、当select提取较为复杂时，可以考虑分开两个select语句处理，再将select结果合并处理
4、没有学生考试的课程也要呈现出课程的cid和name，就是后面的max_score以及count用NULL来处理。用left outer join/full outer join来完成这一功能
题目四
找出选修了“操作系统”并取得学分或者选修“数据结构”并且取得学分，但是没有选修“程序设计语言”或者没有取得这门课的学分的男学生的学号、姓名
create or replace view test6_04 as
select SC.sid,S.name
from pub.student_course SC,pub.student S,pub.course C
where SC.sid=S.sid and SC.cid=C.cid
and SC.score>=60 and (C.name='操作系统' or C.name='数据结构'）
and S.sex='男' and (SC.sid not in(
select SC.sid
from pub.student_course SC,pub.course C
where SC.cid=C.cid and
C.name='程序设计语言' and
SC.score>=60)
)关键点：
1、对于或的关系可以直接在where 的条件中利用or来表达，但是对于与的关系不能在where中利用and来写，而是要select后将结果取交集（intersect）
2、没有 或 没有 =有 与 有 取一个否定 。所以在实现上直接在where上利用not in 来实现
题目五
查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
Test6_05有四个列，并且列名必须是：sid、name、avg_score、sum_score。通过下面方式实现列名定义：
create or replace view test6_05 as select sid,name，（表达式） avg_score,（表达式） sum_score from ……
create or replace view test6_05 as
select S.sid sid,S.name name,round(avg(score),0) avg_score,round(sum(score),0) sum_score
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.age=20
group by S.sid,S.name关键点：
1、四舍五入处理round函数：round（number，digit）
digit>0：四舍五入到第digit位小数
digit=0：四舍五入到整数
digit<0：在整数位置四舍五入（-1：四舍五入到十位；-2：四舍五入到百位）
题目六
找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）
create or replace view test6_06 as
select S.sid,S.name
from pub.student S,pub.student_course SC
where S.sid=SC.sid and
SC.score<60
group by S.sid,S.name,SC.cid
having (count(*)>=2)关键点：
1、这里查找的对象是一个学生在一门课上的所有考试记录
题目七
找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。（题6的延伸和巩固）
create or replace view test6_07 as
select distinct S.sid,S.name
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.sid in(
select sid
from pub.student_course SC
where not exists(
select cid
from pub.course C
minus
select cid
from pub.student_course Temp
where Temp.sid=SC.sid
)
) and S.sid not in(
select sid
from pub.student_course SC
where SC.score<60
)
关键点：
1、选修了所有课程就是除法的应用
2、所有成绩都及格查找对象是一个学生在一门课上的所有考试记录
题目八
找出选修了所有课程并且得到所有课程的学分（即每门课程最少一次考试及格）的学生的学号、姓名。（题6的 延伸和巩固）
create or replace view test6_08 as
with T as(
select sid, cid, MAX(score) AS max_score
from pub.student_course
group by sid, cid
)
select distinct S.sid,S.name
from pub.student S,T SC
where S.sid=SC.sid and S.sid in(
select sid
from T SC
where not exists(
select cid
from pub.course C
minus
select cid
from T Temp
where Temp.sid=SC.sid
)
) and S.sid not in(
select sid
from T SC
where SC.max_score<60
)关键点：
1、本题和上一题不同点在于：查找的对象转变为：一个学生在一门课上的最高成绩
题目九
查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。（不统计没有选课的学生）
create or replace view test6_09 as
select S.sid,S.name,sum(C.credit) sum_credit
from pub.student_course SC,pub.student S,pub.course C
where SC.cid=C.cid and SC.sid=S.sid
and S.dname='化学与化工学院'
and S.class=2010 and SC.score>=60
group by S.sid,S.name题目十
查询学生表中每一个姓氏及其人数（不考虑复姓，用到取子串函数substr(string,postion,length)）），test6_10有两个列：second_name、p_count
create or replace view test6_10 as
select substr(S.name,1,1) second_name,count(*) p_count
from pub.student S
group by substr(S.name,1,1)关键点：
1、group by后面可以跟表达式（例如：to_char(trunc(sc.score, -1), 'fm000') || '-' || to_char(trunc(sc.score, -1) + 9, 'fm000') as score）、字段（substr）
