我做的超经典sql练习

首先初始化一波：

student表：

图片.png

teacher表：

图片.png

course表：

图片.png

sc表：

图片.png

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

select
stu.*
from
(select
A.*
from
(select * from sc where cid='01')A 
INNER JOIN
(SELECT * from sc where cid='02')B
on A.sid = B.sid
where A.score>B.score) A 
LEFT JOIN
student stu
on A.sid=stu.sid
;

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

select
A.*
from
(select * from sc where cid='01') A
INNER JOIN
(SELECT * from sc where cid='02')B
on A.sid=B.sid

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

select
A.*,B.*
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 "课程的情况

SELECT
sid
from sc
where
cid='02'
and
sid
not in (select sid from sc where cid='01')

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

select
sid,
avg(score) avg_score
from
sc
GROUP BY sid HAVING avg_score>60;

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

select * from student where sid in
(select max(sid) from sc GROUP BY sid)
;

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

select
X.*,Y.score
from
(select
A.*,B.*
from student A,course B) X
LEFT JOIN
sc Y on X.cid=Y.cid and X.sid=Y.sid

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

select
count(1)
from teacher
where Tname like '李%';

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

select *
from student
where sid in
(
(select
sid
from
sc where cid in(
(select 
cid
from
course
where tid in
(SELECT tid from teacher where Tname='张三')))));

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

select
A.*
from
student A INNER JOIN
(select sid from
sc
group by sid having count(1)<3) B
on A.sid=B.sid

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

select DISTINCT sid from
sc 
where sid!='01' and cid in
(select cid from sc 
where sid='01')

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

select 
*
from
sc
GROUP BY sid
HAVING count(1)=3 and sid not in(
select
sid
from
sc where cid
not in(select
cid
from
sc where sid='01'))

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

SELECT * from student
where sid not in(
select 
sid
from
sc
where
cid in(
select cid from course
where tid in(
select 
tid
from teacher where Tname='张三')))

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

select 
sid,
avg(score)
from
sc where sid in
(select
sid
from
sc where score<60 group by sid)
GROUP BY sid

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

select * from
sc where cid='01' and score<60 
order by score desc;

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

select 
A.*,B.avg_score
from
sc A INNER JOIN
(select sid,avg(score) avg_score from
sc group by sid
) B on A.sid=B.sid order by B.avg_score desc

17.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

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

select
cid,
max(score) max,
min(score) min,
avg(score) avg_score,
sum(case when score>60 then 1 else 0 end)/count(1) as '及格率',
sum(case when 70<=score<80 then 1 else 0 end)/count(1) as '中等率',
sum(case when 80<=score<90 then 1 else 0 end)/count(1) as '优良率',
sum(case when score>=90 then 1 else 0 end)/count(1) as '优秀率'
from
sc
GROUP BY cid

18.查询各科成绩前三名的记录（取 a 的最高分与本表比较）

select
A.*
from
sc A left JOIN sc B
on A.cid=B.cid and A.score<B.score 
GROUP BY A.cid,A.sid HAVING count(1)<3
order by A.cid,A.score desc

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

select
* from
student
where
sid in
(select
sid
from
sc
group by sid 
HAVING count(1)=2)

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

select
A.*,B.同名人数
from
student A RIGHT JOIN
(select 
sid,count(1) as '同名人数'
from student GROUP BY name HAVING 同名人数>1) B
on A.sid=B.sid

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

select
*
from student
where YEAR(age)=1990

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

select
cid,
avg(score) avg_score
from
sc
group by cid 
order by avg_score desc,cid asc

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

select
A.*,B.avg_score
from
student A
RIGHT JOIN
(select 
sid,
avg(score) avg_score
from
sc
GROUP BY sid
HAVING avg_score>=85 )B
on A.sid=B.sid

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

select 
X.name,Y.*
from
student X RIGHT JOIN
(select
B.*,A.sid,A.score
from
sc A RIGHT JOIN
(select
cid,Cname
from
course
where Cname ='数学') B
on A.cid=B.cid
where A.score<60) Y
on X.sid=Y.sid

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

select
*
from
(select
A.sid,A.name,B.cid,B.Cname
from
student A,course B) X
LEFT JOIN sc Y
on X.cid=Y.cid and X.sid=Y.sid

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

select
A.name,B.*
from
student A RIGHT JOIN
(select
sid,cid,max(score) max_score
from
sc
where cid in
(select
cid
from course
where tid in
(select
tid
from
teacher where Tname='张三'))
GROUP BY cid) B
on A.sid=B.sid

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

select
A.*
from
sc A RIGHT JOIN
(SELECT
cid,max(score) max_score
from sc
where cid in
(select
cid
from course
where tid in
(select
tid
from
teacher
where Tname='张三'))
GROUP BY cid) B
on A.cid=B.cid and A.score=B.max_score

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

select 
A.*
from
sc A,sc B
where A.cid=B.cid and A.score=B.score
group by A.cid,A.sid
HAVING count(1)>1

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

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

select
cid,count(1) count
from sc
GROUP BY cid
HAVING count>5
order by count desc,cid

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

select
sid
from
sc
GROUP BY sid
HAVING count>=2

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

select
sid,count(1) count
from
sc
GROUP BY sid
HAVING count=
(select count(1) from course)

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

select
sid,name,year(now())-year(age) as age 
from
student

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

select
sid,name,TIMESTAMPDIFF(year,age,now()) as '年龄'
from
student

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

select
sid,name
from
student
where week(now())=week(age)

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

select
sid,name
from
student
where (week(now())+1)=week(age)

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

select
sid,name
from
student
where month(now())=month(age)

37.查询各科成绩前三名的记录(用mysql8.0的引入的窗口函数)

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

演示一下3种常见的窗口函数：

select
*
from
(select 
*,rank() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank
图片.png
select
*
from
(select 
*,dense_rank() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank
图片.png
select
*
from
(select 
*,row_number() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank
图片.png
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,406评论 6赞 503
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,732评论 3赞 393
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,711评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,380评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,432评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,301评论 1赞 301
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,145评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,008评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,443评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,649评论 3赞 334
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,795评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,501评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,119评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,731评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,865评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,899评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,724评论 2赞 354