Content

一、应用题

学校数据库中包括四个表,各属性的含义可由属性名体现，不再重复
学生表：S(Sno, Sname, Ssex, Sage, Sdept)，
课程表：C(Cno, Cname, CTno, Ccredit)
学生选课表：SC(Sno, Cno, mid-exam,final-exam,Grade)
课程类别表 CT(CTno, Type)
其中：CT由课程类别编码（CTno）、课程类别（Type）两个属性组成。
Type取值有：公共课、学科基础课、专业必修课、专业选修课、全校选修课 等。
Grade是计算属性，由期中成绩（mid-exam）和期末成绩（final-exam）平均而得到。

用关系代数 完成以下操作：
1 查询成绩为95分以上的学生成绩单，包括学号、姓名、课程名、成绩四个属性。
2 选修了全部“专业选修课”的学生学号、姓名、所在系。

用 SQL 语言 完成以下操作：
3 按系统计不同年龄的学生人数，输出结果按照系名称的字母升序和学生的年龄降序。
4 找出每个学生超过他自己所选修全部课程平均成绩的课程号、课程名称、成绩。
5 对选修数据库系统概论这门课的学生计算其该门课的总成绩，按照期中占40%，期末
占60%计算。
6 查询没有选修过任何“全校选修课”的学生姓名及其所在系。
7 统计每一门课程的选修人数及其平均成绩。要求每门课程都必须出现在查询
结果中,不管有没有学生选修。
8 按照总成绩从高到低的顺序输出学生名单（包括学号，姓名，总成绩），成绩相同时，
按照学号从小到大的顺序输出。

参考解答(第二小问涉及除运算，跳过！)：

(3)/*按系统计不同年龄的学生人数，输出结果按照系名称的字母升序和学生的年龄降序*/
select Sdept,Sage,COUNT(*)
from S
group by Sdept,Sage
order by Sdept,Sage desc
(4)/*找出每个学生超过他自己所选修全部课程平均成绩的课程号、课程名称、成绩*/
select x.Cno,Cname,x.Grade
from C,SC x
where C.Cno=x.Cno 
group by Sno,x.Cno,Cname,x.Grade
having x.Grade>(select AVG(Grade)
                from SC y
                where x.Sno=y.Sno
                )
(5)/*对选修数据库系统概论这门课的学生计算其该门课的总成绩，按照期中占40%，期末
占60%计算*/
update SC
set Grade=0.4*mid-exam+0.6*final-exam
where Cno=(select Cno
           from C
           where Cname='数据库系统概论'
           )
(6)/*查询没有选修过任何“全校选修课”的学生姓名及其所在系*/
select Sname,Sdept
from S
where Sno not in(select SC.Sno
                 from SC,C,CT
                 where SC.Cno=C.Cno and C.CTno=CT.CTno and Type='全校选修课'
                 )
(7)/*统计每一门课程的选修人数及其平均成绩。要求每门课程都必须出现在查询
结果中,不管有没有学生选修*/
select C.Cno,COUNT(*),AVG(Grade)
from C left join SC on C.Cno=SC.Cno
group by C.Cno
(8)/*按照总成绩从高到低的顺序输出学生名单（包括学号，姓名，总成绩），成绩相同时，
按照学号从小到大的顺序输出*/
select S.Sno,Sname,SUM(Grade)
from S,SC
where S.Sno=SC.Sno
group by S.Sno,Sname
order by SUM(Grade) desc,S.Sno