50道SQL练习题(navicat)（部分）

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

select t1.*,t2.CId ,t2.Score 
from (select sc.* from sc where sc.CId='01') as t1,
(select sc.* from sc where sc.CId='02' ) as t2 
where t1.SId = t2.SId and t1.SId

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select * from (select * from sc where sc.CId='01') as t1 
left join (select * from sc where sc.CId='02')as t2 on t1.SId =t2.SId

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

select student.SId,student.Sname,t1.countCId,t1.sumScore 
from student,(select sc.SId,count(sc.CId)as countCId ,sum(sc.Score)as sumScore 
from sc group by SId  )as t1 where student.SId=t1.SId

5. 查询「李」姓老师的数量

select count(*) from teacher where Tname like '%李%'

6. 查询学过「张三」老师授课的同学的信息

select student.* from student,course,teacher,sc 
where teacher.Tname='张三' 
and course.TId=teacher.TId 
an