SQL学习之旅-SQL求职面试题

题目所用数据表

一、简单查询

1、考查知识点

1) SQL基本书写规则

①SQL语句要以英文分号（;）结尾；

②SQL语句不区分关键字的大小写；

③输入符号的时候只能使用英文状态下的。

2) SQL书写和运行顺序

①SQL书写顺序：→FROM→WHERE→GROUP BY→→ORDER BY→LIMIT；

②SQL运算顺序：FROM→WHERE→GROUP BY→→→ORDER BY→LIMIT。

3) SQL查询条件的指定：运算符和通配符的使用

2、面试练习题

1) 查询1990-01-01前出生学生的姓名和出生日期

2) 查询学生猴子和马云的个人信息

3) 查询姓名中带“猴”的学生信息

4) 查询姓名是3个字的学生信息

二、汇总分析

1、考察知识点

1) 常用汇总函数

2) 分组查询-group by、子句，结合汇总函数使用，如查询出男学生的人数

性别,count(*) as ‘学生人数’

From

Group by 性别

count(*) >1;

3) 查询排序-order by子句【升序（asc，默认）-从小到大；降序（desc）-从大到小】，可用limit关键字取出排序后指定行的数据

2、面试练习题

1) 查询各科成绩最高和最低分

2) 查询男、女生人数

3) 查询至少选修两门课程的学生学号和选修课程数

4) 查询同名同姓学生名单和同名人数

5) 查询每门课程的平均成绩，结果按平均成绩升序排序。平均成绩相同时，按课程号降序排列

6) 查询课程编号为“0003”且分数大于60的学生学号，结果按分数降序排列

7) 统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

三、复杂查询

1、考察知识点

1) 标量子查询-指子查询的返回结果必须而且只能是单一（一行一列）的值，可以和比较运算符（in，any，all，等）使用实现复杂的查询。

2) 关联子查询-用来解决在每个组里进行比较的问题，关联子查询首先依靠子查询中的关联语句，从主查询中选取关联属性的第一个值，进入子查询中，然后将子查询结果返回主查询，判断where条件，依次类推，直到主查询中关联属性的值选取完毕。

3) 常用算术、字符串、日期函数

2、面试练习题

1) 查询所有课程成绩大于60分学生的学号、姓名

2) 查询没有学全所有课的学生的学号、姓名

3) 查询只选修了两门课程的全部学生的学号、姓名

4) 查询1990年出生的学生名单

5) 查询各科成绩前两名的记录

6) 查询各学生的年龄（精确到月份）

7) 查询本月过生日的学生

四、多表查询

1、考察知识点

1) 常用联结-内联结、左联结、右联结、全联结

2) 多条件判断-case表达式

Case when then

when then

when then

...

Else

end

2、面试练习题

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

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

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

4) 查询出每门课程的及格人数和不及格人数

5) 使用分段[100-85],[85-70],[70-60],[

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

7) 查询“0001”课程分数小于60的学生信息，按分数降序排列

8) 查询各老师所教课程，按平均分降序排列

9) 查询数学分数低于60的学生姓名和分数

10) 查询两门及其以上不及格课程的学生的学号，姓名及其平均成绩

11) 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

12) 查询课程编号为“0001”的课程比“0002”的课程成绩低的所有学生的学号

13) 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

14) 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名

15) 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩

16) 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

17) 查询所有学生的所有课程的成绩以及平均成绩，按平均成绩降序排列

五、如何提高SQL查询的效率

1、应尽量避免在子句中使用*，*是选择表中全部字段的意思，在实际工作中数据很多，用*会耗费时间等资源，所以建议查询时明确提取需要的字段名称。

2、应尽量避免Where子句比较符号左侧使用函数或者对字段进行null值判断，否则会导致数据库全表扫描，函数问题建议放到比较符号的右侧，如where成绩+5 > 90可优化为where 成绩 > 90-5,null值问题建议设置默认值为0，使用where num = 0。

3、应尽量避免使用in和not in，否则导致数据库全表搜索，增加运行时间，建议可用、或者联结替换。

4、应尽量避免使用or来连接条件，否则将导致数据库放弃使用索引而进行全表扫描，建议使用多条简单查询语句再使用union关键字连接。

5、使用limit子句限制返回的数据行数，数据库表中数据很多，若实际只需要显示前十的数据，建议使用limit子句限制返回的数据行数提高SQL查询效率。

六、解题思路

1、步骤：

1) 结合数据库表字段翻译成最基础的问题

2) 写出分析思路

3) 写出对应的SQL子句

2、举例：查询两门及其以上大于等于80课程的学生的学号，姓名及其平均成绩，并按平均成绩从大到小排列

1) 翻译成最基础的问题：查找学生的学号，姓名和平均成绩，筛选条件是两门及其以上不及格的学生，并按平均成绩降序排列【考察知识点：表联结，分组，排序，比较运算符，汇总函数】

