sqlserver 视图

/*drop table scourse
drop table course
drop table student
drop table major*/

create database db
use db
--专业表
create table major
(spno char(5) not null primary key,
 spname varchar(20) not null,
 pno char(2) )

--学生表
create table student
(sno char(7) not null primary key,
 sname varchar(20) not null,
 ssex char(2) not null,
 sage smalldatetime ,
 spno char(5) not null foreign key references major(spno),
 classid char(5),
 Inyear char(4) not null )

--课程表
create table course
(cno char(10) not null primary key,
cname varchar(20) not null,
credit smallint ,
tno char(3))

 

--选课表
create table scourse
(sno char(7) not null foreign key references student(sno),
 cno char(10) not null foreign key references course(cno),
 Gmark numeric(4,1),
primary key(sno,cno))
/*（1）建立00312专业选修了001号课程的学生视图Stu_01312_1；
（2）建立00312专业选修了1号课程并且成绩不及格的学生视图Stu_00312_2；
（3）建立视图Stu_year，由学生学号、姓名、年龄组成；
（4）通过视图查询20岁以上的学生姓名；
（5）通过视图查询00312专业选修了1号课程并且成绩不及格的学生的学号、姓名、年龄。*/

//第一题
 drop view Stu_01312_1
 go
  create view Stu_01312_1 as select * from student where spno='00312' and sno in (select sno from scourse where cno='1')
 go
  select * from Stu_01312_1

//第二题
drop view Stu_00312_2
  go
  create view   Stu_00312_2 as select * from student where spno='00312'  and sno in(select sno from scourse where cno='1' and Gmark < 60)
 go
select * from Stu_00312_2

//第三题
 drop view Stu_00312_3
go
create view Stu_00312_3 as select sno,sname,sage from student
go

//第四题

select sname from Stu_00312_3 where sage >20

//第五题

select sno,sname,sage from Stu_00312_2