DECLARE @S TABLE(sno VARCHAR(2), sn NVARCHAR(4), sex NVARCHAR(1), age INT, dept NVARCHAR(10))
INSERT @S
SELECT N's2',  N'钱尔',  '男',  18,  N'信息' UNION ALL 
SELECT N's3',  N'孙善',  '女',  20,  N'信息' UNION ALL 
SELECT N's4',  N'李思',  '男',  21,  N'自动化' UNION ALL 
SELECT N's5',  N'周武',  '男',  19,  N'计算机' UNION ALL 
SELECT N's6',  N'吴丽',  '女',  20,  N'自动化' UNION ALL 
SELECT N's7',  N'李器',  '男',  18,  N'外语' UNION ALL 
SELECT N's8',  N'古名',  '男',  18,  N'计算机' UNION ALL 
SELECT N's9',  N'赵一',  '女',  17,  N'计算机'

DECLARE @SCORE TABLE(SNO VARCHAR(2), CNO VARCHAR(2), SCORE INT)
INSERT @SCORE
SELECT 's2',  'c5',  57 UNION ALL 
SELECT 's2',  'c6',  80 UNION ALL 
SELECT 's2',  'c7',  86 UNION ALL 
SELECT 's3',  'c1',  75 UNION ALL 
SELECT 's3',  'c2',  70 UNION ALL 
SELECT 's3',  'c4',  85 UNION ALL 
SELECT 's4',  'c1',  93 UNION ALL 
SELECT 's4',  'c2',  85 UNION ALL 
SELECT 's4',  'c3',  83 UNION ALL 
SELECT 's5',  'c2',  89 UNION ALL 
SELECT 's9',  'c1',  90 UNION ALL 
SELECT 's9',  'c2',  85

DECLARE @COURSE TABLE(CNO VARCHAR(2), CN NVARCHAR(10), CT INT, ID_TC INT)
INSERT @COURSE
SELECT 'C1',  N'程序设计',  60,  1 UNION ALL 
SELECT 'C2',  N'微机原理',  80,  2 UNION ALL 
SELECT 'C3',  N'数字逻辑',  60,  3 UNION ALL 
SELECT 'C4',  N'数据结构',  80,  4 UNION ALL 
SELECT 'C5',  N'数据库',  60,  5 UNION ALL 
SELECT 'C6',  N'编译原理',  60,  6 UNION ALL 
SELECT 'C7',  N'操作系统',  60,  7

SELECT DISTINCT S.SNO,SN
FROM @S AS S JOIN @SCORE AS SC ON S.SNO=SC.SNO
WHERE NOT EXISTS(SELECT 1 FROM (SELECT SNO,CNO FROM @SCORE WHERE SNO=S.SNO) AS SC2
                                                     RIGHT JOIN (SELECT DISTINCT CNO FROM @COURSE  WHERE  CN IN (N'微机原理',N'数据结构')) A
                         ON A.CNO=SC2.CNO
                                       WHERE  SC2.CNO IS NULL)
 /*
SNO  SN   
---- ---- 
s3   孙善
*/

------解决方案--------------------
select s.sno,s.sn from student s left join score sc on s.sno=sc.sno left join course c on sc.cno=c.cno  
where c.cn in ('微机原理','数据结构')
group by s.sno,s.sn
having count(*)>1