面试题(SQL)

Question

有一数据库存储学生表S(学号/姓名)、课程表C(课程编号/课程名称)、成绩表SC(学号/课程编号/学期/分数),部分数据分别如下所示:===================================学生表S
S#       SName
1        张三
2        李四
===================================课程表C
C#       CName
1        语文
2        数学===================================成绩表SC  
S#       C#     Semester    Grade  
1 1 1 80
1 2 1 60
2 1 1 89
2 2 1 72
1 1 2 70
1 2 2 60
2 1 2 86
2 2 2 62第一题:
要得到以下格式的表,问SQL怎写?姓名    第一学期总分    第二学期总分   
张三         146             130      
......第二题:
要得到以下格式的表,问SQL怎写?姓名      语文平均分        数学平均分   
张三         75              60       
......

Answer 1

create table S(S# INT,SName VARCHAR(50))
create table C(C# INT,CName VARCHAR(50))
create table SC(S# INT,C# INT ,Semester INT,Grade INT)
GO
INSERT S SELECT 1    ,    '张三' UNION ALL
         SELECT 2      ,  '李四'
INSERT C SELECT 1      ,  '语文' UNION ALL
         SELECT 2      ,  '数学'
INSERT SC SELECT 1 ,1 ,1 ,80 UNION ALL
          SELECT 1 ,2 ,1 ,60 UNION ALL
          SELECT 2 ,1 ,1 ,89 UNION ALL
          SELECT 2 ,2 ,1 ,72 UNION ALL
          SELECT 1 ,1 ,2 ,70 UNION ALL
          SELECT 1 ,2 ,2 ,60 UNION ALL
          SELECT 2 ,1 ,2 ,86 UNION ALL
          SELECT 2 ,2 ,2 ,62
GO
SELECT * FROM SC
GO
DROP TABLE S,C,SC
GO
SELECT S.SName ,SUM(CASE WHEN Semester=1 THEN  Grade  ELSE 0 END) AS '第一学期',
SUM(CASE WHEN Semester=2 THEN  Grade  ELSE 0 END) AS '第二学期' FROM SC,S WHERE SC.S#=S.S#
GROUP BY S.SName,SC.S#
GO
GO
这是第一问.第二问也差不多。`.`

Answer 2

create table 学生表(s# int,sname varchar(20))
insert into 学生表
select 1,'张三'
union all select 2,'李四'create table 课程表(c# int,cname varchar(20))
insert into 课程表
select 1,'语文'
union all select 2,'数学'create table 成绩表  (s# int,c# int,semester int,grade int)
insert into 成绩表
select   1,1,1,80
union all select 1,2,1,60
union all select 2,1,1,89
union all select 2,2,1,72
union all select 1,1,2,70
union all select 1,2,2,60
union all select 2,1,2,86
union all select 2,2,2,62
--1.
select 学生表.sname,
sum(case semester when 1 then grade else 0 end) as '第一学期总分',
sum(case semester when 2 then grade else 0 end) as '第二学期总分'
from 成绩表
inner join 学生表 on 学生表.s#=成绩表.s#
group by 学生表.sname--2.
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',avg(case when c#='+rtrim(c#)+' then grade end) as '''+cname+'平均分'''
from 课程表
select @sql='select 学生表.sname'+@sql+
' from 成绩表'+
' inner join 学生表 on 学生表.s#=成绩表.s#'+
' group by 学生表.sname'
exec(@sql)

Answer 3

其实也不要弄那么多符号了..
我顺便也把第二题给解了
  接上面的内容 /.
:
SELECT S.SName,AVG(CASE WHEN C.CName='语文' THEN Grade ELSE 0 END) AS '语文平均分',AVG
(CASE WHEN C.CName='数学' THEN Grade ELSE 0 END) AS '数学平均分' FROM SC INNER JOIN C ON SC.C#=C.C# 
INNER JOIN S ON SC.S#=S.S#
GROUP BY S.SName

Answer 4

回楼上的同志,第二题的答案不正确.结果应该和下面的相同:SELECT S.SName,
SUM(CASE WHEN C.CName='语文' THEN Grade ELSE 0 END)/(SELECT COUNT(*) FROM SC SC1,C C1 WHERE SC1.S#=SC.S# AND SC1.C#=C1.C# AND C1.CName='语文') AS '语文平均分',
SUM(CASE WHEN C.CName='数学' THEN Grade ELSE 0 END)/(SELECT COUNT(*) FROM SC SC2,C C2 WHERE SC2.S#=SC.S# AND SC2.C#=C2.C# AND C2.CName='数学') AS '数学平均分' 
FROM SC,S,C 
WHERE SC.C#=C.C# AND SC.S#=S.S#
GROUP BY S.SName,SC.S#

Answer 5

oracle中执行：
create table S20110530(
sno number(19),
sname varchar2(128)
)create table C20110530(
cno number(10),
cname varchar2(128)
)create table SC20110530(
sno number(10),
cno number(10),
semester number(1),
grade number(10)
)select * from SC20110530 for update 
select sname as 姓名,
sum(case when semester=1 then grade else 0 end) as 第一学期总分,
sum(case when semester=2 then grade else 0 end) as 第二学期总分
from SC20110530 a ,S20110530 b,C20110530 c 
where a.sno=b.sno and a.cno=c.cno and sname='张三'
group by sname
select sname as 姓名,
sum(case when cname='语文' then grade end)/count(distinct semester ) as 语文平均分,
sum(case when cname='数学' then grade end)/count(distinct semester ) as 数学平均分
from SC20110530 a ,S20110530 b,C20110530 c 
where a.sno=b.sno and a.cno=c.cno and sname='张三' 
group by sname