查询只选修了数据库课程的学生信息

1、先建表student和课程表sc

 --学生表student
create table student
(
sid character varying(10),  --学号
sname character varying(100)  --姓名
)
COMMENT ON COLUMN edw_tmp.student.sid IS '学号';
COMMENT ON COLUMN edw_tmp.student.sname IS '姓名';

--课程表sc
create table sc
(
sid character varying(10),  --学号
cid character varying(10),  --课程号
cname character varying(100)--课程名
)
COMMENT ON COLUMN sc.sid IS '学号';
COMMENT ON COLUMN sc.cid IS '课程号';
COMMENT ON COLUMN sc.cname IS '课程名';

2、插入样例数据

--插入学生表信息
insert into student
values('1001','张三');
insert into student
values('1002','李四');
insert into student
values('1003','王五');
insert into student
values('1004','赵大');
insert into student
values('1005','钱二');

--插入课程表信息
insert into sc
values('1001','C01','数据库');
insert into sc
values('1001','C02','C语言');
insert into sc
values('1001','C03','数据结构');
insert into edw_tmp.sc
values('1002','C01','数据库');
insert into sc
values('1002','C02','C语言');
insert into sc
values('1003','C01','数据库');
insert into sc
values('1004','C01','数据库');
insert into sc
values('1004','C04','数据结构');
insert into sc
values('1005','C01','数据库');

3、查询只选修了数据库课程的学生信息。

两种方法，推荐第二种

--方法一：
select s.sid
  from student s,
       (select a.sid
          from (select distinct sid from sc where cid = 'C01') A
          left join (select distinct sid from sc where cid <> 'C01') B
            on a.sid = b.sid
         where b.sid is null) t
 WHERE s.sid = t.sid;
		
--方法二：利用group by函数
select s.sid, s.sname
  from student s
  left join  sc
    on s.sid = sc.sid
 where s.sid in (select sid from sc group by sid having count(*) = 1)
   and sc.cname = '数据库';