SQL语句每日一练四

1.Customers表代表顾客信息含有顾客 idcust_id和 顾客名称cust_name

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders表代表订单信息含有订单号order_num和顾客 idcust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

【问题】使用 INNER JOIN 编写 SQL 语句，检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），最后根据顾客姓名 cust_name 升序返回。

select t1.cust_name,t2.order_num from Customers t1
					inner join Orders t2 using(cust_id)
order by t1.cust_name;

2.Orders表代表订单信息含有订单号order_num和顾客 idcust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

Customers表代表顾客信息含有顾客 idcust_id和 顾客名称cust_name

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex
cust40	ace

【问题】检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），列出所有的顾客，即使他们没有下过订单。最后根据顾客姓名 cust_name 升序返回。

select cust_name,order_num from Customer
							left join Orders using(cust_id)
order by cust_name;

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

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

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

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

【问题】使用外连接（left join、 right join、full join）联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

select prod_name,order_num from Products 
left join OrderItems using(prod_id)
order by prod_name;

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

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems表为订单信息表含有字段order_num订单号和产品 idprod_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）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

select prod_name,count(order_num) as total from Products 
left join OrderItesm using(prod_id)
group by prod_name
order by prod_name;

5.有 Vendors 表含有 vend_id （供应商 id）

vend_id
a0002
a0013
a0003
a0010

有 Products 表含有 vend_id（供应商 id）和 prod_id（供应产品 id）

vend_id	prod_id
a0001	egg
a0002	prod_id_iphone
a00113	prod_id_tea
a0003	prod_id_vivo phone
a0010	prod_id_huawei phone

【问题】列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量，最后根据 vend_id 升序排序。

select vend_id,count(prod_id) as total from Vendors 
left join Products using(vend_id)
group by vend_id
order by vend_id;

6.表 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 prod_id,quantity from OrderItems where quantity = 100
union 
select prod_id,quantity from OrderItems where prod_id = 'BEGIN%';

7.表 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 语句。

select prod_id,quantity from OrderItems where prod_id like 'BEGIN%' or quantity = 100;   

8.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）并返回，然后按产品名称对结果进行升序排序。

# UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
select prod_name from Products 
union
select cust_name from Customers
order by prod_name;

9.表 Customers 含有字段 cust_name 顾客名、cust_contact 顾客联系方式、cust_state 顾客州、cust_email 顾客 email

cust_name	cust_contact	cust_state	cust_email
cust10	8695192	MI	cust10@cust.com
cust1	8695193	MI	cust1@cust.com
cust2	8695194	IL	cust2@cust.com

【问题】修正下面错误的 SQL

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'ORDER BY cust_name;

select cust_name,cust_contact,cust_email from Customers where cust_state = 'MI'
union
select cust_name,cust_contact,cust_email from Customers where cust_state = 'IL'
order by cust_name;

select cust_name,cust_contact,cust_name from Customers where cust_state = 'MI' or cust_state = 'IL' order by cust_name;