在MySchool数据库中有俩个表：Student和Score(相关例题)
Student：
Score
1.查询年龄超过20岁的姓名\年龄\所在班级
select
sName,
sAge,
cName
from Student as TS
inner join Class TC
on TC.clsId=TS.sClassId
where sAge>20
------------------------------------------------
2.查询出所有参加考试的同学的学生编号，姓名，考试成绩。
select
sId,
sName,
english,
math
from Student
inner join Score on Score.studentId=Student.sId
------------------------------------------------
3.查询出所有没有参加考试的同学的学生编号，姓名，考试成绩。
select
sId,
sName
from Student
where sId not in(select studentId from Score)
------------------------------------------------
4使用联接重做：查询出所有没有参加考试的同学的学生编号，姓名。
--(1)
select sId,sName
from (
select sId,sName,sAge,score.*
from Student left outer join Score
on Score.studentId=Student.sId) as TB1
where scoreId is null
--(2)
select sId,sName
from (
select sId,sName,sAge,score.*
from Score right outer join Student
on Score.studentId=Student.sId) as TB1
where scoreId is null
------------------------------------------------
5、查询所有英语及格的学生姓名、年龄及成绩
select
sName,
sAge,
english
from Student
inner join Score
on Score.studentId=Student.sId
where english>=60
------------------------------------------------
6、查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩
select
sName,
sAge,
english,
math
from Student
inner join Score
on Student.sId=Score.studentId
where english is not null
------------------------------------------------
7、查询所有学生(报考的和未报考的)的学生姓名、年龄、成绩，如果报考了，但是没有参加考试显示缺考,如果小于english&math小于60分显示不及格，如果没有报考显示没有报考（添加两列 ，“是否报考”，“是否合格”）
select
sName,
sAge,
case
when english IS null then '缺考'
else CONVERT(varchar(50),math)
end,
是否报考=
case
when scoreId IS null then '未报考'
else '已报考'
end,
是否合格=
case
when english>=60 and math>=60 then '合格'
else '不合格'
end
from Student
left join Score
on Score.studentId=Student.sId
