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