mysql经典练习题

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

select

s.sid,s.Sname,s.Sage, s.Ssex,a.score

from 

student as s,

(select score,sid from sc where c='01') as a,

(select score,sid from sc where c='02') as b

where 

s.sid=a.sid and a.score>b.score and s.sid=b.sid;

-- select a.sid from (select score,sid from sc where c='01') as a left join (select score,sid from sc where c='02') as b

-- on a.sid=b.sid where a.score>b.score

 

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

select

s.sid,s.Sname,s.Sage, s.Ssex

from 

student as s,

(select sid from sc where c='01') as a,

(select sid from sc where c='02') as b

where 

s.sid=a.sid and a.sid=b.sid and s.sid=b.sid;

 

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

select * from 

(select * from sc where c="01") as a 

left join 

(select * from sc where c="02") as b 

on a.sid=b.sid;

 

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

select distinct b.sid from 

(select * from sc where c != "01") as a 

left join 

(select * from sc where c="02") as b 

on a.sid=b.sid;

 

select

distinct s.*

from 

student as s,

(select sid from sc where c !='01') as a,

(select sid from sc where c='02') as b

where 

s.sid=a.sid and a.sid=b.sid and s.sid=b.sid;

 

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

 

select 

s.sid,s.Sname,avg(sc.score) as avg_score 

from student as s ,sc 

where s.sid = sc.sid having avg_score >= 60 

 

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

select 

distinct s.*

from 

student as s 

left join 

sc 

on sc.sid=s.sid 

where sc.score is not null

 

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

select 

s.sid,s.Sname,sum(sc.c) as num ,sum(sc.score) as scc

from 

student as s 

left join 

sc 

on sc.sid=s.sid 

group by s.sid,s.Sname

 

4.1 查有成绩的学生信息

select 

distinct s.*

from 

student as s,

sc 

where sc.score is not null and sc.sid=s.sid 

 

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

 

select count(Tname) from teacher where Tname like "李%"

 

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

select 

s.sid,s.Sname,s.Sage, s.Ssex 

from 

student as s,

(select T from teacher where Tname ="张三") as tr,

sc

where sc.sid=s.sid and tr.T = sc.c

 

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

select 

s.*

from 

student as s,

(select count(c) as num,sid from sc group by sid having num<3) as scc

where

s.sid = scc.sid

 

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

select 

distinct s.*

from 

student as s

left join 

sc

on sc.sid=s.sid 

where sc.c in (select c from sc where sid="01")

 

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

select 

s.*,count(sc.c) as u 

from 

student as s

left join 

sc

on sc.sid=s.sid group by s.sid 

having 

u =(select count(c) as num from sc where sid="01") 

 

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

 

select s.Sname from student as s 

where sid not in 

(select sid from sc,teacher as t,course as ce 

where t.Tname="张三" and t.T=ce.T and sc.c=ce.c

)

 

select * from sc where c="02"

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

 

select 

s.sid,s.Sname,scc.score_avg

from student as s,

(select count(sid) as cd,sid,avg(sc.score) as score_avg from sc where score <60 group by sid) as scc

where s.sid = scc.sid and scc.cd>=2

 

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

 

select s.* from student as s,

(select sid,score from sc where c="01" and score <60) as scc

where scc.sid = s.sid order by scc.score desc

 

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

 

select b.c,a.sid,b.score,a.avg_score from 

(select sid,avg(score)as avg_score from sc group by sid) as a,

(select sid,c,score from sc group by c,sid) as b

where a.sid=b.sid order by a.avg_score desc

 

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

 

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

    

select distinct a.c,ce.Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率,ce.T from sc

left join course as ce on ce.c = sc.c

left join (select max(score) as 最高分,min(score)as 最低分,avg(score)as 平均分,c from sc group by c) as a

on a.c=ce.c left join 

(select sum(case when score>=60 then 1 else 0 end)/count(score)*100 as 及格率,c from sc group by c) as b

on a.c = b.c left join 

(select c,sum(case when score>=70 and score<80 then 1 else 0 end)/count(score)*100 as 中等率 from sc group by c) as c

on a.c=c.c left join 

(select c,sum(case when score>=80 and score<90 then 1 else 0 end)/count(score)*100 as 优良率 from sc group by c) as d

on a.c=d.c left join 

(select c,sum(case when score>=90 then 1 else 0 end)/count(score)*100 as 优秀率 from sc group by c) as e

on a.c = e.c order by ce.T

 

select sc.c, ce.Cname,count(sc.sid) as '选修人数', max(sc.score), min(score), avg(score),

