请实现如下四个表格，并填充测试数据，完成如下查询。
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

以下习题，答案仅供参考
1、查询“1”课程比“2”课程成绩高的所有学生的学号

select a.S#  
from (select s#,score from SC where C#='1') a,(select s#,score from SC where C#='2') b  
where a.score>b.score and a.s#=b.s#;  

2、查询平均成绩大于60分的同学的学号和平均成绩

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、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select Student.S#,Student.Sname  
from Student,SC  
where Student.S#=SC.S# and SC.C#='001'  
and exists( Select * from SC 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、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

Select sc1.S#,sc1.Sname from  
(select Student.S#,Student.Sname,score from Student,SC where Student.S#=SC.S# and C#='001') sc1,
(select Student.S#,Student.Sname,score from Student,SC where Student.S#=SC.S# and C#='002') sc2
where sc2.score<sc1.score and sc1.S#=sc2.S#  

9、查询(所有课程)成绩小于60分的同学的学号、姓名(每门课都不及格)

select S#,Sname  
  from Student 
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、查询没有学全所有课的同学的学号、姓名

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);  

11、查询至少有一门课与学号为“1001”的同学所学课程相同的同学的学号和姓名

select distinct(Student.S#),Sname from Student,SC where Student.S#=SC.S# and C# in (select C# from SC where SC.S#='1001');  

12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT distinct(L.C#) As 课程ID,L.score AS 最高分,R.score AS 最低分  
    FROM SC L ,SC R 
    WHERE L.C# = R.C# and 
        L.score = (SELECT MAX(IL.score) 
                      FROM SC IL,Student IM 
                      WHERE L.C# = IL.C# and IM.S#=IL.S# 
                      GROUP BY IL.C#) 
        AND 
        R.Score = (SELECT MIN(IR.score) 
                      FROM SC IR 
                      WHERE R.C# = IR.C# 
                  GROUP BY IR.C#); 

13、查询不同老师所教不同课程平均分从高到低显示

SELECT max(Z.T#) AS 教师ID, MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩  
FROM SC AS T,Course AS C ,Teacher AS Z  
where T.C#=C.C# and C.T#=Z.T#  
GROUP BY C.C#  
ORDER BY AVG(Score) DESC  

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数  
FROM SC t1  
WHERE score IN  
(SELECT TOP 3 score 
FROM SC  
WHERE t1.C#= C#  
ORDER BY score DESC  
) 
ORDER BY t1.C#;  

15、查询每门课程被选修的学生数

select c#,count(S#) from sc group by C#;  

16、查询出只选修了一门课程的全部学生的学号和姓名

select SC.S#,Student.Sname,count(C#) AS 选课数  
from SC ,Student  
where SC.S#=Student.S#  
group by SC.S# ,Student.Sname  
having count(C#)=1;  

17、查询男生、女生人数

Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男'  
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'  

18、查询姓“张”的学生名单

SELECT Sname FROM Student WHERE Sname like '张%';  

19、查询同名同性学生名单，并统计同名人数

select Sname,count(*) from Studentgroup by Sname having  count(*)>1;  

20、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;  

21、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select Sname,SC.S#,avg(score)  
from Student,SC  
where Student.S#=SC.S#  
group by SC.S#,Sname  
having avg(score)>85;  

22、查询所有学生的选课情况

SELECT SC.S#,SC.C#,Sname,Cname  
FROM SC,Student,Course  
where SC.S#=Student.S# and SC.C#=Course.C#  

23、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT distinct student.S#,student.Sname,SC.C#,SC.score  
FROM student,Sc  
WHERE SC.score>=70 AND SC.S#=student.S#;  

24、查询不及格的课程，并按课程号从大到小排列

select c# from sc where score <60 order by C# desc  

25、求选了课程的学生人数

select count(*) from sc  

26、查询各个课程及相应的选修人数

select count(*) from sc group by C#  

27、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select Student.Sname,score  
from Student,SC,Course C,Teacher  
where Student.S#=SC.S# and SC.C#=C.C# and  
C.T#=Teacher.T# and Teacher.Tname='叶平' and  
SC.score=(select max(score)from SC where C#=C.C# );  

28、查询全部学生都选修的课程的课程号和课程名

select  C#,Cname  
from  Course  
where  C#  in  (select  c#  from  sc group  by  c#)  

29、查询没学过“叶平”老师讲授的任一门课程的学生姓名

select Sname  
from Student  
where S# not in  
(select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平')

30、检索“004”课程分数小于60，按分数降序排列的同学学号

select S# from SC where C#='004'and score <60 order by score desc;