目录
一、建表
这里先建好我们下面查询需要的表,方便后续查询。
- 建立如下学生表(命名格式“姓名拼音_三位学号_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
)
)