create database student
use student
create table class
(
classid int primary key identity,
classname nvarchar(20) unique not null
)
insert into class(classname)
values('软件一班'),('软件二班'),('软件三班'),('软件四班'),('软件五班'),
      ('软件六班'),('软件七班'),('软件八班'),('软件九班'),('软件十班')
update class set classname='天堂一班' where classid=1
select * from class
delete from class where classid=10
create table student 
(
stuid int primary key identity,
classsid int foreign key references class(classid),
stuname nvarchar(20) not null,
stusex nvarchar(1) default('男') check(stusex='男' or stusex='女'),
stubtday date,
stuphone nvarchar(11)unique,
stuaddress nvarchar(200),
createdate datetime default(getdate())
)
insert into student(stuid,classsid,stuname,stusex,stubtday,stuphone,stuaddress,)
values(1,'系内',2003-1-23,3189233122),(1,'系内',2003-1-23,3189238543),
      (1,'系内',2003-1-23,3189238654),(1,'系内',2003-1-23,3189238231),
	  (2,'系内',2003-1-23,3189238901),(1,'系内',2003-1-23,3189231321),
	  (1,'系内',2003-1-23,3189232312),(5,'系内',2003-1-23,3189213212),
	  (1,'系内',2003-1-23,3189243532),(1,'系内',2003-1-23,3189232312),
	  (1,'系内',2003-1-23,3189238789),(1,'系内',2003-1-23,3189223122)
delete from student where classid=5
create table course
(
courseld int primary key identity,
coursename nvarchar(50) unique not null,
coursecredit int not null default(1) check(coursecredit>=1 and coursecredit<=5),
coursecredit1 nvarchar(10) check(coursecredit1 in('专业课','公共课')),
)
insert into course (coursename)
values('语文课'),('英语课'),('数学课'),('思修课'),('咳咳咳'),('课课课')
select * from course
update course set coursecredit=coursecredit+1 where coursename='英语课' 
create table score
(
scoreld int primary key identity,
stuid int  references student(stuid),
courseld int  references course(courseld),
score decimal(5,2)unique not null,


)
insert into score(scoreld,stuid,courseld,score)
values(62),(48),(90),(87),(73.2),(72),(82),(73),(72),(32)
update score set score=94.5 where scoreld=1
delete from score where stuid=1
delete from score where courseld=1
alter table score
add constraint CK_score_scoreA check(score>=0 and score<=100)