大学生数据库实验.zip

CREATE DATABASE StudentCourse191426 use StudentCourse191426 drop table Students191426 drop table Courses191426 drop table SC191426 CREATE TABLE Students191426 ( Sno CHAR(10) PRIMARY KEY, Sname nvarchar(20) NOT NULL, Ssex nchar(1) NOT NULL DEFAULT '男' CHECK(Ssex IN ('男','女')), Sbirthday smalldatetime, Sdept nvarchar(20), Memo TEXT ) CREATE TABLE Courses191426 ( Cno CHAR(3) PRIMARY KEY, Cname nvarchar(20) NOT NULL, PreCno char(3), Credit tinyint ) CREATE TABLE SC191426 ( Sno CHAR(10), Cno CHAR(3), Grade smallint check(Grade >=0 and Grade <=100), PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Students191426(Sno), FOREIGN KEY (Cno) REFERENCES Courses191426(Cno) ) insert into Students191426 values('0602001','钟文辉','男','1987-05-01','计算机','优秀毕业生') insert into Students191426 values('0602002','吴细文','女','1987-03-24','计算机','爱好：音乐') insert into Students191426 values('0602003','吴朝西','男','1987-07-01','计算机','') insert into Students191426 values('0701001','王冲瑞','男','1988-05-04','机电','爱好：音乐') insert into Students191426 values('0701002','林滔滔','女','1987-04-03','机电','爱好：体育') insert into Students191426 values('0701003','李修雨','女','1986-03-03','机电','') insert into Students191426 values('0703001','李奇','男','1988-09-17','商贸管理','') insert into Courses191426 values('C1','程序设计基础(C)','',4) insert into Courses191426 values('C2','数据库原理','C1',4) insert into Courses191426 values('C3','SQL程序设计','C2',4) insert into Courses191426 values('C4','音乐欣赏','',1) insert into SC191426 values('0602001','C1',61) insert into SC191426 values('0602001','C2',72) insert into SC191426 values('0602001','C3',88) insert into SC191426 values('0602002','C1',NULL) insert into SC191426 values('0602002','C2',61) insert into SC191426 values('0701001','C1',50) insert into SC191426 values('0701001','C2',NULL) insert into SC191426 values('0701002','C3',78) insert into SC191426 values('0701003','C1',52) insert into SC191426 values('0701003','C3',87) CREATE DATABASE 学生选课191426 use 学生选课191426 drop table 学生191426 CREATE TABLE 学生191426 ( 学号 CHAR(10) PRIMARY KEY, 姓名 nvarchar(20) NOT NULL, 性别 nchar(1) NOT NULL DEFAULT '男' CHECK(性别 IN ('男','女')), 出生日期 smalldatetime, 院系名称 nvarchar(20), 备注 TEXT ) drop table 课程191426 CREATE TABLE 课程191426 ( 课程号 CHAR(3) PRIMARY KEY, 课程名 nvarchar(20) NOT NULL, 先行课 char(3), 学分 tinyint ) drop table 选修191426 CREATE TABLE 选修191426 ( 学号 CHAR(10), 课程号 CHAR(3), 分数 smallint check(分数 >=0 and 分数 <=100), PRIMARY KEY(学号,课程号), FOREIGN KEY (学号) REFERENCES 学生191426(学号), FOREIGN KEY (课程号) REFERENCES 课程191426(课程号) )