SQL面试经典50题（一）——1-5题解析

本来今天应该继续刷牛客网的，但是之前面试的时候用的就是SQL面试经典50题中的两道，我觉得既然这50道题这么受推崇，不如我就先把这50题搞定。

首先我们先导入数据：

create table Students (
s_id varchar(20) primary key,
s_name varchar(20) not null default '',
s_birth varchar(20) not null default '',
s_sex varchar(5) not null default '');
desc Students;
alter table Students rename Student;

create table Course (
c_id varchar(20) not null primary key,
c_name varchar(20) not null default '',
t_id varchar(20) not null);
desc Course;
alter table Courses rename Course;

create table Teacher (
t_id varchar(20) not null primary key,
t_name varchar(20) not null default '');

create table Score (
s_id varchar(20) not null,
c_id varchar(20) not null,
score int(5) not null,
primary key (s_id,c_id));
desc Score;

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' , '女');
select * from Student;

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 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);

这里我为了练习重命名列表，所以用了一些alter语句，实际上是不需要的。

接下来，今天的5道题目如下：

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

【题目1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数】

我记得大约一年前我第一次开始学SQL的时候做过这道题，当时自学了一周自认为掌握的不错，结果就是这道题直接劝退了，现在二番目觉得还算挺简单的。

这道题的思路是：

• 查询出课程01有成绩的学生的成绩
• 查询出课程02有成绩的学生的成绩
• 筛选出01课程成绩比02高的

代码：

select s.*, s1.score, s2.score
from Student s join score s1 on s.s_id=s1.s_id and s1.c_id = '01'
join score s2 on s.s_id=s2.s_id and s2.c_id='02'
where s1.score > s2.score;

【题目2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数】

这道题和第一题基本一样，只需把where语句的>换成<就可以了

代码：

select s.*, s1.score, s2.score
from Student s join score s1 on s.s_id=s1.s_id and s1.c_id = '01'
join score s2 on s.s_id=s2.s_id and s2.c_id='02'
where s1.score < s2.score;

【题目3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩】

不太理解题干里为啥要放那么多“和”，题目本身不难，重点就是要是用group by和having语句，关于having和where的区别，之前的文章已经提过两遍了，这里就不赘述了，思路如下：

• 用s_id字段建立student和score表连接，这里我使用join，当然本题使用left join不影响结果
• 按照学生编号s_id分组，使用group by语句
• 用having语句对平均分数大于60的学生数据做筛选（平均数用avg函数）

代码：

select s.s_id, s_name, avg(score)
from Student as s join Score as sc
on s.s_id=sc.s_id
group by s.s_id
having avg(score) >=60
order by avg(score) desc;

【题目4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)】

这道题的思路和上题一样，但是因为题干需要显示没成绩的学生信息，因此两表连接要是用left join，同时null值不参与<60的判断，所以having语句后要加上avg(score) is null

代码：

select s.s_id, s_name, avg(score)
from Student as s left join Score as sc
on s.s_id=sc.s_id
group by s.s_id
having avg(score) < 60 or avg(score) is null
order by avg(score) desc;

【题目5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩】

这道题就更简单了，只需要两表连接，并且根据s_id进行group by分组即可

代码：

select s.s_id, s_name, count(1) as count, sum(score) as sum
from Student s left join Score sc
on s.s_id = sc.s_id
group by s.s_id

 

以上，欢迎指正