用SQL语句或游标完成以下题目

Question

前两天同学面试回来，给我看了下面试时的数据库题目，说他不会让我帮忙，结果我一看，就第一题会
求大牛帮忙
有成绩表T_Score(Stu_id和Lession_id为联合主键)
缺考情况下不录入，例如B001的L002课程缺考。
Stu_id（学生号） Lession_id（课程） Score（成绩）
A001 L001 90
A001 L002 80
A002 L001 70
A002 L002 60
B001 L001 50
B001 L001 85
…… …… ……学生档案T_Stu_Profile（Stu_id为主键）
包含所有学生信息
Stu_id（学生号） Stu_name（姓名） Class_id（班级）
A001 张三 06101
A002 李四 06101
B001 王五 06102
…… …… ……课程信息表T_Lession（Lission_id为主键）
包含所有课程信息
Lession_id（课程号） Lession_des（课程）
L001 语文
L002 数学
L003 英语
L004 物理
L005 化学
一、找出缺考的学生名单，输出如下格式：
Class_id（班级） Stu_name（姓名） Lession_des（课程）
06102 王五 数学
…… …… ……
要求：如果不使用游标，如何实现；如果使用游标，又如何实现？
二、找出五门课程中的年级前三名，输出如下格式：（假设前三名不出现并列的情况）
Lession_des（课程） 第一名 第二名 第三名
语文
数学
英语
物理
化学 …… …… ……
要求：如果不使用游标，如何实现；如果使用游标，又如何实现？
三、输出06101班的学生成绩单，格式如下：
姓名 语文 数学 英语 物理 化学 总分
……

要求：如果不使用游标，如何实现；如果使用游标，又如何实现？四、假如成绩表增加考试日期Test_Date，记录高中三年大大小小每次考试成绩。请问：如何求出高三阶段（2005年），第位学生的第门课的平均考试成绩。（缺考以及60分以下的成绩不计入平均，如高三数学共考试20次，B001缺考一次、另一次成绩58分，则B001的平均考试成绩以18次计算）。输出格式同试题三。

Answer 1

select t1.lession_des,t1.stu_name as 第一名,t2.stu_name as 第二名 from(select c.lession_des, b.stu_name 
  from test1 a, test2 b, test3 c
 where a.score in (select max(a.score) from test1 a group by a.lession_id) 
   and b.stu_id = a.stu_id
   and c.lession_id = a.lession_id
 order by c.lession_id asc) t1 ,
 
 
 ( select v.lession_des,u.stu_name 
 from test1 t,test2 u,test3 v where  u.stu_id = t.stu_id and v.lession_id = t.lession_id and t.score  in(select max(t.score)
 from test1 t, test2 u
   where u.stu_id = t.stu_id
   and t.score not in (select max(score) from test1 t  group by t.lession_id) group by t.lession_id )) t2
   
 where t2.lession_des = t1.lession_des 
test1是T_Score表，test2是T_Stu_Profile，test3是T_Lession，好繁啊就写1个了。。继续干活了。。

Answer 2

--写下第二题
select Lession_des,Stu_name from (
SELECT 
      Lession_des,
      wm_concat(pro.Stu_name) over(partition by les.Lission_id order by score.Score) Stu_name,
      row_number() over(partition by les.Lission_id order by score.Score) rn
FROM T_Score score,T_Stu_Profile pro,T_Lession les
WHERE 
score.Stu_id = pro.Stu_id
pro.Lession_id = les.Lession_id) t
where t.rn <= 3;

Answer 3

--第三题--游标就不写了，自己研究下吧 呵呵
select  
      T_Stu_Profile.Stu_id 学号,
      T_Stu_Profile,Stu_name 姓名,
      decode(T_Stu_Profile.Lession_id,'L001',T_Score.score) 语文,
      decode(T_Stu_Profile.Lession_id,'L002',T_Score.score) 数学，
      decode(T_Stu_Profile.Lession_id,'L003',T_Score.score) 英语，
      decode(T_Stu_Profile.Lession_id,'L004',T_Score.score) 物理，
      decode(T_Stu_Profile.Lession_id,'L005',T_Score.score) 化学，
      sum(T_Score,score) 总分
from T_Score score,T_Stu_Profile pro,T_Lession les
where 
score.Stu_id = pro.Stu_id
pro.Lession_id = les.Lession_id
group by T_Stu_Profile.Stu_id，T_Stu_Profile,Stu_name

Answer 4

wm_concat这个函数为啥在我的oracle里用不起来

Answer 5

