SQL Server查询，视图，存储过程，触发器练习

--1.查询张姓学生的个数

select count(*) as 张姓学生的个数
?? ?from wmj_Student
?? ?where StudentName like '张%'

--2.查询学号0002学生的总成绩

select wmj_Student.StudentId as 学号,?
?? ??? ?sum(wmj_Score.Score) as 总成绩
?? ?from wmj_Student
?? ?join wmj_Score?
?? ?on wmj_Student.StudentId = wmj_Score.StudentId
?? ?where wmj_Student.StudentId = 0002
?? ?group by wmj_Student.StudentId

--3.查询各科成绩的最高分和最低分

select C.CourseId 课程号,
?? ??? ?C.CourseName 课程名称,
?? ??? ?max(Score) 最高分,
?? ??? ?min(Score) 最低分
?? ?from wmj_Score SC?
?? ?join wmj_Course C?
?? ?on SC.CourseId = C.CourseId
?? ?group by C.CourseId,C.CourseName

--4.查询学生各科成绩排名和总成绩排名
--查询各科成绩排名

select StudentId 学号, StudentName 学生姓名, CourseId 课程号, CourseName 课程名称, Score 成绩,
?? ??? ??? ??? ??? ?--按照课程号分组排序
?? ??? ?rank() over (partition by CourseId order by Score desc) 课程排名
?? ??? ?from (
?? ??? ??? ??? ?--根据成绩表和学生表进行内连接，关联学生的学号和成绩信息（临时表）
?? ??? ??? ??? ??? ?select ST.StudentId, ST.StudentName, C.CourseId, C.CourseName, SC.Score
?? ??? ??? ??? ??? ??? ?from wmj_student ST
?? ??? ??? ??? ??? ??? ?join wmj_score SC?
?? ??? ??? ??? ??? ??? ?on ST.StudentId = SC.StudentId
?? ??? ??? ??? ??? ??? ?join wmj_course C?
?? ??? ??? ??? ??? ??? ?on SC.CourseId = C.CourseId
?? ??? ??? ?)子查询

--查询总成绩排名

select ST.StudentId 学号,
?? ??? ?ST.StudentName 学生姓名,
?? ??? ?sum(Score)as 总成绩,
?? ??? ?--一次排序：对查询结果进行排序（无分组）
?? ??? ?rank() over (order by sum(Score) desc) 总成绩排名
?? ?from wmj_score SC
?? ?join wmj_Student ST
?? ?on SC.StudentId = ST.StudentId
?? ?group by ST.StudentId,ST.StudentName

--5.查询学生中男女人数

select
?? ? count(case Gender when 'True' then '男' end) 男生人数 ,
?? ? count(case Gender when 'False' then '女' end) 女生人数?
? ? from wmj_Student?

--6.查询不及格成绩的科目及人数并排名

select C.CourseName 课程名称,?
?? ??? ?count(*) as 不及格人数
?? ?from wmj_Score CS
?? ?join wmj_Course C?
?? ?on CS.CourseId = C.CourseId
?? ?where CS.Score < 60
?? ?group by C.CourseName
?? ?order by 不及格人数

--7.查询平均分低于60的学生及其平均分

select ST.StudentId 学号,
?? ??? ?ST.StudentName 学生姓名,
?? ??? ?avg(SC.Score) 平均分
?? ?from wmj_Student ST
?? ?left join wmj_Score SC ?
?? ?on ST.StudentId = SC.StudentId
?? ?group by ST.StudentId,ST.StudentName
?? ?having avg(SC.Score) < 60

--8.查询各科平均分并排名

select SC.CourseId 课程号,
?? ??? ?C.CourseName 课程名称,
?? ??? ?avg(Score) 平均分,
?? ??? ?row_number() over (order by avg(Score) desc) 排名 --rank（）
?? ?from wmj_Score SC,wmj_Course C
?? ?where SC.CourseId = C.CourseId
?? ?group by SC.CourseId,C.CourseName
?? ?order by avg(Score) desc

--9.查询两门以上不及格课程的同学的学号及其平均成绩
--理解：将不及格的课程求平均值

select ST.StudentId 学号,
?? ??? ?ST.StudentName 学生姓名,
?? ??? ?avg(SC.Score) 平均成绩
?? ?from wmj_Student ST?
?? ?join wmj_Score SC?
?? ?on ST.StudentId = SC.StudentId
?? ?where SC.Score<60
?? ?group by ST.StudentId, ST.StudentName
?? ?having count(SC.StudentId)>= 2

