已知关系模式:
S(SNO,SNAME) --学生关系SNO为学号,SNAME为姓名
C(CNO,CNAME,CTEACHER)--课程关系CNO为课程号,CNAME为课程名,CTEACHER 为任课教
SC(SNO,CNO,SCGRADE) --选课关系SCGRADE 为成绩
要求实现如下5个处理:
1.找出没有选修过“李明”老师讲授课程的所有学生姓名;
2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩;
3.列出既学过“1”号课程,又学过“2”号课程的所有学生姓名;
4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号;
5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩;
1.找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:
SELECT SNAMEFROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CTEACHER='李明'
AND SC.SNO=S.SNO)
2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--实现代码:
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)AWHERE S.SNO=A.SNOAND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
解法2
select * from table1
where 学号in
(
select 学号from table2
where 成绩<60
group by 课程
having count(课程)>=2
)
3.列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
--实现代码:
SELECT S.SNO,S.SNAME
FROM S,(
SELECT SC.SNO
FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CNAMEIN('1','2')
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)=2
)SCWHERE S.SNO=SC.SNO
4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
--实现代码:
SELECT S.SNO,S.SNAME
FROM S,SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.SNO='2'
AND SC1.CNO=S.CNO
AND SC1.SCGRADE>SC2.SCGRADE
5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--实现代码:
SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.CNO='2'
AND SC1.SNO=SC2.SNO
AND SC1.SCGRADE>SC2.SCGRADE