查询与某一个学生所选修教程完全相同的其他学生的信息

查询与某一个学生所选修课程完全相同的其他学生的信息

CREATE TABLE student  --学生表

(

  sid VARCHAR2(5),

  sname VARCHAR2(10) NOT NULL,

  age DATE,

  sex VARCHAR2(2),

  CONSTRAINT pk_sid PRIMARY KEY(sid),

  CONSTRAINT ck_sex CHECK(sex='男' OR sex='女')

);


CREATE TABLE teacher --教师表

(

  tid VARCHAR2(5),

  tname VARCHAR2(10) NOT NULL,

  CONSTRAINT pk_tid PRIMARY KEY(tid)

);


CREATE TABLE course  --课程表

(

  cid VARCHAR2(5),

  cname VARCHAR2(10) NOT NULL,

  tid VARCHAR2(5),

  CONSTRAINT pk_cid PRIMARY KEY(cid),

  CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES teacher(tid)

);


CREATE TABLE sc --选课表

(

  sid VARCHAR2(5),

  cid VARCHAR2(5),

  score FLOAT,

  CONSTRAINT pk_sc PRIMARY KEY(sid,cid), 

  CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES student(sid),

  CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES course(cid)

); 


--测试数据

INSERT  INTO  student VALUES ('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd') , '男');

INSERT  INTO  student VALUES ('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd') , '男');

INSERT  INTO  student VALUES ('03' , '孙风' , to_date('1990-05-20','yyyy-mm-dd') , '男');

INSERT  INTO  student VALUES ('04' , '李云' , to_date('1990-08-06','yyyy-mm-dd') , '男');

INSERT  INTO  student VALUES ('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd') , '女');

INSERT  INTO  student VALUES ('06' , '吴兰' , to_date('1992-03-01','yyyy-mm-dd') , '女');

INSERT  INTO  student VALUES ('07' , '郑竹' , to_date('1989-07-01','yyyy-mm-dd') , '女');

INSERT  INTO  student VALUES ('08' , '王菊' , to_date('1990-01-20','yyyy-mm-dd') , '女');


INSERT  INTO  teacher VALUES ('01' , '张三');

INSERT  INTO  teacher VALUES ('02' , '李四');

INSERT  INTO  teacher VALUES ('03' , '王五');


INSERT  INTO  course VALUES ('01' , '语文' , '01');

INSERT  INTO  course VALUES ('02' , '数学' , '02');

INSERT  INTO  course VALUES ('03' , '英语' , '03');

INSERT  INTO  course VALUES ('04' , '英语' , '01');


INSERT  INTO  sc VALUES ('01' , '01' , 80);

INSERT  INTO  sc VALUES ('01' , '02' , 90);

INSERT  INTO  sc VALUES ('01' , '03' , 99);

INSERT  INTO  sc VALUES ('01' , '04' , 88);

INSERT  INTO  sc VALUES ('02' , '01' , 70);

INSERT  INTO  sc VALUES ('02' , '02' , 60);

INSERT  INTO  sc VALUES ('02' , '03' , 80);

INSERT  INTO  sc VALUES ('03' , '01' , 80);

INSERT  INTO  sc VALUES ('03' , '02' , 80);

INSERT  INTO  sc VALUES ('03' , '03' , 80);

INSERT  INTO  sc VALUES ('04' , '01' , 50);

INSERT  INTO  sc VALUES ('04' , '02' , 30);

INSERT  INTO  sc VALUES ('04' , '03' , 20);

INSERT  INTO  sc VALUES ('05' , '01' , 76);

INSERT  INTO  sc VALUES ('05' , '02' , 87);

INSERT  INTO  sc VALUES ('06' , '01' , 31);

INSERT  INTO  sc VALUES ('06' , '03' , 34);

INSERT  INTO  sc VALUES ('07' , '02' , 89);

INSERT  INTO  sc VALUES ('07' , '03' , 98);

查询与学号为"02"的学生所选修的课程完全相同的其他学生的信息
------解决思路----------------------

-- 使用一个 minus ,把课程数不一样去掉。

select * from student where sid in (

  select sid from sc 

   where cid in (select cid from sc where sid = '02')

   and sid ! = '02' -- 这个，过滤掉自己

   group by sid 

   having count(*) = (select count(*) from sc where sid = '02')

  minus

  select sid from sc 

   group by sid

   having count(*) != (select count(*) from sc where sid = '02')

);