Oracle day6

--11.查询选修了‘Java Web’课程的学生姓名及其成绩：
select s.sname,t.score from student s join 
(select sno,score from sc where cno =(select cno from course where cname = 'Java Web')) t
on s.sno = t.sno;

--12.查询各课程最高分的学生信息，及其所教课程老师的名字：
/*select s.*,t4.tname from student s join (
select t2.sno,t2.cno,t3.tname from (
select * from sc where (cno,score) in 
 (select cno,max(score) mscore from sc group by cno) ) t2 join(
select * from teacher t join (
select * from course c where cno in
(select cno from (select cno,max(score) mscore from sc group by cno)))t1
 on t.tno = t1.tno ) t3
 on t2.cno = t3.cno) t4 
 on t4.sno = s.sno; */
 
 select s.* from student s join  (
 select * from sc where (cno,score) in(
select cno,max(score) from sc group by cno)) t on t.sno = s.sno
join course c on c.cno = t.cno
join teacher tc on tc.tno = c.tno;

select s3.cno,s1.*,t.tname from student s1 join sc s2 on s1.sno=s2.sno
join (select cno,max(score) a from sc group by cno) s3 on s2.cno=s3.cno 
join course c on c.cno=s3.cno
join teacher t on t.tno=c.tno
where s2.score=s3.a;

--13.查询学习‘c001’课程中优秀（91-100），良好（81-90），及格（60-80）各有多少人：
select grade,count(*) from (
select (
case when score >=91 and score <=100 then '（91-100）'
  when score > 80 and score <=90 then '（81-90）'
    when score >=60 and score <= 80 then '（60-80）'
 end) score,(
case when score >=91 and score <=100 then '优秀'
  when score > 80 and score <=90 then '良好'
    when score >=60 and score <= 80 then '及格'
 end) grade from sc where cno = 'c001') t
 group by t.grade;

--14.查询各科成绩的最高分和最低分。以如下形式显示：课程编号，最高分，最低分：
select cno,max(score),min(score) from sc group by cno;

--15.按各科平均成绩从低到高和及格率的百分数从高到低顺序：
/*select t.cno,count(*) n from (select * from sc where score < 60) t group by t.cno;  -- 不及格人数
select cno,count(*) m from sc group by cno; -- 总人数

select t1.cno, nvl(t2.n/t1.m,0) fail from (select cno,count(*) m from sc group by cno) t1
left join (select t.cno,count(*) n from (select * from sc where score < 60) t group by t.cno) t2
on t1.cno = t2.cno --不及格率

select tt1.*,tt2.fail from (select sc.cno,avg(score) from sc group by cno) tt1 join
(select t1.cno, nvl(t2.n/t1.m,0) fail from (select cno,count(*) m from sc group by cno) t1
left join (select t.cno,count(*) n from (select * from sc where score < 60) t group by t.cno) t2
on t1.cno = t2.cno) tt2 on tt1.cno = tt2.cno order by tt1.cno,tt2.fail desc;*/

-- 优解
select sc.cno,avg(score),t.pass from sc join (
select cno, sum(case when score >= 60 then 1 else 0 end)/count(*) pass  from sc group by cno) t
on t.cno = sc.cno
 group by sc.cno ,t.pass order by avg(score),t.pass desc;

--16.查询不同的老师所教不同课程的平均分从高到低显示：

select t.tno,c.cno,avg(sc.score) from teacher t join course c on c.tno = t.tno 
join sc on sc.cno = c.cno group by t.tno,c.cno order by avg(sc.score) desc;

--17.统计各科成绩，各分数段人数：课程ID、课程名称、[100-85]、[85-70]、[70-60]、[ <60]：
select sc.cno,c.cname,sum(case when score between 85 and 100 then 1 else 0 end) "[100-85]",
                      sum(case when score >= 70 and score < 85 then 1 else 0 end) "[85-70]",
                      sum(case when score >= 60 and score < 70 then 1 else 0 end) "[70-60]",
                      sum(case when score < 60 then 1 else 0 end) "[ <60]" from sc join course c
                      on sc.cno = c.cno group by sc.cno,c.cname;
                      

--18.查询各科成绩前三名的记录：（不考虑成绩并列情况）：
select * from (
select sc.*,row_number()over(partition by cno order by score desc) r from sc) t
where t.r <= 3;

--19.查询每门课程被选修的学生数：
select c.cno,count(sc.cno) from course c left join sc on c.cno = sc.cno
group by c.cno;

--20.查询只选修了一门课程的学生学号和姓名：
select sno,sname from student where sno in
 (select sno from (select sno,count(*) from sc group by sno having count(*) = 1)) ;
 
 --21.查询学生表中男生、女生的人数：
 select ssex,count(*) from student group by ssex;

--22.查询姓‘张’的学生名单：
select * from student where sname like '张%';

--23.查询同名同性的学生名单，并统计同名人数：
select t.sname,count(*) from (
select s1.* from student s1 join student s2 on s1.sname = s2.sname  where s1.sno != s2.sno and s1.ssex = s2.ssex  ) t
group by t.sname;

--24.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列：
select cno,avg(score) from sc group by cno order by avg(score),cno desc;

--25.查询平均成绩大于85 的所有学生的学号、姓名和平均成绩：
select s.sno,s.sname,t.avg_score from student s join (
select sno,avg(score) avg_score from sc group by sno having avg(score) > 85) t
on s.sno = t.sno;

