sql面试题（一）

数据描述：3张表（学生表、课程表、分数表）

 创建表

---

创建学生表：包含 id 、name两个字段
 1 create table student(
id int unsigned primary key auto_increment,
name char(10) not null
);
insert into student(name) values('张三'),('李四');

---

创建课程表：包含 id 、课程名称两个字段

create table course(
id int unsigned primary key auto_increment,
name char(20) not null
);
insert into course(name) values('语文'),('数学');

---

创建分数表：包含sid 、cid 、score 三个字段
13 create table student_course(
sid int unsigned,
cid int unsigned,
score int unsigned not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id),
primary key(sid, cid)
);
insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

---

 

第一题：在student_course 表查询各科成绩最高的学生，结果列出学生id, 课程id，最高分数

知识点：在where子句中的嵌套子查询

问题迷惑点：容易想到按照课程id分组，用聚合函数max() 求最高成绩，但是无法返回非分组列 的信息（比如本题的  学生id）

　　　　　　从本题可以看出，where子句中的 相关子查询 可以起到分组查询的效果

select * 
from student_course as a
where score >= (select max(score) as max_score 
                        from student_course as b
                        where b.cid = a.cid);


-- 也可以用all(),只是比用聚合函数效率低一些

select * 
from student_course as a
where score >= (select score 
                         from student_course as b
                         where b.cid = a.cid);

补充知识点：嵌套子查询

　　（1）子查询：嵌套在其他查询之中的查询，被嵌套的查询叫主查询

　　（2）子查询可分为两类-- 1、相关子查询  2、 非相关子查询

　　　　　　* 非相关子查询： 子查询独立于主查询，仅执行一次，将查询结果传递给主查询，查询效率高

　　　　　　* 相关子查询：依赖于主查询，主查询执行一次，子查询也执行一次

　　（3）如果考察返回值的情况，子查询一般分为  --1、返回一个单值的子查询  2、返回一个列表的子查询

 　　（4） 相关子查询多是出现在 主查询的where子句中，执行过程如下：

　　　　　　1、从外层查询中取出一个元组，将元组相关列的值传给内层查询（注：表的一行 称为一个  元组）

　　　　　　2、执行内层查询，得到子查询操作的值

　　　　　　3、外查询根据子查询得到的结果或结果集得到满足条件的行

　　　　　　4、然后外层查询取出下一个元组重复做1-3步骤，直到外层元组全部处理完毕。

 

 

第二题：在student_course 表中查询课程 1 成绩第二高的学生，如果第二高的 学生不止一个，列出所有学生

知识点：limit [offset][size]   : 第一个参数是偏移量（第一行 偏移量为 0），第二个参数是返回的最大数量

　　　　limit 后面只有一个数字n时，代表返回前n条数据

select *
from student_course 
where cid = 1 and score = ( select  score 
　　　　　　　　　　　　 from student_course
　　　　　　　　　　　　 where cid =1 
　　　　　　　　　　　　 group by score
　　　　　　　　　　       order by score desc
　　　　　　　　　　　　 limit 1,1);
　　　　　　　　　　　　　　

 

 

第三题：在student_course 表中列出平均分不及格（小于60）的学生，列出id 和 平均分

知识点：group by  用having过滤分组，可以使用聚合函数作为过滤条件

select sid ,avg(score) as avg_score
from student_course 
group by sid
having avg_score < 60

 

第四题：在student_course表中查询每门课成绩都不低于80的学生id

知识点：方向思考，将问题转化为其等价问题

select distinct sid
from student_course
where sid not in (
select sid from student_course
where score < 80);

 

第五题：查询每个学生的总成绩，结果列出学生姓名和总成绩

select s.name, sum(sc.score) as sum_score
from student  as s left join student_course as sc
on s.id = sc.sid
group by s.id