學習筆記

select * from STUDENT ;
select * from SC ;
select * from COURSE;
select * from TEACHER ;
select * from TT_JNF001 ;

--1、查詢“c001”課程比“c002”課程成績高的所有學生的學號；
select a.sno
  from sc a
  left join sc b
    on a.sno=b.sno
   and b.cno='c002'
 where a.cno='c001'
   and a.score>nvl(b.score,0);
   
select *
  from sc a,
       (
        select * from sc where cno='c002'
        ) b
 where a.sno=b.sno(+)
   and a.cno='c001'
  

--2、查詢平均成績大於60 分的同學的學號和平均成績；
select sno,avg(score)
  from sc 
 group by sno;
  
--3、查詢所有同學的學號、姓名、選課數、總成績；
select a.sno,a.sname,count(*),avg(nvl(b.SCORE,0))
  from student a
  left join sc b
    on a.sno=b.sno
 group by a.sno,a.SNAME
 order by a.sno;
  
--4、查詢姓“劉”的老師的個數；
select count(*)
  from teacher
 where tname like '劉%';

--5、查詢沒學過“劉明”老師課的同學的學號、姓名；
select a.sno,a.SNAME
  from student a,
  (
    select a.SNO
      from sc a 
      left join COURSE b
        on a.CNO=b.CNO
      left join teacher c
        on b.TNO=c.TNO
     where c.TNAME like '劉%'
  ) b
  where a.SNO != b.sno;

--6、查詢學過“c001”並且也學過編號“c002”課程的同學的學號、姓名；
select a.sno,b.SNAME
  from sc a
  left join student b
    on a.SNO=b.SNO
 where a.CNO in('c001','c002')
 group by a.sno,b.SNAME
 order by a.SNO;

--7、查詢學過“劉明”老師所教的所有課的同學的學號、姓名；
select a.SNO ,d.SNAME
      from sc a 
      left join COURSE b
        on a.CNO=b.CNO
      left join teacher c
        on b.TNO=c.TNO
      left join student d
        on d.sno=a.sno
     where c.TNAME like '劉%';

--8、查詢課程編號“c002”的成績比課程編號“c001”課程低的所有同學的學號、姓名；
select a.sno,c.SNAME
  from sc a
  left join sc b
    on a.sno=b.sno  
  left join student c
    on a.SNO=c.SNO
 where a.cno ='c002'
   and b.cno='c001'
   and a.score<b.score
 group by a.sno,c.SNAME;

--9、查詢所有課程成績小於60 分的同學的學號、姓名；
select a.SNO,b.SNAME
  from sc a
  left join student b
    on a.SNO=b.SNO
 where nvl(score,0)<60;

--10、查詢沒有學全所有課的同學的學號、姓名；
select  a.sno,a.sname--,count(a.sno),(select count(*) from COURSE)
  from student a
  left join SC b
    on b.SNO = a.SNO 
having count(*)<(select count(*) from COURSE)
 group by a.sno,a.sname
 order by a.sno;

--11、查詢至少有一門課與學號為“a01”的同學所學相同的同學的學號和姓名；
select a.sno,c.SNAME
  from SC a
  left join sc b
    on a.cno=b.cno
  left join student c
    on c.sno=a.sno
 where b.SNO='a01'
   and a.sno != 'a01'
 group by a.sno,c.SNAME
 order by a.SNO;

--12、查詢至少學過學號為“a01”同學所有一門課的其他同學學號和姓名；
select sno
  from(
      select sno,row_number()over(partition by sno order by sno)  row_a
        from sc 
       where cno in( select cno
                       from sc
                      where sno='a01')  
      )
where row_a = 1;

--13、把“SC”表中“劉明”老師教的課的成績都更改為此課程的平均成績；
--************************
update sc
   set score= 
     (
        select avg(a.score)
          from sc a
          left join COURSE b
            on a.cno=b.cno
          left join teacher c
            on b.tno=c.tno
         where c.tname='陳彥'
     )
 where  cno in
     (    select a.cno
            from sc a
            left join COURSE b
              on a.cno=b.cno
            left join teacher c
              on b.tno=c.tno
           where c.tname='陳彥'
     );
--下面這樣比較好
declare
cursor cursor_a is(
                  select a.cno as NewCno,round(avg(a.score),1) as AvgScore
                    from sc a
                    left join COURSE b
                      on a.cno=b.cno
                    left join teacher c
                      on b.tno=c.tno
                   where c.tname='陳彥'
                   group by a.cno
                  );
