1、题目要求
已知关系模式:
S (SNO,SNAME)学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER)课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
- 1.找出没有选修过“刘备”老师讲授课程的所有学生姓名
- 2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
- 3.列出既学过“c001”号课程,又学过“c002”号课程的所有学生姓名
- 4.列出“c001”号课成绩比“s002”号同学该门课成绩高的所有学生的学号
- 5.列出“c001”号课成绩比“c002”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
2、建表语句
create table C ( cno VARCHAR2(50), cname VARCHAR2(50), cteacher VARCHAR2(50) ) ; comment on column C.cno is '课程号'; comment on column C.cname is '课程名'; comment on column C.cteacher is '任课教师'; create table S ( sno VARCHAR2(50), sname VARCHAR2(50) ) ; comment on column S.sno is '学号'; comment on column S.sname is '姓名'; create table SC ( sno VARCHAR2(50), cno VARCHAR2(50), scgrade VARCHAR2(50) ) ; comment on column SC.scgrade is '成绩'; insert into C (cno, cname, cteacher) values ('c001', '语文', '刘备'); insert into C (cno, cname, cteacher) values ('c002', '数学', '关于'); insert into C (cno, cname, cteacher) values ('c003', '英语', '张飞'); commit; insert into S (sno, sname) values ('s001', '小红'); insert into S (sno, sname) values ('s002', '小明'); insert into S (sno, sname) values ('s003', '小庄'); commit; insert into SC (sno, cno, scgrade) values ('s001', 'c001', '95'); insert into SC (sno, cno, scgrade) values ('s001', 'c002', '85'); insert into SC (sno, cno, scgrade) values ('s001', 'c003', '75'); insert into SC (sno, cno, scgrade) values ('s002', 'c001', '94'); insert into SC (sno, cno, scgrade) values ('s002', 'c002', '54'); insert into SC (sno, cno, scgrade) values ('s002', 'c003', '44'); insert into SC (sno, cno, scgrade) values ('s003', 'c002', '20'); insert into SC (sno, cno, scgrade) values ('s003', 'c003', '30'); commit;
3、参考答案
--1.找出没有选修过“刘备”老师讲授课程的所有学生姓名 select sname from 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(sc.scgrade) from sc, s, (select sc.sno from sc where sc.scgrade < 60 group by sno having count(distinct cno) >= 2) A where sc.sno = A.sno and s.sno = A.sno group by s.sno, s.sname --3.列出既学过“c001”号课程,又学过“c002”号课程的所有学生姓名 select * from s, (select sc.sno from sc, c where sc.cno = c.cno and c.cno in ('c001', 'c002') group by sc.sno having count(distinct c.cno) = 2) A where s.sno = A.sno --4.列出“c001”号课成绩比“s002”号同学该门课成绩高的所有学生的学号 select s.sno from s, sc sc1, sc sc2 where sc1.cno = 'c001' and sc2.sno = 's002' and s.sno = sc1.sno and sc1.cno = sc2.cno and sc1.scgrade > sc2.scgrade --5.列出“c001”号课成绩比“c002”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩 select sc1.sno, sc1.scgrade, sc2.scgrade from sc sc1, sc sc2 where sc1.cno = 'c001' and sc2.cno = 'c002' and sc1.sno = sc2.sno and sc1.scgrade > sc2.scgrade