create database Student
use Student
go
create table Class(
 ClassID int identity(1,1)primary key,
 ClassName nvarchar(20) unique not null
)
insert into Class(ClassName)
values('1班'),('2班'),('3班'),('4班'),('5班'),('6班'),('7班'),('8班'),('9班'),('10班')
update Class set ClassName='11班' where ClassID=1
delete from Class where ClassID=10
create table Student(
 StuID int identity(1,1) primary key,
 ClassID int references Class(ClassID),
 StuName nvarchar(20) not null,
 StuSex nvarchar(1) check(StuSex in('男','女')),
 StuBrithday date,
 StuPhone nvarchar(11) unique,
 StuAddress nvarchar(200),
 CreateDate datetime ,
)
go
insert into Student(StuName,StuSex,StuBrithday,StuPhone,StuAddress)
values('小李2','男','2050-1-2','1999559794','湖南'),
	('小李1','男','2001-1-2','1899599794','湖南'),
	('小李3','男','2800-1-2','1199599794','湖南'),
	('小李4','男','2900-1-2','1299599794','湖南'),
	('小李5','男','2100-1-2','1399599794','湖南'),
	('小李6','男','2200-1-2','1499599794','湖南'),
	('小李7','男','2300-1-2','1599599794','湖南'),
	('小李8','男','2600-1-2','1699599794','湖南'),
	('小李9','男','2800-1-2','1799599794','湖南'),
	('小李11','男','3000-1-2','1899599714','湖南'),
	('小李12','男','2900-1-2','1919599794','湖南'),
	('小李13','男','2800-1-2','1929599794','湖南'),
	('小李14','男','2050-1-2','1939599794','湖南'),
	('小李15','男','2010-1-2','1949599794','湖南'),
	('小李16','男','2000-2-2','1959599794','湖南'),
	('小李17','男','2000-3-2','1969599794','湖南'),
	('小李18','男','2000-4-2','1979599794','湖南'),
	('小李19','男','2000-5-2','1989599794','湖南'),
	('小李21','男','2000-6-2','1999519794','湖南'),
	('小李20','男','2000-8-2','1999529794','湖南')
 alter table Student
 add constraint DF_Student_CreateDate default( getdate())for CreateDate
 
 delete from Student where ClassID=ClassID
 go

 create table Course(
   CourseID int identity (1,1) primary key,
   CourseName nvarchar(50) unique not null,
   CourseCredit int not null default(1) check(CourseCredit>=1and CourseCredit<=5) , 
   CourseCredit1 nvarchar(10) check( CourseCredit1='专业课'or CourseCredit1='公共课')

 )
 insert into Course(CourseName,CourseCredit) values('计算机基础',3)
insert into Course(CourseName,CourseCredit) values('HTML+CSS网页制作',5)
insert into Course(CourseName,CourseCredit) values('JAVA编程基础',5)
insert into Course(CourseName,CourseCredit) values('SQL Server数据库基础',4)
insert into Course(CourseName,CourseCredit) values('C#面向对象编程',5)
insert into Course(CourseName,CourseCredit) values('Winform桌面应用程序设计',5)
 select*from Course
 update Course set CourseCredit='5' where CourseName='计算机基础'
  go


 create table Scores(
 ScoreID int identity(1,1)  primary key,
  StuId int,
  CourseId int,
  Score decimal(5,2) unique not null
 )
insert into Scores(StuId, CourseId, Score) values (1, 1, 80);
insert into Scores (StuId, CourseId, Score) values (1, 2, 78);
insert into Scores (StuId, CourseId, Score) values (1, 3, 65);
insert into Scores (StuId, CourseId, Score) values (1, 4, 90);

insert into Scores (StuId, CourseId, Score) values (2, 1, 60);
insert into Scores (StuId, CourseId, Score) values (2, 2, 77);
insert into Scores (StuId, CourseId, Score) values (2, 3, 68);
insert into Scores (StuId, CourseId, Score) values (2, 4, 88);

insert into Scores (StuId, CourseId, Score) values (3, 1, 88);
insert into Scores (StuId, CourseId, Score) values (3, 2, 45);
insert into Scores (StuId, CourseId, Score) values (3, 3, 66);
insert into Scores (StuId, CourseId, Score) values (3, 4, 75);

insert into Scores (StuId, CourseId, Score) values (4, 1, 56);
insert into Scores (StuId, CourseId, Score) values (4, 2, 80);
insert into Scores (StuId, CourseId, Score) values (4, 3, 75);
insert into Scores (StuId, CourseId, Score) values (4, 4, 66);
update Course set CourseCredit='3' where CourseName='计算机基础'
delete from Scores where StuId=1
delete from Scores where CourseId=1
alter table Scores
add constraint CK_Scores_Score check(Score>=0and Score<=100)
alter table Scores
add constraint CK_Scores_Score default(0)