SQL 语句练习03
目录
一、建表
这里先建好我们下面查询需要的表，方便后续查询。
- 建立如下学生表(命名格式“姓名拼音_三位学号_week5s”， 如LBJ_023_week5s）
create table LYL_116_week5s(SNO varchar(4) primary key,SNAME varchar(5))- 建立如下课程表(命名格式“姓名拼音_三位学号_week5c”， 如LBJ_023_week5c）
create table LYL_116_week5c(CNO varchar(5),CNAME varchar(6),primary key(CNO))- 建立如下学生表(命名格式“姓名拼音_三位学号_week5sc”， 如LBJ_023_week5sc）
create table LYL_116_week5sc(SNO varchar(4),CNO varchar(5),GRADE int,
foreign key(SNO) references LYL_116_week5s(SNO),
foreign key(CNO) references LYL_116_week5c(CNO))二、插入数据
这里我们先插入好数据，方便后续进行查询。
insert into LYL_116_week5s values('S1','老大');
insert into LYL_116_week5s values('S2','老二');
insert into LYL_116_week5s values('S3','老三');
insert into LYL_116_week5s values('S4','老四');
insert into LYL_116_week5s values('S5','老五');
insert into LYL_116_week5c values('C1','课一')
insert into LYL_116_week5c values('C2','课二')
insert into LYL_116_week5c values('C3','课三')
insert into LYL_116_week5c values('C4','课四')
insert into LYL_116_week5sc values('S1','C1',10)
insert into LYL_116_week5sc values('S1','C2',15)
insert into LYL_116_week5sc values('S1','C3',20)
insert into LYL_116_week5sc values('S1','C4',25)
insert into LYL_116_week5sc values('S2','C1',30)
insert into LYL_116_week5sc values('S2','C2',35)
insert into LYL_116_week5sc values('S2','C3',40)
insert into LYL_116_week5sc values('S3','C1',45)
insert into LYL_116_week5sc values('S3','C2',50)
insert into LYL_116_week5sc values('S4','C1',55)三、查询
- 检索至少选修一门课程的学生学号
select distinct SNO from LYL_116_week5sc- 检索一门课程都没选修的学生学号
select SNO from LYL_116_week5s where SNO
not in (select SNO from LYL_116_week5sc)- 检索至少选修两门课程的学生学号
select SNO from LYL_116_week5sc
group by SNO having count(SNO)>=2- 检索恰好选修一门课程的学生学号
select SNO from LYL_116_week5sc
group by SNO having count(SNO)=1- 检索至少选修三门课程的学生学号
select SNO from LYL_116_week5sc
group by SNO having count(SNO)>=3- 检索全部课程都选的学生学号
select SNO from LYL_116_week5s s where not exists(
select * from LYL_116_week5c c where not exists(
select * from LYL_116_week5sc sc
where s.SNO=sc.SNO and c.CNO=sc.CNO)
)- 检索选了S2所选全部课程的学生学号
select distinct SNO from LYL_116_week5sc sc1
where not exists(
select * from LYL_116_week5sc sc2
where sc2.SNO='S2'
and not exists(
select * from LYL_116_week5sc sc3
where sc3.SNO = sc1.SNO
and sc3.CNO = sc2.CNO
)
)本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-12-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
