SQL练习

这学期学习了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