1.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
描述
有表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date;Customers表含有cust_email 顾客邮件和cust_id顾客id
OrderItems表
prod_id | order_num |
BR01 | a0001 |
BR01 | a0002 |
BR02 | a0003 |
BR02 | a0013 |
Orders表
order_num | cust_id | order_date |
a0001 | cust10 | 2022-01-01 00:00:00 |
a0002 | cust1 | 2022-01-01 00:01:00 |
a0003 | cust1 | 2022-01-02 00:00:00 |
a0013 | cust2 | 2022-01-01 00:20:00 |
Customers表代表顾客信息,cust_id为顾客id,cust_email为顾客email
【问题】返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件(Customers 表中的 cust_email),结果无需排序。
提示:涉及到 SELECT 语句,最内层的从 OrderItems 表返回 order_num,中间的从 Customers 表返回 cust_id,但是必须使用 INNER JOIN 语法。
【示例结果】返回顾客email cust_email
cust_email |
cust10@cust.com |
cust1@cust.com |
- 子查询
select cust_email
from Customers
where
cust_id in (select b.cust_id from OrderItems a Inner Join Orders b on a.order_num = b.order_num where a.prod_id = "BR01");
- 联结表
select cust_email
from Customers a
Inner Join
Orders b
on a.cust_id = b.cust_id
Inner JOIN
OrderItems c
on b.order_num = c.order_num
where c.prod_id = "BR01";
2.确定最佳顾客的另一种方式(二)
描述
OrderItems表代表订单信息,确定最佳顾客的另一种方式是看他们花了多少钱,OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量
order_num | item_price | quantity |
a1 | 10 | 105 |
a2 | 1 | 1100 |
a2 | 1 | 200 |
a4 | 2 | 1121 |
a5 | 5 | 10 |
a2 | 1 | 19 |
a7 | 7 | 5 |
Orders表含有字段order_num 订单号、cust_id顾客id
order_num | cust_id |
a1 | cust10 |
a2 | cust1 |
a3 | cust2 |
a4 | cust22 |
a5 | cust221 |
a7 | cust2217 |
顾客表Customers有字段cust_id 客户id、cust_name 客户姓名
cust_id | cust_name |
cust10 | andy |
cust1 | ben |
cust2 | tony |
cust22 | tom |
cust221 | an |
cust2217 | hex |
【问题】编写 SQL 语句,返回订单总价不小于1000 的客户名称和总额(OrderItems 表中的order_num)。
提示:需要计算总和(item_price 乘以 quantity)。按总额对结果进行排序,请使用INNER JOIN 语法。
【示例结果】
cust_name | total_price |
andy | 1050 |
ben | 1319 |
tom | 2242 |
【示例解析】
总额(item_price 乘以 quantity)大于等于1000的订单号,例如a2对应的顾客id为cust1,cust1的顾客名称cust_name是ben,最后返回ben作为order_num a2的quantity * item_price总和的结果1319。
- Inner Join
SELECT cust_name, sum(oi.item_price * oi.quantity) AS total_price
From OrderItems oi
Inner Join
Orders o
on oi.order_num = o.order_num
Inner JOIN
Customers c
on o.cust_id = c.cust_id
group by c.cust_name
having total_price >= 1000
order by total_price;
- Where
> - 1. select cust_name,sum(item_price*quantity) as total_price
> - 2. from OrderItems a,Orders b,Customers c
> - 3. where a.order_num = b.order_num and b.cust_id = c.cust_id
> - 4. group by cust_name
> - 5. having sum(item_price*quantity) >= 1000
> - 6. order by sum(item_price*quantity)
WHERE
用于筛选行,而HAVING
用于筛选组。WHERE
在数据返回给应用程序前被应用,而HAVING
在数据被分组并返回给应用程序之后被应用。WHERE
可以筛选基于某些列的任何单行数据,而HAVING
只能筛选基于聚合函数(如SUM
、AVG
等)的数据。
3. 返回产品名称和每一项产品的总订单数
描述
Products表为产品信息表含有字段prod_id产品id、prod_name产品名称
prod_id | prod_name |
a0001 | egg |
a0002 | sockets |
a0013 | coffee |
a0003 | cola |
a0023 | soda |
OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id
prod_id | order_num |
a0001 | a105 |
a0002 | a1100 |
a0002 | a200 |
a0013 | a1121 |
a0003 | a10 |
a0003 | a19 |
a0003 | a5 |
【问题】
使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和每一项产品的总订单数(不是订单号),并按产品名称升序排序。
【示例结果】
返回产品名称prod_name和订单号订单数orders
prod_name | orders |
coffee | 1 |
cola | 3 |
egg | 1 |
sockets | 2 |
soda | 0 |
【示例解析】
返回产品和产品对应的实际支付的订单数,但是无实际订单的产品soda也返回,最后根据产品名称升序排序。
Select prod_name, count(order_num) as orders
From Products p
Left Join OrderItems o
on p.prod_id = o.prod_id
group by prod_name
order by prod_name;
将两个 SELECT 语句结合起来(二)(Union关键字)
描述
表OrderItems包含订单产品信息,字段prod_id代表产品id、quantity代表产品数量。
prod_id | quantity |
a0001 | 105 |
a0002 | 100 |
a0002 | 200 |
a0013 | 1121 |
a0003 | 10 |
a0003 | 19 |
a0003 | 5 |
BNBG | 10002 |
【问题】
将两个 SELECT 语句结合起来,以便从 OrderItems表中检索产品 id(prod_id)和 quantity。其中,一个 SELECT 语句过滤数量为 100 的行,另一个 SELECT 语句过滤 id 以 BNBG 开头的产品,最后按产品 id 对结果进行升序排序。
注意:这次仅使用单个 SELECT 语句。
【示例结果】
返回产品id prod_id和产品数量quantity
prod_id | quantity |
a0002 | 100 |
BNBG | 10002 |
【示例解析】
产品id a0002因为数量等于100被选取返回;BNBG因为是以 BNBG 开头的产品所以返回;最后以产品id进行排序返回。
Select prod_id, quantity
From OrderItems
Where quantity = 100
UNION
SELECT prod_id, quantity
FROM OrderItems
Where prod_id like "BNBG%"
order by prod_id;
5.组合 Products 表中的产品名称和 Customers 表中的顾客名称#
描述
Products表含有字段prod_name代表产品名称
prod_name |
flower |
rice |
ring |
umbrella |
Customers表代表顾客信息,cust_name代表顾客名称
cust_name |
andy |
ben |
tony |
tom |
an |
lee |
hex |
【问题】
编写 SQL 语句,组合 Products 表中的产品名称(prod_name)和 Customers 表中的顾客名称(cust_name)并返回,然后按产品名称对结果进行升序排序。
【示例结果】
prod_name |
an |
andy |
ben |
flower |
hex |
lee |
rice |
ring |
tom |
tony |
umbrella |
【示例解析】
拼接cust_name和prod_name并根据结果升序排序
题解:
Select prod_name
From Products
Union
SELECT cust_name As prod_name
From Customers
order by prod_name;