sql试题--经典三表问题----4----

最新推荐文章于 2024-07-09 15:20:37 发布

静山晚风

最新推荐文章于 2024-07-09 15:20:37 发布

阅读量2.2k

点赞数

分类专栏： DB-Common 面试宝典

面试宝典同时被 2 个专栏收录

53 篇文章

订阅专栏

DB-Common

36 篇文章

订阅专栏

本文通过具体实例，详细介绍了如何使用标准SQL嵌套语句进行复杂的数据查询操作，包括但不限于查询特定条件下的学员信息、课程信息等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为管理岗位业务培训信息，建立3个表:

S(S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C (C#,CN) C#,CN 分别代表课程编号、课程名称

cs (S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

create table s 
(sid varchar(22),sn varchar(22),sd varchar(22),sa varchar(22));
insert into s values('1','zhangsan','cn','219');
insert into s values('2','zhangsan2','cn','129');
insert into s values('3','zhangsan3','cn','290');
insert into s values('4','zhangsan4','cn','329');

create table c(cid varchar(22), cn varchar(22));
insert into c values('1','yuwen');
insert into c values('2','yuwen');
insert into c values('3','yuwen');
insert into c values('4','税收基础');
insert into c values('4','yuwen2');


create table cs (sid varchar(22),cid varchar(22),g float);
insert into cs values('1','1','473.8');
insert into cs values('1','2','447.8');
insert into cs values('2','1','147.8');
insert into cs values('2','2','247.8');
insert into cs values('3','1','477.8');

insert into cs values('1','4','4522.8');
insert into cs values('2','4','45.8');
insert into cs values('3','4','37.8');
insert into cs values('1','4','87.8');


select s1.sid, s1.sn from s s1 where s1.sid in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid and c1.cn= '税收基础');

比较复杂的第二种方法：

select s1.sid, s1.sn from s s1 where s1.sid in(
select cs1.sid 
from cs cs1 
	where cs1.cid = 
		(
			select c1.cid from c c1 
			where c1.cn='税收基础'
		)
);

2 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

select s1.sn, s1.sd from s s1 where s1.sid in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid);
3 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
select s1.sn, s1.sd from s s1 where s1.sid not in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid);

使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

select sn,sd
from s
where not exists(
select *
from c
where not exists(
select *
from cs
where sid=s.sid
and cid=c.cid))

SQL 语言中exists，代表‘存在’，带有exists的子查询不返回任何数据，只返回代表‘不’的true和代表‘肯定’的false.not exists表示不存在。选修全部课程的学员姓名和所属单位用逻辑来表达就是‘任意’课程‘他都选’，转换之后，就变成‘没有’一门课程‘他不选’。第一个not exists代表‘没有’，中间的‘select *
from c’表示‘一门课程’，因为是从c中选的嘛，第二个not exists 表示‘不’，下面的select *
from sc
where s#=s.s#
and c#=c.c#表示他选，合起来就是‘他不选’

注：参见王珊的数据库系统概论第三版 P113

查询选修了课程的学员人数

select count(distinct(sid)) from cs;
6 查询选修课程超过5门的学员学号和所属单位

select count(*) from s where sid in(
select sid from cs group by sid having count(sid)=(select count(cid) from c)
) ;
与第四个非常类似
自己看别人写也不理解，但是自己写了就明白了

稍微变下条件

S(SNO,SNAME) 学生关系。SNO 为学号，SNAME为姓名

C(CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE)选课关系。SCGRADE 为成绩

7 找出没有选修过“李明”老师讲授课程的所有学生姓名

第一种方法：

select sn from s where sid not in
(select cs1.sid from cs cs1 where cs1.cid not in
(select c1.cid from c c1 where c1.ct ='laoshi2')
)

第二种方法：
select sn from s where not exists (select * from c,cs where s.sid=cs.sid and cs.cid = c.cid and
c.ct='laoshi2');

8 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select s.sid,s.sn,(select avg(g) from cs where cs.sid=s.sid) from s where s.sid in (select sid from cs where cs.g< '200' group by sid having count(*) >=2);

列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

select s.sn from s where
s.sid in (select cs.sid from cs ,c where cs.cid = c.cid and c.cn in ('yuwen','yuwen2') group by cs.sid having count(cs.sid) =2 )

第二种方法：非常类似

select s.sn from s where
s.sid in (select cs.sid from cs ,c where cs.cid = c.cid and (c.cn ='yuwen' or c.cn='yuwen2') group by cs.sid having count(cs.sid) =2 )

小结

mysql from 表名不能用select代替，
防止出现笛卡尔积就用相对做条件。

having后面不能接字句，只能是函数