SQL练习题

参考：https://zhuanlan.zhihu.com/p/38354000
再次感谢作者的整理！！

常见的SQL面试题：经典50道

已知有如下4张表：
学生表：student（学号，学生姓名，出生年月，性别）
成绩表：score（学号，课程号，成绩）
课程表：course（课程号，课程名称，教师号）
教师表：teacher（教师号，教师姓名）

• 课程表：course
  

• 成绩表：score
  

• 学生表：student
  

• 教师表：teacher
  
  几张表之间的关系：
  

向表中添加数据练习：

insert into student(学生,姓名,出生日期,性别)
values('0001','猴子','1989-01-01','男');

1. 简单查询

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

   select * 
   from student 
   where 姓名 like '猴%';
   

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

   select * 
   from student 
   where 姓名 like '%猴';
   

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

   select * 
   from student 
   where 姓名 like '%猴%';
   

4. 查询姓“孟”的老师的个数

   select count(教师号) 
   from teacher 
   where 教师姓名 like '孟&';
   

2. 汇总分析

1. 查询课程编号为“0002”总成绩

   select sum(成绩) 
   from score 
   where 课程号 = '0002';
   

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

   select count(distinct 学号) as 学生人数 
   from score;
   

3. 查询各科成绩最高和最低的分，以如下形式显示：课程号，最高分，最低分

   select 课程号,max(成绩) as 最高分,min(成绩) as 最低分 
   from score group by 课程号;
   

4. 查询没门课程被选修的学生数

   select 课程号,count(学号) as 学生人数 
   from score 
   group by 课程号;
   

5. 查询男生、女生人数

   select 性别,count(*) 
   from student 
   group by 性别;
   

6. 查询平均成绩大于60分学生的学号和平均成绩

   select 学号,avg(成绩) as 平均成绩 
   from score 
   group by 学号 
   having avg(成绩)>60;
   

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

   select 学号,count(课程号) as 选修课程数量 
   from score 
   group by 学号 
   having count(课程号)>=2;
   

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

   select 姓名,count(学号) as 同名人数 
   from student 
   group by 姓名 
   having count(*)>=2;
   

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

   select 课程号 
   from score 
   where 成绩<60 
   order by 课程号 desc;
   

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

    select avg(成绩) as 平均成绩 
    from score 
    group by 课程号 
    order by avg(成绩) asc,课程号 desc;
    

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

    select 学号 
    from score 
    where 课程号='0004' and 成绩<60 
    order by 成绩 desc;
    

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

    select 课程号,count(学号) 
    from score 
    group by 课程号 
    having count(学号)>2 o
    rder by count(学号) desc,课程号 asc;
    

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

    select 学号,avg(成绩) as 平均成绩 
    from score 
    where 成绩<60 
    group by 学号 
    having count(课程号)>=2;
    

3.复杂查询

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

   select 学号,姓名 
   from student 
   where 学号 in
   (select 学号 from score where 成绩<60);
   

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. 查询所有课程成绩小于60分学生的学号、姓名

   select 学号,姓名 
   from student 
   where 学号 in(select 学号 from score where 成绩<60);
   

4.日期函数

5.多表联查

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 b.学号 
   having avg(b.成绩)>85;
   

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

   select a.学号,a.姓名,c.课程号,c.课程名称 
   from student as a 
   inner join score as b on a.学号=b.学号 
   inner join course as c on b.课程号=c.课程号;
   

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

   select 课程号,
   sum(case when 成绩>=60 then 1 else 0) as 及格人数,
   sum(case when 成绩<60 then 1 else 0) as 不及格人数 
   from score group by 课程号;