关联查询

[sql]代码库

drop table sc;
drop table student;
drop table course;
--学生表
create table student(
  id number(4) primary key,
  name varchar2(10)
);
--课程
create table course(
  id number(4) primary key,
  name1 varchar2(20)
);
--课程表
create table sc(
  sid number(4) references student(id),
  cid number(4) references course(id),
  grade number(4,2)
);
insert into student 
      select 1,'小何' from dual union
      select 2,'小凳' from dual union
      select 3,'小宝' from dual union
      select 4,'小飞' from dual;
insert into course values(1,'java');
insert into course values(2,'c#');
insert into course values(3,'sql');
insert into course values(4,'php');

insert into sc
      select 1,1,98 from dual union
      select 2,1,95 from dual union
      select 4,1,88 from dual union
      select 1,2,78 from dual union
      select 3,2,99 from dual union
      select 4,2,89 from dual union
      select 1,3,76 from dual union
      select 4,3,84 from dual union
      select 1,4,91 from dual union
      select 2,4,73 from dual;
--查询选修所有课程的学生
select name from student where id in(select sid from sc group by
    sid having count(*)=(select count(*) from course));
--查询选修两门课程以上的学生
select name from student where id in(select sid from sc group by sid having count(*)>=2);