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、建表语句

prompt PL/SQL Developer import file
prompt Created on 2018年10月16日 by zxp
set feedback off
set define off
prompt Creating C...
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 ‘任课教师‘;

prompt Creating S...
create table S
(
  sno   VARCHAR2(50),sname VARCHAR2(50)
)
;
comment on column S.sno
  is ‘学号‘;
comment on column S.sname
  is ‘姓名‘;

prompt Creating SC...
create table SC
(
  sno     VARCHAR2(50),cno     VARCHAR2(50),scgrade VARCHAR2(50)
)
;
comment on column SC.scgrade
  is ‘成绩‘;

prompt Disabling triggers for C...
alter table C disable all triggers;
prompt Disabling triggers for S...
alter table S disable all triggers;
prompt Disabling triggers for SC...
alter table SC disable all triggers;
prompt Deleting SC...
delete from SC;
commit;
prompt Deleting S...
delete from S;
commit;
prompt Deleting C...
delete from C;
commit;
prompt Loading C...
insert into C (cno,cname,cteacher)
values (‘c001‘,‘语文‘,‘刘备‘);
insert into C (cno,cteacher)
values (‘c002‘,‘数学‘,‘关于‘);
insert into C (cno,cteacher)
values (‘c003‘,‘英语‘,‘张飞‘);
commit;
prompt 3 records loaded
prompt Loading S...
insert into S (sno,sname)
values (‘s001‘,‘小红‘);
insert into S (sno,sname)
values (‘s002‘,‘小明‘);
insert into S (sno,sname)
values (‘s003‘,‘小庄‘);
commit;
prompt 3 records loaded
prompt Loading SC...
insert into SC (sno,cno,scgrade)
values (‘s001‘,‘c001‘,‘95‘);
insert into SC (sno,‘c002‘,‘85‘);
insert into SC (sno,‘c003‘,‘75‘);
insert into SC (sno,scgrade)
values (‘s002‘,‘94‘);
insert into SC (sno,‘54‘);
insert into SC (sno,‘44‘);
insert into SC (sno,scgrade)
values (‘s003‘,‘20‘);
insert into SC (sno,‘30‘);
commit;
prompt 8 records loaded
prompt Enabling triggers for C...
alter table C enable all triggers;
prompt Enabling triggers for S...
alter table S enable all triggers;
prompt Enabling triggers for SC...
alter table SC enable all triggers;
set feedback on
set define on
prompt Done.

?

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!