超经典SQL基础练习题,做完SQL及格(MYSQL)

SQL经典50题,这套题做的人很多,今天自己也写了一下,互相学习!
引用数据

四张测试表如下:
–1.学生表
Student(Sid,Sname,Sage,Ssex)
–Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

–2.课程表
Course(Cid,Cname,Tid)
–Cid --课程编号,Cname 课程名称,Tid 教师编号

–3.教师表
Teacher(Tid,Tname)
–Tid 教师编号,Tname 教师姓名

–4.成绩表
SC(Sid,Cid,score)
–Sid 学生编号,Cid 课程编号,score 分数

--建表语句
--学生表 Student
create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--科目表 Course
create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表 Teacher
create table Teacher(Tid varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表 SC
create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

--同一个学生科目一有成绩但是科目二无成绩的要注意
select
  s.*,
  c.score1,
  c.score2
from 
  student s join
  (SELECT
    a.sid as sid,
    a.score as score1,
    if(b.score is null,0,b.score) as score2
  from
    (SELECT
      sid,
      cid,
      score
    from sc 
      where cid='01') 
    a left join
    (SELECT
      sid,
      cid,
      score
    from sc 
      where cid='02') 
    b on 
      a.sid=b.sid where 
      a.score>if(b.score is null,0,b.score)) 
  c on 
    c.sid=s.sid

2. 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT
  a.sid as sid,
  a.cid as cid1,
  a.score as score1,
  b.cid as cid2,
  b.score as score2
FROM
  (SELECT
    *
  FROM
    sc where cid='01') 
  a join
  (SELECT
    *
  FROM 
    sc where cid='02') 
  b on 
  a.sid=b.sid

3. 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT
  a.sid as sid,
  a.cid as cid1,
  a.score as score1,
  b.cid as cid2,
  b.score as score2
FROM
  (SELECT
    *
  FROM
    sc where cid='01') 
  a left join
  (SELECT
    *
  FROM 
    sc where cid='02') 
  b on 
  a.sid=b.sid

4. 查询不存在" 01 “课程但存在” 02 "课程的情况

--答案1
--利用not in
select 
  sid,
  cid,
  score
FROM
  sc where 
  cid='02' and sid not in (select sid from sc where cid='01')

--答案2
--利用group_concat作为分组后的过滤条件
SELECT
  *
FROM
  sc group by 
  sid having 
  GROUP_CONCAT(cid) not like '%01%'

5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

--因为某些同学只考了两门,总共科目有三门,总成绩除2或除3的写法是完全不同的
--除2 直接使用avg
select
  s.*,
  a.avg_score as avg_score
from 
  student s join
  (select
    sid,
    avg(score) as avg_score
  from 
    sc group by 
    sid having(avg(score)>60)) 
  a on 
  s.sid=a.sid

--除3 总分除以总门数
SELECT
  s.*,
  a.avg_score as avg_score
FROM 
  student s join
  (select
    sid,
    sum(score)/(select count(1) from course) as avg_score
  from
    sc group by 
    sid having sum(score)/(select count(1) from course)>60) 
  a on 
  s.sid=a.sid

6. 查询在 SC 表存在成绩的学生信息

select
  student.*
from
  sc JOIN 
  student on 
  sc.sid=student.sid group by 
  sid

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT
  s.*,
  a.count_course,
  a.sum_score
from 
  student s left join
  (SELECT
    sid,
    count(1) as count_course,
    sum(score) as sum_score
  FROM 
    sc group by 
    sid) 
  a on 
  s.sid=a.sid

8. 查有成绩的学生信息

SELECT
  s.*
from 
  student s join
  (SELECT
    sid
  FROM 
    sc group by 
    sid) 
  a on 
  s.sid=a.sid

9. 查询「李」姓老师的数量

SELECT
  count(1) as num
FROM
  teacher WHERE
  tname like '李%'

10. 查询学过「张三」老师授课的同学的信息

SELECT
  st.*,
  t.tname as teacher
from 
  sc s join
  student st join 
  course c join
  teacher t on 
  c.tid=t.tid and 
  c.cid=s.cid and 
  s.sid=st.sid where 
  t.tname ='张三'

11. 查询没有学全所有课程的同学的信息

--答案1
--使用group_concat分组拼接课程,注意group_concat无序需要组内排序,还要判断null的情况
SELECT
  s.*,
  GROUP_CONCAT(cid) as concat_cid
from
  student s left JOIN
  sc on 
  s.sid=sc.sid group by 
  s.sid having 
  if(GROUP_CONCAT(cid order by cid) is null,'-1',GROUP_CONCAT(cid order by cid))!=(select GROUP_CONCAT(cid) from course)

--答案2
--多表join需要注意null值,如果三科并不是语文数学英语,这样写并不合适
SELECT
  student.*
FROM
  student left join 
  sc on 
  sc.sid=student.sid group by 
  student.sid having count(1)<3

12. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

--答案1
--使用join结果集要排除学号01
SELECT
  DISTINCT student.* 
FROM
  sc join 
  student on 
  sc.sid=student.sid where 
  sc.sid<>'01' and sc.cid in 
  (SELECT
    cid
  FROM
	sc where 
	sid='01')

--答案2
--使用in
select 
  * 
from 
  student where 
  sid in 
  (select sid from sc where sid<>'01' and cid in 
  (select cid from sc where sid='01'))

13. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

--答案1
--使用group_concat(),注意需要排序
SELECT
  student,*
FROM 
  sc join 
  student on 
  sc.sid=student.sid and 
  sc.sid<>'01' group by 
  sc.sid having 
  GROUP_CONCAT(cid order by cid)=
  (SELECT
    GROUP_CONCAT(cid)
  from
    sc where 
    sid='01')

--使用join有些繁琐,要满足跟01的课程数量与课程名称都一样
SELECT
  student.*
from 
  sc join 
  student on 
  sc.sid=student.sid join
  (SELECT
    cid
  from
    sc where 
    sid='01'
  ) 
  a on 
  sc.cid=a.cid and 
  sc.sid<>'01' group by 
  sc.sid having 
  count(1)=(select count(1) from sc where sid='01')

14. 查询没学过"张三"老师讲授的任一门课程的学生姓名

--答案1 使用in和not in
select
  sname
from 
  student where 
  sid not in 
  (select sid from sc where cid in 
    (select cid from course where tid=
      (select tid from teacher where tname='张三')
    )
  )

--答案2
--使用join
SELECT
  sname
from 
  teacher t join 
  course c on 
  t.tid=c.tid and 
  tname='张三' join 
  sc on 
  c.cid=sc.cid 
  RIGHT JOIN 
  student s on 
  sc.sid=s.sid where 
  sc.sid is null

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

--答案1
--按照现有考试科目统计(排除未考)
select
  s.sid as sid,
  s.sname as sname,
  avg(sc.score) as avg_score
from 
  sc join 
  student s on 
  sc.sid=s.sid where 
  score<60 GROUP BY 
  s.sid HAVING count(1)>1

--答案2
--按照每个同学都需考语文数学英语这三门,缺考算不及格
select 
  s.sid,
  s.sname,
  avg(if(score is null,0.0,score)) as avg_score 
from 
  course c join 
  student s LEFT JOIN 
  sc on 
  s.sid=sc.sid and c.cid=sc.cid 
  where if(score is null,0.0,score)<60 group by 
  s.sid having count(1)>1

16. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

--答案1
--按照现有考试科目统计(未考科目不统计)
select 
  s.*,
  sc.score as score 
from 
  student s join 
  sc on 
  s.sid=sc.sid where 
  cid='01' and score<60 order by 
  score desc,s.sid

--答案2  
--按照每个同学都需考语文数学英语这三门,未考成绩为0
select 
  s.sid,
  s.sname,
  ifnull(score,0) as score 
from 
  course c join 
  student s LEFT JOIN 
  sc on 
  s.sid=sc.sid and c.cid=sc.cid where 
  ifnull(score,0)<60 and c.cid='01' order by 
  ifnull(score,0) desc,sid

17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

--答案1
--按照现有考试科目统计(未考不统计),显示为竖表
select 
  sc.sid as sid,
  c.cname as cname,
  score,
  a.avg_score as avg_score
from 
  sc join 
  (select 
    sid,
    avg(score) as avg_score 
  from 
    sc group by 
    sid
  ) 
  a join course c on
  sc.cid=c.cid and 
  sc.sid=a.sid order by
  avg_score desc,sc.sid

--答案2
--按照现有考试科目统计(排除未考),显示为横表,使用分组case when,并不完美最后表中未考项目显示为0,所以case when后取最小值,字符排序	
select 
  s.sname as name,
  min(case cname when '语文' then score else '缺考' end) as '语文',
  min(case cname when '数学' then score else '缺考' end) as '数学',
  min(case cname when '英语' then score else '缺考' end) as '英语',
  a.avg_score as avg_score
from 
  sc join 
  (select 
    sid,
    avg(score) as avg_score 
  from 
    sc group by 
    sid
  ) 
  a join course c 
  join student s on
  s.sid=sc.sid and
  sc.cid=c.cid and 
  sc.sid=a.sid group by s.sname,s.sid order by
  avg_score desc,sc.sid
  
--答案3
--按照每个同学都需考语文数学英语这三门,未考成绩为0,纵表
-- with <table name> as (........)  -----mysql8.0以上才支持
with tmp as 
(
SELECT
  a.sid as sid,
  sname,
  cname,
  if(score is null,0,score) as score
FROM
  sc right join
  (
  SELECT
    sid,
    sname,
    cid,
    cname
  from 
    student s,
    course c
  ) 
  a on 
  sc.cid=a.cid and sc.sid=a.sid
)

select 
  t1.*,
  t2.avg_score 
from 
  tmp t1 
  join 
  (
  select 
    sname,
    avg(score) as avg_score 
  from 
    tmp group by 
    sname
  ) 
  t2 on 
  t1.sname=t2.sname order by 
  avg_score desc,t1.sid

--答案4
--按照每个同学都需考语文数学英语这三门,未考成绩为0,横表
with tmp as 
(
SELECT
  a.sid as sid,
  sname,
  cname,
  if(score is null,0,score) as score
FROM
  sc right join
  (
  SELECT
    sid,
    sname,
    cid,
    cname
  from 
    student s,
    course c
  ) 
  a on 
  sc.cid=a.cid and sc.sid=a.sid
)

select 
  sid,
  sname,
  max(case when cname='语文' then score else 0 end) as '语文',
  max(case when cname='数学' then score else 0 end) as '数学',
  max(case when cname='英语' then score else 0 end) as '英语',
  avg(score) as avg_score 
from 
  tmp group by 
  sname,sid order by 
  avg(score) desc,sid

--答案5
--按照每个同学都需考语文数学英语这三门,未考成绩为0,纵表利用窗口函数
SELECT
  a.sid as sid,
  sname,
  cname,
  if(score is null,0,score) as score,
  avg(if(score is null,0,score)) over(partition by sid) as avg_score
FROM
  sc right join
  (
  SELECT
    sid,
    sname,
    cid,
    cname
  from 
    student s,
    course c
  ) 
  a on 
  sc.cid=a.cid and sc.sid=a.sid order by avg_score desc,sid

18. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

--按照现有考试科目统计(未考科目不统计)
--答案1
SELECT
  cid,
  cname,
  max(score) as max_score,
  min(score) as min_score,
  avg(score) as avg_score,
  count(1) as counts,
  sum(pass)/count(1) as 'pass%',
  sum(fair)/count(1) as 'fair%',
  sum(good)/count(1) as 'good%',
  sum(excellent)/count(1) as 'excellent%'
FROM
  (
  SELECT
    sid,
    sc.cid,
    score,
    cname,
    if(score>=60,1,0) as 'pass',
    if(score>=70 and score<80,1,0) as 'fair',
    if(score>=80 and score<90,1,0) as 'good',
    if(score>=90,1,0) as 'excellent'
  FROM
    sc join 
    course c on 
    sc.cid=c.cid
  )
  a group by 
  cid,
  cname order by count(1) desc,cid

--答案2
--按照每个同学都需考语文数学英语这三门,未考成绩为0
with tmp as 
(
SELECT
  a.sid as sid,
  sname,
  a.cid,
  cname,
  if(score is null,0,score) as score
FROM
  sc right join
  (
  SELECT
    sid,
    sname,
    cid,
    cname
  from 
    student s,
    course c
  ) 
  a on 
  sc.cid=a.cid and sc.sid=a.sid
)

SELECT
  cid,
  cname,
  max(score) as max_score,
  min(score) as min_score,
  avg(score) as avg_score,
  count(1) as counts,
  sum(pass)/count(1) as 'pass%',
  sum(fair)/count(1) as 'fair%',
  sum(good)/count(1) as 'good%',
  sum(excellent)/count(1) as 'excellent%'
FROM
  (
  SELECT
    sid,
    cid,
    score,
    cname,
    if(score>=60,1,0) as 'pass',
    if(score>=70 and score<80,1,0) as 'fair',
    if(score>=80 and score<90,1,0) as 'good',
    if(score>=90,1,0) as 'excellent'
  FROM
    tmp 
  )
  a group by 
  cid,
  cname order by count(1) desc,cid

19. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

--答案1
--按照现有考试科目统计(未考不统计)
with tmp as 
  (
  SELECT
    s1.cid as cid,
    s1.sid as sid,
    min(s1.score) as score,
    count(1) as ranking
  FROM
    sc s1 join 
    sc s2 on 
    s1.cid=s2.cid and s1.score<=s2.score group by 
    cid,sid
  )

select 
  cid,
  sid,
  score,
  if((cid,score) in 
  (
  select 
    cid,
    score 
  from 
    tmp group by 
    cid,score having 
    count(1)>1),
    (ranking-1),
    ranking
  ) 
  as ranking 
FROM
  tmp order by 
  cid,ranking

--答案2
--窗口函数
SELECT
  sid,
  cid,
  score,
  rank() over(PARTITION BY cid ORDER BY score desc) as ranking,  -- 排序相同时会重复，总数不会变
  dense_rank() over(PARTITION BY cid ORDER BY score desc) as dense_ranking,  -- 排序相同时会重复，总数会减少
  row_number() over(PARTITION BY cid) as num  -- 顺序加1
FROM
  sc

20. 按各科成绩进行排序，并显示排名， Score 重复时合并名次

--答案1
--按照现有考试科目统计(未考不统计),利用窗口函数
SELECT
  sid,
  cid,
  score,
  rank() over(PARTITION BY cid ORDER BY score desc) as ranking,  -- 排序相同时会重复，总数不会变
  dense_rank() over(PARTITION BY cid ORDER BY score desc) as dense_ranking,  -- 排序相同时会重复，总数会减少
  row_number() over(PARTITION BY cid) as num  -- 顺序加1
FROM
  sc

--答案2
--使用join,不用窗口函数有些繁琐
with tmp as 
  (
  SELECT
    s1.cid as cid,
    s1.sid as sid,
    min(s1.score) as score,
    count(1) as ranking
  FROM
    sc s1 join 
    sc s2 on 
    s1.cid=s2.cid and s1.score<=s2.score group by 
    cid,sid
  )

SELECT
  t1.cid as cid,
  t1.sid as sid,
  t1.score as score,
  -- if(t2.score>=t1.score,ranking-1,ranking) as ranking
  case
    when t2.score>=t1.score then ranking-1 else ranking
  end as ranking
FROM
  tmp t1 left join
  (
  select 
    cid,
    score 
  from 
    tmp group by 
    cid,score having count(1)>1
  ) 
  t2 on 
  t1.cid=t2.cid order by 
  cid,score desc

21. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

--答案1
--按照现有考试科目统计(未考不统计),窗口函数
SELECT
  s.sid as sid,
  sname,
  sum(score) as sum_score,
  rank() over(order by sum(score) desc) as ranking
from
  sc join 
  student s on 
  sc.sid=s.sid group by 
  s.sid,s.sname

22. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

--答案1
--按照现有考试科目统计(未考不统计),窗口函数
SELECT
  s.sid as sid,
  sname,
  sum(score) as sum_score,
  dense_rank() over(order by sum(score) desc) as ranking
from
  sc join 
  student s on 
  sc.sid=s.sid group by 
  s.sid,s.sname

23. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85)，[85-70)，[70-60)，[60-0] 及所占百分比

