create database School
use School
--创建班级信息表
create table Class
(
ClassID int identity(1,1) primary key,
ClassName nvarchar(50) unique not null
)
insert into Class(ClassName)
values('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班'),('软件8班'),('软件9班'),('软件10班')
select * from Class
update Class set ClassName='软件一班'where ClassID=1
delete Class where ClassID=1

--创建学生信息表
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='男'or StuSex='女') default('男'),
StuBirthday date,
StuPhone nvarchar(11),
StuAddress nvarchar(200) unique,
CreateDate datetime default (getdate())
) 
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone)
values(5,'刘1','男','2001.3.1',1131703355),
(6,'刘2','男','2001.3.2',1131603455),
(7,'刘3','男','2001.3.3',1131603555),
(7,'刘4','男','2001.3.4',1131603855),
(5,'刘5','男','2001.3.5',1131603755),
(2,'刘6','男','2001.3.6',1131605355),
(3,'刘7','男','2001.3.7',1131613355),
(4,'刘8','男','2001.3.8',1131633355),
(6,'刘9','男','2001.3.9',1131609355),
(8,'刘10','男','2001.3.10',1131612355),
(9,'刘11','男','2001.3.11',1131634355),
(2,'刘12','男','2001.3.12',1131563355),
(6,'刘13','男','2001.3.13',1131783355),
(7,'刘14','男','2001.3.14',1131903355),
(7,'刘15','男','2001.3.15',1131113355),
(5,'刘16','男','2001.3.16',1131903355),
(6,'刘17','男','2001.3.17',7131603355),
(5,'刘18','男','2001.3.18',9131603355),
(4,'刘19','男','2001.3.19',5131603355),
(5,'刘20','男','2001.3.20',3131603355)

delete Student where ClassID=2

--创建课程信息表
create table Course
(
Courseid int identity(1,1) primary key,
CourseName nvarchar(50) unique not null,
CourseCredit1 int default('1') check(CourseCredit1>=1and CourseCredit1<=5) not null,
CourseCredit nvarchar(10) check(CourseCredit='专业课'or CourseCredit='公共课' )
)
insert into
 Course(CourseName,CourseCredit1) values('计算机基础',3),
 ('HTML+CSS网页制作',5),
  ('JAVA编程基础',5),
 ('SQL Server数据库基础',4),
('C#面向对象编程',5),
('Winform桌面应用程序设计',5)
select * from Course
update Course set CourseCredit1='5' where Courseid=5 

--创建成绩表
create table Scores
(
Scoreid int identity(1,1) primary key,
StuId int references Student(StuID),
CourseId int references Course(Courseid),
Score decimal unique not null default('0') check(Score>=0 and Score<=100)
)
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);

insert into Scores (StuId, CourseId, Score) values (5, 1, 88);
insert into Scores (StuId, CourseId, Score) values (5, 2, 79);
insert into Scores (StuId, CourseId, Score) values (5, 3, 72);
insert into Scores (StuId, CourseId, Score) values (5, 4, 85);

insert into Scores (StuId, CourseId, Score) values (6, 1, 68);
insert into Scores (StuId, CourseId, Score) values (6, 2, 88);
insert into Scores (StuId, CourseId, Score) values (6, 3, 73);
insert into Scores (StuId, CourseId, Score) values (6, 5, 63);

insert into Scores (StuId, CourseId, Score) values (7, 1, 84);
insert into Scores (StuId, CourseId, Score) values (7, 2, 90);
insert into Scores (StuId, CourseId, Score) values (7, 3, 92);
insert into Scores (StuId, CourseId, Score) values (7, 5, 78);

insert into Scores (StuId, CourseId, Score) values (8, 1, 58);
insert into Scores (StuId, CourseId, Score) values (8, 2, 59);
insert into Scores (StuId, CourseId, Score) values (8, 3, 65);
insert into Scores (StuId, CourseId, Score) values (8, 5, 75);

insert into Scores (StuId, CourseId, Score) values (9, 1, 48);
insert into Scores (StuId, CourseId, Score) values (9, 2, 67);
insert into Scores (StuId, CourseId, Score) values (9, 3, 71);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
update Course set CourseCredit1='5' where CourseName=3
delete Scores where StuId=1
delete scores where CourseId=1