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

Question

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,课程名称

Answer 1

1.在test表中增加一个字段T0  ,自动增长  ,删除test表中除T0字段外其它字段完全相同的重复多余的记录数据.
--增加字段
alter table test add t0 int identity(1,1)--删除重复记录
delete from test where t0<>(select min(t0) from test a 
where a.t1=test.t1 and a.t2=test.t2 and a.t3=test.t3 and a.t4=test.t4
and a.t5=test.t5 and a.t6=test.t6 and a.t7=test.t7)2. 检索各科成绩最高和最低记录(所有信息).
select * from
where t5=(select max(t5) from test) or t5=(select min(t5) from test)3. 按成绩从高到低顺序,列出所有学生4门(数学,英文,政治,语文)课程成绩,也就是每个学生的4门课程成绩单.
   学生id   学生姓名   数学  英文   政治  语文
select * from(
select 学生id=t1,学生姓名=t2
,数学=max(case t4 when '数学' then t5 else 0 end)
,英文=max(case t4 when '英文' then t5 else 0 end)
,政治=max(case t4 when '政治' then t5 else 0 end)
,语文=max(case t4 when '语文' then t5 else 0 end)
from test group by t1,t2
) a order by 数学 desc,英文 desc,政治 desc,语文 desc4.安各科不及格率的百分数从低到高和平均成绩从高到低的顺序,统计并列出各科平均成绩和不及格百分数.
    课程id     课程名称      平均成绩      及格百分比
select * from(
select 课程id=t3,讲和名称=t4
,平均成绩=avg(t5)
,及格百分比=cast(cast(sum(case t5>=60 then 1.0 else 0.0 end)/sum(1)*100 as decimal(20,2) as varchar)+'%'
from test group by t3,t4
) order by 平均成绩 desc,及格百分比 desc

Answer 2

题是我一年多以前出的!一道褒贬不一的 SQL 考试题
http://www.csdn.net/Develop/Read_Article.asp?Id=15989《程序员》杂志也发表过,已经一年了

Answer 3

本题若是现场做,有一定困难!
可以这样说:
你如果会了本题,你基本上可以解决本论坛的 SQL 查询的大多数问题:
相关子查询
连接
分组汇总(聚集)

Answer 4

http://www.csdn.net/Develop/Read_Article.asp?Id=15989
第一次知道还可以
delete a
from a,b 
where a.1.b.1

Answer 5

用sql实现以下功能  表 test(T1,T2 ,T3,T4,T5,T6,T7)学生id    学生姓名     课程id    课程名称      成绩     教师id   教师姓名
DROP TABLE TEST
GO
create table test(T1 VARCHAR(5),T2 NVARCHAR(20),T3 varchar(5),T4 Nvarchar(20),T5 int,T6 varchar(5),T7 Nvarchar(20))
insert into test select  'A1',         N'李四',        'k1',         N'数学',         50 ,       'T1',      N'王老师'
insert into test select  'A2',         N'小王',        'k2',         N'英文',         60 ,       'T2',      N'李老师'
insert into test select  'A3',         N'小刘',        'k3',         N'政治',         80 ,       'T3',      N'刘老师'
insert into test select  'A4',         N'小张',        'k4',         N'语文',         90 ,       'T4',      N'张老师'
insert into test select  'A1',         N'李四',        'K2',         N'英文',         50 ,       'T2',      N'李老师'
insert into test select  'A1',         N'李四',        'K3',         N'政治',         45 ,       'T3',      N'刘老师'
insert into test select  'A1',         N'李四',        'k4',         N'语文',         50 ,       'T4',      N'张老师'
   
1.在test表中增加一个字段T0  ,自动增长  ,删除test表中除T0字段外其它字段完全相同的重复多余的记录数据.
alter table test add T0 int identity(1,1)
delete from test where T0 in(select max(T0) as T0 from test group by T1,T2,T3,T4,T5,T6,T7 having count(*)>1)-- 2. 检索各科成绩最高和最低记录(所有信息).
select * from test a,
(
select min(T0) as T0,T3,min(T5)AS t5 from test group by T3
)b
where a.T0=b.T0select * from test a,
(
select min(T0) as T0,T3 ,max(T5) from test group by T3
)b
where a.T0=b.T03. 按成绩从高到低顺序,列出所有学生4门(数学,英文,政治,语文)课程成绩,也就是每个学生的4门课程成绩单.
   学生id   学生姓名   数学  英文   政治  语文
select T1 as 学生ID,T2 as 学生姓名,
sum(case T4 when N'数学' then T5 else 0 end) as 数学,
sum(case T4 when N'英文' then T5 else 0 end) as 英文,
sum(case T4 when N'政治' then T5 else 0 end) as 政治,
sum(case T4 when N'语文' then T5 else 0 end) as 语文
from test
group by T1,T2

4.安各科不及格率的百分数从低到高和平均成绩从高到低的顺序,统计并列出各科平均成绩和不及格百分数.
    课程id     课程名称      平均成绩      及格百分比
select T3 as 课程id,T4 as 课程名称,avg(T5) as 平均成绩,cast(sum(case when T5>=60 then 1.0 else 0 end)/sum(1.0) *100 as varchar(20))+'%' as 及格百分比
from test
group by T3,T4
order by sum(case when T5>=60 then 1.0 else 0 end)/sum(1.0) desc,avg(t5) desc

Answer 6

大力的这个不对，sum(1.0)同样也得case，否则都是总数，而不是课程的人数
5.检索出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 test

Answer 7

请仔细看我的回帖:一道褒贬不一的 SQL 考试题
http://www.csdn.net/Develop/Read_Article.asp?Id=15989