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)