SQL 入门教程:GROUP BY 练习题

本文内容

  1. OrderItems 表包含每个订单的每个产品。编写 SQL 语句,返回每个订单号(order_num)各有多少行数(order_lines),并按 order_lines 对结果进行排序。

    1
    2
    3
    4
    
    SELECT order_num, COUNT(*) as order_lines 
    FROM OrderItems 
    GROUP BY order_num 
    ORDER BY order_lines;
    
  2. 编写 SQL 语句,返回名为 cheapest_item 的字段,该字段包含每个供应商成本最低的产品(使用 Products 表中的 prod_price),然后从最低成本到最高成本对结果进行排序。

    1
    2
    3
    4
    
    SELECT vend_id, MIN(prod_price) AS cheapest_item 
    FROM Products 
    GROUP BY vend_id 
    ORDER BY cheapest_item;
    
  3. 确定最佳顾客非常重要,请编写 SQL 语句,返回至少含 100 项的所有订单的订单号(OrderItems 表中的 order_num)。

    1
    2
    3
    4
    5
    
    SELECT order_num 
    FROM OrderItems 
    GROUP BY order_num 
    HAVING SUM(quantity) >= 100 
    ORDER BY order_num;
    
  4. 确定最佳顾客的另一种方式是看他们花了多少钱。编写 SQL 语句,返回总价至少为 1000 的所有订单的订单号(OrderItems 表中的 order_num)。提示:需要计算总和(item_price 乘以 quantity)。按订单号对结果进行排序。

    1
    2
    3
    4
    5
    
    SELECT order_num, SUM(item_price*quantity) AS total_price 
    FROM OrderItems 
    GROUP BY order_num 
    HAVING SUM(item_price*quantity) >= 1000 
    ORDER BY order_num;
    
  5. 下面的 SQL 语句有问题吗?(尝试在不运行的情况下指出。)

    1
    2
    3
    4
    5
    
    SELECT order_num, COUNT(*) AS items
    FROM OrderItems
    GROUP BY items
    HAVING COUNT(*) >= 3
    ORDER BY items, order_num;
    

    答:GROUP BY 项是错误的。GROUP BY 必须是实际列,而不是用于执行汇总计算的列。允许使用 GROUP BY order_num

请参阅

(完)

comments powered by Disqus

本文内容