sql查询练习题
sql查询练习题（1-3 检索数据，4-7 排序检索数据，8-11 过滤数据，12-15 高级数据过滤，16-19 用通配符进行过滤，20-21 创建计算字段，22-23 使用函数处理数据，24-26 汇总数据，27-31 分组数据，32-36 使用子查询，37-41 联结表，42-46 创建高级联结，47-50 组合查询）
1、编写 SQL 语句，从 Customers 表中检索所有的cust_id
答案：select cust_id from Customers;
2、编写SQL 语句，从表OrderItems中检索并列出所有已订购商品（prod_id）的去重后的清单
答案：select distinct prod_id from OrderItems;
3、现在有Customers 表（表中含有列cust_id代表客户id，cust_name代表客户姓名）。需要编写 SQL语句，检索所有列
答案：select cust_id,cust_name from Customers;
4、有表Customers，cust_id代表客户id，cust_name代表客户姓名。从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。
答案：select cust_name from Customers order by cust_name desc;
5、编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。
答案：select cust_id,order_num from Orders order by cust_id,order_date desc;
6、编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。
答案：select quantity,item_price from OrderItems order by quantity desc,item_price desc;
7、下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name逆序排列
SELECT vend_name,
FROM Vendors
ORDER vend_name DESC;
答案：
SELECT vend_name
FROM Vendors
ORDER by vend_name DESC;
8、从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格（prod_price）为 9.49 美元的产品。
答案：select prod_id,prod_name from Products where prod_price=9.49;
9、编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格（prod_price）为 9 美元或更高的产品
答案：select prod_id,prod_name from Products where prod_price>=9;
10、编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序
答案：select prod_name,prod_price from Products where prod_price between 3 and 6 order by prod_price;
11、从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品
答案：select distinct order_num from OrderItems where quantity>=100;
12、Vendors表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）。编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA）
答案：select vend_name from Vendors where vend_country = 'USA' and vend_state = 'CA';
13、OrderItems 表包含了所有已订购的产品（有些已被订购多次）。编写SQL 语句，查找所有订购了数量至少100 个的 BR01、BR02 或BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤
答案：select order_num,prod_id,quantity from OrderItems where quantity>=100 and prod_id in ('BR01','BR02','BR03');
14、有表Products。编写 SQL 语句，返回所有价格在 3美元到 6美元之间的产品的名称（prod_name）和价格（prod_price），使用 AND操作符，然后按价格对结果进行升序排序
答案：select prod_name,prod_price from Products where prod_price >= 3 and prod_price <= 6 order by prod_price;
15、修改正确下面sql，使之正确返回
SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';
答案：
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name;
16、编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称
答案：select prod_name,prod_desc from Products where prod_desc like '%toy%';
17、编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序
答案：select prod_name,prod_desc from Products where prod_desc not like '%toy%' order by prod_name;
18、编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较
答案：select prod_name,prod_desc from Products where prod_desc like '%toy%' and prod_desc like '%carrots%';
19、编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可
答案：select prod_name,prod_desc from Products where prod_desc like '%toy%carrots%';
20、编写 SQL 语句，从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，将 vend_name重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address重命名为 vaddress，按供应商名称对结果进行升序排序
答案：select vend_id,vend_name as vname,vend_address as vaddress,vend_city as vcity from Vendors order by vend_name;
21、我们的示例商店正在进行打折促销，所有产品均降价 10%。Products表包含prod_id产品id、prod_price产品价格。编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）
答案：select prod_id,prod_price,prod_price * 0.9 as sale_price from Products;
22、编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名
答案：select cust_id,cust_name,upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3))) as user_login from Customers;
23、Orders订单表。编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序
答案：select order_num,order_date from Orders where order_date like '2020-01%' order by order_date;
24、OrderItems表代表售出的产品，quantity代表售出商品数量。编写 SQL 语句，确定已售出产品的总数。返回items_ordered列名，表示已售出商品的总数
答案：select sum(quantity) as items_ordered from OrderItems;
25、OrderItems表代表售出的产品，quantity代表售出商品数量，产品项为prod_id。返回items_ordered列名，表示已售出商品的总数，确定已售出产品项（prod_id）为"BR01"的总数
答案：select sum(quantity) as items_ordered from OrderItems where prod_id = 'BR01';
26、编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。返回max_price
答案：select max(prod_price) as max_price from Products where prod_price <= 10;
27、OrderItems 表包含每个订单的每个产品。编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines对结果进行升序排序
答案：select order_num,count(order_num) as order_lines from OrderItems group by order_num order by order_lines;
28、有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id。编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。返回供应商id vend_id和对应供应商成本最低的产品cheapest_item
答案：select vend_id,min(prod_price) as cheapest_item from Products group by vend_id order by cheapest_item;
29、OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。返回order_num订单号（where过滤行；having过滤分组）
答案：select distinct order_num from OrderItems group by order_num having sum(quantity)>=100 order by order_num;
30、OrderItems表代表订单信息，包括字段：订单号order_num和item_price商品售出价格、quantity商品数量。编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序（提示：总价 = item_price 乘以 quantity）
答案：select distinct order_num,sum(item_price * quantity) as total_price from OrderItems group by order_num having sum(quantity * item_price) >= 1000 order by order_num;
31、OrderItems表含有order_num订单号。将下面代码修改正确后执行，返回订单号order_num和出现的次数items
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
答案：
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num #用主键分组
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
32、OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num。使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）
答案：select distinct cust_id from Orders where order_num in (select order_num from OrderItems where item_price >=10);
33、表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date。编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序
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 |
答案：select cust_id,order_date from Orders where order_num in (select order_num from OrderItems where prod_id = 'BR01');
34、你想知道订购 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
| cust_id | cust_email |
|---|---|
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序（提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 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'));
35、 我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。
OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。
| order_num | item_price | quantity |
|---|---|---|
| a0001 | 10 | 105 |
| a0002 | 1 | 1100 |
| a0002 | 1 | 200 |
| a0013 | 2 | 1121 |
| a0003 | 5 | 10 |
| a0003 | 1 | 19 |
| a0003 | 7 | 5 |
Orders表订单号：order_num、顾客id：cust_id
| order_num | cust_id |
|---|---|
| a0001 | cust10 |
| a0002 | cust1 |
| a0003 | cust1 |
| a0013 | cust2 |
编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序（提示：你之前已经使用 SUM()计算订单总数）。
答案：
select cust_id,
(select sum(item_price*quantity)
from OrderItems
where OrderItems.order_num=Orders.order_num group by order_num)
as total_ordered from Orders order by total_ordered desc;
##############################################################################
select cust_id,
(select total_ordered
from
(select distinct order_num,round(sum(item_price*quantity)) as total_ordered
from OrderItems
group by order_num
) as t1
where t1.order_num=Orders.order_num
) as total_ordered
from Orders
order by total_ordered desc;
36、Products 表中检索所有的产品名称：prod_name、产品id：prod_id
| prod_id | prod_name |
|---|---|
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
OrderItems代表订单商品表，订单产品：prod_id、售出数量：quantity
| prod_id | quantity |
|---|---|
| a0001 | 105 |
| a0002 | 1100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）
答案：
select prod_name,
(select sum(quantity)
from OrderItems
where OrderItems.prod_id=Products.prod_id
group by prod_id
) as quant_sold
from Products;
37、描述
Customers 表有字段顾客名称cust_name、顾客id cust_id
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
Orders订单信息表，含有字段order_num订单号、cust_id顾客id
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNER JOIN。
答案：
- select cust_name,order_num from Customers,Orders where Customers.cust_id=Orders.cust_id
order by cust_name,order_num; - select cust_name,order_num from Customers inner join Orders on Customers.cust_id=Orders.cust_id
order by cust_name,order_num;
38、描述
Customers 表有字段，顾客名称：cust_name、顾客id：cust_id
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price
| order_num | quantity | item_price |
|---|---|---|
| a1 | 1000 | 10 |
| a2 | 200 | 10 |
| a3 | 10 | 15 |
| a4 | 25 | 50 |
| a5 | 15 | 25 |
| a7 | 7 | 7 |
除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。
答案：select cust_name,o.order_num,OrderTotal from (select order_num,sum(quantity*item_price) OrderTotal from OrderItems group by order_num ) t join Orders o on t.order_num = o.order_num join Customers c on o.cust_id = c.cust_id order by cust_name,o.order_num;
39、描述
表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date
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 |
编写 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表
| 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
| cust_id | cust_email |
|---|---|
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序（提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法）。
答案：
- 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')); - 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商品数量
| 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 语法）。
答案：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
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
Orders表代表订单信息含有订单号order_num和顾客id cust_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 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
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
Customers表代表顾客信息含有顾客id cust_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 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产品名称
| 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）和与之相关的订单号（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产品名称
| 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）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。
答案：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.
| vend_id |
|---|
| a0002 |
| a0013 |
| a0003 |
| a0010 |
有Products表含有供应商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 升序排序（注意：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代表产品数量
| 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 like 'BNBG%' order by prod_id;
分析
关键词：union
用法：
- join---连接表，对列操作
- union--连接表，对行操作。
-
- union--将两个表做行拼接，同时自动删除重复的行。
-
- union all---将两个表做行拼接，保留重复的行。
48、描述
表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 quantity = 100 or prod_id like 'BNBG%' order by prod_id;
49、描述
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）并返回，然后按产品名称对结果进行升序排序。
答案：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
| 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;
【示例结果】
返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客email：cust_email
| cust_name | cust_contact | cust_email |
|---|---|---|
| cust1 | 8695193 | cust1@cust.com |
| cust10 | 8695192 | cust10@cust.com |
| cust2 | 8695194 | cust2@cust.com |
【示例解析】
返回住在"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;
