MySQL基础训练50题之41~48

MySQL基础训练50题之41~48

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

   SELECT c_id, COUNT( distinct s_id) num FROM score
   GROUP BY c_id
   HAVING num>5 
   ORDER BY num DESC,c_id ASC
   

2. 检索至少选修两门课程的学生学号

   SELECT s_id ,COUNT(*) 选课数量 FROM score 
   GROUP BY s_id 
   HAVING 选课数量 >=2
   

3. 查询选修了全部课程的学生信息

   SELECT s.* FROM student s,
   (SELECT s_id,COUNT(*) AS a FROM score 
   GROUP BY s_id
   HAVING a = (
   SELECT COUNT(*) FROM course))r 
   WHERE s.s_id = r.s_id
   

4. 查询各学生的年龄

   select s_id,s_name,TIMESTAMPDIFF(YEAR,s.s_birth,CURDATE()) "年龄" from student s
   

5. 查询本周过生日的学生

   select s_id,s_name from student
   where  week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now()) 
   

6. 查询下周过生日的学生

   select s_id,s_name from student
   where  week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now())+1
   
   

   select s_id,s_name from student
   where  week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now())+1
   
   

7. 查询本月过生日的学生

    select s_id,s_name from student
    where month(s_birth)=month('2020-12-1')SELECT c_id,COUNT(c_id) FROM score
   GROUP BY c_id
   

8. 查询下月过生日的学生

    select s_id,s_name from student
    where month(s_birth)=month('2020-7-1')+1