SQL 必知必会 50 题（31 - 35）

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

#SQL #MySQL #数据库

SQL31 纠错3

描述

OrderItems 表含有 order_num 订单号

order_num
a002
a002
a002
a004
a007

问题

将下面代码修改正确后执行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

示例结果

返回订单号 order_num 和出现的次数 items

order_numitems
a0023

示例解析

由于订单号 a002 出现了三次，所以返回3

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

解答

最主要的错误在于 GROUP BY 后边跟着的是统计结果，其次在于 HAVING 后边的 COUNT() 其实是可以不用再次计算的，可以直接利用已经统计出的结果。第二处不算错误，但是改了之后能提高 SQL 语句所执行的效率。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num;

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

描述

OrderItems 表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders 表代表订单信息表，含有顾客 id：cust_id 和订单号：order_num

OrderItems 表

order_numitem_price
a110
a21
a21
a42
a55
a21
a77

Orders表

order_numcust_id
a1cust10
a2cust1
a2cust1
a4cust2
a5cust5
a2cust1
a7cust7

问题

使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

示例结果

返回顾客 id cust_id

cust_id
cust10

示例解析

cust10 顾客下单的订单为 a1，a1 的售出价格大于等于 10

示例

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 '售出价格'
  );
  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

  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'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

解答

题目已经提示使用子查询，则先用最简单的条件查询从 OrderItems 表中找出订单价格不低于 10 美元的订单，接着从筛选出的结果中再次筛选出对应订单的顾客 id，需要注意的一点是对顾客 id 去重，需要使用到关键字 DISTINCT。

SELECT DISTINCT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price >= 10)

SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

描述

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

OrderItems 表

prod_idorder_num
BR01a0001
BR01a0002
BR02a0003
BR02a0013

Orders表

order_numcust_idorder_date
a0001cust102022-01-01 00:00:00
a0002cust12022-01-01 00:01:00
a0003cust12022-01-02 00:00:00
a0013cust22022-01-01 00:20:00

问题

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

示例结果

返回顾客 id cust_id 和定单日期 order_date。

cust_idorder_date
cust102022-01-01 00:00:00
cust12022-01-01 00:01:00

示例解析

产品 id 为 “BR01” 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00

示例

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 ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  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',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('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');

解答

使用子查询，先从 OrderItems 表中查询出 prod_id 为 BR01 的记录 ，然后再从 Orders 表中筛选出 order_num 为子查询结果集中的记录，最后按照 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;

SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

描述

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

OrderItems 表

prod_idorder_num
BR01a0001
BR01a0002
BR02a0003
BR02a0013

Orders 表

order_numcust_idorder_date
a0001cust102022-01-01 00:00:00
a0002cust12022-01-01 00:01:00
a0003cust12022-01-02 00:00:00
a0013cust22022-01-01 00:20:00

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

cust_idcust_email
cust10cust10@cust.com
cust1cust1@cust.com
cust2cust2@cust.com

问题

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

示例结果

返回顾客 email cust_email

cust_email
cust10@cust.com
cust1@cust.com

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客email cust_email 分别是：cust10@cust.com 、cust1@cust.com

示例

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 ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  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',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('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');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

解答

多重条件查询的过滤，只要细心一点，就能做出来。拆分为 3 个条件查询后，从内向外依次查询，然后基于上一层查询结果再做条件过滤。

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

SQL35 返回每个顾客不同订单的总金额

描述

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems 表代表订单信息，OrderItems 表有订单号：order_num 和商品售出价格：item_price、商品数量：quantity。

order_numitem_pricequantity
a000110105
a000211100
a00021200
a001321121
a0003510
a0003119
a000375

Orders 表订单号：order_num、顾客 id：cust_id

order_numcust_id
a0001cust10
a0002cust1
a0003cust1
a0013cust2

问题

编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

提示：你之前已经使用 SUM() 计算订单总数。

示例结果

返回顾客 id cust_id 和 total_order 下单总额

cust_idtotal_ordered
cust22242
cust11300
cust101050
cust2104

示例解析

cust2 在 Orders 里面的订单 a0013，a0013 的售出价格是 2 售出数量是 1121，总额是 2242，最后返回 cust2 的支付总额是 2242。

示例

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 ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

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 ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

解答

题目要求利用子查询，则实现如下。

SELECT cust_id, (SELECT SUM(item_price * quantity) FROM OrderItems WHERE OrderItems.order_num = Orders.order_num) AS total_ordered FROM Orders ORDER BY total_ordered DESC;

此外我们可以使用内连接方式：利用 SUM() 函数进行求和，求出每个顾客的订单总数，然后通过条件查询找出两个表中 order_num 相同记录的并通过 cust_id 分组，最后则是将求和的订单总数倒序排列。

SELECT cust_id, SUM(item_price * quantity) AS total_ordered FROM OrderItems, Orders WHERE OrderItems.order_num = Orders.order_num GROUP BY cust_id ORDER BY total_ordered DESC;

致谢

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

