经典SQL文试题

已知关系模式：

S (SNO,SNAME) 学生关系。SNO为学号，SNAME为姓名；

C (CNO,CNAME,CTEACHER) 课程关系。CNO为课程号，CNAME为课程名，CTEACHER为任课教师；

SC(SNO,CNO,SCGRADE)选课关系。SCGRADE为成绩。

1.找出没有选修过“李明”老师讲授课程的所有学生姓名；
2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩；
3.列出既学过课程名为“1”的课程，又学过课程名为“2”的课程的所有学生姓名；
4.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩。
5.列出“01”号课成绩比“02”号同学该门课成绩高的所有学生的学号
解题：

1．找出没有选修过“李明”老师讲授课程的所有学生姓名

Select Sname As 学生姓名 From s Where Not Exists (Select * From c, Sc Where c.Cno = Sc.Cno And Cteacher = ‘李明’ And Sc.Sno = s.Sno);

Select Sname As 学生姓名 From s Where Sno Not In (Select Sno From c, Sc Where c.Cno = Sc.Cno And Cteacher = ‘李明’);

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

Select s.Sno As 学生学号, s.Sname As 学生姓名, Avg(Sc.Scgrade) As 平均成绩 From s, Sc Where Sc.Sno = s.SnoAnd Sc.Sno In (Select Sc.Sno From Sc Where Sc.Scgrade < 60 Group By Sc.Sno Having Count(*) > 2) Group By s.Sno, s.Sname;

Group by 后面接select语句后面的所有属性，不包括聚合函数 SELECT id, COUNT(course) as
numcourse, AVG(score) as avgscore FROM student GROUP BY id HAVING
AVG(score)>=80; Having 用法

3. 列出既学过“01”号课程，又学过“02”号课程的所有学生姓名

select s.sno as 学生学号,s.sname as 学生姓名 from s where sno in(select sno from sc where cno in(‘01’,‘02’) group by sno having count(distinct cno)=2);

4. 列出“01”号课成绩比“02”号课成绩高的所有学生的学号及其“01”号课和“02”号课的成绩

select sc1.sno as 学生学号, sc1.scgrade as no1grade ,sc2.scgrade as no2grade from sc as sc1,c as c1,sc as sc2,c as c2 where sc1.cno=c1.cno and c1.cno=‘01’ and sc2.cno=c2.cno and c2.cno=‘02’ and sc1.scgrade>sc2.scgrade group by sc1.sno;

5.列出“01”号课成绩比“02”号同学该门课成绩高的所有学生的学号

select sc1.sno as 学生学号 from sc as sc1,c as c1,sc as sc2,c as c2 where sc1.cno=c1.cno and c1.cno=‘01’ and sc2.cno=c2.cno and c2.cno=‘02’ and sc1.scgrade>sc2.scgrade group by sc1.sno;