sql查询练习题（下）

Orders表

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序（提示：这一次使用联结和简单的等联结语法）。

答案：select cust_id,order_date from Orders where order_num in (select order_num from OrderItems where prod_id='BR01') order by order_date;

40、描述

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

OrderItems表

Orders表

Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序（提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法）。

答案：

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

2. select cust_email from Customers inner join Orders on Customers.cust_id=Orders.cust_id inner join OrderItems on Orders.order_num=OrderItems.order_num where prod_id='BR01';

41、描述

OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单order_num和item_price商品售出价格、quantity商品数量

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

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

编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）（提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法）。

答案：select cust_name,total_price from Customers inner join Orders on Customers.cust_id = Orders.cust_id inner join (select order_num,sum(item_price*quantity) as total_price from OrderItems group by order_num) t1 on t1.order_num = Orders.order_num where total_price >= 1000 order by total_price;

42、描述

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

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

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

答案：select cust_name,order_num from Customers inner join Orders on Orders.cust_id=Customers.cust_id order by cust_name;

43、描述

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

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

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

答案：select cust_name,order_num from Customers left join Orders on Customers.cust_id=Orders.cust_id order by cust_name;

分析：关键词：left join

用法：

• 内联结：inner join。取两列的交集。

• 外联结：

• left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取null。

• right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null。

44、描述

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

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

使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

答案：select prod_name,order_num from Products left join OrderItems on Products.prod_id=OrderItems.prod_id order by prod_name;

45、描述

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

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

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

答案：select prod_name,if(orders is null,0,orders) as orders from Products left join (select prod_id,count(prod_id) as orders from OrderItems group by prod_id) as t1 on Products.prod_id = t1.prod_id order by prod_name;

46、描述

有Vendors表含有vend_id供应商id.

有Products表含有供应商id和供应产品id

列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量，最后根据vend_id 升序排序（注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它）。

答案：select Vendors.vend_id,if(prod_num is null,0,prod_num) as prod_id from Vendors left join (select vend_id,count(prod_id) as prod_num from Products group by vend_id) t1 on t1.vend_id=Vendors.vend_id order by Vendors.vend_id;

47、描述

表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量

将两个 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 like 'BNBG%' order by prod_id;

分析

关键词：union

用法：

• join---连接表，对列操作

• union--连接表，对行操作。

• union--将两个表做行拼接，同时自动删除重复的行。

• union all---将两个表做行拼接，保留重复的行。

48、描述

表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量。

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

答案：select prod_id,quantity from OrderItems where quantity = 100 or prod_id like 'BNBG%' order by prod_id;

49、描述

Products表含有字段prod_name代表产品名称

Customers表代表顾客信息，cust_name代表顾客名称

编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

答案：select prod_name from Products union select cust_name from Customers order by prod_name;

50、描述

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

【问题】修正下面错误的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;

【示例结果】

返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客email：cust_email

【示例解析】

返回住在"IL"和"MI"的顾客信息，最后根据顾客名称升序排序。

答案：

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;