sql嵌套查询（实验6）

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

1. 查询销售商品包含“计算机”或“电脑”的商店信息。

 

SELECT s."商店名称", s."负责人", s."客服电话", s."地址"  
FROM "销售管理"."商店表" s  
WHERE s."商店编号" IN (  
    SELECT "商店编号"  
    FROM "销售管理"."商品表"  
    WHERE "名称" LIKE '%计算机%' OR "名称" LIKE '%电脑%'  
)

2.查询没有购买商品的客户姓名、电话、地址，分别用NOT IN和NOT EXISTS实现查询

NOT IN 是在主查询的表（这里是 "销售管理"."客户表"）中选择那些不在给定列表（这里是子查询从 "销售管理"."订单表" 中选择的所有 "客户编号"）中的记录。它并不需要建立连接，因为它只是简单地检查一个值是否在一个列表中。

SELECT "姓名" as "客户姓名", "电话", "地址"  
FROM "销售管理"."客户表"  
WHERE "客户编号" NOT IN (SELECT "客户编号" FROM "销售管理"."订单表");

而 NOT EXISTS 则是检查子查询是否为空。如果子查询为空，那么主查询就会选择该行数据。子查询中的每一行都与主查询中的一行进行比较，看是否满足某些条件（在这里是 "客户编号" 是否相等）。因此，需要明确地建立连接，即子查询的结果需要与主查询的某个列相关联。

SELECT "姓名", "电话", "地址"  
FROM "销售管理"."客户表" c  
WHERE NOT EXISTS (  
    SELECT 1  
    FROM "销售管理"."订单表" o   
    WHERE c."客户编号" = o."客户编号"  
);

这里子查询会返回一个布尔值，表示是否存在匹配的行。如果存在匹配的行，返回 true 或 1，表示存在；如果找不到匹配的行，返回 false 或 0，表示不存在。然后，主查询使用 NOT EXISTS 来反转这个结果，即如果子查询找不到匹配的行，主查询就会选择该行。

3.查询没有销售记录的商品，输出商店名称、负责人和商品名称。

SELECT s."商店名称", s."负责人", t."名称"    
FROM "销售管理"."商品表" t    
LEFT JOIN "销售管理"."商店表" s ON t."商店编号" = s."商店编号"    
WHERE t."商品编号" NOT IN (    
    SELECT DISTINCT o."商品编号"    
    FROM "销售管理"."订单表" o    
    JOIN "销售管理"."商品表" t ON o."商品编号" = t."商品编号"  
);

4.通过嵌套查询与“晓晓电脑之家”是同一负责人的商店名称和地址。

子查询嵌套在FROM或者JOIN子句中

SELECT s1.商店名称, s1.地址  
FROM 销售管理.商店表 s1  
JOIN (  
    SELECT DISTINCT 负责人  
    FROM 销售管理.商店表  
    WHERE 商店名称 = '晓晓电脑之家'  
) s2 ON s1.负责人 = s2.负责人;

或者

SELECT s2."商店名称", s2."地址"  
FROM "销售管理"."商店表" s1  
JOIN "销售管理"."商店表" s2 ON s1."负责人" = s2."负责人"  
WHERE s1."商店名称" = '晓晓电脑之家';

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

SELECT "姓名", "电话",(2023-YEAR(出生日期)) AS 年龄  
FROM "销售管理"."客户表"  
ORDER BY 年龄 DESC  
LIMIT 1;

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

select s.商店名称,s.负责人,s.客服电话
from 销售管理.商店表 s
where 商店编号 in(
select 商店编号 from 销售管理.商品表 a group by a.商店编号 
HAVING COUNT(a.商品编号) >= 2  
)order by 负责人 asc

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

select s.姓名,t.名称,b.数量,t.单价,b.折扣率,数量*单价*折扣率 as 成交金额
from 销售管理.商品表 t,销售管理.订单表 b,销售管理.客户表 s 
WHERE   
    t.商品编号 = b.商品编号   
    AND b.客户编号 = s.客户编号   
    AND EXISTS (  
        SELECT 1   
        FROM 销售管理.订单表 b2   
        WHERE b2.客户编号 = b.客户编号   
        GROUP BY b2.订单编号  
        HAVING COUNT(*) > 3   
    );

注意：在GROUP BY语句中选择商品编号。如选择了商品编号，那么SQL将为每个唯一的客户编号-商品编号组合创建一个组，这并不是想要的结果

8.查询各个商店所拥有的产品价格不低于自己全部产品平均定价的商品信息。

select 商品编号,商店编号,名称,单价 from 销售管理.商品表 a
where 单价>=(
select avg(单价) from 销售管理.商品表 b
where a.商店编号=b.商店编号
group by 商店编号
)

9.通过派生表查询各个商店所拥有的产品价格不低于自己全部产品平均定价的商品信息。

SELECT a.商品编号, a.商店编号, a.名称, a.单价  
FROM 销售管理.商品表 a  
INNER JOIN (  
    SELECT 商店编号, AVG(单价) AS 均价  
    FROM 销售管理.商品表  
    GROUP BY 商店编号  
) AS b ON a.商店编号 = b.商店编号  
WHERE a.单价 >= b.均价;

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

set schema 销售管理
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.商品编号
		)
)

11.查询既购买了“计算机”又购买了“打印机”的客户姓名。（仅限嵌套查询）

SELECT s."商店名称", s."负责人", s."客服电话"  
FROM "销售管理"."商店表" s  
JOIN "销售管理"."商品表" p ON s."商店编号" = p."商店编号"  
GROUP BY s."商店名称", s."负责人", s."客服电话"  
HAVING COUNT(p."商品编号") >= 2  
ORDER BY s."负责人" ASC;