【笔试学习工作必备！】六道经典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,SC
where 
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 高等数学   
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) 
      
--第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.SNO, Class, SCgrade
  order by S.SNO)td_a
)td_b , C, S
where td_b.cno = C.CNO and td_b.sno = S.SNO
group 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,c
where sc.cno=c.cno
group by c.cname
order 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)ro
from
  (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, C
where td_b.CNO = C.CNO
group by CNAME
order 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 tscore
from
(
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 sum4
from
(
select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)ro
from
  (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
group by td_b.class
order by td_b.class
)td_c
order by tscore desc
)td_d