a cursor_a%ROWTYPE;  
begin
  open cursor_a;
    loop
       FETCH cursor_a INTO a;
       EXIT WHEN cursor_a%NOTFOUND;
       
       update sc 
          set score = a.AvgScore
        where cno = a.NewCno;
       
    end loop;
  CLOSE cursor_a;
end;

--select * from sc
--14、查詢和“a01”號的同學學習的課程完全相同的其他同學學號和姓名；
--**************************好像怪怪
select b.sno,c.sname
  from sc b
  left join student c
    on b.sno=c.sno
 where cno in
      (
        select a.cno
          from sc a
         where a.sno='a01'
      )
having count(b.cno)=
      (
        select count(a.cno)
          from sc a
         where a.sno='a01'
      )
 group by b.sno,c.sname      

--15、刪除學習“劉明”老師課的SC 表記錄；
delete 
  from sc
 where cno=
 (
      select a.cno
        from sc a
        left join COURSE b
          on a.cno=b.cno
        left join teacher c
          on c.tno=b.tno  
      where c.tname='劉明'
  )
  
  --select *  from sc
  --insert into sc values ('a01','c003','59'); --加回去
--16、向SC 表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“c002”課程的同學學號、“c002”號課的平均成績；
---**************************
declare
cursor cursor_a is (
                  select a.sno from student a
                   where a.sno not in (
                                      select sno
                                        from sc
                                       where cno ='c002'
                                      ) 
                  );
avgScore number(5,2);

bbb cursor_a%ROWTYPE;
begin

  select round(avg(score),2) into avgScore
  from sc
  where cno='c002' ;
  
  open cursor_a;
    loop
       FETCH cursor_a INTO bbb;
       EXIT WHEN cursor_a%NOTFOUND;
       
       insert into sc values(bbb.sno,
                          'c002',
                          avgScore
                          );
    end loop;
  CLOSE cursor_a;
end;
--
--delete from sc
--insert into sc values ('a01','c001',78.9); 
--insert into sc values ('a02','c001',80.9); 
--insert into sc values ('a03','c001',81.9); 
--insert into sc values ('a04','c001',60.9); 
--insert into sc values ('a01','c002',82.9); 
--insert into sc values ('a02','c002',72.9); 
--insert into sc values ('a03','c002',81.9); 
--insert into sc values ('a01','c003','59'); 

select * from sc

--17、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
select a.cno,Max(b.score) as "最高分",Min(b.score) as "最低分"
  from COURSE a
  left join sc b
    on a.cno=b.cno
 group by a.cno

--18、按各科平均成績從低到高和及格率的百分數從高到低順序
select cno,round(avg(score),2),sum((case when score>=60 then 1 else 0 end))/count(1)*100|| '%' aa 
  from sc
 group by cno
 
--19、查詢不同老師所教不同課程平均分從高到低顯示
select a.tno,avg(c.score)
  from teacher a
  left join COURSE b
    on a.tno=b.tno
  left join sc c
    on b.cno=c.cno
 group by a.tno
 order by avg(c.score) DESC

--20、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
select a.cno,b.cname,nvl(sum((case when a.score<=100 and a.score>=85 then 1 else 0 end)),0) as "100-85"
                    ,nvl(sum((case when a.score<85 and a.score>=70 then 1 else 0 end)),0) as "85-70"
                    ,nvl(sum((case when a.score<70 and a.score>=60 then 1 else 0 end)),0) as "70-60"
                    ,nvl(sum((case when a.score<60  then 1 else 0 end)),0) as "<60"
  from sc a
  left join COURSE b
    on a.cno=b.cno
 group by a.cno,b.cname

--21、查詢各科成績前三名的記錄:(不考慮成績並列情況)
--*****
select *
  from 
  (
    select sno,cno,score,ROW_NUMBER() over( partition by cno  order by score DESC ) as aa
      from sc
  )
where aa <=3
 order by cno,score DESC 
 

--22、查詢每門課程被選修的學生數
select a.cno,count(b.sno)
  from COURSE a
  left join sc b
    on a.cno=b.cno
 group by a.cno
--23、查詢出只選修了一門課程的全部學生的學號和姓名
select a.sno,b.sname
  from sc a
  left join student b
    on a.sno=b.sno
having count(a.cno)=1
 group by a.sno,b.sname

--24、查詢男生、女生人數
select ssex,count(*)
  from student
 --where ssex='男'
 group by ssex

--25、查詢姓“張”的學生名單
select *
  from student
 where sname like '張%'
 
--26、查詢同名同性學生名單，並統計同名人數

