一面试题目,有兴趣者请进来

Question

应小弟能力有限,下面的题目只做了部分题目,所有面试失败了.所以想各位看看,提供些思路或者把答案写上来,让小弟也提高提高,先谢谢个位了
 用sql实现以下功能  表 test(T1,T2 ,T3,T4,T5,T6,T7)学生id    学生姓名     课程id    课程名称      成绩     教师id   教师姓名
 A1         李四        k1         数学         50        T1      王老师
 A2         小王        k2         英文         60        T2      李老师
 A3         小刘        k3         政治         80        T3      刘老师
 A4         小张        k4         语文         90        T4      张老师
 A1         李四        K2         英文         50        T2      李老师
 A1         李四        K3         政治         45        T3      刘老师
 A1         李四        k4         语文         50        T4      张老师
 ..         ..          ..         ....         ...       ...     ......
 ..         ...         ..         ....         ....      .....   .......    
1.在test表中增加一个字段T0  ,自动增长  ,删除test表中除T0字段外其它字段完全相同的重复多余的记录数据.2. 检索各科成绩最高和最低记录(所有信息).3. 按成绩从高到低顺序,列出所有学生4门(数学,英文,政治,语文)课程成绩,也就是每个学生的4门课程成绩单.
   学生id   学生姓名   数学  英文   政治  语文4.安各科不及格率的百分数从低到高和平均成绩从高到低的顺序,统计并列出各科平均成绩和不及格百分数.
    课程id     课程名称      平均成绩      及格百分比5.检索出4门课程平均成绩和及格率百分数(用1行4列表示,格式可以自定)
     数学平均分  数学及格率    英文平均分 英文及格率.............6.检索出数学成绩第4名-第7名学生成绩单或检索出总成绩第4-第7名学生成绩单.7.按不同老师所教不同课程平均分从高到低列印
   教师id   姓名   课程id    课程名称    平均分8.统计各门课程成绩各分数段的人数(类似交叉表)
  课程id,  课程名称 ,[100-85] ,[84-70],[69-60],[<60]

Answer 1

1.不太明白你的意思,估计是要求你第三范式
2.select 课程名称,max(成绩),min(成绩) from test
3.select 学生id,学生姓名,课程名称,成绩 from test order by 学生id,成绩 desc
4.select test.课程id,课程名称,avg(成绩) 平均成绩,a.cnt/count(*) 及格百分比
from test,(select count(*) cnt,课程id from test ) a
where a.课程id= test.课程id
group by test.课程id,课程名称
order by avg(成绩) 平均成绩,a.cnt/count(*) 及格百分比
5.
........太累了，自己写吧！

Answer 2

1.在test表中增加一个字段T0  ,自动增长  ,删除test表中除T0字段外其它字段完全相同的重复多余的记录数据.
alter table test add T0 int identity(1,1)
delete test where T0 not in(select min(T0) from test group by 学生id,学生姓名,课程id,课程名称,成绩,教师id,教师姓名)2. 检索各科成绩最高和最低记录(所有信息).
select * from test where exists(select 1 from test group by 课程id having 课程id=tem.课程id and min(成绩)=tem.成绩 and max(成绩)=tem.成绩)3. 按成绩从高到低顺序,列出所有学生4门(数学,英文,政治,语文)课程成绩,也就是每个学生的4门课程成绩单.
   学生id   学生姓名   数学  英文   政治  语文
select 学生id,学生姓名
,sum(case when 课程id='k1' then 成绩 else 0 end) 数学 
,sum(case when 课程id='k2' then 成绩 else 0 end) 英文
,sum(case when 课程id='k3' then 成绩 else 0 end) 政治
,sum(case when 课程id='k4' then 成绩 else 0 end) 语文
group by 学生id,学生姓名 order by sum(成绩) desc4.安各科不及格率的百分数从低到高和平均成绩从高到低的顺序,统计并列出各科平均成绩和不及格百分数.
    课程id     课程名称      平均成绩      及格百分比
select 课程id,课程名称,avg(成绩) 平均成绩,cast(sum(case when 成绩<60 then 1 else 0 end)/sum(1.0)*100 as numeric(10,2)) 不及格百分比 from test group by 课程id,课程名称 order by sum(case when 成绩<60 then 1 else 0 end)/sum(1.0)*100,avg(成绩) desc5.检索出4门课程平均成绩和及格率百分数(用1行4列表示,格式可以自定)
     数学平均分  数学及格率    英文平均分 英文及格率.............
select 
avg(case when 课程id='k1' then 成绩 end) 数学平均成绩
,cast(sum(case when 课程id='k1' and 成绩>60 then 1 else 0 end)/sum(1.0)*100 as numeric(10,2)) 英文及格率
,avg(case when 课程id='k2' then 成绩 end) 英文平均成绩
,cast(sum(case when 课程id='k2' and 成绩>60 then 1 else 0 end)/sum(1.0)*100 as numeric(10,2)) 数学及格率
,avg(case when 课程id='k3' then 成绩 end) 政治平均成绩
,cast(sum(case when 课程id='k3' and 成绩>60 then 1 else 0 end)/sum(1.0)*100 as numeric(10,2)) 政治及格率
,avg(case when 课程id='k4' then 成绩 end) 语文平均成绩
,cast(sum(case when 课程id='k4' and 成绩>60 then 1 else 0 end)/sum(1.0)*100 as numeric(10,2)) 语文及格率
from test6.检索出数学成绩第4名-第7名学生成绩单或检索出总成绩第4-第7名学生成绩单.
select * from test where 课程id='k1' and 学生id in (select top 7 学生id from test where 课程id='k1' order by 成绩 desc) and 学生id not in (select top 3 学生id from test where 课程id='k1' order by 成绩 desc)7.按不同老师所教不同课程平均分从高到低列印
   教师id   姓名   课程id    课程名称    平均分
select 教师id,教师姓名,课程id,课程名称,avg(成绩) 平均分 from test group by 教师id,教师姓名,课程id,课程名称 order by avg(成绩) desc8.统计各门课程成绩各分数段的人数(类似交叉表)
  课程id,  课程名称 ,[100-85] ,[84-70],[69-60],[<60]
select 课程id,课程名称
,sum(case when 成绩 between 85 and 100 then 1 else 0 end) [100-85] 
,sum(case when 成绩 between 70 and 84 then 1 else 0 end) [84-70]
,sum(case when 成绩 between 60 and 69 then 1 else 0 end) [69-60]
,sum(case when 成绩<60 then 1 else 0 end) [<60]
group by 课程id,课程名称