面试题知道的说下

Question

--建数据表--
USE Student
GO
CREATE TABLE Member  --学生表
(
  MID  char(10) primary key,  --学生号
  MName  CHAR(50) NOT NULL  --姓名
)
GO
CREATE TABLE F  --课程表
(
 FID   char(10) primary key,    --课程号
FName  CHAR(50) NOT NULL --课程名
)GO
CREATE TABLE score  --学生成绩表
(
  SID int identity(1,1) primary key,  --成绩记录号
  FID char(10)  foreign key(FID) references F(FID) ,     --课程号
  MID char(10)  foreign key(MID) references Member(MID) ,     --学生号
  Score  int NOT NULL    --成绩
)
》》知道的多写几种方法啊我研究研究（ro）--1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：
--姓名 语文 数学 英语 历史
--张萨 78  67 89 76
--王强  89  67  84  96
--李三 70  87  92  56
--李四  80 78  97 66--2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。--3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。--4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。

Answer 1

--建数据表--
USE Student
GO
CREATE TABLE Member --学生表
(
  MID char(10) primary key, --学生号
  MName CHAR(50) NOT NULL --姓名
)
GO
CREATE TABLE F --课程表
(
 FID char(10) primary key, --课程号
FName CHAR(50) NOT NULL --课程名
)
GO
CREATE TABLE score --学生成绩表
(
  SID int identity(1,1) primary key, --成绩记录号
  FID char(10) foreign key(FID) references F(FID) , --课程号
  MID char(10) foreign key(MID) references Member(MID) , --学生号
  Score int NOT NULL --成绩
)select  * from Member
select  * from F
select  * from score
insert into member 
select '1','张萨' union all
select '2','王强' union all
select '3','李三' union all
select '4','李四' union all
select '5','Bill'
insert into F
select '01','语文' union all
select '02','数学' union all
select '03','英语' union all
select '04','历史'insert into score 
select '01','1',50 union all
select '01','2',60 union all
select '01','3',40 union all
select '01','4',80 union allselect '02','1',88 union all
select '02','2',81 union all
select '02','3',66 union all
select '02','4',44 union allselect '03','1',99 union all
select '03','2',88 union all
select '03','3',77 union all
select '03','4',60 union allselect '04','1',88 union all
select '04','2',66 union all
select '04','3',77 union all
select '04','4',90--1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：
--姓名 语文 数学 英语 历史
--张萨 78 67 89 76
--王强 89 67 84 96
--李三 70 87 92 56
--李四 80 78 97 66select S.mid as '学号',M.mname as '姓名',max(case when S.fid='01' then S.score else null end) as '语文',max(case when S.fid='02' then S.score else null end) as '数学',max(case when S.fid='03' then S.score else null end) as '英语',max(case when S.fid='04' then S.score else null end) as '历史'  from score S inner join member M on S.Mid=M.Mid  group by  S.mid,M.mname
--2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。select S.sid,M.MName,F.FName,S.score from score S inner join member M on S.mid = M.mid inner join F  on F.fid = S.fid  where S.score <70
--3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
select M.Mid,M.MName,avg(S.score) as '平均分' from score S inner join member M on S.mid = M.mid inner join F  on F.fid = S.fid  group by M.Mid,M.MName  order by '平均分' desc
--4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。
create procedure P_CheckIn_Exam
as
select M.mid as '学号',M.mname as '姓名',
case when max(case when S.fid='01' then S.score else null end) is not null then '未缺考' else '缺考' end as '语文',
case when max(case when S.fid='02' then S.score else null end)is not null then '未缺考' else '缺考' end as '数学',
case when max(case when S.fid='03' then S.score else null end) is not null then '未缺考' else '缺考' end as '英语',
case when max(case when S.fid='04' then S.score else null end) is not null then '未缺考'  else '缺考' end  as '历史'  
from score S  right  join member M on S.Mid=M.Mid  group by  M.mid,M.mname
go
exec P_CheckIn_Exam
学号 姓名 语文 数学 英语 历史
1          张萨                                               未缺考 未缺考 未缺考 未缺考
2          王强                                               未缺考 未缺考 未缺考 未缺考
3          李三                                               未缺考 未缺考 未缺考 未缺考
4          李四                                               未缺考 未缺考 未缺考 未缺考
5          Bill                                               缺考 缺考 缺考 缺考

