实验06 嵌套查询

实验目的

使学生进一步掌握加深DMSQL 语言中嵌套查询语句的理解和运用。

实验要求

（1） 掌握带有比较运算符子查询的方法；
（2） 掌握带有IN查询的方法；
（3） 掌握带有ANY（SOME）或ALL查询的方法；
（4） 掌握带有EXISTS查询的方法；
（5） 掌握综合查询的方法。

实验内容

查询年龄最大的客户姓名、电话、年龄。

 select 姓名,电话,年龄
from 客户表
where 年龄=(select max(年龄) 
			from 客户表)

查询客户表中年龄大于平均年龄的客户姓名，电话和地址。

select 姓名,电话,年龄
from 客户表
where 年龄>(select avg(年龄) 
			from 客户表)

查询卖家店名含有“电器”的商品名称、商品单价。

select 名称,单价
from 商品表
where 卖家编号 in (
	select 卖家编号 
	from 卖家表 
	where 店名 like '%电器%')

查询“北京市”客户所购买商品，按客户姓名排序输出客户姓名、地址和商品名称

select 姓名,地址,名称
from 商品表,客户表,购买记录表
where 客户表.客户编号=购买记录表.客户编号
and 商品表.商品编号=购买记录表.商品编号
and 地址 in (
	select 地址 
	from 客户表 
	where 地址 like '%北京市%')
order by 姓名  desc

5. 查询拥有不少于三种商品的卖家，按负责人升序排列输出店名、负责人、客服电话。

select 店名,负责人,客服电话
from 卖家表
where 卖家编号 in
	(select 卖家编号
	from 商品表
	group by 卖家编号
	having count(distinct 商品编号)>=3)
order by 负责人 asc

查询在一笔订单中购买超过三种商品的订单信息，显示为客户姓名、商品名称、数量、单价、折扣率、成交金额。

select 姓名 客户姓名,名称 商品名称,数量,单价,折扣率,数量*折扣率/100*单价 成交金额
from 客户表,购买记录表,商品表 
where 购买记录表.客户编号=客户表.客户编号
 and 购买记录表.商品编号=商品表.商品编号
 and 客户表.客户编号 in
 	(select 客户编号
 	from 购买记录表
 	group by 客户编号
 	having count(distinct 商品编号)>3)

查询有商品但是没有销售记录的店名、负责人和商品名称。

select 店名,负责人,名称 商品名称
from 卖家表,商品表
where 卖家表.卖家编号=商品表.卖家编号
and 商品编号 not in
	(select 商品编号
	from 购买记录表)

查询各个卖家所拥有的产品价格高于自己全部产品平均定价的商品信息。

select 名称,单价
from 商品表 x
where 单价 >
	(select avg(单价)
	from 商品表 y
	where x.卖家编号=y.卖家编号
	group by 卖家编号)

查询至少购买了“李思静”所购买的全部商品的所有客户名称、电话、地址。

select 姓名,电话,地址
from 客户表 x
where not exists 
	(select *
	from 购买记录表 y
	where 客户编号 =
		(select 客户编号
		from 客户表
		where 姓名='李思静'
		)
	and not exists
		(select *
		from 购买记录表 z
		where z.客户编号=x.客户编号
		and z.商品编号=y.商品编号
		)
	)

查询既购买了“计算机”又购买了“打印机”的客户姓名。（通过嵌套查询实现）

select 姓名
from 客户表 
where not exists
(select *
from 商品表
where 商品编号 in
	(select 商品编号
	from 商品表
	where 名称 in ('计算机','打印机')
	)
and not exists
	(select *
	from 购买记录表
	where 客户表.客户编号=购买记录表.客户编号
	and 商品表.商品编号=购买记录表.商品编号
	)
)

总结与思考

什么叫相关子查询与不相关子查询？

相关子查询：子查询的查询条件依赖于父查询
不相关子查询：子查询的查询条件不依赖于父查询

带有IN谓词的查询与带有EXISTS谓词的查询有时候是可以相互转换的，请举例说明。

教材例3-60，查询所有选修了1号课程的学生姓名
Select Sname
From Student
Where exists
（Select *
From Sc 
Where Sno=Student.Sno
And Cno=’1’)
可以改写为
Select Sname
From Student
Where Sno in
 (Select Sno             
  From SC
  Where Cno='1' 
 )

嵌套查询与连接查询有时候是可以相互转换的，请举例说明。

与上题同例
可以改写为
Select Sname
From Student,SC
Where Student.Sno=SC.Sno 
And Cno='1'

实验总结

1 第四题不是很会做，卡在这一题上应该有一天左 右。很疑惑两个没有直接关联的表怎么嵌套查询，一直在尝试只在父查询放一个表，发现结果总是错误的。问老师后发现，两个表必须要进行等值连接，然后再进行嵌套查询。
2 exists的双重否定查询也难住了我一会儿。Exists不查询具体的数值，只查询真假，这让我有些绕不清自己在查什么东西的真假，后来找了张草稿纸，画了张思维导图，并手写了一份代码才绕明白。按我的理解，这个题目的套路是：要查询至少有A的所有B的存在。最外层判断A，第二层判断B，嵌套内是A的所有B