---

题目链接

https://www.nowcoder.com/exam/oj/ta?tpId=298

---

说明

因为是子查询练习，所以，所有答案都是按照子查询的思路来写的。

---

SQL 91 返回购买价格为 10 美元或以上产品的顾客列表

答案

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price>=10);
1
2
3

---

SQL 92 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

答案

SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (
    SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01')
ORDER BY order_date ASC;
1
2
3
4
5
6
7

---

SQL 93 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

答案

SELECT cust_email
FROM Customers
WHERE cust_id IN (
    SELECT cust_id
    FROM Orders
    WHERE order_num IN (
        SELECT order_num
        FROM OrderItems
        WHERE prod_id = 'BR01'
    )
);
1
2
3
4
5
6
7
8
9
10
11

---

SQL 94 返回每个顾客不同订单的总金额

答案

在FROM中使用子查询，按照订单号order_num进行分组（ORDER BY），在同一组的数据先求出每一个商品的总价（商品数量 x 出售价格），再求和（SUM(聚合函数)）。

注意，在题目给的示例中，有两个cust_id=2的记录，表示，是同一个顾客的两个订单，要分开显示

SELECT cust_id, total_ordered 
FROM Orders o1,
        ( SELECT order_num, SUM( item_price * quantity ) total_ordered 
         FROM OrderItems 
         GROUP BY order_num ) o2 
WHERE o1.order_num = o2.order_num 
ORDER BY total_ordered DESC;
1
2
3
4
5
6
7

---

SQL 95 从 Products 表中检索所有的产品名称以及对应的销售总数

答案

sql92语法

SELECT prod_name, quant_sold
FROM Products p1, (
    SELECT prod_id,SUM(quantity) quant_sold
    FROM OrderItems
    GROUP BY prod_id) p2
WHERE p1.prod_id = p2.prod_id;
1
2
3
4
5
6

sql99语法

SELECT prod_name, quant_sold
FROM Products p1 JOIN (
    SELECT prod_id,SUM(quantity) quant_sold
    FROM OrderItems
    GROUP BY prod_id) p2
ON p1.prod_id = p2.prod_id;
1
2
3
4
5
6