感觉还是一些常规的题目，全都可以直接通过sql解决。论坛上有很多类似的目标集，只是应用环境不同。

Answer 6

第一题SELECT B.STU_ID, A.STU_NAME,A.LESSION_DES,a.class_id from 
(SELECT STU_ID,STU_NAME,LESSION_ID,LESSION_DES,CLASS_ID FROM T_STU_PROFILE  ,T_LESSION
WHERE LESSION_ID IN (SELECT DISTINCT LESSION_ID  FROM T_SCORE)) A, T_SCORE B
WHERE A.STU_ID=B.STU_ID(+) AND A.LESSION_ID=B.LESSION_ID(+) AND B.STU_ID IS NULL第二题SELECT L.LESSION_DES, MAX(CASE WHEN RN=1 THEN STU_ID END) 第一名,
                      MAX(CASE WHEN RN=2 THEN STU_ID END) 第二名,
                      max(CASE WHEN RN=3 THEN stu_id END) 第三名
                                                                FROM T_LESSION L,
(SELECT STU_ID,LESSION_ID,SCORE,ROW_NUMBER()OVER(PARTITION BY LESSION_ID ORDER BY SCORE desc) RN FROM T_SCORE) A
WHERE L.LESSION_ID=A.LESSION_ID(+)
group by L.LESSION_DES;第三题
SELECT STU_NAME,
MAX(CASE WHEN B.LESSION_ID='L001' THEN SCORE ELSE 0 END)  语文,
MAX(CASE WHEN B.LESSION_ID='L002' THEN SCORE ELSE 0 END)  数学,
MAX(CASE WHEN B.LESSION_ID='L003' THEN SCORE ELSE 0 END)  英语,
MAX(CASE WHEN B.LESSION_ID='L004' THEN SCORE ELSE 0 END)  物理,
MAX(CASE WHEN B.LESSION_ID='L005' THEN SCORE ELSE 0 END)  化学,
sum(score) 合计
FROM 
T_SCORE A ,(SELECT * FROM T_STU_PROFILE,T_LESSION WHERE CLASS_ID='06101') B
WHERE A.STU_ID(+)=B.STU_ID 
AND  A.LESSION_ID(+)=B.LESSION_ID
GROUP BY STU_NAME第四题，懒得写了把关键的写出来，其他的懂的自然懂--关键表出来，另外的case一下就出来了
SELECT 
(SELECT STU_ID,LESSION_ID,AVG(SCORE) FROM T_SCORE 
WHERE SCORE>60 AND TEST_DATE=TO_DATA('2005','yyyy')
group by stu_id,lession_id)

Answer 7

这里哪个sql没有用到游标！
我觉得用显式游标反而更加的麻烦,纯sql才考验同学们的水平，嘿嘿.

Answer 8

第一题    select a.stu_id,b.lession_id from t_student a , t_lesson b
minus
select stu_id, lession_id from t_score;
第二题  select lession_id, max(score) , sum(decode(n,2,score)), sum(decode(n,3,score)) from (select A.*, row_number() over(partition by lession_id order by score desc) n from t_score A) group by lession_id;第三题  select d.stu_name , sum(decode(d.lession_desc,'数学', d.score)) 数学 , sum(decode(d.lession_desc,'语文', d.score)) 语文 , sum(d.score) from (select b.stu_name,c.lession_desc,a.score from t_score a, t_student b, t_lesson c where a.stu_id=b.stu_id and b.class_no=6101 and a.lession_id = c.lession_id) d group by d.stu_name第三题的科目可以加上你需要的 ，暂时怎么用sql实现动态的，游标肯定是可以的

Answer 9

用一个sql就能解决问题，啥使不使用游标如何解决的都是扯淡……要是我面试就直接给个思路完事
第一个，学生和科目表做笛卡尔积，然后minus成绩表；
第二个，先对成绩排序ROW_NUMBER()OVER(PARTITION BY LESSION_ID ORDER BY SCORE DESC)，然后三个表关联做行转列，docode取ROW_NUMBER是1，2，3的；
第三个，三个表关联，用decode做行转列；
第四个，在where条件里面限制高三阶段，用sum(decode(………………))行转列求出每门高三总成绩，直接在select子句里面做关联查询，用sum的结果除考试次数
架子大概就是select b.Stu_name，sum(decode(……))/(select count(*) from T_Score t1 where t1.Stu_id=b.Stu_id and t1.Lession_id=c.Lession_id and Test_Date in (高三) and score>=60),sum(……………………
from T_Score a,T_Stu_Profile b,T_Lession c where …………………………