SQL经典50题
时间：2021-07-01 10:21:17
帮助过：6人阅读
.查询"
01"课程比"
02"课程成绩高的学生的信息及课程分数
select *
from
(select t1.sid,class1,class2
from
(select sid,score
as class1
from sc
where cid
=‘01‘ )
as t1
join
(select sid,score
as class2
from sc
where cid
=‘02‘)
as t2
on t1.sid
=t2.sid
and t1.class1
>t2.class2
)r
left join student
on r.sid
=student.sid
;
1.1 查询存在"
01 "课程但可能不存在"
02 "课程的情况(不存在时显示为
null )
select *
from
(select * from sc
where cid
=‘01‘)t1
left join
(select * from sc
where cid
=‘02‘)t2
on t1.sid
=t2.sid
;
1.2 查询同时存在01和02课程的情况
select *
from
(select * from sc
where cid
=‘01‘
)t1
join (
select * from sc
where cid
=‘02‘
)t2
on t1.sid
=t2.sid
;
1.3 查询选择了02课程但没有01课程的情况
select * from sc
where cid
=‘02‘ and sid
not in (
select sid
from sc
where cid
=‘01‘)
;
2.查询平均成绩大于等于
60 分的同学的学生编号和学生姓名和平均成绩
select t1.sid,t1.meanscore,student.sname
from
(select sid,
avg(score)
as meanscore
from sc
group by sid
having avg(score)
>=60
)t1
left join student
on t1.sid
=student.sid
;
3.查询在 SC 表存在成绩的学生信息
select distinct student.
*
from sc
join student
on sc.sid
=student.sid
;
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select r.
*,student.sname
from
(select sid,
count(
distinct cid)
as qcourse,
sum(score)
as sumscore
from sc
group by sid
)r
left join student
on r.sid
=student.sid
;
5.查询「李」姓老师的数量
select count(
*)
from teacher
where tname
like (
‘李%‘)
;
6.查询学过「张三」老师授课的同学的信息
select student.
*
from
(select * from teacher
where tname
= ‘张三‘
)t1
left join course
on t1.tid
=course.tid
left join sc
on course.cid
=sc.cid
left join student
on sc.sid
=student.sid
;
@7.查询没有学全所有课程的同学的信息
select student.
*,qcourse
from
(select sid,
count(
distinct cid)
as qcourse
from sc
group by sid
having count(
distinct cid)
<(
select count(
distinct cid)
from course)
)
left join student
on sc.sid
=student.sid
;
@8.查询至少有一门课与学号为"
01 "的同学所学相同的同学的信息
select student.
*
from sc
join student
on sc.sid
=student.sid
where sc.cid
in (
select cid
from sc
where sid
=‘01‘
)
and sc.sid
!=‘01‘
group by sc.sid
;
@9.查询和"
01 "号的同学学习的课程完全相同的其他同学的信息
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select sname
from student
where sid
not in(
select sid
from sc
left join course
on sc.cid
=course.cid
left join teacher
on course.tid
=teacher.tid
where teacher.tname
!=‘张三‘)
;
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select t1.
*,student.sname
from
(select sid,
avg(score)
as meanscore
from sc
where sid
in (
select sid,
count(
distinct cid)
from sc
where score
<60
group by sid
having count(
distinct cid)
>=2)
)t1
left join student
on t1.sid
=student.sid
;
12.检索"
01 "课程分数小于
60，按分数降序排列的学生信息
select student.
*,t1.score
from
(select *
from sc
where cid
=‘01‘
and score
<60
)t1
left join student
on t1.sid
=student.sid
order by t1.score
desc
;
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.
*,t1.meanscore
from sc
left join
(select sid,
avg(score)
as meanscore
from sc
group by cid
)t1
on sc.sid
=t1.sid
order by t1.meanscore
;
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：
70-80，优良为：
80-90，优秀为：
>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序
排列
select cid
as ‘课程 ID‘,
count(sid)
as ‘课程人数‘,
max(score)
as ‘最高分‘,
min(score)
as ‘最低分‘,
avg(score)
as ‘平均分‘,
cast(
cast sum(及格)
as double)
/(
cast count(sid)
as double)
as ‘及格率‘,
SUM(中等)
/ COUNT(sid)
AS ‘中等率‘,
SUM(优良)
/ COUNT(sid)
AS ‘优良率‘,
SUM(优秀)
/ COUNT(sid)
AS ‘优秀率‘
from
(select *,
case when score
>=60 then 1 else 0 end as ‘及格‘,
case when score
>=70 and score
<80 then 1 else 0 end as ‘中等 ‘,
case when score
>=80 and score
<90 then 1 else 0 end as ‘优良‘,
case when score
>=90 then 1 else 0 end as ‘优秀‘
from sc
)a
group by cid
order by count(sid)
desc,cid
;
@15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select a.
*,
count(a.score)
as ‘排名‘
from sc a
left join sc b
on a.sid
=b.sid
and a.score
< b.score
group by a.cid,a.sid,a.score
order by a.cid
;
--用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。
@15.
1 按各科成绩进行行排序，并显示排名， Score 重复时合并名次
select a.
*,
count(b.score)
+1 as ‘排名‘
from sc a
left join sc b
on a.sid
=b.sid
and a.score
< b.score
group by a.cid,a.sid,a.score
order by a.cid
;
@16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.
*,
@rank:
=@rank+1 as rank
from
(select sid,
sum(score)
from sc
group by sid
order by sum(score)
desc
)a,
(select @rank:
=0
)b
;
17. 统计各科成绩各分数段人数：课程编号，课程名称，
[100-85]，
[85-70]，
[70-60]，
[60-0] 及所占百分比
select a.
*,course.cname
from
(select cid,
sum(
case when score
>=85 and score
<=100 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-75]‘,
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
group by cid
)a
left join course
on a.cid
=course.cid
;
18.查询各科成绩前三名的记录
select a.
*,
count(b.score)
+1 as ranking
from sc
as a
left join sc
as b
on a.cid
=b.cid
and a.score
<b.score
group by a.cid,a.sid
having ranking
<=3
order by a.cid,ranking
--having where不能使用别名,group by order by中可以使用别名
--
select *
from
(select *,row_number()
over(partition
by cid
order by score
desc)
as ranking
from sc)
where ranking
>=3
;
19.查询每门课程被选修的学生数
select cid,
count(
distinct sid)
as num
from sc
group by cid
;
20.查询出只选修两门课程的学生学号和姓名
select sc.sid,student.sname
from sc
left join student
on sc.sid
=student.sid
group by sc.cid
having count(cid)
=2
;
21. 查询男生、女生人数
select count(
case when ssex
=‘男‘ then sid
else null end)
as ‘男生人数‘,
count(
case when ssex
=‘女‘ then sid
else null end)
as ‘女生人数‘
from student
;
22. 查询名字中含有「风」字的学生信息
select *
from student
where sname
like ‘%风%‘
;
23查询同名同性学生名单，并统计同名人数
select sname,
count(ssex)
from student
group by sname
having count(ssex)
>1
;
24.查询
1990 年出生的学生名单
select *
from student
where year(sage)
like‘1990%‘
;
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,
avg(score)
as meanscore
from sc
group by cid
order by meanscore
desc,cid
;
26.查询平均成绩大于等于
85 的所有学生的学号、姓名和平均成绩
select a.sid,student.sname,a.meanscore
from
(select sid,
avg(score)
as meanscore
from sc
group by sid
having avg(score)
>=85
)a
left join student
on a.sid
=student.sid
;
27.查询课程名称为「数学」，且分数低于
60 的学生姓名和分数
select student.sname,b.socre
from
(select cid
from course
where cname
=‘数学‘
)a
left join
(select *
from sc
where score
<60
)b
on a.cid
=b.cid
left join student
on b.sid
=student.sid
;
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select sname,sc.cid,sc.score
from sc
join student
on sc.sid
=student.sid
;
29.查询任何一门课程成绩在
70 分以上的姓名、课程名称和分数
select sname,sc.cid,sc.score
from sc
join student
on sc.sid
=student.sid
where sc.score
>70
;
30.查询不及格的课程
select distinct cid
from sc
where score
<60
;
31.查询课程编号为
01 且课程成绩在
80 分以上的学生的学号和姓名
select sc.sid,sname
from
(select *
from sc
where cid
=‘01‘and score
>80
)a
left join student
on a.sid
=student.sid
;
32.求每门课程的学生人数
select cid,
count(sid)
as cnt
from sc
group by cid
;
33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select student.
*,sc.score
from
(select tid
from teacher
where tname
=‘张三‘
)a
left join course
on a.tid
=course.tid
left join sc
on course.cid
=sc.cid
left join student
on sc.sid
=student.sid
order by sc.score
desc
limit 1
;
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.
*
from sc
as a
join sc
as b
on a.score
=b.score
and a.cid
!=b.cid
;
36. 查询每门成绩最好的前两名
select *
from
(select *,
row_number()over(partition
by cid
order by score
desc)
as ranking
)
where ranking
<=2
;
--方法2自交
select *
from sc
where (
select count(
*)
from sc
as a
where sc.cid
=a.cid
and sc.score
<a.score
)<2
order by cid ,sc.score
desc
;
37. 统计每门课程的学生选修人数（超过
5 人的课程才统计）
select cid,
count(sid)
from sc
group by sc
having count(sid)
>5
;
38.检索至少选修两门课程的学生学号
select cid,
count(sid)
from sc
group by sc
having count(sid)
>2
;
39.查询选修了全部课程的学生信息
select student.
*
from
(select sid
from sc
where cid
=(
select count(
distinct cid)
from course
)
)a
left join student
on a.sid
=student.sid
;
40.查询各学生的年龄，只按年份来算
select sname,
year(now())
-year(age)
as ‘年级‘
from student
;
41. 按照出生日期来算，当前月日
< 出生年月的月日则，年龄减一
select sname,
case when (date_format(now(),
‘%m-%d‘)
-date_format(sage,
‘%m-%d‘)
<0
then year(now())
-year(sage)
+1
else year(now())
-year(sage)
end as ‘年龄‘
from student
;
--date_format转化为时间戳，利用时间戳求差值
42.查询本周过生日的学生
select *
from student
where weekofyear(date_format(now(),
‘%y%m%d‘))
=
weekofyear(date_format(sage,‘%y%m%d‘)
-date_format(sage,
‘%y‘)
*10000+date_format(now(),
‘%y‘)
*10000)
;
--出生日期的年份去掉，得到月份和日期，再拼接上当前的年份
--weekofyear函数是计算出当前日期所在周数
43. 查询下周过生日的学生
select *
from student
where weekofyear(date_format(now(),
‘%y%m%d‘))
+1
=
weekofyear(date_format(sage,‘%y%m%d‘)
-date_format(sage,
‘%y‘)
*10000+date_format(now(),
‘%y‘)
*10000)
;
44.查询本月过生日的学生
select sname
from student
where month(sage)
=month
