小白请教一个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);

sql

1个回答

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

qq_32764407
qq_32764407 正解,谢谢了,以前理解不够,没用过where后面还能select的。
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!