【笔试学习工作必备！】六道经典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分（可以并列名次），最后给班级排名。（最后成绩一样，按班级序号小的排前）

第一名第二名第三名

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  
    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