create database ClassicDb
GO

use ClassicDb
GO

create table StudentInfo
(
    Id int PRIMARY key not null IDENTITY,
    StudentCode nvarchar(80),
    StudentName nvarchar(80),
    Birthday date not null,
    Sex nvarchar(2),
    ClassId int not null
)

GO

-- select * from StudentInfo

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('01' , '赵雷' , '1990-01-01' , 'm',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('02' , '钱电' , '1990-12-21' , 'm',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('03' , '孙风' , '1990-12-20' , 'm',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('04' , '李云' , '1990-12-06' , 'm',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('05' , '周梅' , '1991-12-01' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('06' , '吴兰' , '1992-01-01' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('07' , '郑竹' , '1989-01-01' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('09' , '张三' , '2017-12-20' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('10' , '李四' , '2017-12-25' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('11' , '李四' , '2012-06-06' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('12' , '赵六' , '2013-06-13' , 'f',1)
insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('13' , '孙七' , '2014-06-01' , 'f',1)


GO


CREATE TABLE Teachers
(
    Id int PRIMARY key not null IDENTITY,
    TeacherName nvarchar(80)
)

go
-- select * from Teachers

insert into Teachers (TeacherName) values('张三')
insert into Teachers (TeacherName) values('李四')
insert into Teachers (TeacherName) values('王五')

GO

create table CourseInfo
(
    Id int PRIMARY key not null IDENTITY,
    CourseName NVARCHAR(80) not null,
    TeacherId int not null
)

go
-- select * from CourseInfo

insert into CourseInfo (CourseName,TeacherId) values( '语文' , 2)
insert into CourseInfo (CourseName,TeacherId) values( '数学' , 1)
insert into CourseInfo (CourseName,TeacherId) values( '英语' , 3)

GO

create table StudentCourseScore
(
    Id int PRIMARY key not null IDENTITY,
    StudentId int not null,
    CourseId int not null,
    Score int not null
)
go
-- select * from StudentCourseScore

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 1 , 80)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 2 , 90)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 3 , 99)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 1 , 70)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 2 , 60)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 3 , 80)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 1 , 80)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 2 , 80)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 3 , 80)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 1 , 50)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 2 , 30)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 3 , 20)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='05') , 1 , 76)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='05') , 2 , 87)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='06') , 1 , 31)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='06') , 3 , 34)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='07') , 2 , 89)
insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='07') , 3 , 98)

go