SELECT C.Cno,C.Cn
FROM 
C LEFT JOIN SC
ON C.Cno = SC.Cno 
GROUP BY C.Cno,C.Cn
HAVING COUNT(1)=(SELECT COUNT(1) FROM S)

------解决方案--------------------
SQL code


insert into s
select 'S1','Zhang',19 union all
select 'S2','Wang',20

insert into sc
select 'S1','C1',90 union all 
select 'S1','C2',81 union all 
select 'S2','C2',78

insert into c  
select 'C1','Database','Sun' 
union all select 'C2','MIS','Sun' 
union all select 'C3','OS','Liu'



select cno,cn from c where cno not in
( select cno from s,c  
  where not exists(select * from sc where cno=c.cno and sno=s.sno)
)

/*
cno    cn         
------ ---------- 
C2     MIS

（所影响的行数为 1 行）

*/