牛客-SQL篇-SQL必知必会-10使用子查询-使用子查询-SQL94返回每个顾客不同订单的总金额
SunshineLu2019的博客
11-24 1267
编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。【示例解析】cust2在Orders里面的订单a0013，a0013的售出价格是2售出数量是1121，总额是2242，最后返回cust2的支付总额是2242。OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。
你必知必会的sql练习
Town
03-18 399
Part 1 练习题和参考解 （1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩 SELECT s1.StudentNo, s1.score AS '001', s2.score AS '002' FROM score s1, score s2 WHERE s1.CourseNo = 1 AND s2.Co...
《SQL必知必会》（第五版）挑战题练习记录
juajuafu的博客
01-03 1215
SQL必知必会（第五版）挑战题的个人练习记录
SQL 必知必会 50 题（1 - 5）
村雨遥
04-04 1663
牛客网 SQL 必知必会 50 题
《SQL必知必会》--第4课过滤数据及课后练习
weixin_45212094的博客
08-02 392
《SQL必知必会》--第4课过滤数据及课后练习部分示例语句where子句 用于过滤数据order by子句用于排序，但要放在where子句之后[！=]不匹配查询between...and... 用于范围值的检查is null 用于空值的检查课后练习 部分示例语句 where子句 用于过滤数据 ①从products表中查询prod_price为3.49的prod_name和prod_price。 select prod_name,prod_price from [dbo].[Products] where p
《测试必知必会系列》48道经典sql练习题
qq_39075632的博客
05-05 609
建表 课程表： create table course ( id int auto_increment primary key, name varchar(10) null, t_id int null ); 教师表： create table teacher ( id int auto_increment primary key, n...
SQL必知必会（第四版）
01-09
《SQL必知必会》第四版是一本专为数据库初学者设计的教程，它以其基础、易懂和实践性强的特点，成为了许多人学习SQL语言的首选。SQL，全称Structured Query Language，即结构化查询语言，是用于管理和处理关系数据库...
《SQL必知必会第五版》第十四章（组合查询）挑战题
不蒸馒头的博客
03-25 780
我知道这有点荒谬，但这节课中的一个注释提到过。（尝试在不运行的情况下指出。）并返回，然后按产品名称对结果进行排序。OrderItems表中检索产品 ID。中的顾客名称（cust_name。语句，组合 Products。语句过滤数量为 100。语句结合起来，以便从。
记录自己做《SQL必知必会》挑战题
isdongdong的博客
08-11 1273
问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。...
《SQL必知必会第五版》自留笔记
不蒸馒头的博客
03-27 2201
注：本书介绍的SQL主要适用于以下系统：IBM DB2,Microsoft SQL Server,MariaDB,MySQL,
【SQL必知必会】初学者快速入门
最新发布
m0_67357141的博客
07-22 710
SQLStructured Query Language（结构 化查询语言），标准SQL由ANSI标准委员会管理，从而称为。
《SQL必知必》会数据库实例
10-04
《SQL必知必》会数据库实例
【SQL牛客网刷题】SQL必知必会50题
weixin_46861115的博客
09-05 4967
上次做这个还是一年前，这两天打算把所有的都刷一遍，做一个简单汇总
SQL必知必会-习题总结
weixin_66549104的博客
09-15 256
n ot like【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。12341234。
SQL刷题：SQL必知必会
ZJH
01-28 1423
2.解释：返回目标字符串substr在字符串集str的截取范围为从第pos位到字符串末尾是否出现，以及在整个str中第一次出现的位置；其实是两个步骤，1.先判断在截取字符串中是否出现，若出现则返回在整个字符串第一次出现的位置，若未出现，则返回0。MySQL中的LOCATE()函数用于查找字符串中子字符串的位置。在字符串中搜索子字符串的位置时，MySQL LEFT() 函数从指定字符串的左侧返回指定数量的字符组成的字符串。类型的，下面两种写法都一样，在SQL中，数字类型都可以不加单引号。
SQL必知必会-笔记(一）创建练习数据库
diqiuda3513的博客
01-30 248
创建DB 使用以下命令创建db，用于后续所有命令练习 create table -- ----------------------------------------- -- Sams Teach Yourself SQL in 10 Minutes -- http://forta.com/books/0672336073/ -- Example table creation scr...
SQL 必知必会 Chapter 10 —— 分组数据
Jame Lu 的博客
03-14 1116
typora-copy-images-to: SQL 必知必会 文章目录typora-copy-images-to: SQL 必知必会SQL 必知必会 Chapter 10 分组数据10.1 数据分组10.2 创建分组10.3 过滤分组10.4 分组和排序10.5 SELECT 子句顺序10.6 小结 SQL 必知必会 Chapter 10 分组数据 10.1 数据分组 介绍如何分组数据，...
45个SQL练习题，涵盖基本语法的使用
AC1C1C1的博客
07-07 1420
根据优快云名叫【梦起丶】发的关于SQL练习题，用MySQL做了一遍，题目挺好的，能重新熟悉大部分SQL语法使用，感谢！！！ 部分题目存在歧义表述不清楚（对28、29、30题做了语义补充），有一两个题目解法有误（好像是20和21题，做了修改），把我自己跟着做了一遍的发上来，部分题目有自己写的解法，大同小异，欢迎大家的检查！！！ 以下是表的创建语句（使用的是MySQL数据库和数据库管理工具Navic...
mysql 订单表与商品表_数据库——订单表中有不同类型的商品，表的设计
weixin_42305634的博客
01-19 5046
具体需求，做一个类似购物平台，该平台中有多个商家，提供多种销售如酒水，虚拟商品(送花)，座位预订等。现在有两方面的问题，一个在订单方面，是将不同的商品放在不同的订单表中，还是放在同一的一个订单表中。平台中有多个商家，对不同商家的相同的产品还需要做区分另一个是尽量实现商家端与用户端的分离，订单与付款部分的分离第一个问题解决方案如下：参考:http://www.iteye.com/problems/8...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

村雨遥

众筹一毛买键盘！

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

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

打赏作者

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

抵扣说明：

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

余额充值