数据分析mysql面试题_数据分析之数据库面试题练习

练习题中表的结构：

简单查询查询姓“猴”的学生名单

SELECT * FROM student WHERE `姓名` LIKE '猴%';

2. 查询姓名中最后一个字是“猴”的学生名单

SELECT * FROM student WHERE `姓名` LIKE '%猴';

3. 查询姓名带“猴”的学生名单

SELECT * FROM student WHERE `姓名` LIKE '%猴%';

汇总分析查询课程编号为“0002”的总成绩

SELECT SUM(`成绩`) AS 总成绩 FROM score WHERE `课程号`='0002';

2. 查询选了课程的学生人数

SELECT COUNT(DISTINCT(`学号`)) AS 选课的学生人数 FROM score;

分组练习查询各科成绩最高和最低的分

SELECT `课程号`, MAX(`成绩`) AS 最高分, MIN(`成绩`) AS 最低分 FROM score GROUP BY `课程号`;

2. 查询每门课程被选修的学生数

SELECT `课程号`, COUNT(DISTINCT(`学号`)) AS 选修的学生数 FROM score GROUP BY `课程号`;

3. 查询男生、女生人数

SELECT `性别`,COUNT(*) AS 人数 FROM student GROUP BY `性别`;

分组结果的条件查询平均成绩大于60分学生的学号和平均成绩

SELECT `学号`, AVG(`成绩`) AS 平均成绩 FROM score GROUP BY `学号` HAVING AVG(`成绩`)>60;

2. 查询至少选修两门课程的学生学号

SELECT `学号`, COUNT(*) AS 选修课程数 FROM score GROUP BY `学号` HAVING COUNT(*)>=2;

3. 查询同名同姓的学生名单并统计同名人数

SELECT `姓名`,COUNT(*) AS 同名人数 FROM student GROUP BY `姓名` HAVING COUNT(*)>1;

4. 查询不及格的课程并按课程号从大到小排序

SELECT DISTINCT(`课程号`) FROM score WHERE `成绩` <60 ORDER BY `课程号` DESC;

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

SELECT `课程号`,AVG(`成绩`) AS 平均成绩 FROM score GROUP BY `课程号` ORDER BY AVG(`成绩`) ASC, `课程号` DESC;

6. 检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列

SELECT `学号`,`成绩` FROM score WHERE `课程号` = '0004' AND `成绩` <60 ORDER BY `成绩` DESC;

7. 查询两门以上不及格课程的同学的学号及其平均成绩

SELECT `学号`,AVG(`成绩`) AS 平均成绩 FROM score WHERE `成绩`<60 GROUP BY `学号` HAVING COUNT(`课程号`)>2;

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

SELECT DISTINCT(`学号`),`姓名` FROM student WHERE `学号` in (SELECT `学号` FROM score WHERE `成绩` <60);

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

SELECT `学号`,`姓名` FROM student WHERE `学号` in (SELECT `学号` FROM score GROUP BY `学号` HAVING COUNT(`课程号`)= (SELECT COUNT(DISTINCT(`课程号`)) FROM score));

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

SELECT `学号`,`姓名` FROM student WHERE `学号` in (SELECT `学号` FROM score GROUP BY `学号` HAVING COUNT(`课程号`)=2);

4. 查找1990年出生的学生名单

SELECT * FROM student WHERE YEAR(`出生日期`)=1990;

Top N问题分组取每组最大值

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

SELECT * FROM score AS a WHERE `成绩` =(SELECT MAX(`成绩`) FROM score AS b WHERE a.`课程号`= b.`课程号`) ORDER BY `课程号`;

2. 分组取每组最小值

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

SELECT * FROM score AS a WHERE `成绩` =(SELECT MIN(`成绩`) FROM score AS b WHERE a.`课程号`= b.`课程号`) ORDER BY `课程号`;

3. 每组最大的N条记录

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

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

多表查询查询所有学生的学号、姓名、选课数和总成绩

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 RIGHT JOIN score AS b

ON a.`学号` = b.`学号`

GROUP BY a.`学号`

HAVING AVG(b.`成绩`)>85;

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

SELECT a.`学号`,a.`姓名`,b.`课程号`,c.`课程名称`

FROM student AS a LEFT JOIN score AS b

ON a.`学号`= b.`学号`

LEFT JOIN course AS c

ON b.`课程号`=c.`课程号`

ORDER BY a.`学号`;

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 `课程号`;

2. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

SELECT a.`课程号`, b.`课程名称`,

SUM(CASE WHEN a.成绩 BETWEEN 100 AND 85 THEN 1

ELSE 0

END) AS '[100-85]',

SUM(CASE WHEN a.成绩>70 THEN 1

ELSE 0

END) AS '[85-70]',

SUM(CASE WHEN a.成绩>60 THEN 1

ELSE 0

END) AS '[70-60]',

SUM(CASE WHEN a.成绩<60 THEN 1

ELSE 0

END) AS '[<60]'

FROM score AS a INNER JOIN course AS b

ON a.`课程号`=b.`课程号`

GROUP BY a.`课程号`,b.`课程名称`;

3. 将成绩表的行转列为下面的表结构

SELECT `学号`,

MAX(CASE WHEN `课程号`='0001' THEN `成绩`

ELSE 0

END) AS '课程号0001',

MAX(CASE WHEN `课程号`='0002' THEN `成绩`

ELSE 0

END) AS '课程号0002',

MAX(CASE WHEN `课程号`='0003' THEN `成绩`

ELSE 0

END) AS '课程号0003'

FROM score

GROUP BY `学号`;