如何执行和理解实验二中的表连接查询以及表综合查询操作？

### 实验二：表连接查询及表综合查询

#### 一、实验目的与要求
1. **掌握表连接查询的使用方法**：
- 内连接（INNER JOIN）
- 左外连接（LEFT OUTER JOIN）
- 右外连接（RIGHT OUTER JOIN）
- 交叉连接（CROSS JOIN）

2. **掌握子查询的使用方法**：
- 能够使用上述连接方法解决具体的问题

4. **熟悉系统函数的使用**：
- 如聚合函数、字符串处理函数等

5. **熟练应用SQL语句**：
- SELECT语句及其相关子句（如 WHERE, GROUP BY, ORDER BY 等）

6. **综合应用能力**：
- 将SELECT与系统函数、IF ELSE、WHILE等语句结合，解决复杂问题

#### 二、实验内容

1. **查找每个供应商供应的商品名称**：

   SELECT S.SupplierName, P.ProductName
   FROM Suppliers S
   INNER JOIN Products P ON S.SupplierID = P.SupplierID;

2. **查找与“李云”签订采购单的供应商名称**：

   SELECT S.SupplierName
   FROM Suppliers S
   INNER JOIN PurchaseOrders PO ON S.SupplierID = PO.SupplierID
   INNER JOIN PurchasingAgents PA ON PO.AgentID = PA.AgentID
   WHERE PA.AgentName = '李云';

3. **使用IN关键字完成相同查询**：

   SELECT SupplierName
   FROM Suppliers
   WHERE SupplierID IN (
       SELECT SupplierID
       FROM PurchaseOrders
       WHERE AgentID = (SELECT AgentID FROM PurchasingAgents WHERE AgentName = '李云')
   );

4. **使用左外连接查找所有采购员签订的采购合同详细信息**：

   SELECT *
   FROM PurchasingAgents A
   LEFT OUTER JOIN PurchaseOrders B ON A.AgentID = B.AgentID
   LEFT OUTER JOIN Contracts C ON B.ContractID = C.ContractID;

5. **查找所有客户购买的商品详细信息**：

   SELECT C.CustomerName, P.ProductName, O.Quantity, P.UnitPrice
   FROM Customers C
   LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
   LEFT OUTER JOIN Products P ON O.ProductID = P.ProductID;

6. **使用内连接查找每个供应商供应的商品种类**：

   SELECT S.SupplierName, COUNT(DISTINCT P.ProductCategory) AS CategoryCount
   FROM Suppliers S
   INNER JOIN Products P ON S.SupplierID = P.SupplierID
   GROUP BY S.SupplierName;

7. **查找购买了编号为‘A001’的供应商供应的商品的客户名称**：

   SELECT C.CustomerName
   FROM Customers C
   INNER JOIN Orders O ON C.CustomerID = O.CustomerID
   INNER JOIN Products P ON O.ProductID = P.ProductID
   WHERE P.SupplierID = (SELECT SupplierID FROM Suppliers WHERE SupplierCode = 'A001');

8. **查找销售员‘王良’在2005年签订的销售合同详细信息**：

   SELECT *
   FROM SalesAgents SA
   INNER JOIN SalesContracts SC ON SA.AgentID = SC.AgentID
   WHERE SA.AgentName = '王良' AND YEAR(SC.ContractDate) = 2005;

9. **查询所有供应‘鲜橙多’的供应商的最低报价**：

   SELECT MIN(P.UnitPrice)
   FROM Suppliers S
   INNER JOIN Products P ON S.SupplierID = P.SupplierID
   WHERE P.ProductName = '鲜橙多';

10. **查找销售员‘王良’在2005年签订的所有销售合同中每一类商品的总金额**：

    SELECT P.ProductCategory, SUM(O.TotalAmount) AS TotalAmount
    FROM SalesAgents SA
    INNER JOIN SalesContracts SC ON SA.AgentID = SC.AgentID
    INNER JOIN OrderDetails OD ON SC.OrderID = OD.OrderID
    INNER JOIN Products P ON OD.ProductID = P.ProductID
    WHERE SA.AgentName = '王良' AND YEAR(SC.ContractDate) = 2005
    GROUP BY P.ProductCategory;

11. **汇总由姓名为‘刘明’的采购员在2004年采购的‘数码相机’的总金额**：

    SELECT SUM(PO.TotalAmount) AS TotalAmount
    FROM PurchasingAgents PA
    INNER JOIN PurchaseOrders PO ON PA.AgentID = PO.AgentID
    INNER JOIN Products P ON PO.ProductID = P.ProductID
    WHERE PA.AgentName = '刘明' AND YEAR(PO.PurchaseDate) = 2004 AND P.ProductName = '数码相机';

