第九章

use StudentManagement
go
create view view_course_credits
    as
    select Course_No,Course_Name,Course_Credits
    from Course
        where Course_Credits=4
    go

create view view_Is_Student
    as
    select Student_No,Student_Name,Student_Sex,Student_Birthday
    from Student
        where Student_ClassNo='200701'
    go

create view view_Studentinfo
    as
    select Student.Student_No,Student_Name,Student_Sex,Course_Name,SelectCourse_Score
    from Course inner join SelectCourse
    on Course.Course_No=SelectCourse.SelectCourse_CourseNo
    inner join Student on SelectCourse.SelectCourse_StudentNo=Student.Student_No
    go

create view view_Is_Student_Birth
    as
    select Student_No,Student_Name,Student_Birthday
    from view_Is_Student
        where Student_Birthday>='1988/4/1'
    go

create view view_Avg
    as
    select SelectCourse_StudentNo,Avg=avg(selectcourse_score)
    from SelectCourse
        group by SelectCourse_StudentNo
go 

select * 
from view_Studentinfo
    where Student_Name='王小蒙'
go

select Student_No,Student_Name,SelectCourse_Score
from view_Studentinfo
    where SelectCourse_Score>=60
go

select *
from view_Avg
where Avg>80
go

sp_help view_Studentinfo
go

sp_helptext view_Studentinfo
go

sp_depends view_Studentinfo
go

alter view view_Studentinfo
    as
    select Student.Student_No,Student_Name,Course_Name,Course_Credits,SelectCourse_Score
    from Course inner join SelectCourse
    on Course.Course_No=SelectCourse.SelectCourse_CourseNo
    inner join Student
    on SelectCourse.SelectCourse_StudentNo=Student.Student_No
go
sp_help view_studentinfo
go

update view_Studentinfo
    set SelectCourse_Score=90
    where Student_Name='王小蒙' and Course_Name='物理'
    go
    select *
    from view_Studentinfo
    where Student_Name='王小蒙'
go

create view view_Student_Score
    as
    select Student.Student_No,sum(Course_Credits) as Credits_Total,avg(selectcourse_score) as Score_Average
    from Course inner join SelectCourse
    on Course.Course_No=SelectCourse.SelectCourse_CourseNo
    inner join Student 
    on SelectCourse.SelectCourse_StudentNo=Student.Student_No
    group by student.Student_No
    go
select * 
from view_Student_Score
go

select Student_No,Score_Average
from view_Student_Score
    where Score_Average>80
go


use LibraryManagement
go
create view view_Book
    as
    select *
    from 图书表
    go
select *
from view_Book
go

create view view_Press
    as
    select *
    from 图书表
    where 出版社='电子工业出版社' 
        or 出版社='科学出版社'
        or 出版社='人民邮电出版社'
    go
select *
from view_Press
go

create view view_Press_Phei
    as
    select 平均价格=avg(单价),最高价=max(单价),最低价=min(单价)
    from 图书表
        where 出版社='电子工业出版社'
go
    select *
    from view_Press_Phei
go

create view view_Readers
    as
    select 读者表.读者号,姓名,书名,归还日期,借出日期
    from 读者表 inner join 借阅表
    on 读者表.读者号=借阅表.读者号
    inner join 图书表
    on 图书表.图书号=借阅表.图书号
go
select *
    from view_Readers
go