--10.查询有且只有2科低于60分的学生

select ?ST.StudentId 学号, ST.StudentName 姓名?
?? ?from wmj_Student ST
?? ?join wmj_Score SC
?? ?on ST.StudentId=SC.StudentId
?? ?where SC.Score<60?
?? ?group by ST.StudentId, ST.StudentName
?? ?having count(SC.StudentId) = 2

--11.查询所有学生的年龄

select StudentName 姓名,
?? ??? ?datediff(year,Birthday,getdate()) 年龄 ?
?? ?from wmj_Student

--12.查询每个老师所带课程的平均成绩排名

select T.TeacherId 教师号,
?? ??? ?T.TeacherName 教师姓名,
?? ??? ?C.CourseName 课程名称,
?? ??? ?avg(SC.Score) 平均成绩,
?? ??? ?row_number() over (order by avg(SC.Score) desc) 排名 --rank（）
?? ?from wmj_Teacher T?
?? ?join wmj_Course C?
?? ?on T.TeacherId = C.fk_teacher_id
?? ?join wmj_Score SC?
?? ?on C.CourseId = SC.CourseId
?? ?group by T.TeacherId,T.TeacherName,C.CourseName
?? ?order by avg(SC.Score) desc

--13.搞一个成绩单

select ST.StudentId,ST.StudentName,
?? ?sum(case when C.CourseName='语文' then SC.Score end )as 语文,
?? ?sum(case when C.CourseName='数学' then SC.Score end )as 数学,
?? ?sum(case when C.CourseName='英语' then SC.Score end )as 英语,
?? ?sum(case when C.CourseName='物理' then SC.Score end )as 物理,
?? ?sum(case when C.CourseName='化学' then SC.Score end )as 化学,
?? ?sum(case when C.CourseName='生物' then SC.Score end )as 生物,
?? ?sum(case when C.CourseName='计算机' then SC.Score end )as 计算机,--sum()函数仅返回 then 取值中唯一值的总和
?? ?sum(SC.Score) 总成绩,
?? ?rank() over(order by sum(SC.Score) desc) as '排名'
? from wmj_Score SC
? join wmj_Student ST
? on SC.StudentId = ST.StudentId
? join wmj_Course C
? on SC.CourseId = C.CourseId
? group by ST.StudentId,ST.StudentName

--14.写一个视图，内容是学生和各科成绩

create view wmj_Student_Score?
?? ?as
?? ??? ?select ST.StudentId 学号, ST.StudentName 学生姓名, C.CourseName 课程名称, SC.Score 成绩
?? ??? ??? ?from wmj_student ST
?? ??? ??? ?join wmj_score SC
?? ??? ??? ?on ST.StudentId = SC.StudentId
?? ??? ??? ?join wmj_course C?
?? ??? ??? ?on SC.CourseId = C.CourseId

select * from wmj_Student_Score

--15.写一个存储过程，我传名字或id，可以查出来该学生各科成绩和总成绩

create proc usp_select_score
@StudentId int=0001
as
begin
--执行部分
? ? -- 查询学生各科成绩和总成绩
? ? select CourseName 课程名称,
?? ??? ??? ?Score 成绩
?? ?from wmj_Score SC
?? ?left join wmj_Course C
?? ?on SC.CourseId = C.CourseId
?? ?where SC.StudentId = @StudentId?
?? ?
? ? select sum(Score) 总成绩?
?? ?from wmj_Score?
?? ?where wmj_Score.StudentId = @StudentId
end

--调用存储过程
exec usp_select_score @StudentId =0002

--16.新建一个记录表，写一个触发器，每当学生信息变更时，记录变更前后信息到记录表
--理解：
--update更新表时，同时使用了deleted表和inserted表（本题删除不行 -,- 由于设置外键问题）
?

--创建一个和学生表结构一模一样的表
select top 0 * into ?wmj_StudentBackup from wmj_Student
select * from wmj_StudentBackup

--创建添加触发器
create trigger tri_insert_wmj_Student on wmj_Student
after insert --当添加以后触发
as
begin
?? ?--inserted表
?? ?insert into wmj_StudentBackup (StudentId,StudentName,Birthday,Gender) select?
?? ?StudentId,StudentName,Birthday,Gender from inserted --数据来源
end

--执行更新操作
update wmj_Student set StudentName = '柯南',Birthday = 2013-8-1,Gender = 0 where StudentId = 9
select * from wmj_StudentBackup
select * from wmj_Student
--insert ?into wmj_Student values( 11,'邓为', 2013-8-1, 0 )?