SQL多表查询面试题

001经典SQL笔试题题干

学生表：student(学号sno,学生姓名sname,出生年月sbirth,性别ssex)
成绩表：score(学号sno,课程号cno,成绩score)
课程表：course(课程号cno,课程名称cname,教师号ctno)
教师表：teacher(教师号tno,教师姓名tname)
注意：下面SQL的实现以MySQL为主

002查询所有课程成绩小于60分学生的学号、姓名。

/*
第1步，写子查询（所有课程成绩 < 60 的学生）*/
select 学号  from score where 成绩 < 60;
/*第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号*/
select 学号,姓名 from student where  学号 in ( select 学号 
from score where 成绩 < 60);

003查询没有学全所有课的学生的学号、姓名。

/*
查找出学号，条件：没有学全所有课，也就是该学生选修的课程数 < 总的课程数
【考察知识点】in，子查询
*/
select 学号,姓名from student where 学号 in( select 学号 from score
group by 学号 having count(课程号) < (select count(课程号) from course));

004查询出只选修了两门课程的全部学生的学号和姓名。

select 学号,姓名from student where 学号 in( select 学号 from score
group by 学号having count(课程号)=2);

005按课程号分组取成绩最大值所在行的数据。

/*我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。
但是无法得到成绩最大值所在行的数据。*/
select 课程号,max(成绩) as 最大成绩 from score group by 课程号;
/*我们可以使用关联子查询来实现：*/
select * from score as a where 成绩 = (select max(成绩) 
from score as b where b.课程号 = a.课程号);
/*上面查询结果课程号“0001”有2行数据，是因为最大成绩80有2个
分组取每组最小值：按课程号分组取成绩最小值所在行的数据*/
select * from score as a where 成绩 = (select min(成绩) 
from score as b where b.课程号 = a.课程号);

006查询各科成绩前两名的记录。

/*第1步，查出有哪些组，我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号*/
select 课程号,max(成绩) as 最大成绩from score group by 课程号;
/*第2步：先使用order by子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名*/
select * from score where 课程号 = '0001' order by 成绩  desc limit 2;
/*第3步，使用union all 将每组选出的数据合并到一起.同样的，可以写出其他组的（其他课程号）取出成绩前2名的sql*/
(select * from score where 课程号 = '0001' order by 成绩  desc limit 2) union all
(select * from score where 课程号 = '0002' order by 成绩  desc limit 2) union all
(select * from score where 课程号 = '0003' order by 成绩  desc limit 2);

007查询所有学生的学号、姓名、选课数、总成绩。

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b on a.学号 = b.学号group by a.学号;

008查询平均成绩大于85的所有学生的学号、姓名和平均成绩。

select a.学号,a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号group by a.学号having avg(b.成绩)>85;

009查询学生的选课情况：学号，姓名，课程号，课程名称。

select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

010查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名。

select a.学号,a.姓名
from student  as a inner join score as b on a.学号=b.学号
where b.课程号='0003' and b.成绩>80;