2) 分析思路：

查询结果【学号，姓名，平均成绩：avg(成绩)】

From从哪张表中查找数据【学号和姓名在学生表中，平均成绩从成绩表中计算得到，所以需要联结两张表，用内联结方式通过学号相等联结】

Where查询条件【成绩不及格，即成绩>=80】

Group by分组【需按学生统计课程数，所以按学号分组】

对分组结果指定条件【学生的课程是两门及以上】

Order by对查询结果排序【按平均成绩降序排列】

3) 写出对应的SQL子句

a.学号,a.姓名,AVG(b.成绩) AS 平均成绩

FROM a INNER JOIN score b ON a.学号 = b.学号

WHERE b.成绩 >= 80

GROUP BY b.学号

COUNT(b.课程号) >= 2

ORDER BY AVG(b.成绩) DESC;

七、答案

（一）简单查询

-- 1、查询1990-01-01前出生学生的姓名和出生日期
Select 姓名,出生日期
From student
Where 出生日期 < ‘1990-01-01’;
-- 2、查询学生猴子和马云的个人信息
Select *
From student
Where 姓名= ‘猴子’ or 姓名= ‘马云’;
-- 3、查询姓名中带“猴”的学生信息
Select *
From student
Where 姓名 like ‘%猴%’;
-- 4、查询姓名是3个字的学生信息
Select *
From student
Where 姓名 like ‘___’;

（二）汇总分析

-- 1、查询男、女生人数
SELECT 性别,COUNT(学号) AS 学生人数
FROM student
GROUP BY 性别;
-- 2、查询各科成绩最高和最低分
SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分
FROM score
GROUP BY 课程号;
-- 3、查询同名同姓学生名单和同名人数
SELECT 姓名,COUNT(姓名) AS 人数
FROM student
GROUP BY 姓名
HAVING COUNT(姓名) >=2;
-- 4、查询至少选修两门课程的学生学号和选修课程数
SELECT 学号,COUNT(课程号) AS 选修课程数
FROM score
GROUP BY 学号
HAVING COUNT(课程号) >=2;
-- 5、查询每门课程的平均成绩，结果按平均成绩升序排序。平均成绩相同时，按课程号降序排列
SELECT 课程号,AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号
ORDER BY 平均成绩 ASC,课程号 DESC;
-- 6、查询课程编号为“0003”且分数大于60的学生学号，结果按分数降序排列
SELECT 学号,成绩
FROM score
WHERE 课程号 = '0003' AND 成绩 >60
ORDER BY 成绩 DESC;
-- 7、统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT 课程号,COUNT(学号) AS 选修人数
FROM score
GROUP BY 课程号
HAVING COUNT(学号) >2
ORDER BY 选修人数 DESC,课程号 ASC;

（三）复杂查询

-- 1、查询所有课程成绩大于60分学生的学号、姓名
SELECT 学号,姓名
FROM student
WHERE 学号 in (SELECT 学号 FROM score WHERE 成绩 > 60 GROUP BY 学号);
-- 2、查询没有学全所有课的学生的学号、姓名
SELECT 学号,姓名
FROM student
WHERE 学号 in (SELECT 学号 FROM score GROUP BY 学号 
HAVING COUNT(课程号) < (SELECT COUNT(课程号) FROM course));
-- 3、查询只选修了两门课程的全部学生的学号、姓名
SELECT 学号,姓名
FROM student
WHERE 学号 in (SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号) = 2);
-- 4、查询1990年出生的学生名单
SELECT * 
FROM student
WHERE YEAR(出生日期) = 1990;
-- 5、查询各科成绩前两名的记录
(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);
-- 6、查询各学生的年龄（精确到月份）
SELECT 学号,姓名,DATEDIFF(NOW(),出生日期)/365 AS 学生年龄
FROM student;
-- 7、查询本月过生日的学生
SELECT *
FROM student
WHERE month(出生日期) = month(NOW());

（四）多表查询

-- 1、查询所有学生的学号、姓名、选课数、总成绩
SELECT a.学号,a.姓名,COUNT(b.课程号) AS 选课数,
SUM(b.成绩) AS 总成绩
FROM student AS a LEFT JOIN score AS b 
ON a.学号 = b.学号
GROUP BY a.学号;
-- 2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT a.学号,a.姓名,AVG(b.成绩) AS 平均成绩
FROM student AS a LEFT JOIN score AS b
ON a.学号 = b.学号
GROUP BY 学号

HAVING AVG(b.成绩) > 85;
-- 3、查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT a.学号,a.姓名,c.课程号,c.课程名称
FROM student AS a LEFT JOIN score AS b
ON a.学号 = b.学号
INNER JOIN course c ON b.课程号 = c.课程号;
-- 4、查询出每门课程的及格人数和不及格人数
SELECT 课程号,
SUM((CASE WHEN 成绩 >=60 THEN 1 ELSE 0 END)) AS 及格人数,
SUM((CASE WHEN 成绩 <60 THEN 1 ELSE 0 END)) AS 不及格人数
FROM score
GROUP BY 课程号;
  
