1.表结构和数据
prompt PL/SQL Developer import file
prompt Created on 2016年10月21日 by kick
set feedback off
set define off
prompt Creating COURSE...
create table COURSE
(
cid VARCHAR2(10) not null,
cname VARCHAR2(10),
tid NUMBER(20)
)
;
alter table COURSE
add primary key (CID);
prompt Creating SC...
create table SC
(
sid VARCHAR2(10),
cid VARCHAR2(10),
score NUMBER(10)
)
;
prompt Creating STUDENT...
create table STUDENT
(
sid VARCHAR2(10) not null,
sname VARCHAR2(20),
sage DATE default to_date('1980-10-12 23:12:36','yyyy-MM-dd HH24:mi:ss'),
ssex VARCHAR2(10)
)
;
alter table STUDENT
add primary key (SID);
prompt Creating TEACHER...
create table TEACHER
(
tid NUMBER(10),
tname VARCHAR2(10)
)
;
prompt Loading COURSE...
insert into COURSE (cid, cname, tid)
values ('001', '企业管理', 3);
insert into COURSE (cid, cname, tid)
values ('002', '马克思', 3);
insert into COURSE (cid, cname, tid)
values ('003', 'UML', 2);
insert into COURSE (cid, cname, tid)
values ('004', '数据库', 1);
insert into COURSE (cid, cname, tid)
values ('005', '英语', 1);
commit;
prompt 5 records loaded
prompt Loading SC...
insert into SC (sid, cid, score)
values ('6', '001', 83);
insert into SC (sid, cid, score)
values ('6', '002', 92);
insert into SC (sid, cid, score)
values ('1', '001', 80);
insert into SC (sid, cid, score)
values ('1', '002', 60);
insert into SC (sid, cid, score)
values ('1', '003', 75);
insert into SC (sid, cid, score)
values ('2', '001', 85);
insert into SC (sid, cid, score)
values ('2', '002', 70);
insert into SC (sid, cid, score)
values ('3', '004', 100);
insert into SC (sid, cid, score)
values ('3', '001', 90);
insert into SC (sid, cid, score)
values ('3', '002', 55);
insert into SC (sid, cid, score)
values ('4', '002', 59);
insert into SC (sid, cid, score)
values ('4', '003', 58);
insert into SC (sid, cid, score)
values ('1', '004', 87);
insert into SC (sid, cid, score)
values ('1', '005', 92);
insert into SC (sid, cid, score)
values ('5', '001', 87);
commit;
prompt 15 records loaded
prompt Loading STUDENT...
insert into STUDENT (sid, sname, sage, ssex)
values ('1', '张三丰', to_date('12-10-1980 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '男');
insert into STUDENT (sid, sname, sage, ssex)
values ('2', '张无极', to_date('12-10-1995 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '男');
insert into STUDENT (sid, sname, sage, ssex)
values ('3', '李奎', to_date('12-10-1992 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '女');
insert into STUDENT (sid, sname, sage, ssex)
values ('4', '李元宝', to_date('12-10-1980 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '女');
insert into STUDENT (sid, sname, sage, ssex)
values ('5', '李世明', to_date('12-10-1981 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '男');
insert into STUDENT (sid, sname, sage, ssex)
values ('6', '赵六', to_date('12-10-1986 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '男');
insert into STUDENT (sid, sname, sage, ssex)
values ('7', '田七', to_date('12-10-1981 23:12:36', 'dd-mm-yyyy hh24:mi:ss'), '女');
commit;
prompt 7 records loaded
prompt Loading TEACHER...
insert into TEACHER (tid, tname)
values (1, '李老师');
insert into TEACHER (tid, tname)
values (2, '何以琛');
insert into TEACHER (tid, tname)
values (3, 'kick');
commit;
prompt 3 records loaded
set feedback on
set define on
prompt Done.
2查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名
网上给出的大部分答案都如下(注意是错的:错误点在于聚合函数having count(sc.cid)生效之前,只要有人学习的课程比2号多,多余的部分已经被sc.cid in (..)过滤掉了,最终导致聚合函数判断课程总数与2号相同不起作用,会查询出学习课程包含2号的课程并且多于他的同学学号和姓名,与题意有差别;--我真怀疑在银行如果这样做,出了事情会不会被人打死^-^)
select sc.sid,sname
from sc,student
where sc.sid = student.sid
and sc.cid in (
select cid
from sc
where sc.sid=2
)
group by sc.sid,sname
having count(sc.cid) = (
select count(sc.cid)
from sc
where sc.sid=2
)
and sc.sid !=2;
新答案【可直接运行】
select sc2.sid,sname
from
(
select sc.sid
from sc
group by sc.sid
having count(sc.cid)=(
select count(1)
from sc
where sc.sid=2
)
)sc1, --构建一张学习课程数目与2号相同的新课程表sc1(为了排除掉学习课程比2号多的人)
(
select *
from sc
where sc.cid in ( -- 由于这里是in,拿一个个的课程id去与2号所选课程比较,会造成符合条件但课程数比2号少的异常
select cid
from sc
where sc.sid=2
)
) sc2, -- 构建一张学习课程包含2号课程的新课程表sc2
student
where sc1.sid = sc2.sid
and sc2.sid = student.sid
group by sc2.sid,sname
having count(sc2.cid) = -- 再次聚合函数判断总数(为了排除符合条件但课程数比2号少的异常)
(
select count(*)
from sc
where sc.sid=2
)
and sc2.sid!=2 -- 排除自己