六道经典SQL语句题完全掌握SQL语句
学生表S （SNO 学号， Sname 姓名， Class 班级, Shigh 身高）
课程表C（CNO 课程号， CName 课程名， CPreNO 预修课程编码）
成绩表SC（CNO 课程号， SNO 学号， SCgrade 成绩）
1、体育生选拔，要求身高在170cm以上，考试不及格课程在3门以下（不包括3门）平均成绩在60分以上。 www.2cto.com
学号 姓名 身高 平均成绩
2、所有预选课程都已经合格的学生（预选课程不存在嵌套循环的情况）。
3、同时选修了‘离散数学’与‘组成原理’的，且高等数学成绩大于80分的。
学号 姓名 离散数学 组成原理 高等数学
分数 分数 分数
4、列出各门课程前三名（成绩相同，学号小的排名在前）
第一名 第二名 第三名
课程名 班级：姓名：成绩 班级：姓名：成绩 班级：姓名：成绩
5、统计各科的学习情况。
课程名 60以下 [60，70） [70，85） [85，100）
6、按班级统计各科平均成绩。
课程名 第一 第二 第三
班级：平均成绩 班级：平均成绩 班级：平均成绩
7、按班级统计各科平均成绩后，第一名3分，第二名2分，第三名1分，其他计0.5分（可以并列名次），最后给班级排名。（最后成绩一样，按班级序号小的排前）
第一名 第二名 第三名
[sql]
--第1题
select td_b.SNO 学号, td_b.SNAME 姓名, td_b.SHIGH 身高, td_a.avgGrade 平均成绩 from
(select S.SNO,avg(SCgrade) as avgGrade from S,SC
where www.2cto.com
S.SNO = SC.SNO and
S.Shigh>170
group by S.SNO
having
avg(SCgrade)>60) td_a
left join(
select * from S where not exists (
select a.sno from S a, SC b where
a.sno = b.sno
and b.scgrade<60
group by a.sno
having count(*) < 3)
) td_b
on td_a.SNO = td_b.SNO
--第2题
--第3题
select distinct S1.SNO as 学号, S1.SNAME as 姓名,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='离散数学' and SC.SNO = SC1.SNO) as 离散数学,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='组成原理' and SC.SNO = SC1.SNO) as 组成原理,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='高等数学' and SC.SNO = SC1.SNO) as 高等数学 www.2cto.com
from SC SC1, S S1
where
SC1.SNO = S1.SNO
and S1.SNO in
(
select S.SNO from S,C,SC
where
S.SNO = SC.SNO and
SC.CNO = C.CNO and
C.CNAME='离散数学' and
S.SNO in (select t1.SNO from SC t1, C t2 where t1.CNO=t2.CNO and t2.cname='组成原理') and
S.SNO in (select t3.SNO from SC t3, C t4 where t3.CNO=t4.CNO and t4.Cname='高等数学'and t3.scgrade>80)
)
----另解
select distinct s.sno,s.sname,
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='离散数学' and sc.sno=sc1.sno)"离散数学",
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='组成原理' and sc.sno=sc1.sno) "组成原理",
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.sno=sc1.sno) "高等数学"
from s,sc sc1,C
where sc1.sno=s.sno
and s.sno in
(
select sno from sc left join c on sc.cno=c.cno where c.cname='离散数学' intersect
select sno from sc left join c on sc.cno=c.cno where c.cname='组成原理' intersect
select sno from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.scgrade>80) www.2cto.com
--第4题
select C.CNAME as 课程名,
max(case when ro=1 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第一名,
max(case when ro=2 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第二名,
max(case when ro=3 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第三名
from
(
select td_a.CNO, td_a.SNO, td_a.class, td_a.SCgrade, row_number() over(partition by CNO order by SCgrade desc) ro
from
(select CNO, S.SNO, Class, SCgrade
from S inner join SC
on S.SNO = SC.SNO
group by CNO, S.SN