SQL2005建表

 DROP TABLE SC,
DROP TABLE Student,
DROP TABLE Course,
CREATE TABLE Student
(--Sno CHAR(9) PRIMARY KEY,
 Sno NUMERIC(9)
 CONSTRAINT C1 CHECK(Sno BETWEEN 200606140 AND 200606180),
 Sname CHAR(20)
 CONSTRAINT C2 NOT NULL,
 Ssex CHAR(2)
 CONSTRAINT C3 CHECK(Ssex IN('男','女')),
 --Sage SMALLINT,
 Sage NUMERIC(3)
 CONSTRAINT C4 CHECK(Sage>10 AND Sage<30),
 Sdept CHAR(20),
 CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),
 Cpno CHAR(4),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno NUMERIC(9),
 Cno CHAR(4),
 Grade SMALLINT CHECK(Grade>=0 AND Grade<=100),
 PRIMARY KEY(Sno,Cno),
 FOREIGN KEY (Sno) REFERENCES Student(Sno)
 ON DELETE CASCADE
 ON UPDATE CASCADE,
 FOREIGN KEY (Cno) REFERENCES Course(Cno)
 ON DELETE NO ACTION
 ON UPDATE CASCADE
);

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

SELECT *
FROM IS_Student;