求个简单的sql语句，谢谢！

[lanyanse0504]

如下表：课程表course，学生成绩表sc，求全部学生都选修的课程的课程名
create table course
(
c# int, //课程号
t# int, //课程名
cname varchar(50)
)
create table sc
(
s# int, //学生号
c# int, //课程号
score int //分数
)
//测试数据
insert into course values(1,1,'语文')
insert into course values(2,1,'数学')
insert into course values(3,2,'英语')

insert into sc values(1,1,50)
insert into sc values(1,2,60)
insert into sc values(1,3,70)
insert into sc values(2,1,80)
insert into sc values(2,3,90)
insert into sc values(2,2,90)

insert into sc values(3,2,50)

则上面全部学生都选修的课程为课程2，数学

[zs621]

SELECT * FROM course C
WHERE (SELECT COUNT(0) FROM sc WHERE c#=C.c#)=(SELECT COUNT(DISTINCT s#) FROM sc)

很多方法的

[超凡]

select a.s#,a.score,b.cname  from sc as a inner join course as b on a.c#=b.c# where a.c# =2

[wxl3wei]

偶再发个方法，拓展一下思路

select * from course
where c# in
(
select c# from
(
select COUNT(s#) cnt,c# from sc
group by c#
) a
where cnt=(SELECT COUNT(DISTINCT s#) FROM sc)
)

[ddjm123456]

很好的例子~~~~

[liang145]

create table course
(
 c# int, --课程号
 t# int, --课程名
 cname varchar(50)
)
create table sc
(
 s# int, --学生号
 c# int, --课程号
 score int --分数
)
--测试数据
insert into course values(1,1,'语文')
insert into course values(2,1,'数学')
insert into course values(3,2,'英语')

insert into sc values(1,1,50)
insert into sc values(1,2,60)
insert into sc values(1,3,70)
insert into sc values(2,1,80)
insert into sc values(2,3,90)
insert into sc values(2,2,90)
insert into sc values(3,2,50)

select c.c#, c.cname from course as c join sc on sc.c#=c.c# group by  c.c#, c.cname
having count(1)=(select count(distinct s#) from sc)

c#          cname
----------- --------------------------------------------------
2           数学

(1 row(s) affected)