SQL语句练习03

目录

一、建表

二、插入数据

三、查询

---

一、建表

        这里先建好我们下面查询需要的表，方便后续查询。

1. 建立如下学生表(命名格式“姓名拼音_三位学号_week5s”， 如LBJ_023_week5s）

create table LYL_116_week5s(SNO varchar(4) primary key,SNAME varchar(5))

1. 建立如下课程表(命名格式“姓名拼音_三位学号_week5c”， 如LBJ_023_week5c）

create table LYL_116_week5c(CNO varchar(5),CNAME varchar(6),primary key(CNO))

1. 建立如下学生表(命名格式“姓名拼音_三位学号_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)

三、查询

1. 检索至少选修一门课程的学生学号

select distinct SNO from LYL_116_week5sc

1. 检索一门课程都没选修的学生学号

select SNO from LYL_116_week5s where SNO 
not in (select SNO from LYL_116_week5sc)

1. 检索至少选修两门课程的学生学号

select SNO from LYL_116_week5sc 
group by SNO having count(SNO)>=2

1. 检索恰好选修一门课程的学生学号

select SNO from LYL_116_week5sc 
group by SNO having count(SNO)=1

1. 检索至少选修三门课程的学生学号

select SNO from LYL_116_week5sc 
group by SNO having count(SNO)>=3

1. 检索全部课程都选的学生学号

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)
)

1. 检索选了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
  )
)