12. **汇总由姓名为‘刘明’的采购员在2005年采购的各类商品的数量**：

    SELECT P.ProductCategory, SUM(PO.Quantity) AS TotalQuantity
    FROM PurchasingAgents PA
    INNER JOIN PurchaseOrders PO ON PA.AgentID = PO.AgentID
    INNER JOIN Products P ON PO.ProductID = P.ProductID
    WHERE PA.AgentName = '刘明' AND YEAR(PO.PurchaseDate) = 2005
    GROUP BY P.ProductCategory;

13. **查找没有供应任何一类商品的供应商的名字**：

    SELECT S.SupplierName
    FROM Suppliers S
    LEFT OUTER JOIN Products P ON S.SupplierID = P.SupplierID
    WHERE P.ProductID IS NULL;

14. **查找在2006年各个客户购买商品的总金额，并按总金额降序排序**：

    SELECT C.CustomerName, SUM(O.TotalAmount) AS TotalAmount
    FROM Customers C
    INNER JOIN Orders O ON C.CustomerID = O.CustomerID
    WHERE YEAR(O.OrderDate) = 2006
    GROUP BY C.CustomerName
    ORDER BY TotalAmount DESC;

15. **使用左连接查找每个销售人员销售的商品详细信息**：

    SELECT SA.AgentName, SC.ContractID, C.CustomerName, P.ProductName, OD.Quantity, P.UnitPrice
    FROM SalesAgents SA
    LEFT OUTER JOIN SalesContracts SC ON SA.AgentID = SC.AgentID
    LEFT OUTER JOIN OrderDetails OD ON SC.OrderID = OD.OrderID
    LEFT OUTER JOIN Products P ON OD.ProductID = P.ProductID
    LEFT OUTER JOIN Customers C ON SC.CustomerID = C.CustomerID;

16. **查找每个采购员和每个供应商签订的合同的总金额**：

    SELECT PA.AgentName, S.SupplierName, SUM(PO.TotalAmount) AS TotalAmount
    FROM PurchasingAgents PA
    INNER JOIN PurchaseOrders PO ON PA.AgentID = PO.AgentID
    INNER JOIN Suppliers S ON PO.SupplierID = S.SupplierID
    GROUP BY PA.AgentName, S.SupplierName;

#### 三、自我测试

1. **使用右外连接查找所有采购员签订的采购合同详细信息，没有签订采购单的采购员也要显示**：

    SELECT *
    FROM PurchaseOrders PO
    RIGHT OUTER JOIN PurchasingAgents PA ON PO.AgentID = PA.AgentID
    LEFT OUTER JOIN Contracts C ON PO.ContractID = C.ContractID;

2. **查找购买了名称为‘联想集团’的供应商供应的商品的客户名称**：

    SELECT C.CustomerName
    FROM Customers C
    INNER JOIN Orders O ON C.CustomerID = O.CustomerID
    INNER JOIN Products P ON O.ProductID = P.ProductID
    WHERE P.SupplierID = (SELECT SupplierID FROM Suppliers WHERE SupplierName = '联想集团');

3. **查找编号为‘A002’的供应商没有供应的商品的名称**：

    SELECT P.ProductName
    FROM Products P
    LEFT OUTER JOIN Suppliers S ON P.SupplierID = S.SupplierID
    WHERE S.SupplierCode = 'A002' AND P.ProductID IS NULL;

4. **分别使用UNION和INNER JOIN关键字完成以下查询：查询由‘三高计算机公司’和‘联想集团’供应商的所有商品的商品ID**：

    -- Using UNION
    SELECT ProductID
    FROM Products
    WHERE SupplierID IN (
        SELECT SupplierID FROM Suppliers WHERE SupplierName = '三高计算机公司'
    )
    UNION
    SELECT ProductID
    FROM Products
    WHERE SupplierID IN (
        SELECT SupplierID FROM Suppliers WHERE SupplierName = '联想集团'
    );

    -- Using INNER JOIN
    SELECT P.ProductID
    FROM Products P
    INNER JOIN Suppliers S ON P.SupplierID = S.SupplierID
    WHERE S.SupplierName IN ('三高计算机公司', '联想集团');

通过这些示例，可以更好地理解和应用各种表连接查询和综合查询技术，从而解决实际数据库管理中的复杂问题。