用户注册



邮箱:

密码:

用户登录


邮箱:

密码:
记住登录一个月忘记密码?

发表随想


还能输入:200字
云代码 - sql代码库

关联查询

2017-09-19 作者:举报

[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);


分享到:
更多

网友评论    (发表评论)

共1 条评论 1/1页

发表评论:

评论须知:

  • 1、评论每次加2分,每天上限为30;
  • 2、请文明用语,共同创建干净的技术交流环境;
  • 3、若被发现提交非法信息,评论将会被删除,并且给予扣分处理,严重者给予封号处理;
  • 4、请勿发布广告信息或其他无关评论,否则将会删除评论并扣分,严重者给予封号处理。