(sum(case when score>=60 then 1 else 0 end)/count(sc.sid)) as '及格率',

(sum(case when score>=70 and score<80 then 1 else 0 end) /count(sc.sid)) as '中等率',

(sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.sid)) as '优良率',

(sum(case when score>=90 then 1 else 0 end)/count(sc.sid)) as '优秀率'

from sc, course as ce

where sc.c=ce.c

group by sc.c

order by count(sc.sid) desc, c;

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

 

select sid,c,score from sc group by c,sid order by c,score desc

 

select a.sid,a.c,a.score from sc as a left join sc as b

on a.sid=b.sid and a.score <b.score

group by a.c,a.sid order by a.c,a.score desc

 

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

 

select a.sid ,a.c, a.score, count( distinct b.score) as rank

from sc a left join sc b on a.c=b.c and a.score<=b.score

group by a.c, a.sid

order by a.c, a.score desc

 

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

 

select sid,sum(score) as num from sc group by sid order by num desc

 

select a.*,count(a.aj>b.bj)+1 as rank from 

(select sid,sum(score) as aj from sc group by sid) as a

left join 

(select sid,sum(score) as bj from sc group by sid) as b

on a.aj<b.bj

group by a.sid order by rank asc

 

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

select a.*,count(distinct b.bj)+1 as rank from 

(select sid,sum(score) as aj from sc group by sid) as a

left join 

(select sid,sum(score) as bj from sc group by sid) as b

on a.aj<b.bj

group by a.sid order by rank asc

 

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

select

ce.T,ce.Cname,

(sum(case when score>=85 then 1 else 0 end)/count(distinct sid))as "100-85",

(sum(case when score>=70 and score<85 then 1 else 0 end)/count(distinct sid))as "85-70",

(sum(case when score>=60 and score<70 then 1 else 0 end)/count(distinct sid))as "70-60",

(sum(case when score<60 then 1 else 0 end)/count(distinct sid))as "60-0" 

from sc,course as ce

where ce.c= sc.c

group by sc.c

 

-- select sc.c,Cname,count(sid) as '总人数',

-- concat(round(sum(case when score>=0 and score<60 then 1 else 0 end) /count(sid)*100,2),'%') as '0-60',

-- concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sid)*100,2),"%") as '60-70',

-- concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(sid)*100,2),"%") as '70-85',

-- concat(round(sum(case when score<=100 and score>=85 then 1 else 0 end )/count(*)*100,2),'%') as '85-100'

-- from sc join course on sc.c=course.c

-- group by c;

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

select * from 

(select a.*, count(distinct b.score) +1 as rank

from sc a

left join sc b

on a.c=b.c and a.score<b.score

group by a.c, a.sid

order by a.c, a.score desc) c

where c.rank<=3;

 

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

 

select count(sid)as 选修的学生数,c from sc group by c

 

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

 

select * from student as s,

(select count(sid) as ce,sid from sc group by sid having ce=2) as cf 

where cf.sid=s.sid

 

21. 查询男生、女生人数

 

select 

sum(case when Ssex="男" then 1 else 0 end)as 男生,

sum(case when Ssex="女" then 1 else 0 end)as 女生

from student

 

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

 

select * from student where Sname like "%风%"

 

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

 

select Ssex,Sname,

count(*) as 人数

from student group by Ssex,Sname having 人数>=2

 

 

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

 

select * from student where Sage like "1990%"

 

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

 

select c,avg(score) as ag from sc group by c order by ag desc,c asc;

 

 

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

 

select s.sid,s.Sname,平均成绩 from student as s, 

(select avg(score) as 平均成绩,sid from sc group by sid having 平均成绩>=85) as scc

where s.sid =scc.sid

 

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

 

select s.Sname,sc.score from student as s,sc,

(select c from course where Cname ="数学") as number

where s.sid=sc.sid and sc.c=number.c and sc.score<60;

 

 

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

 

select distinct s.* ,sc.c,sc.score from student as s

left join sc 

on s.sid= sc.sid

 

 

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

 

select s.Sname,ce.Cname,sc.score from student as s left join 

sc on s.sid = sc.sid

left join course as ce

on ce.c=sc.c

where sc.score>70

group by sc.c,s.sid

 

 

30. 查询不及格的课程

 

select s.Sname,ce.Cname,sc.score from student as s left join 

sc on s.sid = sc.sid

left join course as ce

on ce.c=sc.c

where sc.score<60

group by sc.c,s.sid

 

 

select ce.Cname,sc.score from sc 

left join course as ce

on ce.c=sc.c

