oracle数据库学习（教师，学生，课程，成绩）实例问题之（二）

11.查询至少有一门课与学号为"1001"的同学所学相同的同学的信息

select  distinct s.* from student s,
(select sid from sc
where cid in
(select cid from sc
where sid='1001')
and sid!='1001'
) a
where a.sid=s.sid

12.查询和"1001"号的同学学习的课程完全相同的其他同学的信息

思路： cid in(sid=1001的cid)且count(a.cid)课程门数=count(sid=1001的cid)

select  s.* from student s,
(select sid,count(cid) from sc
where sid!='1001'
and cid in(select cid from sc
where sid='1001')
group by sid
having count(cid)=(select count(cid) from sc where sid='1001')
) a
where s.sid=a.sid

13.查询没学过"叶平老师的任意一门课的学生姓名

select distinct s.sname from student s,
(select sid from sc
where cid not in
(select cid from course c,teacher t
where t.tid=c.tid
and tname='叶平')) a
where a.sid=s.sid

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

思路：先查找出两门课不及格学生的学号 再求平均分

select s.sid,sname,avg1 from student s,
(select sid,round(avg(score)) as avg1 from sc
where sid in(select sid from sc where score<60
group by sid
having count(cid)>=2)
group by sid
) a
where a.sid=s.sid

15.检索"01"课程分数小于60，按分数降序排列的学生信息

select s.* from student s,sc
where score<60
and cid='001'
and s.sid=sc.sid
order by score

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

思路：多表连接

select distinct s.sid,sc.cid,sname,sc.score,a.avg1 from student s,sc,
(select sid,round(avg(score)) as avg1 from sc
group by sid
) a
where s.sid=sc.sid
and sc.sid=a.sid
and s.sid=a.sid
order by avg1

17.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

思路：cast(select count()/select count() as decimal) 求比率

select c.cid,cname,a.max1,a.min1,a.avg1,
cast((select count(1) from sc where cid=c.cid and score>=60)/(select count(1) from sc where cid=c.cid) as decimal(18,2)) as 及格率 ,
cast((select count(1) from sc where cid=c.cid and score>=70 and score<80)/(select count(1) from sc where cid=c.cid) as decimal(18,2))as 中等率,
cast((select count(1) from sc where cid=c.cid and score>=80 and score<90)/(select count(1) from sc where cid=c.cid) as decimal(18,2)) as 优良率,
cast((select count(1) from sc where cid=c.cid and score>=90)/(select count(1) from sc where cid=c.cid) as decimal(18,2)) as 优秀率
from course c,
(select cid, max(score) as max1,min(score) as min1,round(avg(score)) as avg1 from sc
group by cid) a
where a.cid=c.cid

18.按各科成绩进行排序，并显示排名

思路：rank() over

select sname,sc.cid,rank() over(partition by cid order by score desc) from student,sc
where sc.sid=student.sid

19.查询学生的总成绩并进行排名

思路：先查询总成绩在排名

select s.sid,sum1,rank() over(order by sum1 desc) from student s,
(select sid, sum(score) as sum1 from sc
group by sid) a
where a.sid=s.sid

20.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

思路：嵌套row_number() over() 进行分组排名再取m-n条数据

select sname,s.sid,a.cid,a.score from student s,
(select * from
(select sid,cid,score, row_number() over(partition by cid order by score desc) as rn from sc )
where rn>1 and rn<=3) a
where s.sid=a.sid
order by cid