这学期学习了MySQL查询语言,感觉理论听得懂但是做题就不会,于是找了一些查询题目来练习,记录一下做题的思路
题目及数据来源
https://blog.csdn.net/qq_45574891/article/details/102559547
1.查询" 01 “课程比” 02 "课程成绩高的学生的信息
#根据返回的子查询中的SId来返回学生信息
select * from student
where SId in( select SId
from (
#将课程01的成绩和课程02的成绩单独做成表后自然链接(根据学号相同自然连接)
(select SId, score as class1_score from sc where CId = "01") as t1
natural join
(select SId, score as class2_score from sc where CId = "02") as t2
)
#筛选符合题中条件的元组
where class1_score > class2_score )
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select SId, Sname, avg_score from (
(select SId, avg(score) as avg_score from sc group by SId) as t1
natural join
student)
where avg_score >= 60
3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.SId, Sname, count_CId, sum_score
from(
#用右外链接保持右表数据元组不变,连接后左表无数据处为null
(select SId, count(CId) as count_CId, sum(score) as sum_score from sc group by SId) as t1
right join
student on t1.SId = student.SId )
4.查询「李」姓老师的数量
select count(*)
from teacher
where Tname like "李%"
5 查询学过「张三」老师授课的同学的信息