where sc.score<60

group by sc.c

 

 

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

 

select s.sid,s.Sname from student as s,sc where c="01" and score >80 and s.sid=sc.sid;

 

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

 

select count(distinct sid) as 学生人数,c from sc group by c;

 

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

 

select s.*,sc.score from student as s

left join sc 

on sc.sid=s.sid

left join

course as ce 

on ce.c=sc.c 

left join 

teacher as tr

on tr.T=ce.T

where tr.Tname="张三"

order by sc.score desc limit 1

 

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

 

select s.*,a.score from sc as a

left join sc as b on a.c=b.c and a.score<b.score 

inner join course as ce on a.c=ce.c inner join 

teacher as tr on tr.T = ce.T and tr.Tname="张三"

inner join student as s on s.sid=a.sid

group by a.c,a.sid

having count(distinct b.score)=0

 

 

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

 

select sc.* from sc join 

(select *,count(sid) as num from sc group by score having num>1) as a

on sc.c=a.c and sc.score = a.score

order by sc.score;

 

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

 

select a.*,count(distinct b.score)+1 as rank from sc as a left join sc as b on a.c= b.c and a.score<b.score 

group by a.sid,a.c having rank <=2 order by a.c 

 

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

 

select c,count(c) as num from sc group by c having num >5

 

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

 

select *,count(c) as num from sc group by sid having num >1

 

 

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

 

select s.* from student as s ,sc

where s.sid =sc.sid group by s.sid having count(*) = (select count(*) from course)

 

-- select *,count(c) as num from sc group by sid having num >2

 

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

 

select *,(year(now())- year(Sage)) as 年龄 from student

 

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

 

select timestampdiff(year,sage,now()) from student

select student.sid, student.sname,student.ssex, sage,

 

timestampdiff(year,sage,now()) as '按月日计算', # 出生月日< 当前日期的月日时，年龄会减一 ,该题目功能是通过这一句实现的，下一句只是为了对比说明两者之间的差别

year(now())-year(sage) as '按年份计算' 

from student; 

 

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

 

select * from student 

where week(concat_ws('-',year(now()),date_format(Sage,'%m-%d')))=week(now());

 

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

 

select * from student 

where week(concat_ws('-',year(now()),date_format(Sage,'%m-%d')))=week(now())+1;

 

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

select *

from student

where month(student.sage)=month(now());

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

select *

from student

