SQL语句编写经典50题

参考链接：面试 SQL整理 常见的SQL面试题：经典50题

SQL数据库面试题以及答案（50例题）

-- 模糊查询：查询学生中名字带“猴”的名字
SELECT * FROM student WHERE 姓名 LIKE '%猴%'

-- 汇总统计分析========================================
-- 汇总统计分析：查询课程编号为“0002”的总成绩
SELECT SUM(成绩) FROM score WHERE 课程号='0002'

-- 汇总统计分析：查询选了课程的学生人数
SELECT COUNT(DISTINCT(学号)) AS 选课人数 FROM score

-- 分组查询=================================================
-- 分组：查询各科成绩最高和最低的分
SELECT 课程号,MAX(成绩) AS 最高,MIN(成绩) AS 最低 FROM score GROUP BY 课程号

-- 分组：查询男生、女生人数
SELECT 性别,COUNT(1) AS 人数 FROM student GROUP BY 性别

-- 分组结果的条件查询：查询平均成绩大于60分的学生的学号和平均成绩
SELECT 学号,AVG(成绩) 平均成绩 FROM score GROUP BY 学号 HAVING AVG(成绩)  > 60

-- 分组结果的条件查询：查询至少选修两门课程的学生学号
SELECT 学号,COUNT(1) AS 选修课程数目 FROM score GROUP BY 学号 HAVING COUNT(1)>=2

-- 分组结果的条件查询：查询不及格的课程并按照课程号从大到小排列
SELECT 课程号,成绩 FROM score WHERE 成绩 < 60 ORDER BY 课程号 DESC

-- 分组结果的条件查询：查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT 课程号,AVG(成绩) AS 平均成绩 FROM score GROUP BY 课程号 ORDER BY 平均成绩 ASC,课程号 DESC

-- 分组结果的条件查询：检索课程编号为0004且分数小于60的学生学号，结果按照分数降序排列
SELECT 学号,成绩 FROM score  WHERE 课程号='0004' AND 成绩<60 GROUP BY 学号 ORDER BY 成绩 DESC

-- 分组结果的条件查询：查询两门以上不及格的同学的学号及其成绩
SELECT 学号,课程号,成绩 FROM score WHERE 成绩<60 GROUP BY 学号 ORDER BY 学号 DESC,课程号 DESC

-- 查询所有同学的成绩
SELECT 学号,课程号,成绩 FROM score WHERE 成绩<60 ORDER BY 学号 DESC,成绩 DESC

-- 复杂查询=========================================================
-- 复杂查询：查询所有课程成绩小于60分的学生的学号，姓名
SELECT s.学号,姓名,成绩 FROM student s,score sc WHERE s.学号=sc.学号 AND sc.成绩<60

-- 复杂查询：查询没有选全部课程的学生的学号、姓名
SELECT 学号,姓名 FROM student WHERE 学号 IN (SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号) <(SELECT COUNT(1) FROM course)) 

-- 查询出1990年出生的学生名单
SELECT * FROM student WHERE YEAR(出生日期)=1990

-- topN问题：查询各科成绩前两名的记录

-- 按课程号分组去成绩最大值所在行的数据
SELECT 课程号,MAX(成绩) AS 最高分 FROM score GROUP BY 课程号

-- 按课程号分组取最小值所在行的数据
SELECT * FROM score a WHERE 成绩 IN (SELECT MIN(成绩) FROM score b WHERE a.课程号=b.`课程号`) ORDER BY 课程号

-- 查询各科成绩前两名的记录
(SELECT * FROM score WHERE 课程号='0001' ORDER BY 成绩 LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号='0002' ORDER BY 成绩 LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号='0003' ORDER BY 成绩 LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号='0004' ORDER BY 成绩 LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号='0005' ORDER BY 成绩 LIMIT 2)

-- 多表查询：常见面试题，分组取每组最大值、最小值的N条记录===========================================
-- 查询所有学生的学号、姓名、选择总数、总成绩
SELECT s.学号,姓名,COUNT(sc.课程号) AS 选课数,SUM(成绩) AS 总成绩 FROM student s LEFT JOIN score sc ON s.学号=sc.学号 GROUP BY a.学号

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student.学号,姓名,AVG(成绩) AS 平均成绩 FROM student LEFT JOIN score ON student.`学号`=score.`学号` GROUP BY student.学号 HAVING AVG(成绩)>80

-- 查询学生的选课情况：学号、姓名、课程号、课程名称
SELECT s.学号,s.姓名,sc.课程号,c.课程名称 FROM student s INNER JOIN score sc ON s.学号=sc.学号 INNER JOIN course c ON sc.课程号=c.课程号


-- 考察case表达式===========================================================
-- 查询出每门课程及格人数和不及格人数
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 课程号
		

-- 使用分段[100-85]、[85-70]、[70-60]、[<60]来统计各科成绩
SELECT sc.课程号,c.课程名称,
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 sc RIGHT JOIN course AS c 
ON sc.课程号=c.课程号
GROUP BY sc.课程号,c.课程名称;


-- 查询课程编号为0003且课程成绩在90分以上的学生的学号和姓名
SELECT s.学号,s.姓名,sc.`成绩` FROM student s INNER JOIN score sc ON s.`学号`=sc.`学号` WHERE 课程号='0003' AND 成绩>90 

--