1、题目要求

为管理岗位业务培训信息，建立3个表:

S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C (C#,CN ) C#,CN 分别代表课程编号、课程名称

SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

• 1.查询选修课程名称为’数学’的学员学号和姓名
• 2.查询选修课程编号为’c002’的学员姓名和所属单位
• 3.查询不选修课程编号为’c001’的学员姓名和所属单位
• 4.查询选修全部课程的学员姓名和所属单位

2、建表语句

create table C(  c# VARCHAR2(50),  cn VARCHAR2(50));comment on column C.c#  is '课程编号';comment on column C.cn  is '课程名称';create table S(  s# VARCHAR2(50),  sd VARCHAR2(50),  sn VARCHAR2(50),  sa VARCHAR2(50));comment on column S.s#  is '学号';comment on column S.sd  is '学员姓名';comment on column S.sn  is '所属单位';comment on column S.sa  is '学员年龄';create table SC(  s# VARCHAR2(50),  c# VARCHAR2(50),  g  VARCHAR2(50));comment on column SC.s#  is '学号';comment on column SC.c#  is '课程编号';comment on column SC.g  is '学习成绩';insert into C (c#, cn)values ('c001', '语文');insert into C (c#, cn)values ('c002', '数学');insert into C (c#, cn)values ('c003', '英语');commit;insert into S (s#, sd, sn, sa)values ('s001', 'd001', '小红', '21');insert into S (s#, sd, sn, sa)values ('s002', 'd002', '小明', '22');insert into S (s#, sd, sn, sa)values ('s003', 'd003', '小庄', '23');commit;insert into SC (s#, c#, g)values ('s001', 'c001', '95');insert into SC (s#, c#, g)values ('s001', 'c002', '85');insert into SC (s#, c#, g)values ('s001', 'c003', '75');insert into SC (s#, c#, g)values ('s002', 'c001', '94');insert into SC (s#, c#, g)values ('s002', 'c002', '54');insert into SC (s#, c#, g)values ('s002', 'c003', '44');insert into SC (s#, c#, g)values ('s003', 'c002', '20');insert into SC (s#, c#, g)values ('s003', 'c003', '30');commit;

 

3、参考答案

--1.查询选修课程名称为’数学’的学员学号和姓名select s.s#, s.sn  from s, c, sc where s.s# = sc.s#   and c.c# = sc.c#   and c.cn = '数学'--2.查询选修课程编号为’c002’的学员姓名和所属单位select s.sn, s.sd  from s, c, sc where s.s# = sc.s#   and c.c# = sc.c#   and c.c# = 'c002'--3.查询不选修课程编号为’c001’的学员姓名和所属单位select s.sn, s.sd  from s where s.s# not in (select sc.s#                      from sc, c                     where sc.c# = c.c#                       and c.c# = 'c001'                       and s.s# = sc.s#)--4.查询选修全部课程的学员姓名和所属单位select s.sn, s.sd  from s,       (select sc.s#          from sc         group by sc.s#        having count(sc.s#) = (select count(1) from C)) A where s.s# = A.s#