where month(student.sage)=month(now())+1;

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数selects.sid,s.Sname,s.Sage, s.Ssex,a.scorefrom student as s,(select score,sid from sc where c='01') as a,(select score,sid from sc where c='02') as bwhere s.sid=a.sid and a.score>b.score and s.sid=b.sid;-- select a.sid from (select score,sid from sc where c='01') as a left join (select score,sid from sc where c='02') as b-- on a.sid=b.sid where a.score>b.score
1.1 查询同时存在" 01 "课程和" 02 "课程的情况selects.sid,s.Sname,s.Sage, s.Ssexfrom student as s,(select sid from sc where c='01') as a,(select sid from sc where c='02') as bwhere s.sid=a.sid and a.sid=b.sid and s.sid=b.sid;
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )select * from (select * from sc where c="01") as a left join (select * from sc where c="02") as b on a.sid=b.sid;
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况select distinct b.sid from (select * from sc where c != "01") as a left join (select * from sc where c="02") as b on a.sid=b.sid;
selectdistinct s.*from student as s,(select sid from sc where c !='01') as a,(select sid from sc where c='02') as bwhere s.sid=a.sid and a.sid=b.sid and s.sid=b.sid;
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.Sname,avg(sc.score) as avg_score from student as s ,sc where s.sid = sc.sid having avg_score >= 60 
3. 查询在 SC 表存在成绩的学生信息select distinct s.*from student as s left join sc on sc.sid=s.sid where sc.score is not null
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )select s.sid,s.Sname,sum(sc.c) as num ,sum(sc.score) as sccfrom student as s left join sc on sc.sid=s.sid group by s.sid,s.Sname
4.1 查有成绩的学生信息select distinct s.*from student as s,sc where sc.score is not null and sc.sid=s.sid 
5. 查询「李」姓老师的数量 
select count(Tname) from teacher where Tname like "李%"
6. 查询学过「张三」老师授课的同学的信息 select s.sid,s.Sname,s.Sage, s.Ssex from student as s,(select T from teacher where Tname ="张三") as tr,scwhere sc.sid=s.sid and tr.T = sc.c
7. 查询没有学全所有课程的同学的信息select s.*from student as s,(select count(c) as num,sid from sc group by sid having num<3) as sccwheres.sid = scc.sid
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 select distinct s.*from student as sleft join scon sc.sid=s.sid where sc.c in (select c from sc where sid="01")
9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 select s.*,count(sc.c) as u from student as sleft join scon sc.sid=s.sid group by s.sid having u =(select count(c) as num from sc where sid="01") 
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
select s.Sname from student as s where sid not in (select sid from sc,teacher as t,course as ce where t.Tname="张三" and t.T=ce.T and sc.c=ce.c)
select * from sc where c="02"11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid,s.Sname,scc.score_avgfrom student as s,(select count(sid) as cd,sid,avg(sc.score) as score_avg from sc where score <60 group by sid) as sccwhere s.sid = scc.sid and scc.cd>=2
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.* from student as s,(select sid,score from sc where c="01" and score <60) as sccwhere scc.sid = s.sid order by scc.score desc
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select b.c,a.sid,b.score,a.avg_score from (select sid,avg(score)as avg_score from sc group by sid) as a,(select sid,c,score from sc group by c,sid) as bwhere a.sid=b.sid order by a.avg_score desc
14. 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列    select distinct a.c,ce.Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率,ce.T from scleft join course as ce on ce.c = sc.cleft join (select max(score) as 最高分,min(score)as 最低分,avg(score)as 平均分,c from sc group by c) as aon a.c=ce.c left join (select sum(case when score>=60 then 1 else 0 end)/count(score)*100 as 及格率,c from sc group by c) as bon a.c = b.c left join (select c,sum(case when score>=70 and score<80 then 1 else 0 end)/count(score)*100 as 中等率 from sc group by c) as con a.c=c.c left join (select c,sum(case when score>=80 and score<90 then 1 else 0 end)/count(score)*100 as 优良率 from sc group by c) as don a.c=d.c left join (select c,sum(case when score>=90 then 1 else 0 end)/count(score)*100 as 优秀率 from sc group by c) as eon a.c = e.c order by ce.T
select sc.c, ce.Cname,count(sc.sid) as '选修人数', max(sc.score), min(score), avg(score),(sum(case when score>=60 then 1 else 0 end)/count(sc.sid)) as '及格率',(sum(case when score>=70 and score<80 then 1 else 0 end) /count(sc.sid)) as '中等率',(sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.sid)) as '优良率',(sum(case when score>=90 then 1 else 0 end)/count(sc.sid)) as '优秀率'from sc, course as cewhere sc.c=ce.cgroup by sc.corder by count(sc.sid) desc, c;15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select sid,c,score from sc group by c,sid order by c,score desc
select a.sid,a.c,a.score from sc as a left join sc as bon a.sid=b.sid and a.score <b.scoregroup by a.c,a.sid order by a.c,a.score desc
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select a.sid ,a.c, a.score, count( distinct b.score) as rankfrom sc a left join sc b on a.c=b.c and a.score<=b.scoregroup by a.c, a.sidorder by a.c, a.score desc
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sid,sum(score) as num from sc group by sid order by num desc
select a.*,count(a.aj>b.bj)+1 as rank from (select sid,sum(score) as aj from sc group by sid) as aleft join (select sid,sum(score) as bj from sc group by sid) as bon a.aj<b.bjgroup by a.sid order by rank asc
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺select a.*,count(distinct b.bj)+1 as rank from (select sid,sum(score) as aj from sc group by sid) as aleft join (select sid,sum(score) as bj from sc group by sid) as bon a.aj<b.bjgroup by a.sid order by rank asc
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比selectce.T,ce.Cname,(sum(case when score>=85 then 1 else 0 end)/count(distinct sid))as "100-85",(sum(case when score>=70 and score<85 then 1 else 0 end)/count(distinct sid))as "85-70",(sum(case when score>=60 and score<70 then 1 else 0 end)/count(distinct sid))as "70-60",(sum(case when score<60 then 1 else 0 end)/count(distinct sid))as "60-0" from sc,course as cewhere ce.c= sc.cgroup by sc.c
-- select sc.c,Cname,count(sid) as '总人数',-- concat(round(sum(case when score>=0 and score<60 then 1 else 0 end) /count(sid)*100,2),'%') as '0-60',-- concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sid)*100,2),"%") as '60-70',-- concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(sid)*100,2),"%") as '70-85',-- concat(round(sum(case when score<=100 and score>=85 then 1 else 0 end )/count(*)*100,2),'%') as '85-100'-- from sc join course on sc.c=course.c-- group by c;18. 查询各科成绩前三名的记录select * from (select a.*, count(distinct b.score) +1 as rankfrom sc aleft join sc bon a.c=b.c and a.score<b.scoregroup by a.c, a.sidorder by a.c, a.score desc) cwhere c.rank<=3;
19. 查询每门课程被选修的学生数
select count(sid)as 选修的学生数,c from sc group by c
20. 查询出只选修两门课程的学生学号和姓名 
select * from student as s,(select count(sid) as ce,sid from sc group by sid having ce=2) as cf where cf.sid=s.sid
21. 查询男生、女生人数
select sum(case when Ssex="男" then 1 else 0 end)as 男生,sum(case when Ssex="女" then 1 else 0 end)as 女生from student
22. 查询名字中含有「风」字的学生信息
select * from student where Sname like "%风%"
23. 查询同名同性学生名单，并统计同名人数
select Ssex,Sname,count(*) as 人数from student group by Ssex,Sname having 人数>=2

