我做的超经典sql练习
2020-10-02 本文已影响0人
一起吃麻辣糖
首先初始化一波：
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
