经典数据库面试题

题目和答案来源：https://www.cnblogs.com/pythonxiaohu/p/5749864.html

3、查询平均成绩大于60分的同学的学号和平均成绩；
思路：根据学生分组，使用avg获取平均值，通过having对avg进行筛选
select student_id,avg(num) from score group by student_id having avg(num) > 60

4、查询所有同学的学号、姓名、选课数、总成绩；
select score.student_id,sum(score.num),count(score.student_id),student.sname
from score  left join student  on score.student_id = student.sid
group by score.student_id

5、查询姓“李”的老师的个数；
select count(tid) from teacher where tname like '李%'

6、查询没学过“叶平”老师课的同学的学号、姓名；
思路：先查到“李平老师”老师教的所有课ID
     获取选过课的所有学生ID
     学生表中筛选
    select * from student where sid not in (
        select DISTINCT student_id from score where score.course_id in (
            select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
        )
    )

10、查询有课程成绩小于60分的同学的学号、姓名；
    select sid,sname from student where sid in (
        select distinct student_id from score where num < 60
    )

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
    select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;

22、查询每门课程被选修的学生数；
    select course_id, count(1) from score group by course_id;

24、查询男生、女生的人数；
    select * from
    (select count(1) as man from student where gender='男') as A ,
    (select count(1) as feman from student where gender='女') as B

25、查询姓“张”的学生名单；
    select sname from student where sname like '张%';

26、查询同名同姓学生名单，并统计同名人数；
    select sname,count(1) as count from student group by snam

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
    select student.sname,score.num from score
    left join course on score.course_id = course.cid
    left join student on score.student_id = student.sid
    where score.num < 60 and course.cname = '生物'

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
    select * from score where score.student_id = 3 and score.num > 80

31、求选了课程的学生人数
    select count(distinct student_id) from score

36、检索至少选修两门课程的学生学号；
    select student_id from score group by student_id having count(student_id) > 1

40、检索“004”课程分数小于60，按分数降序排列的同学学号；
    select student_id from score where num< 60 and course_id = 4 order by num desc;

41、删除“002”同学的“001”课程的成绩；
    delete from score where course_id = 1 and student_id = 2