提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL 语句练习03

SQL 语句 练习 03
2023-06-13 09:15:55 时间

目录

一、建表

二、插入数据

三、查询

一、建表

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

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

相关文章