目录
题目链接
https://www.nowcoder.com/exam/oj/ta?tpId=298
SQL题解汇总
https://blog.csdn.net/guliguliguliguli/article/details/126109166
说明
因为是子查询练习,所以,所有答案都是按照子查询的思路来写的。
SQL 91 返回购买价格为 10 美元或以上产品的顾客列表
答案
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price>=10);
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;
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'
)
);
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;
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;
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;