SQL面试题（16 - 25）oracle写的

建的表在这里
https://mp.csdn.net/mdeditor/102653936#

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、3号课的平均成绩；

insert into sc
select distinct(sid),(select avg(score) from sc where cid ='003')  
from sc where sid not in (select sid from sc where cid = '003');

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,数据库,企业管理,英语,有效课程数,有效平均分。

select sid,
       sum(decode(cid,(select cid from course where cname = '数据库'),score,0)) 数据库,
       sum(decode(cid,(select cid from course where cname = '企业管理'),score,0)) 企业管理,
       sum(decode(cid,(select cid from course where cname = '英语'),score,0)) 英语,
       count(*) 有效课程数,
       to_char(avg(score),'fm9999.00') 有效平均分
from sc
where cid in (select cid from course where cname = '数据库' or cname = '企业管理' or cname ='英语')
group by sid
order by avg(score) desc

这题如果答案是类似于这样

的话，答案应该是错的，因为不是所有学生都有所有课,没有这个课的成绩应该赋值为0,
然后我的答案也有点问题，因为没有显示所有学生，只有三门课至少有一门课的才会显示。

2019-10-30更新：

select sid,
       sc1 数据库,
       sc2 企业管理,
       sc3 英语,
       cnt 有效课程数,
       (case when cnt > 0 then round((sc1+sc2+sc3)/cnt,2) else 0 end) 有效平均分
from
(
       select sid,
         sum(decode(cname,'数据库',sc.score,0)) sc1,
         sum(decode(cname,'企业管理',sc.score,0)) sc2,
         sum(decode(cname,'英语',sc.score,0)) sc3,
         count(case when cname in('数据库','企业管理','英语') then 1 end) cnt
       from sc,course
       where sc.cid = course.cid
       group by sid
)t
order by (case when cnt > 0 then round((sc1+sc2+sc3)/cnt,2) else 0 end)

结果

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cid 课程ID,max(score) 最高分,min(score) 最低分 
from sc
group by cid

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select cid 课程ID,to_char(round(avg(score),2),'fm9999.00') 平均成绩,
       to_char(round(100*sum(case when score > 60 then 1 else 0 end)/count(*),2),'fm9999.00')||'%' 及格率
from sc
group by cid
order by avg(score) asc,
         sum(case when score > 60 then 1 else 0 end)/count(*) desc

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），UML （003），数据库（004）

select cname,to_char(round(avg(score),2),'fm9999.00') 课程平均成绩,
      to_char(round(100*sum(case when score > 60 then 1 else 0 end)/count(*),2),'fm9999.00')||'%' 及格率
from sc,course
where sc.cid = course.cid
and cname in ('企业管理','马克思','UML','数据库')
group by cname

数据库里没插马克思和UML的数据所以没有

21、查询不同老师所教不同课程平均分从高到低显示

select tname,cname,to_char(avg(score),'fm9999.00') 平均分 
from sc,course,teacher
where sc.cid = course.cid 
and course.tid = teacher.tid
group by tname,cname
order by avg(score)

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）

select * from 
(     select sc.sid,sname,cname,score,row_number() over(partition by cname order by score desc) r
      from sc,student,course
      where student.sid(+) = sc.sid    --右边的表会全部显示                           
      and sc.cid = course.cid 
      group by sc.sid,sname,cname,score
)  t
where t.r >= 3 and t.r <= 6

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select sc.cid,
       cname,
       sum(case when score <= 100 and score > 85 then 1 else 0 end) as "[100-85]",
       sum(case when score <= 85  and score > 70 then 1 else 0 end) as "[85-70]",
       sum(case when score <= 70 and score > 60 then 1 else 0 end) as "[70-60]",
       sum(case when score <= 60  and score >= 0 then 1 else 0 end) as "[<=60]"
from sc,course
where sc.cid = course.cid
group by sc.cid,cname

24、查询学生平均成绩及其名次

select sc.sid,
       sname,
       to_char(avg(score),'fm9999.00') 平均分,
       rank() over(order by avg(score) desc) 名次
from sc left join student
on sc.sid = student.sid 
group by sc.sid,sname;  

select *from sc left join student on  sc.sid = student.sid;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

select * from 
(     select sc.sid,sname,cname,score,row_number() over(partition by cname order by score desc) r
      from sc,student,course
      where student.sid(+) = sc.sid
      and sc.cid = course.cid 
      group by sc.sid,sname,cname,score
)  t
where t.r <= 3

不一定都对。有错还请指出。