Answer 2

--1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：
--姓名 语文 数学 英语 历史
--张萨 78 67 89 76
--王强 89 67 84 96
--李三 70 87 92 56
--李四 80 78 97 66--1、如果确定为语文、数学、英语、历史四门课程，则使用静态SQL。
select s.MName,
       max(case f.FName when '语文' then m.Score else 0 end) [语文],
       max(case f.FName when '数学' then m.Score else 0 end) [数学],
       max(case f.FName when '英语' then m.Score else 0 end) [英语],
       max(case f.FName when '历史' then m.Score else 0 end) [历史]
from Student s , F , score m
where s.MID = m.MID and m.fid = f.fid
group by s.MName--2、如果不能确定为语文、数学、英语、历史四门课程，课程名称不确定，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select s.MName '
select @sql = @sql + ' , max(case f.FName when ''' + FName + ''' then m.Score else 0 end) [' + FName + ']'
from (select distinct fname from f) as a
set @sql = @sql + ' from from Student s , F , score m where s.MID = m.MID and m.fid = f.fid group by s.MName'
exec(@sql) 
--2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
select s.mname , f.FName , m.score from Student s , F , score m where s.MID = m.MID and m.fid = f.fid and m.Score < 70select s.MName,
       max(case f.FName when '语文' then ltrim(m.Score) else '无' end) [语文],
       max(case f.FName when '数学' then ltrim(m.Score) else '无' end) [数学],
       max(case f.FName when '英语' then ltrim(m.Score) else '无' end) [英语],
       max(case f.FName when '历史' then ltrim(m.Score) else '无' end) [历史]
from Student s , F , score m
where s.MID = m.MID and m.fid = f.fid and m.Score < 70
group by s.MName
--3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
select s.MName , avg(m.score) avg_score from Student s , F , score m where s.MID = m.MID and m.fid = f.fid group by s.mname order by avg_score desc--4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。
create proc my_proc @cnt int
as
  select s.* , isnull(count(1),0) cnt from Student s left join score m on s.mid = m.mid group by s.mid , s.MName having isnull(count(1),0) = @cnt
go

Answer 3

用9楼的数据测试了一下。
CREATE TABLE student --学生表
(
MID char(10) , --学生号
MName CHAR(10) NOT NULL --姓名
)
GO
CREATE TABLE F --课程表
(
FID char(10) , --课程号
FName CHAR(10) NOT NULL --课程名
)
GO
CREATE TABLE score --学生成绩表
(
  SID int identity(1,1) , --成绩记录号
  FID char(10)  , --课程号
  MID char(10)  , --学生号
  Score int NOT NULL --成绩
)insert into student
select '1','张萨' union all
select '2','王强' union all
select '3','李三' union all
select '4','李四' union all
select '5','Bill'insert into F
select '01','语文' union all
select '02','数学' union all
select '03','英语' union all
select '04','历史'insert into score 
select '01','1',50 union all
select '01','2',60 union all
select '01','3',40 union all
select '01','4',80 union all
select '02','1',88 union all
select '02','2',81 union all
select '02','3',66 union all
select '02','4',44 union all
select '03','1',99 union all
select '03','2',88 union all
select '03','3',77 union all
select '03','4',60 union all
select '04','1',88 union all
select '04','2',66 union all
select '04','3',77 union all
select '04','4',90--1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：
--姓名 语文 数学 英语 历史
--张萨 78 67 89 76
--王强 89 67 84 96
--李三 70 87 92 56
--李四 80 78 97 66
--1、如果确定为语文、数学、英语、历史四门课程，则使用静态SQL。
select s.MName,
       max(case f.FName when '语文' then m.Score else 0 end) [语文],
       max(case f.FName when '数学' then m.Score else 0 end) [数学],
       max(case f.FName when '英语' then m.Score else 0 end) [英语],
       max(case f.FName when '历史' then m.Score else 0 end) [历史]
from Student s , F , score m
where s.MID = m.MID and m.fid = f.fid
group by s.MName/*
MName      语文          数学          英语          历史          
---------- ----------- ----------- ----------- ----------- 
李三         40          66          77          77
李四         80          44          60          90
王强         60          81          88          66
张萨         50          88          99          88（所影响的行数为 4 行）
*/--2、如果不能确定为语文、数学、英语、历史四门课程，课程名称不确定，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select s.MName '
select @sql = @sql + ' , max(case f.FName when ''' + FName + ''' then m.Score else 0 end) [' + FName + ']'
from (select distinct fname from f) as a
set @sql = @sql + ' from Student s , F , score m where s.MID = m.MID and m.fid = f.fid group by s.MName'
exec(@sql) 
/*
MName      历史          数学          英语          语文          
---------- ----------- ----------- ----------- ----------- 
李三         77          66          77          40
李四         90          44          60          80
王强         66          81          88          60
张萨         88          88          99          50
*/--2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
select s.mname , f.FName , m.score from Student s , F , score m where s.MID = m.MID and m.fid = f.fid and m.Score < 70
/*
mname      FName      score       
---------- ---------- ----------- 
张萨         语文         50
王强         语文         60
王强         历史         66
李三         语文         40
李三         数学         66
李四         数学         44
李四         英语         60（所影响的行数为 7 行）
*/select s.MName,
       max(case f.FName when '语文' then ltrim(m.Score) else ''  end) [语文],
       max(case f.FName when '数学' then ltrim(m.Score) else ''  end) [数学],
       max(case f.FName when '英语' then ltrim(m.Score) else ''  end) [英语],
       max(case f.FName when '历史' then ltrim(m.Score) else ''  end) [历史]
from Student s , F , score m
where s.MID = m.MID and m.fid = f.fid and m.Score < 70
group by s.MName
/*
MName      语文           数学           英语           历史           
---------- ------------ ------------ ------------ ------------ 
李三         40           66                        
李四                      44           60           
王强         60                                     66
张萨         50                                     （所影响的行数为 4 行）
*/--3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
select s.MName , cast(avg(m.score*1.0) as decimal(18,2)) avg_score from Student s , F , score m where s.MID = m.MID and m.fid = f.fid group by s.mname order by avg_score desc
/*
MName      avg_score            
---------- -------------------- 
张萨         81.25
王强         73.75
李四         68.50
李三         65.00（所影响的行数为 4 行）
*/--4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。
go
create proc my_proc @cnt int
as
  select s.* , isnull(count(1),0) cnt from Student s left join score m on s.mid = m.mid group by s.mid , s.MName having isnull(count(1),0) = @cnt order by s.mid
goexec my_proc 1
/*
MID        MName      cnt         
---------- ---------- ----------- 
5          Bill       1（所影响的行数为 1 行）
*/exec my_proc 2
/*
MID        MName      cnt         
---------- ---------- ----------- （所影响的行数为 0 行）
*/exec my_proc 3
/*
MID        MName      cnt         
---------- ---------- ----------- （所影响的行数为 0 行）
*/exec my_proc 4
/*
MID        MName      cnt         
---------- ---------- ----------- 
1          张萨         4
2          王强         4
3          李三         4
4          李四         4（所影响的行数为 4 行）
*/exec my_proc 0
/*
MID        MName      cnt         
---------- ---------- ----------- （所影响的行数为 0 行）
*/drop table Student 
drop table f 
drop table score 
drop proc my_proc

Answer 4

请问一下，动态SQL在哪里执行啊？（不好意思，高手别见笑哦）