--使用master数据库
use master
go

--如果存在TestDB数据库删除数据库
if exists(select * from sys.databases where name='TestDB')
	drop database TestDB
go

--创建数据库
create database TestDB
go
--go批处理标志，分割sql文件；等待前面语句执行完毕再执行后面的sql语句

--使用TestDB数据库
use TestDB 
go


-----------------
--建表部分
-----------------

--创建班级表
create table ClassInfo
(
	ClassId int identity(1,1) primary key,  --主键，班级编号，标识列
	ClassName nvarchar(20) not null			--班级名称，非空
)
go

--创建学生信息表
create table StuInfo
(
	StuId int identity(1,1) primary key,	--主键，学号，标识列
	ClassId int references ClassInfo(ClassId) on delete set null, --所属班级编号，外键关联班级表的班级编号
	StuName nvarchar(10) not null,		--姓名，非空
	StuSex  nvarchar(1) default('男') check(StuSex in('男','女')),	--性别
	StuBrithday date,	--出生日期
	StuPhone nvarchar(11) check(len(StuPhone)=11) unique,--手机号，限制11位,唯一不重复
	StuProvince nvarchar(200),--地址
	CreateDate datetime default(getdate())	--创建时间，默认为系统时间
)
go

--创建课程信息表
create table CourseInfo
(
	CourseId int identity(1,1) primary key,	--课程编号，主键，标识列
	CourseName nvarchar(50) unique not null ,--课程名称，非空，唯一不重复
	CourseCredit int default(1) check(CourseCredit between 1 and 5) --学分，默认值为1，取值范围1-5
)
go

--创建成绩表
create table Scores
(
	ScoreId int identity(1,1) primary key,--成绩编号，主键，标识列
	StuId int references StuInfo(StuId),--学号，外键关联学生信息表的学号
	CourseId int references CourseInfo(CourseId),--课程编号，外键关联课程信息表的课程编号
	Score int default(0)	--成绩,默认为0
)
go



----------------
--插入数据部分
----------------
--插入班级信息表
insert into ClassInfo(ClassName)
values('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班')
go


--插入学生信息
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'刘正','男','2002-08-02','13245678121','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'黄贵','男','2003-07-02','13345678121','江西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'陈美','女','2002-07-22','13355678125','福建省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'江文','男','2001-07-02','13347678181','湖南省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'钟琪','女','2003-01-13','13345778129','安徽省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'曾小林','男','2003-05-15','13345378563','安徽省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'欧阳天天','女','2002-08-19','13347878121','湖北省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'李逍遥','男','2003-09-02','13345678557','广东省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘德华','男','2003-06-11','15345679557',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘翔','男','2003-07-09','18346679589',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'曾小贤','男','2003-07-02','18348979589',null)


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘','男','2002-07-02','18348979509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'陈天翔','男','2002-07-02','18348079509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘能','男','2002-08-02','13245678122','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟馗','男','2002-08-02','13245678123','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟吴艳','女','2002-08-02','13245678124','广西省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘欢','男','2002-07-02','13245678125',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'张庭','女','2002-07-02','13245678126',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹植','男','2002-08-02','13245678127','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹操','男','2002-08-02','13245678128','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'孙尚香','女','2002-08-02','13245678129','')
go



--插入课程信息
insert into CourseInfo(CourseName,CourseCredit) values('计算机基础',3)
insert into CourseInfo(CourseName,CourseCredit) values('HTML+CSS网页制作',5)
insert into CourseInfo(CourseName,CourseCredit) values('JAVA编程基础',5)
insert into CourseInfo(CourseName,CourseCredit) values('SQL Server数据库基础',4)
insert into CourseInfo(CourseName,CourseCredit) values('C#面向对象编程',5)
insert into CourseInfo(CourseName,CourseCredit) values('Winform桌面应用程序设计',5)
go

--插入成绩信息
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);
go




--1.查询学号为1的所有课程的总成绩
     select sum(Score) 一号所有课程总成绩 from Scores where StuId=1
	
--2.查询成绩表中所有成绩的总和
      select sum(Score) 所有成绩的总和总成绩 from Scores 
--3.查询学号为1的平均成绩
    select avg(Score) 平均成绩 from Scores where StuId=1
--4.查询课程编号为1的最高成绩信息

   select max(Score) 最高成绩 from Scores where CourseId=1
--5.查询课程编号为1的最低成绩信息

   select min(Score) 最低成绩 from Scores where CourseId=1
--6.查询学号为1的学生参加的考试次数
  select count(*) 参加考试次数 from Scores where StuId=1

--7.查询课程编号为1的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分

  select count(*)参加考试次数,sum(Score)总分,avg(Score)平均分,max(Score)最高分,min(Score)最低分 from Scores where CourseId=1
--8.查询学号为1的学生的成绩统计信息：总分、平均分、最高分、最低分

   select  sum(Score)最高分, avg(Score)平均分,max(Score)最高分,min(Score)最低分 from Scores where StuId=1

--9.查询每个学生的总成绩
  select StuId 学号,sum(Score) 总成绩 from Scores
  group by StuId

--10.查询每个学生的成绩统计信息：参考次数、总分、平均分、最高分
 select StuId 学号, COUNT(*)参考次数, sum(Score)总分,avg(Score)平均分,max(Score)最高分 from Scores
 group by StuId

--11.查询每门课程的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分
  select CourseId 课程编号, COUNT(*)参考次数, sum(Score)总分,avg(Score)平均分,max(Score)最高分  from Scores    
  group by CourseId
--12.查询学号1-5的学生的总成绩信息
select sum(Score) from Scores where StuId like'[1-5]'