已知是以下4张表：
–1.学生表
Student(s_id, s_name, s_birth, s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id, c_name, t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id, t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id, c_id, s_score) –学生编号,课程编号,分数
首先，我们需要搞清楚这4张表的关联关系，这样有助于编写对应的SQL语句。下面是我做的关于4个表的关联图：
搞清表之间的关联关系后，开始创建数据库和表，我用的是navicat客户端。
1.创建表：
(1)学生表：
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT ' ',
`s_birth` VARCHAR(20) NOT NULL DEFAULT ' ',
`s_sex` VARCHAR(10) NOT NULL DEFAULT ' ',
PRIMARY KEY(`s_id`)
);
(2)成绩表：
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
(3)课程表：
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT ' ',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
(4)教师表：
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT ' ',
PRIMARY KEY(`t_id`)
);
2.插入数据
(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','郑竹','1989-07-01','女');
insert into Student values('08','王菊','1990-01-20','女');
(2)插入成绩表数据：
insert into Score values('01','01',80);
insert into Score values('01','02',90);
insert into Score values('01','03',99);
insert into Score values('02','01',70);
insert into Score values('02','02',60);
insert into Score values('02','03',80);
insert into Score values('03','01',80);
insert into Score values('03','02',80);
insert into Score values('03','03',80);
insert into Score values('04','01',50);
insert into Score values('04','02',30);
insert into Score values('04','03',20);
insert into Score values('05','01',76);
insert into Score values('05','02',87);
insert into Score values('06','01',31);
insert into Score values('06','03',34);
insert into Score values('07','02',89);
insert into Score values('07','03',98);
(3)插入课程表数据：
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');
(4)插入教师表数据：
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');
以上4张表已创建完成并插入数据，下面详细解析经典题型
****-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
理解题意：查询学生信息及01、02课程的成绩在'01'课程分数大于'02'课程条件下
分析思路：由于要查询学生信息和分数，那么需要用2个表，即"Student"和"Score"表，多表查询需要用到Join语句连接，要使用子查询查询各课程的成绩。
方法一：
#查询'01'课程的成绩
SELECT s_id, s_score as s1 From score where c_id='01';
#查询'02'课程的成绩
SELECT s_id, s_score as s2 From score where c_id='02';
（注：s1,s2 分别是'01'和'02'课程对应分数的别名，方便作比较）
#查询'01'课程分数>'02'课程分数的s_id,s1,s2
SELECT r1.s_id, s1,s2 From
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2
where r1.s_id=r2.s_id and s1>s2;
得到的结果集如下：
#使用JOIN连接"student"表,得到学生信息及课程成绩
SELECT * FROM Student s Right Join
(SELECT r1.s_id, s1,s2 From
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2
where r1.s_id=r2.s_id and s1>s2)R
On s.s_id=r.s_id;
最终结果应该是这样的：
方法二：
#使用INNER JOIN 连接子查询，将满足条件的s_id, s1,s2查询出来
SELECT r1.s_id, s1,s2 from
(SELECT s_id, s_score as s1 from score where c_id='01')r1
INNER JOIN
(SELECT s_id, s_score as s2 from score where c_id='02')r2
on r1.s_id=r2.s_id and s1>s2
#再使用INNER JOIN 连接Student表和上面的临时表R
SELECT s.* ,R.s1,R.s2 from student s
INNER JOIN
(SELECT r1.s_id, s1,s2 from
(SELECT s_id, s_score as s1 from score where c_id='01')r1
INNER JOIN
(SELECT s_id, s_score as s2 from score where c_id='02')r2
on r1.s_id=r2.s_id and s1>s2)R
on s.s_id=R.s_id;
得到如下结果：
总结：方法一和方法二都能得到正确的结果，相较于方法二，方法一逻辑更清晰，语句较短，比较推荐。
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
解题思路与上题一致。
只需将上题的查询语句稍加改变即可，如下：
SELECT * FROM Student s Right Join
(SELECT r1.s_id, s1,s2 From
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2
where r1.s_id=r2.s_id and s1<s2)R
On s.s_id=r.s_id;
结果集：
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
分析思路：要使用AVG(),那么就要用到group by，结果要有学生姓名，那么需要用的join连接student表
方法一：子查询
#查询平均成绩大于等于60的学生编码和平均成绩
SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60;
#把学生姓名加进来
SELECT s.s_id, s_name, r.avs from student s right join
(SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60)r
on s.s_id=r.s_id ;
方法二：不使用join语句
SELECT s.s_id, s_name, r.avs from student s ，
(SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60)r
where s.s_id=r.s_id ;
结果和上面一样。
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
分析思路：有成绩的且平均分小于60的学生和没有成绩的同学，分2部再连接在一起
#没成绩的学生编码，姓名和平均成绩（null）
SELECT s_id, s_name, null from student where s_id not in(SELECT DISTINCT s_id from score) ;
#查询平均成绩低于60分的学生编码，姓名和平均成绩
SELECT r.s_id, s_name, r.avs from student s,
(SELECT s_id, ROUND(avg(s_score),2) avs from score
GROUP BY s_id HAVING avs<60)r
where s.s_id=r.s_id;
用了ROUND函数，使平均成绩保留2位小数。
#最后使用UNION合并到一起
SELECT r.s_id, s_name, r.avs from student s, (SELECT s_id,ROUND(avg(s_score),2) avs from score GROUP BY s_id HAVING avs<60)r where s.s_id=r.s_id
UNION
SELECT s_id,s_name,null from student where s_id not in(SELECT DISTINCT s_id from score)
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
分析思路：要用到COUNT(),SUM(), group by 分组，student表和score表，join连接，注意是所有学生，也就是包括没成绩的，所以要以student表为基准表
SELECT r.s_id, s.s_name, r.count_course, r.total_score from student s
LEFT JOIN
(SELECT s_id, count(*) as count_course, sum(s_score) as total_score from score GROUP BY s_id)r on s.s_id=r.s_id;
还有另一种写法：
select s.s_id, s.s_name, count(sc.c_id) as count_course, sum(sc.s_score) as sum_score from student s
left join score sc on s.s_id=sc.s_id GROUP BY s.s_id, s.s_name;
-- 6、查询"李"姓老师的数量
分析思路:想到用LIKE，count()函数，teacher表
SELECT count(*) from teacher where t_name like "李%";
-- 7、查询学过"张三"老师授课的同学的信息
分析思路：4张表都要用到，需要从teacher先找到张三老师的编号，在course表中找到教的课程，在从score表找选修这门课程的学生编码，在连接student表查询出学生信息。
SELECT * from student where s_id in
(SELECT s_id from score where c_id=
(SELECT c_id from course where t_id=
(SELECT T_id from teacher where t_name='张三')))
这还真是一层一层的查（嵌套查询）
还有一种方法:（联合查询）
SELECT student.* from student where s_id in
(SELECT s_id from score sc,course c,teacher t
where sc.c_id=c.c_id
and c.t_id=t.t_id
and t_name='张三');
结果是一样的。
-- 8、查询没学过"张三"老师授课的同学的信息
分析思路：首先要知道张三老师教的哪门课程，然后逆向思维，先找到学过张三老师课程的学生，用not in找到没学过的学生信息
SELECT * from student where s_id not in
(SELECT s_id from score where c_id in
(SELECT c_id from course where t_id in
(SELECT t_id from teacher where t_name='张三')));
还有一种方法：
SELECT * from student where s_id not in
(SELECT s_id from score sc, course c, teacher t
where sc.c_id = c.c_id
and c.t_id=t.t_id
and t_name='张三')
相较第一种，第二种更简洁些。和7题相反，逻辑相似。
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
分析思路：分别查询学习2门课程的学生编号，再找到相同的，最后连接student表查询。
SELECT r.s_id,s_name,s_birth,s_sex from student
right join (SELECT s1.s_id from (SELECT s_id from score where c_id='01')s1
INNER JOIN (SELECT s_id from score where c_id='02')s2 on s1.s_id=s2.s_id)r
on student.s_id=r.s_id;
还有一种简洁的查询语句：
SELECT s.* from student s, score a, score b
where s.s_id = a.s_id and s.s_id = b.s_id and a.c_id='01' and b.c_id='02';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
分析思路：查询学生编号在学习01课程的学生编号但不在学习02课程的编号里。
SELECT s.* from student s where s.s_id in
(SELECT s_id from score where c_id='01')
and s.s_id not in (SELECT s_id from score where c_id='02');
以上10个题，其中1，3，4，8，9，10题很重要，也有些难度，需要搞清逻辑，当然方法不是唯一的，我相信还有其他解法的，学习sql一定要会做这几道题哦，大家相互参考，共同学习。
