sql 经典练习 ()
--1. 查询01 课程比02 课程 成绩高的学生以及课程分数.
--查询课程1，和分数，
select * from SC sc
where sc.CId='01'
---查询课程2 和分数 ，
select * from SC sc2
where sc2.CId='02'
select * from Student RIGHT JOIN (
select t1.SId,t1.class1,t2.class2 from
(select sc.SId,sc.score as class1 from SC sc where sc.CId='01' )as t1,
(select sc2.SId,sc2.score as class2 from SC sc2 where sc2.CId='02') as t2
where t1.SId=t2.SId and t1.class1>t2.class2
) r
on Student.SId = r.SId;
---2.平均分 高于60分的同学的情况
select s.Sname,s.SId ,k.平均分 from Student s
inner join
(select sc.SId,AVG(sc.score) as 平均分
from SC sc
group by sc.SId
)k
on s.SId=k.SId
--3. 成绩表中，存在成绩的学生的信息
select s.* from student s inner join
(select distinct sc.SId from SC sc
where sc.score is not null ) k
on s.SId=k.SId
---4. 所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.SId,s.Sname,k.countCorse,k.scoreSum from Student s
inner join
(
select sc.SId,COUNT(sc.CId) as countCorse,SUM(sc.score)scoreSum from SC sc
group by sc.SId
) k
on s.SId=k.SId
---4-1. 有成绩的学生信息
select * from Student s
where s.SId in(select SC.SId from SC )
select * from Student s
where exists (select * from SC sc where sc.SId=s.SId )
---5.查询姓李的老师的数目
select count(1) from Teacher t
where t.Tname like '李%'
group by t.TId
--6. 听过张三老师课的学生的信息
--(1)张三老师教过的课
select c.CId from Course c,Teacher t
where c.TId=t.TId and t.Tname='张三'
--(2)听过这门课的学生的信息
select * from Student s, SC sc
where sc.CId in(
select c.CId from Course c,Teacher t
where c.TId=t.TId and t.Tname='张三'
)
and s.SId=sc.SId
---连接查询方法
select s.* from Student s,SC sc,Course c,Teacher t
where c.TId=t.TId and
c.CId=sc.CId and
sc.SId=s.SId
and t.Tname='张三'
--7 没有学完 全部课程的人的信息 ：
--先找出 学完全部课程的人的姓名。然后not in
select * from Student s
where s.SId not in(
select sc.SId from SC sc
group by sc.SId
having count(sc.CId)=( select count(1) from Course)
--8. 求出和01同学 至少有一门课 都是一起学过的人
---先求 01 同学的课程
--然后求 修改这些课程的学生的id
--然后连表查询
--求s1的同学学过的课.
select s.* from Student s
inner join
(
select distinct sc2.SId from SC sc2 where sc2.CId in(
select sc.CId from SC sc where sc.SId='01')
) k
on s.SId=k.SId
---11. 查询两门课低于 60分的学生的信息，和平均分
---12 .01课程 小于60分的人， 降序排列
--先查出 01 小于60分的 sid
select s.* from Student s ,SC sc
where sc.CId='01'and
sc.score<60
and s.SId=sc.SId
order by sc.score desc
---13.按照平均成绩的高到低，求出 学生信息，平均成绩，总成绩
select s.Sname,k.theAvg,k.theSum from Student s
inner join
(select sc.SId,AVG(sc.score) as theAvg ,SUM(sc.score) theSum from SC sc
group by sc.SId)k
on s.SId=k.SId
order by k.theAvg desc
---19,查询每门课程 选修的学生数
select cid, count(sc.SId)
from SC sc
group by sc.CId
---20.只修2门课的学生名字，和学生id
select s.* from Student s
inner join
(select sc.SId theid from SC sc
group by sc.SId
having count(sc.CId) =2 ) k
on s.SId=k.theid
--21查询男生人数，女生人数
select COUNT(s.Ssex),s.Ssex from Student s
group by s.Ssex
---22.查询含有风字的学生信息
select * from Student s
where s.Sname like '%风%'
--23 查询同名的学生 的所有信息
select * from Student s
where s.Sname in
(
select s.Sname from Student s
group by s.Sname
having count( s.Sname)>1
)
---24 1900年出生的人
select * from Student s
where YEAR( s.Sage)=1990;
---26 平局分大于85的 人的姓名 ，平均分
select s.Sname, s.SId,k.avgScore from Student s
inner join
(
select sc.SId theid,AVG(sc.score) avgScore from SC sc
group by sc.SId
) k
on s.SId=k.theid
---27 数学低于60的名字，和分数
--方法1：
select * from Student s
where s.SId in
(
select sc.SId from Course c,SC sc
where c.Cname='数学' and sc.CId=c.CId and sc.score<60
)
--方法2：
select s.* from Student s,Course c,SC sc
where s.SId=sc.SId and
sc.CId=c.CId and
c.Cname='数学' and
sc.score<60
--28 所有学生，所有课程的 分数。 允许没成绩
select s.Sname,sc.CId,sc.score from Student s
inner join SC sc on s.SId=sc.SId
--29 有一门课 是70分以上的学生的 姓名、课程名、分数
--（1）先找出70分以上的，sid，课程名，分数
select sc.SId, c.Cname,sc.score
from SC sc,Course c
where sc.CId=c.CId and sc.score>70
---综合
select s.Sname,c.Cname,sc.score from Student s ,SC sc,Course c
where sc.CId=c.CId and sc.score>70 and s.SId=sc.SId
--30 存在不合格成绩的课程
select sc.CId,count(*)
from SC sc
where sc.score<60
group by sc.CId
--31. 01课程在 80分以上的 学生名字，学号
select s.* from SC sc,Student s
where sc.score>78 and sc.CId='01'and
s.SId=sc.SId
---32 每门课的学生数
select sc.CId, count(sc.SId)
from SC sc
group by sc.CId
---37 每门课的选修人数，大于5才统计
select sc.CId,count(sc.SId)
from SC sc
group by sc.CId
---38.选修了2门课的学生
select sc.SId ,count(sc.CId) from SC sc
group by sc.SId
having count(sc.CId)>2
---39 选修了全部课程的 学生名称
select * from Student s
where s.SId in (
select sc.SId
from SC sc
group by sc.SId
having count(sc.CId)=(select count(*) from Course)
)
参考https://www.jianshu.com/p/476b52ee4f1b
