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