【SQL刷题】DAY16----SQL高级联结专项练习

博主昵称：跳楼梯企鹅
博主主页面链接：主页传送门
博主专栏页面连接：网络安全专栏传送么门
创作初心：本博客的初心为与技术朋友们相互交流，每个人的技术都存在短板，博主也是一样，虚心求教，希望各位技术友给予指导。
博主座右铭：发现光，追随光，成为光，散发光;
博主研究方向：渗透测试、机器学习 ;
博主寄语：感谢各位技术友的支持，您的支持就是我前进的动力 ;

牛客刷题：网站传送门

---

目录

 一、SQL联结表

二、刷题

1.题目一

(1)题目

(2)题目难度

(3)示例

(4)代码

(5)运行截图

2.题目二

(1)题目

(2)题目难度

(3)示例

(4)代码

(5)运行截图

3.题目三

(1)题目

(2)题目难度

(3)示例

(4)代码

(5)运行截图

4.题目四

(1)题目

(2)题目难度

(3)示例

(4)代码

(5)运行截图

5.题目五

(1)题目

(2)题目难度

(3)示例

(4)代码

(5)运行截图

---

 一、SQL联结表

SQL最强大的功能之一就是在select中执行联结表（inner）。联结表是利用SQL的select语句能执行的最重要的操作，很好地理解联结及其语法是学习SQL的极其重要的部分。

二、刷题

1.题目一

(1)题目

题目：检索每个顾客的名称和所有的订单号（一）

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

 

(2)题目难度

本题难易程度：中等

(3)示例

输入：
 
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');
 
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
 
输出：
 
an|a5
andy|a1
ben|a2
hex|a7
tom|a4
tony|a3

(4)代码

SELECT 
  C.cust_name, O.order_num
FROM
  Customers AS C
  INNER JOIN Orders AS O USING(cust_id)
ORDER BY
  C.cust_name

(5)运行截图

2.题目二

(1)题目

题目：检索每个顾客的名称和所有的订单号（二）

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

(2)题目难度

本题难易程度：中等

(3)示例

输入：
 
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),('cust40','ace');
 
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
 
输出：
 
ace|None
an|a5
andy|a1
ben|a2
hex|a7
tom|a4
tony|a3

(4)代码

select
  cust_name,
  order_num
from
  Customers
  left join Orders using(cust_id)
order by
  cust_name asc

(5)运行截图

3.题目三

(1)题目

题目：返回产品名称和与之相关的订单号

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

(2)题目难度

本题难易程度：简单

(3)示例

输入：
 
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola'),
('a0023','soda');
 
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	order_num VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');
 
输出：
 
coffee|a1121
cola|a5
cola|a19
cola|a10
egg|a105
sockets|a200
sockets|a1100
soda|None

(4)代码

select prod_name, order_num
from Products a left join OrderItems b using(prod_id)
union
select prod_name, order_num
from Products a right join OrderItems b using(prod_id)
 
order by prod_name

(5)运行截图

4.题目四

(1)题目

题目：返回产品名称和每一项产品的总订单数

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

(2)题目难度

本题难易程度：中等

(3)示例

输入：
 
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola'),
('a0023','soda');
 
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	order_num VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');
 
输出：
 
coffee|1
cola|3
egg|1
sockets|2
soda|0

(4)代码

select
    prod_name,
    count(order_num) as orders
from Products
    left join OrderItems using(prod_id)
     
group by prod_name
 
order by 1

(5)运行截图

5.题目五

(1)题目

题目：列出供应商及其可供产品的数量

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

注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

(2)题目难度

本题难易程度：简单

(3)示例

输入：
 
DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('a0002'),
('a0013'),
('a0003'),
('a0010');
 
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','prod_id_iphone'),
('a00113','prod_id_tea'),
('a0003','prod_id_vivo phone'),
('a0010','prod_id_huawei phone');
 
输出：
 
a0002|1
a0003|1
a0010|1
a0013|0

(4)代码

select a.vend_id, count(prod_id) as prod_id
from Vendors a 
left join Products b 
using(vend_id)
group by a.vend_id
order by a.vend_id 

(5)运行截图

 三、小结

本篇文章为SQL刷题第16天

欢迎大家一起和博主来刷题SQL刷题点击开始学习