24. 查询 1990 年出生的学生名单
select * from student where Sage like "1990%"
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c,avg(score) as ag from sc group by c order by ag desc,c asc;

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
select s.sid,s.Sname,平均成绩 from student as s, (select avg(score) as 平均成绩,sid from sc group by sid having 平均成绩>=85) as sccwhere s.sid =scc.sid
27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 
select s.Sname,sc.score from student as s,sc,(select c from course where Cname ="数学") as numberwhere s.sid=sc.sid and sc.c=number.c and sc.score<60;

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select distinct s.* ,sc.c,sc.score from student as sleft join sc on s.sid= sc.sid

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.Sname,ce.Cname,sc.score from student as s left join sc on s.sid = sc.sidleft join course as ceon ce.c=sc.cwhere sc.score>70group by sc.c,s.sid

30. 查询不及格的课程
select s.Sname,ce.Cname,sc.score from student as s left join sc on s.sid = sc.sidleft join course as ceon ce.c=sc.cwhere sc.score<60group by sc.c,s.sid

select ce.Cname,sc.score from sc left join course as ceon ce.c=sc.cwhere sc.score<60group by sc.c

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.sid,s.Sname from student as s,sc where c="01" and score >80 and s.sid=sc.sid;
32. 求每门课程的学生人数 
select count(distinct sid) as 学生人数,c from sc group by c;
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,sc.score from student as sleft join sc on sc.sid=s.sidleft joincourse as ce on ce.c=sc.c left join teacher as tron tr.T=ce.Twhere tr.Tname="张三"order by sc.score desc limit 1
*34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,a.score from sc as aleft join sc as b on a.c=b.c and a.score<b.score inner join course as ce on a.c=ce.c inner join teacher as tr on tr.T = ce.T and tr.Tname="张三"inner join student as s on s.sid=a.sidgroup by a.c,a.sidhaving count(distinct b.score)=0

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select sc.* from sc join (select *,count(sid) as num from sc group by score having num>1) as aon sc.c=a.c and sc.score = a.scoreorder by sc.score;
36. 查询每门功成绩最好的前两名
select a.*,count(distinct b.score)+1 as rank from sc as a left join sc as b on a.c= b.c and a.score<b.score group by a.sid,a.c having rank <=2 order by a.c 
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计。
select c,count(c) as num from sc group by c having num >5
38. 检索至少选修两门课程的学生学号 
select *,count(c) as num from sc group by sid having num >1

39. 查询选修了全部课程的学生信息
select s.* from student as s ,scwhere s.sid =sc.sid group by s.sid having count(*) = (select count(*) from course)
-- select *,count(c) as num from sc group by sid having num >2
40. 查询各学生的年龄，只按年份来算 
select *,(year(now())- year(Sage)) as 年龄 from student
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select timestampdiff(year,sage,now()) from studentselect student.sid, student.sname,student.ssex, sage,
timestampdiff(year,sage,now()) as '按月日计算', # 出生月日< 当前日期的月日时，年龄会减一 ,该题目功能是通过这一句实现的，下一句只是为了对比说明两者之间的差别year(now())-year(sage) as '按年份计算' from student; 
42. 查询本周过生日的学生
select * from student where week(concat_ws('-',year(now()),date_format(Sage,'%m-%d')))=week(now());
43. 查询下周过生日的学生
select * from student where week(concat_ws('-',year(now()),date_format(Sage,'%m-%d')))=week(now())+1;
44. 查询本月过生日的学生select *from studentwhere month(student.sage)=month(now());45. 查询下月过生日的学生select *from studentwhere month(student.sage)=month(now())+1;