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 )