qq_32764407 2017-03-12 16:41 采纳率: 100%
浏览 845
已采纳

小白请教一个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条回答 默认 最新

  • threenewbee 2017-03-12 23:44
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 双层网络上信息-疾病传播
  • ¥50 paddlepaddle pinn
  • ¥20 idea运行测试代码报错问题
  • ¥15 网络监控:网络故障告警通知
  • ¥15 django项目运行报编码错误
  • ¥15 请问这个是什么意思?
  • ¥15 STM32驱动继电器
  • ¥15 Windows server update services
  • ¥15 关于#c语言#的问题:我现在在做一个墨水屏设计,2.9英寸的小屏怎么换4.2英寸大屏
  • ¥15 模糊pid与pid仿真结果几乎一样