Use TPCH;
Go
-- IN嵌套查询
-- 查询订购了“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECT custkey, name
FROM Sales.Customer
WHERE custkey IN(
    SELECT O.custkey
    FROM Sales.Orders AS O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
    WHERE O.orderkey = L.orderkey
    AND L.partkey = PS.partkey
    AND L.suppkey = PS.suppkey
    AND PS.partkey = P.partkey
    AND P.mfgr = N'海大' AND P.name = N'船舶模拟驾驶舱'
);
Go
-- 单层EXISTS嵌套查询
-- 查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECT custkey, name
FROM Sales.Customer C
WHERE NOT EXISTS(
    SELECT O.custkey
    FROM Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
    WHERE C.custkey = O.custkey
    AND O.orderkey = L.orderkey
    AND L.partkey = PS.partkey
    AND L.suppkey = PS.suppkey
    AND PS.partkey = P.partkey
    AND P.mfgr = N'海大' AND P.name = N'船舶模拟驾驶舱'
);
Go
-- 双层EXISTS嵌套查询
-- 查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。
SELECT CA.name
FROM Sales.Customer CA
WHERE NOT EXISTS(
    SELECT *
    FROM Sales.Customer CB, Sales.Orders OB, Sales.Lineitem LB
    WHERE CB.custkey = OB.custkey
    AND OB.orderkey = LB.orderkey
    AND CB.name = N'张三'
    AND NOT EXISTS(
        SELECT *
        FROM Sales.Orders OC, Sales.Lineitem LC
        WHERE CA.custkey = OC.custkey
        AND OC.orderkey = LC.orderkey
        AND LB.suppkey = LC.suppkey
        AND LB.partkey = LC.partkey
    )
);
Go
-- FROM子句中的嵌套查询
-- 查询订单平均金额超过1万元的顾客中的中国籍顾客信息。
SELECT C.*
FROM Sales.Customer C,(
    SELECT custkey
    FROM Sales.Orders
    GROUP BY custkey
    HAVING AVG(totalprice) > 10000
) B, Sales.Nation N
WHERE C.custkey = B.custkey
AND C.nationkey = N.nationkey
AND N.name = N'中国';
Go
-- 集合查询（交）
-- 查询顾客“张三”和“李四”都订购过的全部零件的信息。
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'张三'
INTERSECT
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'李四';
Go
-- 集合查询（并）
-- 查询顾客“张三”和“李四”订购的全部零件的信息。
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'张三'
UNION
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'李四';
Go
-- 集合查询（差）
-- 顾客“张三”订购过而“李四”没订购过的零件的信息。
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'张三'
EXCEPT
SELECT P.*
FROM Sales.Customer C, Sales.Orders O, Sales.Lineitem L, Sales.Partsupp PS, Sales.Part P
WHERE C.custkey = O.custkey
AND O.orderkey = L.orderkey
AND L.suppkey = PS.suppkey
AND L.partkey = PS.partkey
AND PS.partkey = P.partkey
AND C.name = N'李四';
Go