Oracle 基础练习题_学生篇笔记（二）

上文链接：Oracle 基础练习题_学生篇笔记(一)_m0_64778515的博客-CSDN博客

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

select sc2.cno,c.cname,
       sum(case when sc2.score between 85 and 100 then 1 else 0  end) as "[ 100 - 85 ] 人数",
       sum(case when sc2.score between 70 and 85 then 1 else  0 end) as"[ 85 - 70 ] 人数",
       sum(case  when sc2.score between 60 and 70 then  1  else  0 end) as"[ 70 - 60 ] 人数",
       sum(case when sc2.score < 60 then 1 else 0 end) as"[ < 60 ] 人数"
  from sc2, course2 c
 where sc2.cno = c.cno
 group by sc2.cno, c.cname

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

select sno,sum(coo1),sum(coo2),sum(coo3) from (
select sc2.sno,
(decode(sc2.cno,'c001',sc2.score) ) coo1,
(decode(sc2.cno,'c002',sc2.score) ) coo2,
(decode(sc2.cno,'c003',sc2.score) ) coo3
from sc2)
group by sno;
=

select *
  from (select sno,
               cno,
               score,
               row_number() over(partition by cno order by score desc) rn
          from sc2)
 where rn < 4;

--22、查询每门课程被选修的学生数

select cno, count(*) from sc2 group by cno;

--23、查询出只选修了一门课程的全部学生的学号和姓名  

select a.sno, s.sname
  from (select sno, count(*) b from sc2 group by sno) a, student2 s
 where s.sno = a.sno
   and a.b = 1;

--24、查询男生、女生人数

select 
sum(case when ssex='女' then 1 else 0 end) 女生人数,
sum(case when ssex='男' then 1 else 0 end) 男生人数
from student2;

--25、查询姓“张”的学生名单

select * from student2 where sname like '张%';

--26、查询同名同性学生名单，并统计同名人数

select a.sname,count(a.sname)
  from student2 a
 inner join student2 b  on a.sno = b.sno
 where a.sname = b.sname
   and a.sage = b.sage
   and a.sno <> b.sno
   group by a.sname

--27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)

select * from student2 where to_char(sysdate,'yyyy')-sage =1981

注释：将当前时间sysdate 改为文本格式。
 

--28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cno, avg(score) from sc2 group by cno order by avg(score), cno desc;

--29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

select sc2.sno, avg(sc2.score)
  from sc2, student2 s
 where sc2.sno = s.sno
 group by sc2.sno
having avg(sc2.score) > 85;

--30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

select s.sname, sc2.score
  from sc2
 inner join student2 s
    on sc2.sno = s.sno
 inner join course2 c
    on sc2.cno = c.cno
 where c.cname = '数据库'
   and sc2.score < 60;

--31、查询所有学生的选课情况； 
--①查询每个学生选了几门课

select sc2.sno, count(sc2.score) 选课数 from sc2 group by sc2.sno;

--②查询每个学生选了哪几门课

select st.sno,st.sname,c.cname from student2 st,sc2 sc,course2 c
where sc.sno=st.sno and sc.cno=c.cno;

--32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；

select st.sname, c.cname, sc.score
  from student2 st, sc2 sc, course2 c
 where sc.sno = st.sno
   and sc.cno = c.cno
   and sc.score > 70;

--33、查询不及格的课程，并按课程号从大到小排列

select cno,score from sc2 where score<60 order by cno desc;

--34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；

select sc2.sno, s.sname
  from sc2, student2 s
 where sc2.sno = s.sno
   and sc2.score > 80;

--35、求选了课程的学生人数

select count(distinct(sno)) from sc2; 

--36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select s.sname,sc2.cno,max(a.score) from(
select sc2.score,sc2.cno 
from sc2
where cno in (select cno from  course2  where tno in(select tno from teacher2 where tname='谌燕' )))a
inner join sc2  on a.cno=sc2.cno
inner join student2 s on s.sno=sc2.sno
group by s.sname,sc2.cno

--37、查询各个课程及相应的选修人数

select sc2.cno, count(sc2.cno) from sc2 group by sc2.cno;

--38、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select a.*
  from sc2 a, sc2 b
 where a.score = b.score
   and a.cno <> b.cno;

--39、查询每门功课成绩最好的前两名

select * from (
select sno,cno,row_number()over(partition by cno order by score desc ) a from sc2) b
where  b.a<=2;

--40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cno, count(sno)
  from sc2
 group by cno
having count(sno) > 10
 order by count(sno) desc, cno asc;

--41、检索至少选修两门课程的学生学号

select sno from sc2 group by sno having count(sno)>=2； 

--42-1 查询学生选修的所有课程名和课程号

select distinct (c.cno), c.cname
  from course2 c, sc2 sc
 where sc.cno = c.cno

--43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select st.sname
  from student2 st
 where st.sno not in (select distinct sc.sno
                        from sc2 sc, course2 c, teacher2 t
                       where sc.cno = c.cno
                         and c.tno = t.tno
                         and t.tname = '谌燕')

--44、查询两门以上不及格课程的同学的学号及其平均成绩

select sno, avg(score)
  from sc2 sc
 where sno in (select sno
                 from sc2 sc
                where sc.score < 60
                group by sno
               having count(sno) > 1)
 group by sno;

--45、检索“c004”课程分数小于60，按分数降序排列的同学学号

select score from sc2 where cno='c004' and score <60 order by score desc;

--46、删除“s002”同学的“c001”课程的成绩

delete from sc2 where sno='s002' and cno='c001';