（二）Mysql面试笔试练习题

最常用聚合函数：
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和
6、查询平均成绩大于60分的同学的学号和平均成绩
SELECT s.student_id,AVG(s.score) FROM score s GROUP BY s.student_id HAVING AVG(s.score)>60;
说明：HAVING 经常跟 GROUP BY 分组过滤一起用

7、查询每个同学的最高分是多少
SELECT s.student_id,SUM(s.score) FROM score s GROUP BY s.student_id

8、查询各个课程的名称及相应的选修人数
SELECT c.course_id,COUNT()FROM course c,score s WHERE c.course_id=s.course_id GROUP BY s.course_id
扩展题：
9、查询选修人数大于４的各个课程的名称及相应的选修人数
SELECT c.course_id,COUNT()FROM course c,score s WHERE c.course_id=s.course_id GROUP BY s.course_id HAVING COUNT(*)>4

10、查询所有同学的学号、姓名、选课数、总成绩；
SELECT s.student_id,s.student_name,COUNT(sc.student_id),SUM(sc.score) FROM student s LEFT JOIN score sc ON s.student_id =sc.student_id GROUP BY s.student_id

11、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
技巧：
（1）先查出"c001"课程所有学生的学号，成绩
SELECT student_id,course_id,score FROM score WHERE course_id = ‘c001’
（2）先查出"c002"课程所有学生的学号，成绩
SELECT student_id,course_id,score FROM score WHERE course_id = ‘c002’
（3）然后在合在一起，最终结果：
SELECT a.student_id,a.score,a.course_id,b.course_id,b.score FROM (
SELECT student_id,course_id,score FROM score WHERE course_id = ‘c001’
)a,
(
SELECT student_id,course_id,score FROM score WHERE course_id = ‘c002’
)b
WHERE a.student_id = b.student_id AND a.score > b.score

12、查询没学过“teacher001”老师课的同学的学号、姓名；
技巧：
（1）先查出学过"teacher001"老师课的同学的学号、姓名
SELECT * FROM course c,score s,teacher t WHERE c.teacher_id= t.teacher_id AND s.course_id=s.course_id AND t.teacher_name=‘teacher001’
（2）然后在用not in 就是没学过的
SELECT t.* FROM student t WHERE t.student_id NOT IN
(SELECT DISTINCT(s.student_id) FROM course c,score s,teacher t WHERE c.teacher_id= t.teacher_id AND s.course_id=s.course_id AND t.teacher_name=‘teacher001’)

13、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

SELECT s.student_id,s.student_name FROM student s,course c,score sc WHERE s.student_id=sc.student_id AND sc.course_id =c.course_id AND c.course_id=
( SELECT sc2.student_id FROM score AS sc2 WHERE sc2.student_id =s.student_id AND sc2.course_id =‘c002’ )

用in的方式：
SELECT s.student_id,s.student_name FROM student s ,score sc WHERE s.student_id= sc.student_idAND sc.course_id = ‘c001’ AND s.student_id IN
(
SELECT sc2.student_id FROM score AS sc2 WHERE sc2.student_id = s.student_id AND sc2.course_id= ‘c002’
)
14、查询“c001”课程比“c002”课程成绩高的所有学生的学号、姓名；
SELECT
sc3.student_id,sc3.student_name
FROM
(SELECT
s.student_id,
s.student_name,
sc.score,
(SELECT sc2.score FROM score sc2 WHERE sc2.student_id = s.student_id AND sc2.course_id = ‘c002’) score2
FROM student s,score sc WHERE s.student_id= sc.student_id AND sc.course_id = ‘c001’) sc3
WHERE sc3.score > sc3.score2

15、查询所有课程成绩小于60分的同学的学号、姓名
SELECT student_id,student_name FROM student WHERE student_id NOT IN (
SELECT s.student_id FROM student s,score c WHERE s.student_id = c.student_id AND c.score >60
)

16、查询至少有一门课与学号为“s001”的同学所学相同課程的同学的学号和姓名；
SELECT DISTINCT s.student_id,s.student_name FROM student s,score sc WHERE s.student_id =sc.student_id AND sc.course_id in
(SELECT sc.course_id FROM score sc WHERE sc.student_id =‘s001’)

17、查询每门课程被选修的学生数
SELECT c.course_id,COUNT(*) FROM course c GROUP BY c.course_id;