SQL 必知必会 50 题（41 - 45）

最新推荐文章于 2024-07-22 22:21:01 发布
原创 最新推荐文章于 2024-07-22 22:21:01 发布 · 1.3k 阅读 · 6 ·
CC 4.0 BY-SA版权
首发地址：https://cunyu1943.github.io，转载请署名出处！
文章标签：

#SQL #MySQL #数据库

本文探讨如何通过SQL查询OrderItems和Orders表，计算并筛选出订单总价超过1000元的顾客及其总花费，揭示他们的消费习惯。

SQL41 确定最佳顾客的另一种方式（二）

描述

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。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);


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

解答

主要考察的知识点：

  • 取别名：AS 关键字
  • 求和：SUM()
  • GROUP BY 和 HAVING 同时使用用于过滤结果
  • 排序：ORDER BY 关键字

主要考察对多个 SQL 关键字的运用，同时还需要注意 SQL 中关键字的前后顺序。

SELECT cust_name, SUM(item_price * quantity) AS total_price
FROM OrderItems, Orders, Customers
WHERE OrderItems.order_num = Orders.order_num AND Orders.cust_id = Customers.cust_id
GROUP BY cust_name
HAVING total_price >= 1000
ORDER BY total_price;

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

描述

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

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

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

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

问题

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

示例结果

返回顾客名称 cust_name 和订单号 order_num

cust_nameorder_num
ana5
andya1
bena2
hexa7
toma4
tonya3

示例

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

解答

主要考察 INNER JOIN 的用法，其使用语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

要实现本题，利用上述语法填入对应字段名和表名即可，最后则是关键字 ORDER BY 的使用。

SELECT cust_name, Orders.order_num FROM Customers JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name;

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

描述

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

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

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

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex
cust40ace

问题

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

示例结果

返回顾客名称 cust_name 和订单号 order_num

cust_nameorder_num
aceNULL
ana5
andya1
bena2
hexa7
toma4
tonya3

示例解析

基于两张表，返回订单号 a1 的顾客名称 andy 等人，没有下单的顾客 ace 也统计了进来。

示例

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

解答

根据题意，主要以 Customers 表中的列为主，然后取 Customers 和 Orders 中的交集。对于 Orders 表中不存在的列则取值 null。所以可以使用外联结中的 LEFT JION，其使用语法如下：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

套用上述语法，填入对应表和列名即可实现，最后则是再加入简单的排序即可。

SELECT cust_name, Orders.order_num FROM Customers LEFT JOIN Orders On Orders.cust_id = Customers.cust_id ORDER BY cust_name;

SQL44 返回产品名称和与之相关的订单号

描述

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）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

示例结果

返回产品名称 prod_name 和订单号 order_num

prod_nameorder_num
coffeea1121
colaa5
colaa19
colaa10
egga105
socketsa200
socketsa1100
sodaNULL

示例解析

返回产品和对应实际支付订单的订单号，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。

示例

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

解答

此题解法类似于 43 题，主要使用 OUTER JOIN 中的左联结 LEFT JOIN，主要清除相关语法，然后套用填入表名和列名即可。

SELECT prod_name, OrderItems.order_num FROM Products 
LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
ORDER BY prod_name;

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

描述

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 也返回，最后根据产品名称升序排序。

示例

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

解答

考察的知识点仍然是外连接，只不过加入了 COUNT() 函数用于分组统计，最后同样是简单的排序。

SELECT prod_name, COUNT(order_num) AS orders
FROM Products LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
GROUP BY prod_name ORDER BY prod_name;

致谢

感谢牛客网提供的题目列表。