--答案1
--按照现有考试科目统计(未考不统计),分组后case when再聚合
SELECT
  sc.cid,
  cname,
  sum(case when score>85 and score<=100 then 1 else 0 end)/count(1) as '100-85',
  sum(case when score>70 and score<=85 then 1 else 0 end)/count(1) as '85-70',
  sum(case when score>=60 and score<=70 then 1 else 0 end)/count(1) as '70-60',
  sum(case when score>0 and score<60 then 1 else 0 end)/count(1) as '60-0'  
from 
  sc join 
  course c on 
  sc.cid=c.cid group by 
  cid,cname order by 
  sc.cid

24. 查询各科成绩前三名的记录

--答案1
--按照现有考试科目统计(未考不统计),窗口函数,假设有四个相同最高分,使用dense_rank更合适
SELECT
  cid,
  sid,
  score,
  dense_rank() over(PARTITION BY cid order by score desc)
FROM
  sc

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

select 
  cid,
  count(*) as num 
from 
  sc GROUP BY 
  cid

26. 查询出只选修两门课程的学生学号和姓名

select
  sc.sid as sid,
  s.sname as sname
FROM
  sc join 
  student s on 
  sc.sid=s.sid group by 
  sc.sid,s.sname having 
  count(1)=2

27. 查询男生、女生人数

