SQL练习题

该练习题引用网站为《超经典SQL练习题，做完这些你的ＳＱＬ就过关了》
网址为：

https://blog.csdn.net/flycat296/article/details/63681089
原作者用的是Ms SQL Server 2008 ，我用的是也是MySQL,IDE是MySQL workbench

Step 1: Create tables，insert values

1. create table student

drop table student; #防止有重名的情况
create table `student`(
`StudentID` int,
`StudentName`  varchar(10),
`Birthday` datetime,
`StudentSex` varchar(10)
);

insert into `student`(`StudentID`,`StudentName`,`Birthday`,`StudentSex`) values
(01 , '赵雷' , '1990-01-01' , '男'),
(02 , '钱电' , '1990-12-21' , '男'),
(03 , '孙风' , '1990-05-20' , '男'),
(04 , '李云' , '1990-08-06' , '男'),
(05 , '周梅' , '1991-12-01' , '女'),
(06 , '吴兰' , '1992-03-01' , '女'),
(07 , '郑竹' , '1989-07-01' , '女'),
(08 , '王菊' , '1990-01-20' , '女');

# 由于只想展示birthday 的date 部分，现用modify将birthday的数据类型进行修改
alter table student modify birthday date;

2. create table course

create table `course`(
`CourseID` int,
`CourseName`  varchar(10),
`TeacherID` int
);

insert into `course`(`CourseID`,`CourseName`,`TeacherID`) values
(01 , '语文' , 02),
(02 , '数学' , 01),
(03 , '英语' , 03);

3、create table teacher

create table `teacher`(
`TeacherID` int,
`TeacherName` varchar(10)
);
insert into `teacher`(`TeacherID`,`TeacherName`) values
(01 , '张三'),
(02 , '李四'),
(03 , '王五');

4、create table score

create table `score`(
`StudentID` INT,
`CourseID` INT,
`Score` int
);
insert into`score`(`StudentID`,`CourseID`,`Score`) values
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

Step 2： The questions

1.1查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select a.StudentID, a.Score
from (select * from score where CourseID=01) as a #先选出课程1的分数
left join (select * from score where CourseID=02 ) as b #再选出课程b的分数
on a.StudentID=b.StudentID
where a.Score > b.Score; #进行比较