50道sql题练习 未更新完

-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80
04 01 50
04 02 30
04 03 20
05 01 76
05 02 87
06 01 31
06 03 34
07 02 89
07 03 98
select stu.*,temp3.score score
from
(select temp1.Sid Sid,temp1.score1 score
from
(select Sid,score score1 from sc where Cid = '01') as temp1
join
(select Sid,score score2 from sc where Cid = '02') as temp2
on temp1.Sid = temp2.Sid where temp1.score1 > temp2.score2
) temp3
join
student stu
on temp3.Sid = stu.Sid

-- 2.查询同时存在" 01 "课程和" 02 "课程的情况
select t1.Sid,t1.Cid,t2.Cid
from
(select Sid,Cid from sc where Cid = '01') as t1
join
(select Sid,Cid from sc where Cid = '02') as t2
on t1.Sid = t2.Sid

-- 3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null
select t1.*,t2.Cid
from
(select Sid,Cid from sc where Cid = '01') as t1
left join
(select Sid,Cid from sc where Cid = '02') as t2
on t1.Sid = t2.Sid

--4.查询不存在" 01 "课程但存在" 02 "课程的情况
select Sid,Cid,score
from sc
where Cid = '02' and Sid not in (select Sid from sc where Cid = '01')

-- 5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select
stu.Sid,stu.Sname,t1.avg_score
from
student stu
join
(select Sid,avg(score) avg_score
from sc
group by Sid
having avg_score > 60) as t1
on stu.Sid = t1.Sid

-- 6.查询在 SC 表存在成绩的学生信息
select stu.*
from student stu
where stu.Sid in (select Sid from sc)

-- 7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select t1.*,t2.count_cid,t2.sum_score
from
(select Sid,Sname from student) as t1
left join
(select Sid,count(Cid) count_cid,sum(score) sum_score from sc group by Sid) as t2
on t1.Sid = t2.Sid

-- 8.查有成绩的学生信息
select * from student
where Sid in(select Sid from sc)

-- 大表使用exists效率更高
select * from student
where exists (select sc.sid from sc where student.sid = sc.sid)

-- 9.查询「李」姓老师的数量
select count(1)
from teacher
where Tname like '李%'

-- 10.查询学过「张三」老师授课的同学的信息
select student.* from teacher tea join course cou on tea.Tid = cou.Tid join sc on cou.Cid = sc.Cid join student on sc.Sid = student.Sid where tea.Tname = '张三'
select student.* from teacher,course,sc,student where teacher.Tid = course.Tid and course.Cid = sc.Cid and sc.Sid = student.Sid and teacher.Tname = '张三'

-- 11.查询没有学全所有课程的同学的信息
select * from student where Sid not in (select Sid from sc group by Sid having count(Cid) = 3)

-- 12.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select Sid,Cid from sc where Sid = '01'