-- 5、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
SELECT a.课程号,b.课程名称,
SUM((CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END)) AS '[100-85]',
SUM((CASE WHEN 成绩 >= 70 AND 成绩 < 85 THEN 1 ELSE 0 END)) AS '[85-70]',
SUM((CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 ELSE 0 END)) AS '[70-60]',
SUM((CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END)) AS '[<60]'
FROM score AS a RIGHT JOIN course AS b
ON a.课程号 = b.课程号
GROUP BY a.课程号,b.课程名称;
-- 6、查询课程编号为0003且成绩在80分以上的学生的学号和姓名
SELECT a.学号,a.姓名
FROM student a INNER JOIN score b ON a.学号 = b.学号
WHERE b.课程号 = '0003' AND b.成绩 > 80;
-- 7、查询“0001”课程分数小于60的学生信息，按分数降序排列
SELECT a.*,b.成绩
FROM student a INNER JOIN score b ON a.学号 = b.学号
WHERE b.课程号 = '0001' AND b.成绩 <60
ORDER BY b.成绩 DESC;
-- 8、查询各老师所教课程，按平均分降序排列
SELECT a.教师号,a.教师姓名,b.课程号,b.课程名称,AVG(c.成绩) AS 平均分
FROM teacher a INNER JOIN course b ON a.教师号 = b.教师号
INNER JOIN score c ON c.课程号 = b.课程号
GROUP BY a.教师姓名
ORDER BY 平均分 DESC;
-- 9、查询数学分数低于60的学生姓名和分数
SELECT a.姓名,b.成绩
FROM student a INNER JOIN score b ON a.学号 = b.学号
INNER JOIN course c ON c.课程号 = b.课程号
WHERE c.课程名称 = '数学' AND b.成绩 <60;
-- 10、查询两门及其以上不及格课程的学生的学号，姓名及其平均成绩
SELECT a.学号,a.姓名,AVG(b.成绩) AS 平均成绩
FROM student a INNER JOIN score b ON a.学号 = b.学号
WHERE b.成绩 < 80
GROUP BY b.学号
HAVING COUNT(b.课程号) >= 2;

-- 11、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT a.学号,a.课程号,a.成绩
FROM score a INNER JOIN score b ON a.学号 = b.学号
WHERE a.成绩 = b.成绩 AND a.课程号 <> b.课程号;
-- 12、查询课程编号为“0001”的课程比“0002”的课程成绩低的所有学生的学号
SELECT a.学号
FROM
		(SELECT 学号,成绩 FROM score WHERE 课程号 = '0001') a
INNER JOIN
		(SELECT 学号,成绩 FROM score WHERE 课程号 = '0002') b
ON a.学号 = b.学号
WHERE a.成绩 < b.成绩;
-- 13、查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
SELECT a.学号,c.姓名
FROM 
(SELECT 学号,课程号 FROM score WHERE 课程号 = '0001') a 
INNER JOIN 
(SELECT 学号,课程号 FROM score WHERE 课程号 = '0002') b 
ON a.学号 = b.学号
INNER JOIN student c ON c.学号 = a.学号
WHERE a.学号 = b.学号;
-- 14、查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
SELECT a.学号,a.姓名
FROM student a INNER JOIN score b ON a.学号 = b.学号
INNER JOIN course c ON c.课程号 = b.课程号
INNER JOIN teacher d ON d.教师号 = c.教师号
WHERE d.教师姓名 = '孟扎扎';
-- 15、查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩
SELECT a.姓名,b.成绩
FROM student a INNER JOIN score b ON a.学号 = b.学号
INNER JOIN course c ON c.课程号 = b.课程号
INNER JOIN teacher d ON d.教师号 = c.教师号
WHERE d.教师姓名 = '孟扎扎'
ORDER BY b.成绩 DESC
LIMIT 1;
-- 16、查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
SELECT a.学号,a.姓名
FROM student a INNER JOIN score b ON a.学号 = b.学号
WHERE b.课程号 in (SELECT DISTINCT 课程号 FROM score WHERE 学号 = '0001')
AND a.学号 <> '0001'
GROUP BY a.学号;
-- 17、查询所有学生的所有课程的成绩以及平均成绩，按平均成绩降序排列
SELECT a.学号,
MAX(CASE WHEN b.课程名称 = '语文' THEN a.成绩 ELSE NULL END) '语文',
MAX(CASE WHEN b.课程名称 = '数学' THEN a.成绩 ELSE NULL END) '数学',
MAX(CASE WHEN b.课程名称 = '英语' THEN a.成绩 ELSE NULL END) '英语',
AVG(a.成绩) AS 平均成绩
FROM score a INNER JOIN course b ON a.课程号 = b.课程号
GROUP BY a.学号;