SQL语句
二维表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:------------------------------------------------------------------------------
│ 学生ID │ 学生姓名 │ 课程ID │ 课程名称 │ 成绩 │ 教师ID │ 教师姓名 │
│ S3 │ 王五 │ K4 │ 政治 │ 53 │ T4 │ 赵老师 │
│ S1 │ 张三 │ K1 │ 数学 │ 61 │ T1 │ 张老师 │
│ S2 │ 李四 │ K3 │ 英语 │ 88 │ T3 │ 李老师 │
│ S1 │ 张三 │ K4 │ 政治 │ 77 │ T4 │ 赵老师 │
│ S2 │ 李四 │ K4 │ 政治 │ 67 │ T5 │ 周老师 │
│ S3 │ 王五 │ K2 │ 语文 │ 90 │ T2 │ 王老师 │
│ S3 │ 王五 │ K1 │ 数学 │ 55 │ T1 │ 张老师 │
│ S1 │ 张三 │ K2 │ 语文 │ 81 │ T2 │ 王老师 │
│ S4 │ 赵六 │ K2 │ 语文 │ 59 │ T1 │ 王老师 │
│ S1 │ 张三 │ K3 │ 英语 │ 37 │ T3 │ 李老师 │
│ S2 │ 李四 │ K1 │ 数学 │ 81 │ T1 │ 张老师 │
│ .... │ │ │ │ │ │ │
│ .... │ │ │ │ │ │ │
------------------------------------------------------------------------------
1.简述规范化思路
请以一句 T-SQL (Ms SQL Server) 或 Jet SQL (Ms Access) 作答
2.如果 T 表还有一字段 F0 数据类型为自动增量整型(唯一,不会重复),
而且 T 表中含有除 F0 字段外,请删除其它字段完全相同的重复多余的脏记录数据:
3.列印各科成绩最高和最低的记录: (就是各门课程的最高、最低分的学生和老师)
课程ID,课程名称,最高分,学生ID,学生姓名,教师ID,教师姓名,最低分,学生ID,学生姓名,教师ID,教师姓名
4.按成绩从高到低顺序,列印所有学生四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)
学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
(注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")
5.按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
课程ID,课程名称,平均成绩,及格百分数
6.列印四门课程平均成绩和及格率的百分数(用"1行4列"表示): (就是分析哪门课程难)
数学平均分,数学及格百分数,语文平均分,语文及格百分数,英语平均分,英语及格百分数,政治平均分,政治及格百分数
7.列印数学成绩第 10 名到第 15 名的学生成绩单
或列印总成绩第 10 名到第 15 名的学生成绩单
8.按不同老师所教不同课程平均分从高到低列印: (就是分析哪个老师的哪个课程水平高)
教师ID,教师姓名,课程ID,课程名称,平均分
9.统计列印各门课程成绩各分数段人数: (类似交叉报表)
课程ID,课程名称,[100-85],[84-70],[69-60],[<60]
.删除其它字段完全相同的重复多余的脏记录数据
delete a
from t a,t b
where a.学生ID=b.学生ID and a.课程ID=b.课程ID and a.F0>b.F0
3.列印各科成绩最高和最低的记录
select a.课程ID,a.课程名称,a.[成绩] as 最高分,a.[学生ID],a.[学生姓名],a.[教师ID],a.[教师姓名],
b.[成绩] as 最低分,b.[学生ID],b.[学生姓名],b.[教师ID],b.[教师姓名]
from t a,t b,(select 课程ID,max([成绩]) 最高分,min([成绩]) 最低分 from t group by 课程ID) c
where a.[成绩]=c.最高分 and a.课程ID=c.课程ID and b.[成绩]=c.最低分 and b.课程ID=c.课程ID
4.按成绩从高到低顺序,列印所有学生四门(数学,语文,英语,政治)课程成绩
select 学生id,
max(case when 课程名称='数学' then 成绩 end) as '数学',
max(case when 课程名称='语文' then 成绩 end) as '语文',
max(case when 课程名称='英语' then 成绩 end) as '英语',
max(case when 课程名称='政治' then 成绩 end) as '政治',
count(成绩) as 有效课程数,
(case when count(成绩)=0 then 0 else isnull(sum(成绩),0)/count(成绩) end) as 有效平均分
from t group by 学生ID
order by 有效平均分 desc
5.按各科不及格率的百分数从低到高和平均成绩从高到低顺序
select 课程ID,max(课程名称) as 课程名称,left(avg(成绩),4) as 平均成绩,
left(100.0* sum(case when 成绩 >=60 then 1 else 0 end)/count(课程ID) as 及格百分数
from t group by 课程ID
order by 及格百分数 desc,平均成绩 desc
6.列印四门课程平均成绩和及格率的百分数
select
left(max(case when 课程ID = 'k1' then ac end),4) as 数学平均分,
left(max(case when 课程ID = 'k1' then passperc end),4) as 数学及格百分数,
left(max(case when 课程ID = 'k2' then ac end),4) as 语文平均分,
left(max(case when 课程ID = 'k2' then passperc end),4) as 语文及格百分数,
left(max(case when 课程ID = 'k3' then ac end),4) as 英语平均分,
eft(max(case when 课程ID = 'k3' then passperc end),4) as 英语及格百分数,
left(max(case when 课程ID = 'k4' then ac end),4) as 政治平均分,
left(max(case when 课程ID = 'k4' then passperc end),4) as 政治及格百分数
FROM
(select 课程ID,left(avg(成绩),4) as ac,
left(100.0* sum(case when 成绩 >=60 then 1 else 0 end)/count(成绩),4) as passperc
from t group by 课程ID) a
7. 列印总成绩第 10 名到第 15 名的学生成绩单
select top 6 学生ID, sum(成绩) as 总分
from t
where 学生ID not in (select top 9 学生ID from t group by 学生ID order by sum(成绩) desc )
group by 学生ID
order by 总分 desc
8.按不同老师所教不同课程平均分从高到低列印
select 教师ID,max(教师姓名),课程ID,max(课程名称) as 课程名称,avg(成绩) as 平均成绩
from t
group by 课程ID,教师ID
order by avg(成绩) desc
9.统计列印各门课程成绩各分数段人数
select 课程ID,课程名称,
count(case when 成绩<=100 and 成绩>=85 then 课程ID end) as '[100-85]',
count(case when 成绩<=84 and 成绩>=70 then 课程ID end) as '[84-70]',
count(case when 成绩<=69 and 成绩>=60 then 课程ID end) as '[69-60]',
count(case when 成绩<60 then 课程ID end) as '[<60]'
from t
group by 课程ID,课程名称