--26.查询课程名称为‘Oracle’，且分数低于60 的学生姓名和分数：
select s.sname,t.score from student s join (
select sno,score from sc where cno = (
select cno from course where cname = 'Oracle') and score < 60) t
on t.sno = s.sno;

--27.查询所有学生的选课情况：
select s.sname,sc.cno,c.cname from student s left join sc on sc.sno = s.sno
left join course c on c.cno = sc.cno;

--28.查询任何一门课程成绩在70 分以上的姓名、课程名称和分数：
with t as (select * from sc where score > 70)
select s.sname,c.cname,t.score from student s join t on t.sno = s.sno
join course c on t.cno = c.cno;

--29.查询不及格的课程，并按课程号从大到小排列：
select c.cno,c.cname ,t.score from course c join (
select * from sc where score < 60 ) t
on t.cno = c.cno order by c.cno desc;

--30.查询课程编号为‘c001’且课程成绩在80 分以上的学生学号和姓名：
select sno,sname from student where sno in (
select sno from sc where cno = 'c001');

--31.查询学过c002课程但没学过c003课程的学生信息：
select * from student where sno in (
select sno from sc where cno != 'c003'
intersect
select sno from sc where cno = 'c002');

--32.查询选修‘谌燕’老师所授课程的学生中，成绩最高的学生姓名及其成绩：
select s.sname,t.cno,t.score from student s join (
select sno,cno,score from sc where (cno,score) in (
select cno,max(score) ms from sc where cno in (
select cno from course where tno =(
select tno from teacher where tname = '谌燕')) group by cno)) t
on s.sno = t.sno  ;

--33.查询各个课程及相应的选修人数：
select c.cno,count(sc.cno) from course c left join sc
on c.cno = sc.cno group by c.cno;

--34.查询不同课程成绩相同的学生学号、课程号、学生成绩：
select sc1.* from sc sc1 join sc sc2 
on sc1.sno =sc2.sno where sc1.cno != sc2.cno and sc1.score = sc2.score ;
--35.查询每门功课成绩最好的前两名：
select * from (
select sc.*,row_number()over(partition by cno order by score desc) r from sc) t 
where t.r < 3;

--36.统计每门课程的学生选修人数（超过10 人的课程才统计）。
--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列：
select cno,count(*) from sc group by cno having count(*) > 10 order by count(*) desc,cno;

--37.检索至少选修两门课程的学生学号：
select sno,count(*) from sc group by sno having count(*) > 1;

--38.查询全部学生都选修的课程的课程号和课程名：
select c.cno,c.cname from course c join (
select cno,count(*) from sc group by cno having count(*) =
(select count(*) from student)) t
on t.cno = c.cno;

--39.查询两门以上不及格课程的同学的学号及其平均成绩：
select sc.sno,avg(sc.score) from sc join (
select sno,count(cno) from sc where score < 60 group by sno having count(cno) > 1) t
on t.sno = sc.sno group by sc.sno;

--40.查询各科成绩：课程名称，选课人数，最高分、最低分、平均分、
--及格率(60-69)、中等率(70-79)、优良率(80-89)、优秀率(90-100)：（保留两位小数，加上%）

select c.cname,count(sc.sno) n ,max(sc.score) 最高分,min(sc.score) 最低分, round(avg(sc.score),2) 平均分,
round((sum(case when score >= 60 and score < 69 then 1 else 0 end)/count(*)) ,2)*100 || '%'"及格率",
round((sum(case when score >= 69 and score < 79 then 1 else 0 end)/count(*)) ,2)*100 || '%'"中等率",
round((sum(case when score > 79 and score < 89 then 1 else 0 end)/count(*)),2)*100 || '%' "优良率",
round((sum(case when score > 89 and score <= 100 then 1 else 0 end)/count(*)),2)*100 || '%' "优秀率"
from course c left join sc on
c.cno = sc.cno group by c.cname;

select c.cname,count(sc.sno) n ,max(sc.score) 最高分,min(sc.score) 最低分, round(avg(sc.score),2) 平均分,
concat(round((sum(case when score >= 60 and score < 69 then 1 else 0 end)/count(*)) ,2)*100,'%') 及格率,
concat(round((sum(case when score >= 69 and score < 79 then 1 else 0 end)/count(*)) ,2)*100,'%') 中等率,
concat(round((sum(case when score > 79 and score < 89 then 1 else 0 end)/count(*)),2)*100,'%') 优良率,
concat(round((sum(case when score > 89 and score <= 100 then 1 else 0 end)/count(*)),2)*100,'%') 优秀率
from course c left join sc on
c.cno = sc.cno group by c.cname;

--31.查询学过c002课程但没学过c003课程的学生信息：
select s.* from student s join sc s1 on s.sno = s1.sno
where s.cno = 'c002' not  like  s1 =c003 
--32.查询选修‘谌燕’老师所授课程的学生中，成绩最高的学生姓名及其成绩：
select t1.sname,max(score)score1 from sc 
inner join student on sc.sno=student.sno
where cno in( select cno from course
inner join teacher on course.tno=teacher.tno
where teacher.tno = (select tno from teacher
where tname='谌燕'))
group by sname
order by score1 desc