SQL文の練習

   ：  101   102             
select S ,score from SC where C=101;
select S ,score from SC where C=102;

  ：select a.S from (select S ,score from SC where C=101) a,(select S ,score from SC where C=102) b  where a.score > b.score and a.S=b.S;
  ：    select S from (select S ,score from SC where C=101) a,(select S ,score
from SC where C=102) b  where a.score > b.score and a.S=b.S;
       ：Column 'S' in field list is ambiguous
  ：  ：        'xxxx'
 ：select name from a,b where a.id=b.id      a、b2    name  
  ：         ， ： select a.name from a,b where a.id=b.id

   ：        60            
select S ,avg(score) from SC group by S having avg(score)>60;
  ：having      ，where      

   ：         ，  ，   ，   
select Student.S,Student.Sname,count(SC.C),sum(score) from Student left join SC on Student.S=SC.S group by Student.S,Sname;
  ：            

   ：   “ ”     
select count(distinct(Tname)) from Teacher where Tname like ' %';
  ：  like   

   ：      “  ”         ，  
select Student.S,Student.Sname from Student where S not in (select distinct(SC.S) from SC,Course,Teacher where SC.C=Course.C and Teacher.T=Course.T and Teacher.Tname='  ');
  ：  not in   ，       

   ：    “101”       “102”        ，  
select Student.S,Student.Sname from Student,SC where Student.S=SC.S and SC.C =101 and exists(select * from SC as SC_2 where SC.S=SC_2.S and SC_2.C =102);
  ：  exists   ：    ，              

   ：    “  ”              ，  
select S,Sname from Student where S in (select S from SC,Course,Teacher where SC.C=Course.C and Teacher.T=Course.T and Teacher.Tname='  ' group by S having count(SC.C)=(select count(C) from course,Teacher where Teacher.T=Course.T and Tname='  '));
  ：  count(SC.C)=(select count(C) from course,Teacher where Teacher.T=Course.T and Tname='  ')  


   ：          90       ，  
select S,Sname from Student where S not in (select Student.S from Student,SC where Student.S=SC.S and score>90);
  ：     ，    not in，     

   ：               
select Student.S,Student.Sname from Student,SC where Student.S=SC.S group by Student.S,Student.Sname having count(C)<(select count(C) from Course);

   ：            “3”                
select Student.S,Student.Sname from Student,SC where Student.S=SC.S and C in(select C from SC where S=3);

    ：            ：       ：  ID，   ，   
select L.C   ID,L.score    ,R.score     from SC L,SC R where L.C=R.C and L.score=(select MAX(IL.score) from SC IL ,Student IM where IL.C=L.C and IM.S=IL.S group by IL.C) and R.score=(select min(IR.score) from SC IR where IR.C=R.C group by IR.C);

    ：           
select 1+(select count(distinct     ) from (select S,AVG(score)      from SC group by S )T1 where     >T2.    )   ,S     ，     from (select S,avg(score)      from SC group by S)T2 order by      desc;