查询01课程比02课程成绩高的学生信息及其分数

# 学生表
create table Student(
SId varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);
# 教师表
create table Teacher(
TId varchar(10),
Tname varchar(10)
);
# 科目表
create table Course(
CID varchar(10),
Cname nvarchar(10),
TId varchar(10)
);
# 成绩表
create table SC(
SId varchar(10),
CID varchar(10),
score decimal(18,1)
);
#学生表
insert into Student values('01','赵雷','1990-01-01','男');
insert into Student values('02','钱电','1990-12-21','男');
insert into Student values('03','孙风','1990-05-20','男');
insert into Student values('04' ,'李云','1990-08-06','男');
insert into Student values('05','周梅','1991-12-01','女');
insert into Student values('06','吴兰','1992-03-01','女');
insert into Student values('07','郑竹','1999-07-01','女');
insert into Student values('09','张三','2017-12-20','女');
insert into Student values('10','李四','2017-12-25','女');
insert into Student values('11','李四','2017-12-30','女');
insert into Student values('12','赵六','2017-01-01','女');
insert into Student values('13','孙七','2018-01-01','女');
#科目表Course
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');
#教师表
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');
#成绩表
insert into SC(CId,score,SId) values('01',110.5,'01');
insert into SC(CId,score,SId) values('02',33.5,'01');
insert into SC(CId,score,SId) values('03',90.2,'01');
insert into SC(CId,score,SId) values('01',79,'02');
insert into SC(CId,score,SId) values('02',19,'02');
insert into SC(CId,score,SId) values('03',59,'02');
insert into SC(CId,score,SId) values('01',120,'03');
insert into SC(CId,score,SId) values('02',110,'03');
insert into SC(CId,score,SId) values('03',100,'03');

select *
from Student 
inner join sc sc1 
on student.sid = sc1.sid   // student 和 sc表进行第一次连接 第1次连接后可以在一行记录中出现学生信息和课程信息
inner join sc sc2
on student.sid = sc2.sid    // 连接结果 和 sc表进行第二次连接，第2次连接后一行记录中可以出现学生信息，课程1信息，课程2信息
where sc1.cid = '01' and sc2.cid='02' and sc1.score > sc2.score;  // 筛选结果，把一条记录中同时出现课程1和课程2的记录过滤出来，并选出课程1分数高于课程2的

select student.*
from Student
inner join sc sc1
on student.sid = sc1.sid
inner join sc sc2
on student.sid = sc2.sid
where sc1.cid = '01' and sc2.cid = '02';// 过滤出其中一门课程等于01，另一门课程等于02的课程

select *
from student 
inner join sc
on student.sid = sc.sid;