BI面试题收集

面试题（用oracle 或sql server 实现)
T_Score(分数表)
Stu_id Lession_id Score
001 L001 90
001 L002 86
002 L001 84
002 L004 75
003 L003 85
004 L005 98
…..

T_Stu_Profile(学生表)
Stu_id Stu_Name Sex Age Class_id
001 郭东 F 16 0611
002 李西 M 18 0612
003 张北 F 16 0613
004 钱南 M 17 0611
005 王五 F 17 0614
006 赵七 F 16 0615
……

T_Lession(课程表)
Lession_id Lession_Name
L001 语文
L002 数据
L003 英语
L004 物理
L005 化学

1. 写出学生没有参加考试的课程,以下形式显示
学生姓名 班级 课程


以最简单SQL语句显示，最好不要使用游标与变量
2. 找出课程的前三名，以下列形式显示

课程 第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)
语文
数学
英语
物理
化学

以最简单SQL语句显示，最好不要使用游标与变量

3. 找出0611班所有人成绩，以下列格式显示
姓名 语文 数学 英语 物理 化学 总分


以最简单SQL语句显示，最好不要使用游标与变量


答案：
1:
select stu_name,class_id,lession_name
from
(select stu_id,stu_name,class_id,lession_id,lession_name
from T_Stu_Profile,T_Lession) b
where not exists(select 1 from t_score c
where c.stu_id=b.stu_id
and c.lession_id=b.lession_id);

2:
select l.lession_name,
max(decode(tmp.id, 1, st.stu_name||':'||tmp.score, null)) as Highest,
max(decode(tmp.id, 2, st.stu_name||':'||tmp.score, null)) as Second,
max(decode(tmp.id, 3, st.stu_name||':'||tmp.score, null)) as Third
from (select row_number() over(partition by lession_id order by score desc) as id,
sc.*
from t_score sc) tmp
left join t_stu_profile st on tmp.stu_id = st.stu_id
left join t_lession l on tmp.lession_id = l.lession_id
group by l.lession_name;


3:
select tmp.stu_name,
max(decode(tmp.lession_name, 'YW', tmp.score, null)) YW,
max(decode(tmp.lession_name, 'SX', tmp.score, null)) SX,
max(decode(tmp.lession_name, 'YY', tmp.score, null)) YY,
max(decode(tmp.lession_name, 'WL', tmp.score, null)) WL,
max(decode(tmp.lession_name, 'HX', tmp.score, null)) HX,
SUM(TMP.SCORE) SUM
from (select *
from t_score sc
left join t_stu_profile st on st.stu_id = sc.stu_id
left join t_lession l on l.lession_id = sc.lession_id
where st.class_id = '0611') tmp
group by tmp.stu_name;


陆续补充中.......