SQL练习(查询)

59 阅读5分钟

1.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件

描述

有表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date;Customers表含有cust_email 顾客邮件和cust_id顾客id

OrderItems表

prod_idorder_num
BR01a0001
BR01a0002
BR02a0003
BR02a0013

Orders表

order_numcust_idorder_date
a0001cust102022-01-01 00:00:00
a0002cust12022-01-01 00:01:00
a0003cust12022-01-02 00:00:00
a0013cust22022-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
  1. 子查询
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");

  1. 联结表
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_numitem_pricequantity
a110105
a211100
a21200
a421121
a5510
a2119
a775

Orders表含有字段order_num 订单号、cust_id顾客id

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

【问题】编写 SQL 语句,返回订单总价不小于1000 的客户名称和总额(OrderItems 表中的order_num)。

提示:需要计算总和(item_price 乘以 quantity)。按总额对结果进行排序,请使用INNER JOIN 语法。

【示例结果】

cust_nametotal_price
andy1050
ben1319
tom2242

【示例解析】

总额(item_price 乘以 quantity)大于等于1000的订单号,例如a2对应的顾客id为cust1,cust1的顾客名称cust_name是ben,最后返回ben作为order_num a2的quantity * item_price总和的结果1319。

  1. 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;

  1. 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)
  1. WHERE 用于筛选行,而 HAVING 用于筛选组。
  2. WHERE 在数据返回给应用程序前被应用,而 HAVING 在数据被分组并返回给应用程序之后被应用。
  3. WHERE 可以筛选基于某些列的任何单行数据,而 HAVING 只能筛选基于聚合函数(如 SUMAVG 等)的数据。

3. 返回产品名称和每一项产品的总订单数

描述

Products表为产品信息表含有字段prod_id产品id、prod_name产品名称

prod_idprod_name
a0001egg
a0002sockets
a0013coffee
a0003cola
a0023soda

OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id

prod_idorder_num
a0001a105
a0002a1100
a0002a200
a0013a1121
a0003a10
a0003a19
a0003a5

【问题】

使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和每一项产品的总订单数(不是订单号),并按产品名称升序排序。

【示例结果】

返回产品名称prod_name和订单号订单数orders

prod_nameorders
coffee1
cola3
egg1
sockets2
soda0

【示例解析】

返回产品和产品对应的实际支付的订单数,但是无实际订单的产品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_idquantity
a0001105
a0002100
a0002200
a00131121
a000310
a000319
a00035
BNBG10002

【问题】

  将两个 SELECT 语句结合起来,以便从 OrderItems表中检索产品 id(prod_id)和 quantity。其中,一个 SELECT 语句过滤数量为 100 的行,另一个 SELECT 语句过滤 id 以 BNBG 开头的产品,最后按产品 id 对结果进行升序排序。
注意:这次仅使用单个 SELECT 语句。

【示例结果】

    返回产品id prod_id和产品数量quantity

prod_idquantity
a0002100
BNBG10002

【示例解析】

    产品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;