SQL 语句 表连接

CREATE TABLE S(
Snum VARCHAR(10),
Sname VARCHAR(50),
Ssex VARCHAR(10),
Sage VARCHAR(10),
Dnum VARCHAR(10)
);
go 

insert [S]   
select 'S001','王明','男','19','D2' union all  
select 'S002','李勇','男','23','D3' union all
select 'S003','刘燕','女','21','D1' union all  
select 'S004','王萍','女','23','D1' union all  
select 'S005','王佳','男','24','D3' union all  
select 'S006','赵婷','女','20','D1'

CREATE TABLE SC(
Snum VARCHAR(10),
Cnum VARCHAR(10),
Score VARCHAR(10)
);

insert [SC]   
select 'S001','C1','83' union all 
select 'S001','C2','89' union all 
select 'S001','C3','65' union all 
select 'S001','C4','85' union all 
select 'S001','C5','85' union all  
select 'S002','C3','69' union all 
select 'S002','C4','75' union all  
select 'S003','C1','95' union all  
select 'S004','C1','85' union all  
select 'S005','C2','92' union all  
select 'S005','C3','76'

CREATE TABLE C(
Cnum VARCHAR(10),
Cname VARCHAR(50),
Cfreq VARCHAR(50)
);
insert [C]   
select 'C1','数据库系统原理','4' union all 
select 'C2','C程序设计','4' union all 
select 'C3','计算机体系结构','3' union all 
select 'C4','自动控制原理','2' union all 
select 'C5','数据结构','4'


--李勇选修的所有课程和分数
SELECT S.SNAME,C.CNAME,SC.SCORE FROM C
JOIN SC
ON SC.CNUM=C.CNUM
JOIN S 
ON SC.SNUM=S.SNUM AND S.SNAME='李勇'
--选修所有课程的学生
select Sname 
from S 
where not exists 
(select * 
from C 
where not exists 
(select * 
from SC 
where Snum=S.Snum 
and Cnum=C.Cnum))