1 Star 0 Fork 29

老胡来也 / SQL经典50题

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
初始化数据.sql 4.75 KB
一键复制 编辑 原始数据 按行查看 历史
老胡来也 提交于 2020-05-27 09:08 . 初始
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
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
SQL
1
https://gitee.com/myhfw003/sqljingdian50ti.git
git@gitee.com:myhfw003/sqljingdian50ti.git
myhfw003
sqljingdian50ti
SQL经典50题
master

搜索帮助

344bd9b3 5694891 D2dac590 5694891