--1、建立以上三张表
CREATE TABLE Student(
Sno char(9),
Sname varchar(20),
Ssex char(2),
Sage int,
Sdept varchar(30),
)
CREATE TABLE Course(
Cno int,
Cname varchar(30),
Cpno int,
Ccredit numeric(3,1),
)
CREATE TABLE SC(
Sno char(9),
Cno int,
Grade numeric(4,1),
)
--2、定义三张表的主码
ALTER TABLE Student ALTER COLUMN Sno char(9) not null
ALTER TABLE Student add constraint P_Sno primary key(Sno)
ALTER TABLE Course ALTER COLUMN Cno int not null
ALTER TABLE Course add constraint P_Cno primary key(Cno)
ALTER TABLE SC ALTER COLUMN Sno char(9) not null
ALTER TABLE SC ALTER COLUMN Cno int not null
ALTER TABLE SC add constraint P_Sno_Cno primary key(Sno , Cno)
--3、定义SC表中的参照完整性(删除、更新级联)
ALTER TABLE SC ADD FOREIGN KEY (sno) REFERENCES Student(Sno)
on delete cascade
on update cascade
ALTER TABLE SC ADD FOREIGN KEY (Cno) REFERENCES Course(Cno)
on delete cascade
on update cascade
--4、向Student表增加“入学成绩列”
ALTER TABLE Student ADD Sgrade int
--5、Student表的Ssex只允许取“男”或“女”,年龄小于30
ALTER TABLE Student ADD constraint S_sex check ((Ssex = '男') or (Ssex = '女'))
ALTER TABLE Student ADD constraint S_age check (Sage < 30)
--6、SC表的Grade的值应该在0—100之间
ALTER TABLE SC ADD constraint S_grade check((0 < Grade) and (Grade < 100))
--7、去掉第5题中的性别的限制
ALTER table Student drop constraint S_sex
--8、修改表Student的约束条件,年龄由小于30改为14—40之间
ALTER table Student drop constraint S_age
ALTER TABLE Student ADD constraint S_age check ((Sage <40 ) and (Sage > 14))
--9、建立信息系学生的视图
go
CREATE VIEW IS_Student(sno,sname,ssex,sage,sdept)
as
select sno,sname,ssex,sage,sdept
from Student
go
--10、建立信息系选修了1号课程的学生的视图
go
create view IS_CS(sno,sname,grade)
as
select Student.sno,sname,grade
from Student,SC
where Sdept = 'IS'and Student.sno = SC.Cno and SC.Cno = '1'
go
--11、建立信息系选修1号课程且成绩在90分以上的学生的视图
go
create view IS_S2(sno,sname,ssex,sage,sdept)
as
select Student.sno,sname,ssex,sage,sdept
from Student,SC
where Sdept = 'IS'
and Student.sno = sc.Sno
and sc.Cno = '1'
and grade>=90;
Go
--12、所有女生记录定义为一个视图
go
create view IS_sex(ssex)
as
select ssex
from Student
where ssex='女';
Go