oracle——SQL复习02

select * from t_score order by sid,cid;


--1./** 查询“001”课程比“002”课程成绩高的所有学生的学号 */
select t1.sid
  from (select t1.sid, t1.grade from t_score t1 where t1.cid = '001') t1
 inner join (select t2.sid, t2.grade from t_score t2 where t2.cid = '002') t2
    on t1.sid = t2.sid
   and t1.grade > t2.grade;


--考虑到t_score中的cid只有001没有002的数据,将其归0处理  
select t1.sid
  from (select t.sid,
               --case when t1.cid is null then '001' else t1.cid end cid, 
               nvl(t1.cid, '001') cid,
               nvl(t1.grade, 0) grade
          from t_student t
          left join (select t1.sid, t1.cid, t1.grade
                      from t_score t1
                     where t1.cid = '001') t1
                        on t.sid = t1.sid
          ) t1
 inner join (select t.sid, nvl(t2.cid, '002') cid, nvl(t2.grade, 0) grade
               from t_student t
               left join (select t2.sid, t2.cid, t2.grade
                           from t_score t2
                          where t2.cid = '002') t2
                             on t.sid = t2.sid
          ) t2
    on t1.sid = t2.sid
   and t1.grade > t2.grade;


 
--/**查询所有同学的学号、姓名、选课数、总成绩*/
select * from t_score;


select t.sid, t1.num, t1.sum_grade
  from t_student t
  left join (select t.sid, count(1) num, sum(t.grade) sum_grade
               from t_score t
              group by t.sid) t1
    on t.sid = t1.sid 



-- 查询每门功成绩最好的前两名  
select t.sid, t.cid, t.grade
  from (select t.sid,
               t.cid,
               t.grade,
               row_number() over(partition by cid order by grade desc) rn
          from t_score t) 
          t
 where rn <= 2


--/** 查询两门以上不及格课程的同学的学号及其平均成绩 */
select * from t_score t order by sid;

-- 这条语句算出来的是59分以下的平均成绩
select t1.sid, nvl(t2.cmt,0) cmt, nvl(t2.avg_grade,0) avg_grade
  from t_student t1
  inner join (select t.sid, count(1) cmt, round(avg(t.grade) ,2) avg_grade
               from t_score t
              where t.grade <= 59
              group by t.sid) t2
    on t1.sid = t2.sid 
    and cmt > = 2 ;
 -- 这条才是按sid计算的平均成绩
select t1.sid, round(avg(nvl(t1.grade, 0)), 2) avg_grade
  from t_score t1
 where t1.sid in ( 
        select t.sid from ( 
               select t.sid, count(1) cmt
                      from t_score t
               where t.grade <= 59
               group by t.sid) t
                   where t.cmt > = 2
                ) 
 group by t1.sid ;

select t1.sid, round(avg(nvl(t1.grade, 0)), 2) avg_grade
  from t_score t1
 where t1.sid in (  
               select t.sid   from t_score t
               where t.grade <= 59
               group by t.sid 
               having  count(1)  >= 2 
                ) 
 group by t1.sid ;



---、查询平均成绩大于60分的同学的学号和平均成绩;
select t.sid,avg(t.grade)  from t_score t group by t.sid having avg(t.grade) > 60;

--查询没学过“叶平”老师课的同学的学号、姓名
 
-- 1 获取tid 2 获取cid  3获取sid 4取差集
select t.sid, t.sname
  from t_student t
 where t.sid not in
       (select distinct t.sid
          from t_score t
         where t.cid in
               (select t.cid
                  from t_course t
                 where t.tid =
                       (select t.tid from t_teacher t where t.tname = '叶平')));

select sid, sname
  from t_student
 where sid not in (select distinct t1.sid
                     from t_score t1, t_course t2, t_teacher t3
                    where t1.cid = t2.cid
                      and t3.tid = t2.tid
                      and t3.tname = '叶平');