SQL查询全部学生都选修的课程的课程号和课程名问题

Question

有3个表：
create table student
(sid int primary key,
sname varchar(50) NOT NULL,
age smallint,
sex nchar(1),
department varchar(20))
create table course
(cid int primary key,
cname varchar(50) NOT NULL,
department varchar(20),
teacher varchar(10))
create table study
(sid int,
cid int,
score smallint,
constraint fk_sid foreign key(sid) references student(sid),
constraint fk_cid foreign key(cid) references course(cid),
constraint pk primary key(sid,cid))
问查询全部学生都选修的课程的课程号和课程名，请大神写出SQL语句和逻辑分析

Answer 1

SELECT
course.cid,
course.cname
FROM
course JOIN study ON (course.cid = study.cid)
GROUP BY
course.cid,
course.cname
HAVING
COUNT(study.sid) = (SELECT COUNT(sid) FROM student);

逻辑：

首先，简单的把 课程表 与 选修表 关联
course JOIN study ON (course.cid = study.cid)

然后 ，按照 课程号和课程名 分组
GROUP BY
course.cid,
course.cname

最后， 要求 选修的人数 = 学生总数
HAVING
COUNT(study.sid) = (SELECT COUNT(sid) FROM student)