SQL 入门教程:联结(JOIN)练习题

本文内容

  1. 编写 SQL 语句,返回 Customers 表中的顾客名称(cust_name)和 Orders 表中的相关订单号(order_num),并按顾客名称再按订单号对结果进行排序。实际上是尝试两次,一次使用简单的等联结语法,一次使用 INNER JOIN

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    
    -- 等值连接语法
    SELECT cust_name, order_num 
    FROM Customers, Orders 
    WHERE Customers.cust_id = Orders.cust_id 
    ORDER BY cust_name, order_num; 
    -- ANSI INNER JOIN 语法
    SELECT cust_name, order_num 
    FROM Customers INNER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id 
    ORDER BY cust_name, order_num;
    
  2. 我们来让上一题变得更有用些。除了返回顾客名称和订单号,添加第三列 OrderTotal,其中包含每个订单的总价。有两种方法可以执行此操作:使用 OrderItems 表的子查询来创建 OrderTotal 列,或者将 OrderItems 表与现有表联结并使用聚合函数。提示:请注意需要使用完全限定列名的地方。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    
    -- 使用子查询的解法
    SELECT cust_name, 
    order_num, 
    (SELECT Sum(item_price*quantity) 
    FROM OrderItems 
    WHERE Orders.order_num=OrderItems.order_num) AS OrderTotal 
    FROM Customers, Orders 
    WHERE Customers.cust_id = Orders.cust_id 
    ORDER BY cust_name, order_num; 
    -- 使用联结的解法
    SELECT cust_name, 
    Orders.order_num, 
    Sum(item_price*quantity) AS OrderTotal 
    FROM Customers, Orders, OrderItems 
    WHERE Customers.cust_id = Orders.cust_id 
    AND Orders.order_num = OrderItems.order_num 
    GROUP BY cust_name, Orders.order_num 
    ORDER BY cust_name, order_num;
    
  3. 我们重新看一下 子查询练习题 的练习题 2。编写 SQL 语句,检索订购产品 BR01 的日期,这一次使用联结和简单的等联结语法。输出应该与 子查询练习题 的输出相同。

    1
    2
    3
    4
    5
    
    SELECT cust_id, order_date 
    FROM Orders, OrderItems 
    WHERE Orders.order_num = OrderItems.order_num 
    AND prod_id = 'BR01' 
    ORDER BY order_date;
    
  4. 很有趣,我们再试一次。重新创建为 子查询练习题 练习题 3 编写的 SQL 语句,这次使用 ANSI 的 INNER JOIN 语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它,需要两个 INNER JOIN 语句,每个语句的格式类似于本部分讲到的 INNER JOIN 示例,而且不要忘记 WHERE 子句可以通过 prod_id 进行过滤。

    1
    2
    3
    4
    5
    
    SELECT cust_email 
    FROM Customers 
    INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
    INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num 
    WHERE prod_id = 'BR01';
    
  5. 再让事情变得更加有趣些,我们将混合使用联结、聚合函数和分组。准备好了吗?回到 GROUP BY 练习题,当时的挑战是要求查找值等于或大于 1000 的所有订单号。这些结果很有用,但更有用的是订单数量至少达到这个数的顾客名称。因此,编写 SQL 语句,使用联结从 Customers 表返回顾客名称(cust_name),并从 OrderItems 表返回所有订单的总价。

提示:要联结这些表,还需要包括 Orders 表(因为 Customers 表与 OrderItems 表不直接相关,Customers 表与 Orders 表相关,而 Orders 表与 OrderItems 表相关)。不要忘记 GROUP BYHAVING,并按顾客名称对结果进行排序。你可以使用简单的等联结或 ANSI 的 INNER JOIN 语法。或者,如果你很勇敢,请尝试使用两种方式编写。

```sql
-- 等值连接语法
SELECT cust_name, SUM(item_price*quantity) AS total_price 
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id 
AND Orders.order_num = OrderItems.order_num 
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000 
ORDER BY cust_name;

-- ANSI INNER JOIN 语法
SELECT cust_name, SUM(item_price*quantity) AS total_price 
FROM Customers 
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num 
GROUP BY cust_name 
HAVING SUM(item_price*quantity) >= 1000 
ORDER BY cust_name;
```

请参阅

(完)

comments powered by Disqus

本文内容