【笔试学习工作必备！】六道经典SQL语句题完全掌握SQL语句
学生表S （SNO 学号， Sname 姓名， Class 班级, Shigh 身高）
课程表C（CNO 课程号， CName 课程名， CPreNO 预修课程编码）
成绩表SC（CNO 课程号， SNO 学号， SCgrade 成绩）
1、体育生选拔，要求身高在170cm以上，考试不及格课程在3门以下（不包括3门）平均成绩在60分以上。
学号 姓名 身高 平均成绩
2、所有预选课程都已经合格的学生（预选课程不存在嵌套循环的情况）。
3、同时选修了‘离散数学’与‘组成原理’的，且高等数学成绩大于80分的。
学号 姓名 离散数学 组成原理 高等数学
分数 分数 分数
4、列出各门课程前三名（成绩相同，学号小的排名在前）
第一名 第二名 第三名
课程名 班级：姓名：成绩 班级：姓名：成绩 班级：姓名：成绩
5、统计各科的学习情况。
课程名 60以下 [60，70） [70，85） [85，100）
6、按班级统计各科平均成绩。
课程名 第一 第二 第三
班级：平均成绩 班级：平均成绩 班级：平均成绩
7、按班级统计各科平均成绩后，第一名3分，第二名2分，第三名1分，其他计0.5分（可以并列名次），最后给班级排名。（最后成绩一样，按班级序号小的排前）
第一名 第二名 第三名
--第1题select td_b.SNO 学号, td_b.SNAME 姓名, td_b.SHIGH 身高, td_a.avgGrade 平均成绩 from(select S.SNO,avg(SCgrade) as avgGrade from S,SCwhere S.SNO = SC.SNO and S.Shigh>170group by S.SNOhaving avg(SCgrade)>60) td_aleft 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_bon 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 高等数学 from SC SC1, S S1where SC1.SNO = S1.SNOand S1.SNO in(select S.SNO from S,C,SCwhere S.SNO = SC.SNO and SC.CNO = C.CNO and C.CNAME='离散数学' andS.SNO in (select t1.SNO from SC t1, C t2 where t1.CNO=t2.CNO and t2.cname='组成原理') andS.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,Cwhere sc1.sno=s.snoand s.sno in(select sno from sc left join c on sc.cno=c.cno where c.cname='离散数学' intersectselect sno from sc left join c on sc.cno=c.cno where c.cname='组成原理' intersectselect sno from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.scgrade>80) --第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) rofrom (select CNO, S.SNO, Class, SCgrade from S inner join SC on S.SNO = SC.SNO group by CNO, S.SNO, Class, SCgrade order by S.SNO)td_a)td_b , C, Swhere td_b.cno = C.CNO and td_b.sno = S.SNOgroup by CNAME--第5题select c.cname "课程号",sum(case when (scgrade<60) then 1 else 0 end) "60分以下",sum(case when (scgrade<70 and scgrade>=60) then 1 else 0 end) "[60,70)",sum(case when (scgrade<85 and scgrade>=70) then 1 else 0 end) "[70,85)",sum(case when (scgrade<=100 and scgrade>=85) then 1 else 0 end) "[85,100]"from sc,cwhere sc.cno=c.cnogroup by c.cnameorder by c.cname--第6题select CNAME 课程名, max(case when ro=1 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第一, max(case when ro=2 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第二, max(case when ro=3 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第三from(select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)rofrom (select distinct CNO, Class, avg(SCgrade)K_avg from S inner join SC on S.SNO = SC.SNO group by CNO, class order by class)td_a)td_b, Cwhere td_b.CNO = C.CNOgroup by CNAMEorder by CNAME--第7题----没有max选出来有三行数据（td_e有三行数据），有了max就只有一行数据了正为所有数据select max(case when rownum=1 then td_d.class else null end) 第一名, max(case when rownum=2 then td_d.class else null end) 第二名, max(case when rownum=3 then td_d.class else null end) 第三名from(select td_c.class, sum1+sum2+sum3+sum4 as tscorefrom(select td_b.class, sum(case when ro=1 then 3 else 0 end) as sum1, sum(case when ro=2 then 2 else 0 end) as sum2, sum(case when ro=3 then 1 else 0 end) as sum3, sum(case when ro<>1 and ro<>2 and ro<>3 then 0.5 else 0 end) as sum4from(select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)rofrom (select distinct CNO, Class, avg(SCgrade)K_avg from S inner join SC on S.SNO = SC.SNO group by CNO, class order by class)td_a)td_bgroup by td_b.classorder by td_b.class)td_corder by tscore desc)td_d