select count(*)
  from
      (
      select sname,count(sname) as aa
        from student
       group by sname
      )
 where aa >=2

--27、1981 年出生的學生名單(注：Student 表中Sage 列的類型是number)

select a.sno,a.sname
  from student a,(select to_number(to_char(sysdate,'yyyy')) yyyear from dual ) b
 where a.sage=(b.yyyear-1981)

--28、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列
select a.cno,avg(b.score)
  from  COURSE a
  left join sc b
    on a.cno=b.cno
 group by a.cno
 order by avg(b.score) ,a.cno DESC

--29、查詢平均成績大於85 的所有學生的學號、姓名和平均成績
select a.sno,b.sname,avg(a.score)
  from sc a
  left join student b
    on a.sno=b.sno
having avg(a.score)>85
 group by a.sno,b.sname

--30、查詢課程名稱為“數據庫”，且分數低於60 的學生姓名和分數
select c.sname,b.score
  from COURSE a
  left join sc b
    on a.cno=b.cno
  left join student c
    on b.sno=c.sno
 where a.cname='數據庫'
   and b.score<60
  
--31、查詢所有學生的選課情況；
select a.sno,b.cno
  from student a
  left join sc b
    on a.sno=b.sno
--32、查詢任何一門課程成績在70 分以上的姓名、課程名稱和分數；
select a.sno,b.sname,c.cname,a.score
  from sc a
  left join student b
    on a.sno=b.sno
  left join COURSE c
    on c.cno=a.cno
 where a.score>70

--33、查詢不及格的課程，並按課程號從大到小排列

select cno
  from sc
 where score <60
 group by cno
 order by cno DESC

--34、查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名；
select a.sno,b.sname
  from sc a
  left join student b
    on a.sno=b.sno
 where a.cno='c001'
   and a.score>80
--35、求選了課程的學生人數
select count(*)
  from
      (
      select sno
        from sc
       group by sno
      )

--36、查詢選修“劉明”老師所授課程的學生中，成績最高的學生姓名及其成績
select sno,sname,score
  from
    (
     select a.sno,d.sname,a.score,ROW_NUMBER() over( order by score DESC ) as nnnum
       from sc a
       left join COURSE b
         on a.cno=b.cno
       left join teacher c
         on c.tno=b.tno  
       left join student d
         on a.sno=d.sno
      where c.tname='劉明'
    )
  where nnnum=1
  
--37、查詢各個課程及相應的選修人數
select a.cno,count(a.sno)
  from sc a
 group by a.cno
 
--38、查詢不同課程成績相同的學生的學號、課程號、學生成績
select b.sno,b.cno,a.score
  from
    (
    select score,count(score) as CountScore
      from sc
     group by score
    ) a
  left join sc b
    on a.score=b.score
 where a.CountScore>1

--39、查詢每門功課成績最好的前兩名
select *
  from 
      (
        select sno,cno,row_number() over(partition by cno  order by score DESC ) as Good2Score
          from sc 
      ) 
 where Good2Score <=2

--40、統計每門課程的學生選修人數（超過10 人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
select *
  from
      (
      select cno,count(cno) as CountStudent
        from sc
       group by cno
      )
 where CountStudent>10
 order by CountStudent DESC,cno

--41、檢索至少選修兩門課程的學生學號
select a.sno
  from sc a
having count(a.cno)>=2
 group by a.sno
 
--42、查詢全部學生都選修的課程的課程號和課程名
select a.cno,b.cname
  from sc a
  left join COURSE b
    on a.cno=b.cno
 having count(a.cno)=(select count(*) from student)
 group by a.cno,b.cname

--43、查詢沒學過“劉明”老師講授的任一門課程的學生姓名
select *
  from student a
  left join 
           ( 
           select a.sno
                 from sc a
                 left join COURSE b
                   on a.cno=b.cno
                 left join teacher c
                   on c.tno=b.tno  
                 left join student d
                   on a.sno=d.sno
                where c.tname='劉明'
            )b
    on a.sno=b.sno
 where b.sno is null

--44、查詢兩門以上不及格課程的同學的學號及其平均成績

select sno,count(sno),avg(score)
  from sc 
 where score<60
 having count(sno)>=2
 group by sno

--45、檢索“c004”課程分數小於60，按分數降序排列的同學學號
select a.sno
  from sc a
 where a.cno='c004'
   and a.score<60
 order by a.score Desc

--46、刪除“a02”同學的“c001”課程的成績
delete 
  from sc a
 where a.sno='a02'
   and a.cno='c001';