【SQL刷题】Day13----SQL分组数据专项练习
你在看屏幕的同时，屏幕也在注视着你
09-04 1320
Day13----SQL分组专项练习
《SQL必知必会第五版》第十四章（组合查询）挑战题
不蒸馒头的博客
03-25 778
我知道这有点荒谬，但这节课中的一个注释提到过。（尝试在不运行的情况下指出。）并返回，然后按产品名称对结果进行排序。OrderItems表中检索产品 ID。中的顾客名称（cust_name。语句，组合 Products。语句过滤数量为 100。语句结合起来，以便从。
mysql游标触发器批量_MySQL必知必会笔记（六）存储过程 游标 触发器
weixin_36408592的博客
02-02 325
留印：http://blog.sina.com.cn/s/articlelist_1254871964_5_1.html第二十三章 使用存储过程MySQL5中添加了存储过程的支持。大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不...
SQL子查询
xrj333的博客
01-23 1089
利用子查询进行过滤 需要订购物品TNT2的所有客户 检索包含物品TNT2的所有订单的编号 检索具有前一步骤列出的订单编号的所有客户的ID 检索前一步骤返回的所有客户ID的客户信息 列出包含此物品的订单 select order_num from orderitems where prod_id = 'TNT2' ; 查询具有订单a，b的客户ID select cust_id from ord...
SQL如何分析商品订单？（表连接的区别、汇总函数）
前景
08-08 912
SQL如何分析商品订单？（表连接的区别、汇总函数）
SQL必知必会（第四版）
01-09
《SQL必知必会》第四版是一本专为数据库初学者设计的教程，它以其基础、易懂和实践性强的特点，成为了许多人学习SQL语言的首选。SQL，全称Structured Query Language，即结构化查询语言，是用于管理和处理关系数据库...
记录自己做《SQL必知必会》挑战题
isdongdong的博客
08-11 1271
问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。...
《SQL必知必会第五版》自留笔记
不蒸馒头的博客
03-27 2192
注：本书介绍的SQL主要适用于以下系统：IBM DB2,Microsoft SQL Server,MariaDB,MySQL,
【SQL必知必会】初学者快速入门
最新发布
m0_67357141的博客
07-22 708
SQLStructured Query Language（结构 化查询语言），标准SQL由ANSI标准委员会管理，从而称为。
mysql订单表设计
得塔云的博客
03-08 3482
请注意，在设计数据库时，确保字段的命名具有描述性且遵循命名规范，以便其他开发人员能够轻松理解字段的用途。此外，合理设置索引（如主键和外键）可以提高查询性能并维护数据完整性。设计MySQL中的订单表时，表名和字段的选择应基于您的业务需求和数据库设计原则。这些字段是基于常见的订单管理需求设计的。根据您的具体业务需求，您可能还需要添加其他字段或调整现有字段。
数据分析笔试经典sql题解
joychun的博客
03-05 4526
前言：sql是数据分析师笔试必考的考点之一，常考的题型有行列转换、联表查询，这些都比较简单，一般考的最难的就是hivesql窗口函数联表查询，普通的聚合函数每组(Group by)只返回一个值，而窗口函数则可为窗口中的每行都返回一个值。下面分享几道数据分析笔试中常考的经典sql题。（本文默认大家了解窗口函数语法） 1、行列转换（京东数据分析笔试题） 表sales year m1 ...
2021年全国职业院校技能大赛 “大数据技术与应用”—模拟赛题（四）
white_mvlog的博客
03-24 4784
2021年全国职业院校技能大赛 “大数据技术与应用” ——模拟赛题（四） 持续更新相关赛题，包括2021年国赛模拟题10套、2019年广东省真题赛题、相关比赛技巧、难点突破技巧等等，需要的关注小编或者公众号 NoWrite
MySQL练习三
bao_14440的博客
07-22 336
继续在company数据库中根据以下图示创建表，表结构如下，并插入以下数据，完成下面的sql。
SQL 必知必会 50 题（46 - 50）
村雨遥
04-13 898
牛客网 SQL 必知必会 50 题
订单表order，字段有：order_id(订单ID), user_id(用户ID),amount(金额), pay_datetime(付费时间),channel_id(渠道ID),dt(分区字段)
beimu101的博客
04-29 8500
SQL
电子商务(电销)平台中订单模块(Order)数据库设计明细（转载）
weixin_30701521的博客
03-17 2424
电子商务(电销)平台中订单模块(Order)数据库设计明细 以下是自己在电子商务系统设计中的订单模块的数据库设计经验总结，而今发表出来一起分享，如有不当，欢迎跟帖讨论~ 订单表 (order)|-- 自动编号（order_id, 自增长主键）|-- 订单单号（order_no, 唯一值，供客户查询）|-- 商店编号（shop_id, 商店表自动编号）|-- 订单状态 (order_status...
sql多表联合查询示例
xnzhangxiaowei5的博客
04-22 850
这个查询会返回 "users" 表中的所有用户，以及与其相关联的 "orders" 表中的订单日期和订单金额，如果用户没有订单，对应的订单日期和订单金额会返回 NULL 值。这个查询会返回 "orders" 表中的所有订单，以及与其相关联的 "users" 表中的用户名，如果订单没有关联的用户，对应的用户名会返回 NULL 值。这个查询会返回 "users" 表中的所有用户和 "orders" 表中的所有订单的组合，这是一种非常耗时的查询方式，一般不建议使用，除非有特殊的需求。
SQL必知必会第十二节挑战题
weixin_54215671的博客
10-21 3074
1．编写SQL语句，返回Customers表中的顾客名称（cust_name）和Orders表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行排序。 （1）等值联结 select cust_name,order_num from customers,orders where customers.cust_id=orders.cust_id order by order_num; （2）使用inner join select cust_name,order_num from
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

村雨遥

众筹一毛买键盘！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值