qq_32764407
qq_32764407
采纳率100%
2017-03-12 16:41

小白请教一个sql查询语句

已采纳

有三张表,学生表,选修课表,学生-选修课表,想查询至少未选修A和B两门课程的学生编号,SQL怎么写,各位大大帮忙看看,谢谢。

建表插数据的语句贴上。

create table Student
(
sid number(8) primary key,
sname varchar2(16),
sex number(8),
age number(8)
);

create table Course
(
cid number(8) primary key,
cname varchar2(16)
);

create table Stu_Course
(
sid number(8),
cid number(8),
primary key(sid,cid)
);

create sequence seq_stu
minvalue 0
start with 0
increment by 1;

create sequence seq_cou
minvalue 0
start with 0
increment by 1;

insert into Student values(seq_stu.nextval,'张三丰',0,24);
insert into Student values(seq_stu.nextval,'梅超风',1,25);
insert into Student values(seq_stu.nextval,'张无忌',0,23);
insert into Student values(seq_stu.nextval,'周芷若',1,22);
insert into Student values(seq_stu.nextval,'柳乘风',0,27);
insert into Student values(seq_stu.nextval,'黄药师',0,18);
insert into Student values(seq_stu.nextval,'周伯通',0,19);

insert into Course values(seq_cou.nextval,'A');
insert into Course values(seq_cou.nextval,'B');
insert into Course values(seq_cou.nextval,'C');
insert into Course values(seq_cou.nextval,'E');
insert into Course values(seq_cou.nextval,'F');
insert into Course values(seq_cou.nextval,'G');
insert into Course values(seq_cou.nextval,'H');

insert into Stu_Course values(1,1);
insert into Stu_Course values(1,2);
insert into Stu_Course values(1,3);
insert into Stu_Course values(2,1);
insert into Stu_Course values(2,2);
insert into Stu_Course values(3,1);
insert into Stu_Course values(3,3);
insert into Stu_Course values(4,1);
insert into Stu_Course values(4,2);
insert into Stu_Course values(4,4);
insert into Stu_Course values(5,1);
insert into Stu_Course values(5,5);
insert into Stu_Course values(5,7);
insert into Stu_Course values(7,1);

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • caozhy 从今以后生命中的每一秒都属于我爱的人 4年前

    select 学生编号 from 学生表 a where (select count(*) from 学生选课表 bwhere a.学生编号=b.学生编号) < (select count(*) from 选课表) - 2

    点赞 1 评论 复制链接分享

相关推荐