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))
SQL 语句 表连接
最新推荐文章于 2024-06-22 17:47:40 发布