一、找出缺考的学生名单,输出如下格式:
Class_id(班级) Stu_name(姓名) Lession_des(课程)
B001 王五 数学
…… …… ……
要求: 如果不使用游标,如何实现;
如果使用游标,又如果实现?
select class_id ,
st_name ,
lession_des
from
(
select stu_id ,
lession_id
from t_stu_profile a ,
t_lession b
where not exists (select 1 from t_score where a.stu_id = stu_id and b.lession_id = lession_id)
) aa , t_stu_profile bb, t_lession cc
where aa.stu_id = bb.stu_id
and aa.lession_id = cc.lession_id
二、找出五门课程中的年级前三名,输出如下格式:(假设前三名不出现并列的情况)
select aa.stu_id , bb.st_name, cc.lession_des , aa.score
from t_score aa, t_stu_profile bb , t_lession cc
where aa.stu_id = bb.stu_id
and aa.lession_id = cc.lession_id
and aa.score in ( select top 3 score from t_score where aa.lession_id = lession_id)
Lession_des(课程) 第一名 第二名 第三名
语文
数学
英语
物理
化学 …… …… ……
要求:如果不使用游标,如何实现;如果使用游标,又如果实现?
三、输出06101班的学生成绩单,格式如下:
姓名 语文 数学 英语 物理 化学 总分
…… …… …… …… …… …… ……
要求:如果不使用游标,如何实现;如果使用游标,又如果实现?
select st_name ,
Max(case when lession_des = '语文' then Isnull(score,0) end )as '语文',
Max(case when lession_des = '数学' then Isnull(score,0) end )as '数学',
Max(case when lession_des = '历史' then Isnull(score,0) end )as '历史',
Max(case when lession_des = '英语' then Isnull(score,0) end )as '英语',
sum(score)
from
(
select aa.stu_id , bb.st_name, cc.lession_des , aa.score
from t_score aa, t_stu_profile bb , t_lession cc
where aa.stu_id = bb.stu_id
and aa.lession_id = cc.lession_id
and bb.class_id = '06101'
) dd
group by st_name
四、假如成绩表增加考试日期Test_Date,记录高中三年大大小小每次考试成绩。
请问:如何求出高三阶段(2005年),每位学生的每门课的平均考试成绩。
(缺考以及60分以下的成绩不计入平均,如高三数学共考试20次,B001缺考一次、另一次成绩58分,则B001的平均考试成绩以18次计算)。
输出格式同试题三。
select st_name ,
Sum(case when lession_des = '语文' then Isnull(score,0) end ) / count(1) as '语文',
SUM(case when lession_des = '数学' then Isnull(score,0) end ) / count(1) as '数学',
SUm(case when lession_des = '历史' then Isnull(score,0) end ) / count(1) as '历史',
SUm(case when lession_des = '英语' then Isnull(score,0) end ) / count(1) as '英语',
sum(score) / / count(1) as '总平均'
from
(
select aa.stu_id , bb.st_name, cc.lession_des , aa.score
from t_score aa, t_stu_profile bb , t_lession cc
where aa.stu_id = bb.stu_id
and aa.lession_id = cc.lession_id
and aa.score > 60
) dd
group by st_name