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 allselect '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 CJOIN SCON SC.CNUM=C.CNUMJOIN 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))