SELECT
  ssex,
  count(1) as num
FROM
  student group by 
  ssex

28. 查询名字中含有「风」字的学生信息

select
  *
from
  student where sname like '%风%'

29. 查询同名同性学生名单，并统计同名人数

SELECT
  sname,
  count(1) as num
FROM
  student group by 
  sname HAVING count(1)>1

30. 查询 1990 年出生的学生名单

SELECT
  *
FROM
  student where 
  substr(sage,1,4)='1990' 

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

SELECT
  cid,
  avg(score) as avg_score
FROM
  sc group by 
  cid order by 
  avg(score) desc,cid

32. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT
  s.sid as sid,
  sname,
  avg(score) as avg_score
FROM
  sc join 
  student s on 
  sc.sid=s.sid group by 
  s.sid,sname having avg(score)>85

33. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT
  sname,
  score
FROM
  sc join 
  course c join 
  student s on 
  sc.sid=s.sid and sc.cid=c.cid where 
  cname='数学' and score<60

34. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

with tmp as 
    (
	SELECT
		sid,
		sname,
		cid,
		cname
	FROM
		student s,
		course c
    )

SELECT
  tmp.sid as sid,
  sname,
  tmp.cid as cid,
  cname,
  score
FROM
  tmp left join 
  sc on 
  tmp.cid=sc.cid and tmp.sid=sc.sid

35. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

with tmp as 
    (
	SELECT
		sc.sid as sid,
		sname,
		cname,
		score,
		case when score>70 then 1 else 0 end as mark
	FROM
		sc join 
		student s join 
		course c on 
		sc.sid=s.sid and sc.cid=c.cid
    )


SELECT
  sname,
  cname,
  score
FROM
  tmp where 
  (sid,sname) in 
  (
  SELECT
  	sid,
  	sname
  FROM
  	tmp group by 
  	sid,sname having count(1)=sum(mark)
  )

36. 查询不及格的课程

--查询不及格的课程(姓名,课程名称与分数)
SELECT
  s.sid as sid,
  sname,
  cname,
  score
FROM
  sc join 
  course c join 
  student s on 
  sc.sid=s.sid and sc.cid=c.cid where 
  score<60

--查询有不及格的课程(课程名去重)
SELECT
  DISTINCT(cname)
FROM
  sc join 
  course c join 
  student s on 
  sc.sid=s.sid and sc.cid=c.cid where 
  score<60

37. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT
  s.sid as sid,
  sname
FROM
  sc join 
  student s on 
  sc.sid=s.sid where 
  score>80 and cid='01'

38. 求每门课程的学生人数

SELECT
  cid,
  count(1) as num
FROM
  sc group by 
  cid 

