50Ìõ³£ÓÃsqlÓï¾ä

Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“”¿Î³Ì±È“”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
  SELECT a.S# from (SELECT s#,score from SC WHERE C#='001') a,
  (SELECT s#,score  from SC WHERE C#='002') b 
  WHERE a.score>b.score AND a.s#=b.s#; 
2¡¢²éѯƽ¾ù³É¼¨´óÓÚ·ÖµÄͬѧµÄѧºÅºÍƽ¾ù³É¼¨£»
    SELECT S#,avg(score) 
    from sc 
    GROUP BY S# having avg(score) >60; 
3¡¢²éѯËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡¿ÎÊý¡¢×ܳɼ¨£»
  SELECT Student.S#,Student.Sname,count(SC.C#),sum(score) 
  from Student left Outer JOIN SC on Student.S#=SC.S# 
  GROUP BY Student.S#,Sname 
4¡¢²éѯÐÕ“ÀÄÀÏʦµÄ¸öÊý£»
  SELECT count(distinct(Tname)) 
  from Teacher 
  WHERE Tname like 'Àî%'; 
5¡¢²éѯûѧ¹ý“Ҷƽ”ÀÏʦ¿ÎµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
    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='Ҷƽ'); 
6¡¢²éѯѧ¹ý“”²¢ÇÒҲѧ¹ý±àºÅ“”¿Î³ÌµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
  SELECT Student.S#,Student.Sname from Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * from SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002'); 
7¡¢²éѯѧ¹ý“Ҷƽ”ÀÏʦËù½ÌµÄËùÓпεÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
  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='Ҷƽ')); 
8¡¢²éѯ¿Î³Ì±àºÅ“”µÄ³É¼¨±È¿Î³Ì±àºÅ“&rdqu