• 1.查询”001”课程比”002”课程成绩高的学生
• 2.查询平均成绩大于60分的同学的学号和平均成绩
• 3.查询所有同学的学号、姓名、选课数、总成绩
• 4.查询姓“李”的老师的个数
• 5.查询没学过“叶平”老师课的同学的学号、姓名
• 6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
• 7.查询学过“叶平”老师所教的所有课的同学的学号、姓名
• 8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
• 9.查询所有课程成绩小于60分的同学的学号、姓名;
• 10.查询没有学全所有课的同学的学号、姓名；

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

1.查询”001”课程比”002”课程成绩高的学生

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.查询平均成绩大于60分的同学的学号和平均成绩

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

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

select SC.S#,Sname,count(C#),sum(Score) from Student,SC 
where Student.S#=SC.S# 
group by Student.S#;

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

select count(T#) from Teacher 
where Tname like '李%';

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

select SC.S#,Sname from Student,Course,SC,Teacher 
where Tname<>'叶平' 
and Student.S#=SC.S# 
and Course.C#=SC.C# 
and Teacher.T#=Course.T#;

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

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

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

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

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

select b.S#,b.Sname from (select S#,Sname,Score from SC,Student where C#='001' and SC.S#=Student.S#)a,
(select S#,Sname,Score from SC,Student where C#='002' and SC.S#=Student.S#)b 
where 
a.Score > b.Score 
and a.S#=b.S#;

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

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

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

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