SELECT
  DISTINCT cid as cid,
  sum(1) over(partition by cid) as num
FROM
  sc

39. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select
  s.sid as sid,
  sname,
  score,
  sc.cid,
  cname,
  tname
FROM
  sc join 
  course c join 
  teacher t join 
  student s on 
  s.sid=sc.sid and 
  sc.cid=c.cid and 
  c.tid=t.tid where 
  tname='张三' order by 
  score desc limit 1

40. 成绩有重复的情况下，查询选修「李四」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
  s.*,
  score
FROM
  sc join 
  student s on 
  sc.sid=s.sid where 
  (cid,score) in
  (
  SELECT
    sc.cid as cid,
    max(score) as score
  FROM
    sc join 
    course c join 
    teacher t on 
    sc.cid=c.cid and c.tid=t.tid where 
    tname='李四' group by 
    sc.cid
  )

41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
  *
FROM
  sc s1 join 
  sc s2 on 
  s1.cid<>s2.cid and 
  s1.sid<>s2.sid and 
  s1.score=s2.score

42. 查询每门功成绩最好的前两名

SELECT
  cid,
  sid,
  score
FROM
	(
	SELECT
		cid,
		sid,
		score,
		row_number() over(PARTITION BY cid order by score desc) as rn,
		rank() over(PARTITION BY cid order by score desc) as rk,
		dense_rank() over(PARTITION BY cid order by score desc) as dr
	FROM
		sc
	)
	a where rn<3

43. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT
  cid,
  count(1) as num
FROM
  sc group by 
  cid HAVING count(1)>5

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

SELECT
  sid,
  count(1) as num
FROM
  sc group by 
  sid HAVING count(1)>=2

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

SELECT
  *
FROM
  student where 
  sid in
  (
  SELECT
    sid
  FROM
    sc group by 
    sid HAVING 
    GROUP_CONCAT(cid order by cid)=
    (
    SELECT
      GROUP_CONCAT(cid)
    FROM
      course
    )
  )

46. 查询各学生的年龄，只按年份来算

SELECT
  sname,
  year(now())-year(sage) as age
FROM
  student


SELECT
  sname,
  FLOOR((TO_DAYS(SYSDATE())-TO_DAYS(sage))/365) as age
FROM
  student

SELECT
  cast(substr(now(),1,4) as signed)-cast(substr(sage,1,4) as signed) 
FROM
  student

47. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

--答案1
select
  sname,
  if(month(SYSDATE())<month(sage) or (month(now())=month(sage) and day(now())<day(sage)),year(now())-year(sage)-1,year(now())-year(sage)) as age,
from
  student

--答案2
select 
  sname, 
  timestampdiff(year, sage, now()) as age 
from 
  student

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

-- SELECT sid,sname FROM student WHERE WEEK(sage)=WEEK(NOW()); 这样并不严谨,同年同日可能不在同一周
SELECT 
  sid,
  Sname 
FROM 
  Student WHERE 
  WEEK(CONCAT(YEAR(NOW()),'-',MONTH(sage),'-',DAY(sage)))=WEEK(NOW());

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

-- 下周可能是新年的第一周,所以不能简单的用week()+1判断
SELECT
  sid,
  sname
FROM
  student where 
  week(CONCAT(year(now()),'-',month(sage),'-',day(sage)))=(case when week(now())=52 then 1 else week(now())+1 end)

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

SELECT
  sid,
  sname
FROM
  student where month(now())=month(sage)

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

-- 12月后直接加1为13月,不能直接写需要判断
SELECT
  sid,
  sname
FROM
  student where   
  month(sage)=if(month(now())=12,1,month(now())+1)

终于完成了!!!