数据库50道练习题表结构、数据及答案【重点为特殊用法和纠正网上错误答案】

本文链接：https://blog.csdn.net/leadseczgw01/article/details/52887239

1.表结构和数据如下【使用oracle的plsql可一键导入】

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.特殊题目及答案

2.1查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;（这里学号数据为2号）

网上给出的大部分答案都如下（注意是错的：错误点在于聚合函数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 -- 排除自己