任务12 P193 12.5独立实践

P193 12.5独立实践 ISBN 978-7-302-28577-9
问题描述：某一院系，有很多学生，他们被分在不同的班，每个专业有一个或多个班。院系领导要了解每个专业设置了
哪些课程，学生学习的每门课程的成绩如何，哪个班的学生的成绩比较好等。根据问题描述设计数据库，用SQL语言完成以下各任务。
（1）根据表12.1提供的信息，修改不合理的表，创建数据库，在数据库中创建各表。
表12.1表信息

实体	属性
学生	姓名、性别、出生日期、入学时间、省、城市、地址、家庭电话
班级	班级编号、名称
专业	专业名称、必修课学分、选修课学分
课程	课程编号、课程名称、学分

Create Database JWGL_DB
Use JWGL_DB
Go

Create Table 班级
(
	班级编号 int identity(1,1) not null,
	名称 nvarchar(8) not null,
)
Go

Create Table 学生
(
	学号 int identity(1,1) not null,
	姓名 nvarchar(8) not null,
	性别 bit null,
	出生日期 datetime null,
	入学时间 datetime null,
	省 nvarchar(20) null,
	城市 nvarchar(20) null,
	地址 nvarchar(20) null,
	家庭电话 varchar(20) null
)
Go

Create Table 课程
(
	课程编号 int identity(1,1) not null,
	课程名称 nvarchar(20) not null,
	学分 tinyint null
)
Go

Create Table 专业
(
	专业编号 int identity(1,1) not null,
	专业名称 nvarchar(20) not null,
	必修课学分 tinyint null,
	选修课学分 tinyint null,
)
Go
/*
(1)为每一个表创建一个Primary Key约束，并将班级表的“班级编号”列创建为自动编号标识符列。
*/
/*Alter table 班级
Drop Column 班级编号*/

Alter table 班级
add 班级编号 int identity(1,1)

/*
Alter table 班级						分析：不能修改现有表的列来添加identity属性。P62
alter column 班级编号 int identity(1,1)	
*/

/*
(2)创建默认值。在创建表时设置“入学时间”字段的默认值为当前日期，“学分”列的默认值为3。
*/
alter table 学生
alter column 入学时间 datetime default(getdate())

alter table 课程
alter column 学分 int default(3)

/*(3)创建约束。“学分”字段的值必须大于0且小于5。“入学时间”列的值必须大于“出生日期”列的值。*/
alter table 课程
add constraint ck_学分
Check(学分>0 and 学分<5)

alter table 学生
add constraint ck_入学时间
Check(Datediff(day,Getdate(),出生日期)>=0)

/*
创建表之间的关系（外键约束）
*/
Alter Table 班级
Add 专业编号 int constraint [fk_班级(专业编号)] Foreign Key(专业编号)
References 专业(专业编号) On Delete Cascade

Alter Table 学生
Add 班级编号 int constraint [fk_学生(班级编号)] Foreign Key(班级编号)
References 班级(班级编号) On Delete Cascade

Create table 学生_课程
(
	学号 int not null Constraint [FK_学生_课程(学号)] Foreign Key(学号)
					  References 学生(学号) On Delete Cascade On Update Cascade,
	课程编号 int not null Constraint [FK_学生_课程(课程编号)] Foreign Key(课程编号)
					  References 课程(课程编号) On Delete Cascade On Update Cascade,
	成绩 tinyint null Constraint [CK_学生_课程(成绩)] Check(成绩>0 and 成绩 <100)
	Primary Key(学号,课程编号)
)
Go

Create table 专业_课程
(
	专业编号 int not null Constraint [Fk_专业_课程(专业编号)] Foreign Key(专业编号)
						References 专业(专业编号) On Delete Cascade On Update Cascade,
	课程编号 int not null Constraint [Fk_专业_课程(课程编号)] Foreign key(课程编号)
						References 课程(课程编号) On Delete Cascade On Update Cascade
	Primary Key(专业编号,课程编号)
)
Go

/*(2)创建视图CScoreAVG，计算每门课程的平均成绩及课程编号。*/
Create View CScoreAVG
As
Select AVG(学生_课程.课程成绩),[课程编号]
From [学生_课程]
Group by [课程编号]
Go