[2017年整理]sql 作业答案.doc

现有学生表（学号，姓名，性别，年龄，入学年份，籍贯，手机号码，系号，班长学号），学号是主码，系号和班长学号是外部码，手机号码必须唯一，学生的年龄不得小于10岁和大于50岁，性别必须是男或者女。 系表（系号，系名，系主任），其中系号是主码，系名不能有重复的。 选课表（学号，课程号，成绩），（学号，课程号）是主码，学号和课程号是外部码，成绩不能小于0分和大于100分 课程表（课程号，课程名，先修课，学分），课程号是主码，课程名必须唯一，学分必须大于0小于5。 学分计算表（最低成绩，最高成绩，计算比率）。最低成绩和最高成绩都必须大于0小于100，同时最低成绩不能大于最高成绩。 试完成以下工作： create table 系表 (系号char(4) PRIMARY KEY, 系名varchar(50) UNIQUE, 系主任varchar(10)) create table 学生表 (学号 char(10) PRIMARY KEY, 姓名varchar(10), 性别 char(2) CHECK(性别 IN (男,女)) , 年龄 int CHECK(年龄 between 10 and 50 ) , 入学年份, 籍贯 varchar(100), 手机号码 varchar(20) UNIQUE, 系号 char(4), 班长学号 char(10), FOREIGN KEY(班长学号)REFERENCES 学生表(学号), FOREIGN KEY(系号)REFERENCES 系表(系号)) create table 课程表 (课程号 char(10) PRIMARY KEY, 课程名varchar(100) UNIQUE, 先修课 char(10) FOREIGN KEY REFERENCES 表(课程号), 学分 int CONSTRAINT CK_课程表_学分 CHECK(学分 between 0 and 5)) create table 选课表 (学号 char(10), 课程号 char(10), 成绩 int CHECK(成绩 between 0 and 100 ) default 0, PRIMARY KEY(学号,课程号), FOREIGN KEY(学号)REFERENCES 学生表(学号), FOREIGN KEY(课程号)REFERENCES 课程表(课程号))create table 学分计算表 (最低成绩 real CHECK(最低成绩 between 0 and 100), 最高成绩 real CHECK(最高成绩 between 0 and 100), 计算比率 real , CHECK(最高成绩= 最低成绩) )insert into 学生表 values(26,李四,女,20,2008,广东,10010001000, NULL,10) insert into 学生表values(26,李四,女,20,2008,广东,10010001000, 10) 3. 删除上述记录 delete from 学生表 where 学号=26 alter table 学生表ALTER COLUMN 姓名varchar(6) 5. 为学生表增加一个字段电子邮件，20个字符。 ALTER TABLE 学生表 ADD 电子邮件 varchar(200) NULL 6. 对课程表的学分字段上的完整性约束进行修改，使其在0到6之间取值。 ALTER TABLE 课程表 DROP CONSTRAINT CK_课程表_学分 ALTER TABLE 课程表 ADD CONSTRAINT CK_课程表_学分 CHECK(学分 between 0 and ) 7. 为学生表在学号列上创建cluster索引。 CREATE CLUSTERED INDEX IX_学号ON 学生表(学号)8. 创建一个视图，计算每门课的最高分。 CREATE VIEW v_每门课的最高分(课程号,每门课的最高分) AS SELECT 课程号,MAX(成绩) FROM 选课表 group by 课程号 CREATE VIEW v_每门课的最高分 AS SELECT 课程号,MAX(成绩) as 每门课的最高分 FROM 选课表 group by 课程号 9. 将6系所有学生的年龄，改为7系学生的平均年龄。 update 学生表 set 年龄=(select avg(年龄) from 学生表 where 系号=7系) where 系号=系 10. 查找所有学生的姓名、入学年份和籍贯。 select 姓名,入学年份,籍贯 from 学生表 11. 列出籍