一天几道SQL题目（一）

Student(S#,Sname,Sage,Ssex)学生表    
Course(C#,Cname,T#) 课程表    
SC(S#,C#,score) 成绩表    
Teacher(T#,Tname) 教师表 

1、查询“001”课程比“002”课程成绩高的所有学生的学号；   

select S# from SC sc1,SC sc2

where sc1.s#=sc2.s# and sc1.c#=’001’ and sc2.c#=’002’ and sc1.score>sc2.score

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

select S#, avg(score) from SC group by S# having avg(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩；

select student.s#,sname,count(*),sum(score)

from student,sc

where student.s#=sc.s#

group by student.s#,sname

4、查询姓“李”的老师的个数；

select count(*) from teacher where tname like ‘李%’

5、查询没学过“叶平”老师课的同学的学号、姓名；

select s#, sname

from student

where s# not in

(

select s#

from student,sc,course,teacher

where student.s#=sc.s# and sc.c#=course.c# and course.t#=teacher.t# and teacher.tname=’叶平’

)

6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select student.s#, sname

from student, sc

where student.s#=sc.s# and sc.c#=’001’ and student.s# in

(

select student.s#

from student.sc

where student.s#=sc.s# and sc.c#=’002’

)

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；    

select student.s#,sname

from student,sc,course,teacher

where student.s#=sc.s# and sc.c#=course.c# and course.t#=teacher.t# and teacher.tname=’叶平’

group by student.s# having count(sc.c#)=

(select count(course.c#) from course,teacher where course.c#=teacher.c# and teacher.tname=’叶平’)

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select s#,sname

from student 

where s# in

(select s#

from sc sc1,sc sc2

where sc1.c#=’001’ and sc2.c#=’002’ and sc1.score<sc2.score

)

9、查询所有课程成绩小于60分的同学的学号、姓名；

select student.s#,sname

from student,sc

where student.s#=sc.s# and sc.score<60

group by student.s#,sname

having count(sc.c#)=

(select count(*) from sc where sc.score<60 group by s#)

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

select student.s#,sname

from student,sc

where student.s#=sc.s# 

group by student.s#,sname

where count(*)<

(select distinct count(*) from course)