牛客网 SQL 83-95

SQL83 确定已售出产品的总数

select sum(quantity) as items_ordered
from OrderItems;

SQL84 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id = 'BR01';

SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格

select max(prod_price) as max_price
from Products
where prod_price <= 10;

SQL86 返回每个订单号各有多少行数

select order_num,count(order_num) as order_lines
from OrderItems
group by order_num
order by order_lines;

SQL87 每个供应商成本最低的产品

select vend_id, min(prod_price) as cheapest_item
from Products 
group by vend_id
order by cheapest_item;

SQL88 返回订单数量总和不小于100的所有订单的订单号

select order_num 
from OrderItems
group by order_num
having sum(quantity) >= 100
order by order_num;

SQL89 计算总和

【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。

select order_num,sum(item_price * quantity) as total_price
from OrderItems 
group by order_num
having total_price >= 1000
order by order_num;

SQL90 纠错3

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

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

select cust_id
from Orders
where order_num in (
    select order_num 
    from OrderItems
    group by order_num
    having sum(item_price) >= 10
);

SQL92 确定哪些订单购买了 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;

方法二：连接表

select b.cust_id,b.order_date
from OrderItems a , Orders b
where a.order_num = b.order_num and a.prod_id = 'BR01'
order by order_date;

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

方法一：连接表（inner join）

select c.cust_email
from OrderItems a , Orders b ,Customers c
where 
a.order_num = b.order_num
and
a.prod_id = 'BR01'
and
b.cust_id = c.cust_id;

方法二：子查询

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'
    )
);

方法三：连接表（left join）

select c.cust_email
from Orders a 
left join OrderItems b on a.order_num = b.order_num
left join Customers c on a.cust_id = c.cust_id
where b.prod_id = 'BR01';

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

select b.cust_id,sum(a.item_price * a.quantity) as total_ordered
from OrderItems a ,Orders b
where a.order_num = b.order_num
group by cust_id
order by total_ordered desc;

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

select a.prod_name,sum(b.quantity) as quant_sold
from Products a ,OrderItems b
where a.prod_id = b.prod_id
group by a.prod_name;

参考：https://javaguide.cn/database/sql/sql-questions-01.html