8.4 SQL练习题汇总（一）

已于 2024-04-14 18:10:04 修改
阅读量280 收藏 2
点赞数
分类专栏： 零基础入门到就业--JAVA篇 文章标签： sql 数据库 sqlserver
于 2023-03-06 22:20:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/whatfuswd/article/details/128528083
版权

文章目录

1.检索数据

  1. 编写 SQL 语句，从 Customers 表中检索所有的cust_id；

    输入：

    SELECT cust_id FROM customers;

    输出：
    在这里插入图片描述

    分析：
    简单的查询语句，要求只输出cust_id列，所以只查询输出cust_id列即可；

  2. 表OrderItems含有非空的列prod_id代表商品id，包含了所有已订购的商品（有些已被订购多次）。 编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。

    输入：

    SELECT DISTINCT prod_id FROM OrderItems;

    输出：
    在这里插入图片描述

    分析：
    简单的查询，要求查询OrderItems表中prod_id列去重后的结果，使用关键字 DISTINCT 对需要去重的字段进行去重后输出；

  3. 现在有Customers 表，表中含有列cust_id代表客户id，cust_name代表客户姓名，要查询表中。编写 SQL语句，查询这俩列的所有行。

    输入：

    SELECT cust_id,cust_name FROM Customers;

    输出：
    在这里插入图片描述

    分析：
    简单的查询语句，要求输出限定列的所有数据，多个列的查询，列明之间使用逗号分隔即可；

2.排序检索数据

  1. 有表Customers，cust_id代表客户id，cust_name代表客户姓名。从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。
    输入：

    SELECT cust_name FROM Customers ORDER BY cust_name DESC;

    输出：
    在这里插入图片描述

    分析：
    要求进行简单的查询后，对查询结果进行排序，并且按照=降序的方式排序，使用ORDER BY关键字后跟需要排序的列明，并指明排序方式来进行排序。

  2. 现有Orders表 ，编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id），订单号（order_num）和订单时间（order_date），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

    输入：

    SELECT cust_id,order_num,order_date 
FROM Orders 
ORDER BY cust_id,
		order_date DESC;

    输出：
    在这里插入图片描述

    分析：
    涉及多个字段排序的查询，首先对字段cust_id进行升序排序，然后对字段order_date进行降序排序，因为ORDER BY排序默认就是升序排序，所以可以不指定cust_id字段的排序方式，当然也可以全部指定，全部指定的SQL语句为：SELECT cust_id,order_num,order_date FROM Orders ORDER BY cust_id ASC,order_date DESC;。

  3. 现有OrderItems表 ，编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。
    输入：

    SELECT quantity,item_price 
FROM OrderItems 
ORDER BY quantity DESC,
		item_price DESC;

    输出：
    在这里插入图片描述

    分析：
    涉及多个字段的查询后排序，并且均为降序排序，均需要使用关键字DESC，其先对quantity字段进行降序排序，对quantity字段中的价格，再进行降序排序。

  4. 现有Vendors表，下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name逆序排列。

    SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

    输入：

    SELECT vend_name 
FROM Vendors 
ORDER BY vend_name DESC;

    输出：
    在这里插入图片描述

    分析：
    当查询多个字段时，每个字段之间使用逗号分隔，最后一个字段不使用逗号，当只查询一个字段时，不需要使用逗号，因为没有可以分隔的对象；
    排序使用ORDER BY关键字，ORDER并不是MySql的相关命令符。

3.过滤数据

  1. 有表Products，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 4.49 美元的产品。

    输入：

    SELECT prod_id,prod_name 
FROM Products 
WHERE prod_price = 4.49;

    输出：
    在这里插入图片描述

    分析：
    简单的条件查询语句。查询价格为4.49的产品名及产品ID。当使用数字作为条件时，可以不加单引号，当然加上单引号也不会产生错误。

  2. 有表Products，编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。

    输入：

    SELECT prod_id,prod_name 
FROM Products 
WHERE prod_price >= 9;

    输出：
    在这里插入图片描述

    分析：
    简单的条件查询语句，按照要求查询价格大于等于9的产品ID与产品名称。

  3. 有表Products，编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。

    输入：

    SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price >= 3 
	AND prod_price <= 6;

    输出：
    在这里插入图片描述

    分析：
    简单的条件查询语句，要求查询价格大于等于3，并且小于等于6的产品名称及价格。当有多个条件，并要求条件同时满足时，需要使用AND关键字连接多个条件。对于在一个数值范围内的条件，我们也可以使用BETWEEN关键字进行查询，后跟具体的数值范围，使用AND关键字连接，具体SQL如下：

    SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price BETWEEN 3 AND 6;

  4. OrderItems表含有：订单号order_num，quantity产品数量 。从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 5 个或更多的产品。

    输入：

    SELECT DISTINCT order_num 
FROM OrderItems 
WHERE quantity >= 5;

    输出：
    在这里插入图片描述

    分析：
    简单的查询语句，要求查询出订单中包含5个产品及以上的订单号，并对返回的订单号进行去重处理。

4.高级过滤数据

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

    输出：
    在这里插入图片描述

    分析：
    简单的查询语句，需要同时满足国家及州的条件要求。

  2. OrderItems 表包含了所有已订购的产品（有些已被订购多次）。 编写SQL 语句，查找所有订购了数量至少3个的 ANV01、ANV02或ANV03的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。

    输入：

    SELECT order_num,prod_id,quantity 
FROM OrderItems 
WHERE quantity >= 3 
AND prod_id IN ('ANV01','ANV02','ANV03');

    输出：
    在这里插入图片描述

    分析：
    简单的条件查询，考察多个条件查询及关键字in的使用。

  3. 有表Products，编写 SQL 语句，返回所有价格在 3美元到 6美元之间的产品的名称（prod_name）和价格（prod_name），使用 AND操作符，然后按价格对结果进行升序排序 。

    输入：

    SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price BETWEEN 3 AND 6 
ORDER BY prod_price;

    输出：
    在这里插入图片描述

    分析：
    满足多个条件的查询，并按照升序输出结果，有多个实现办法，例如：

    SELECT
    prod_name,
    prod_price
FROM
    Products
WHERE
    prod_price >= 3 AND
    prod_price <= 6
ORDER BY
    prod_price;

  4. 供应商表Vendors有字段供应商名称vend_name、供应商国家vend_country、供应商省份vend_state。修改正确下面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;

    输出：
    在这里插入图片描述

    分析：
    题目中的SQL不满足运行顺序，首先是WHERE条件，其次才是ORDER BY排序。要首先使用条件查询到结果之后，才能对结果进行排序。

5.用通配符进行过滤

  1. 现有Products表，编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 ton 一词的产品名称。

    输入：

    SELECT prod_name,prod_desc 
FROM Products 
WHERE prod_desc LIKE '%ton%';

    输出：
    在这里插入图片描述

    分析：
    主要考察like 关键字的用法。 like关键字用法：[字符] like ‘%字符%’ %表示任何字符出现任意次数。

  2. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

    输入：

    SELECT prod_name,prod_desc 
FROM Products 
WHERE prod_desc NOT LIKE '%ton%' 
ORDER BY prod_name;

    分析：
    考察like关键字与ORDER BY关键字的同时使用。

  3. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 ton 和 carr 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

    输入：

    SELECT prod_name,prod_desc 
FROM Products 
WHERE prod_desc LIKE '%ton%' 
	AND prod_desc LIKE '%carr%';

    输出：
    在这里插入图片描述

    分析：
    考察多个like关键字的同时使用。

  4. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carr 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

    输入：

    SELECT prod_name,prod_desc 
FROM Products 
WHERE prod_desc LIKE '%ton%carr%';

    输出：
    在这里插入图片描述

    分析：
    主要考察通配符的使用。

6. 创建计算字段

  1. 有表Vendors代表供应商信息，vend_id供应商id、vend_name供应商名称、vend_address供应商地址、vend_city供应商城市。
    编写 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 vname;

    输出：
    在这里插入图片描述

    分析：
    主要考察别名的使用。

  2. 我们的示例商店正在进行打折促销，所有产品均降价 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;

    输出：
    在这里插入图片描述

    分析：主要列计算及别名使用

7.使用函数处理数据

  1. 我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。
    编写 SQL 语句，在Customers表中返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_name）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

    输入：

    SELECT cust_id,
cust_name, 
UPPER(
	CONCAT(
		SUBSTRING(cust_name,1,2),
		SUBSTRING(cust_city,1,3)
		)
			) AS user_login 
FROM Customers;

    输出：
    在这里插入图片描述

    分析：
    主要考察关键词：substing,concat,upper的用法。

    用法：
    - 字符串的截取：substring(字符串，起始位置，截取字符数）
    - 字符串的拼接：concat(字符串1，字符串2，字符串3,…)
    - 字母大写：upper(字符串）

  2. 在Orders订单表中，编写 SQL 语句，返回 2005 年 9 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序。

    输入：

    SELECT order_num,order_date 
FROM Orders 
WHERE YEAR(order_date) = 2005 
	AND MONTH(order_date) = 9
	ORDER BY order_date;

    输出：
    在这里插入图片描述

    分析：考察时间函数的相关使用，所以我们使用时间函数处理，不建议使用通配符，截取字符串，正则表达式来进行查找。还可以使用date_format函数查询：

    select order_num, order_date 
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date

8.汇总数据

  1. OrderItems表中prod_id字段代表售出的产品ID，Orderitems表中quantity字段代表售出商品数量。
    【问题】编写 SQL 语句，确定已售出产品的总数，返回items_ordered列名，表示已售出商品的总数。

    输入：

    SELECT 
SUM(quantity) AS items_ordered 
FROM Orderitems;

    输出：
    在这里插入图片描述

    分析：
    单纯考察sum函数的使用，题目中给出的另一个表具有迷惑性，其实没有任何作用。

  2. OrderItems表代表售出的产品，quantity字段代表售出商品数量，产品项为prod_id。编写 SQL 语句，确定已售出产品项（prod_id ）为"TNT2"的总数，返回items_ordered列名，表示已售出商品的总数。

    输入：

    	SELECT 
	SUM(quantity) AS items_ordered 
	FROM OrderItems 
	WHERE prod_id = 'tnt2';

    输出：
    在这里插入图片描述

    分析：考察条件筛选中的sum函数的使用。

  3. 编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。

    输入：

    SELECT 
MAX(prod_price) AS max_price 
FROM Products 
WHERE prod_price <= 10 
LIMIT 1;

    输出：
    在这里插入图片描述

    分析：
    首先要求查询价格小于等于10的产品价格，然后选择其中价格最大的，然后只输出这一个结果。
    主要是考察max函数及limit函数的使用。

9.分组数据

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

    输出：
    在这里插入图片描述

    分析：
    返回每个订单号（order_num）各有多少行数（order_lines），order_num是普通字段，需要用到聚合行数，是聚合字段，

    由于普通字段不能和聚合字段同时出现在select后面，所以得加 group by order_num，变为分组字段，group by 后，select 后面只能跟分组字段和聚合字段，这样就符合要求了。

  2. 有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id 。编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。

    输入：

    SELECT vend_id,
	MIN(prod_price) AS cheapest_item 
FROM Products 
GROUP BY vend_id 
ORDER BY cheapest_item;

    输出：
    在这里插入图片描述
    分析：
    先用函数生成一个符合vend_id分组并根据prod_price的新表 然后嵌套查询这个新表+ where条件找到想要检索的结果

  3. OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。请编写 SQL 语句，返回订单数量总和不小于10的所有订单号，最后结果按照订单号升序排序。

    输入：

    SELECT order_num 
FROM OrderItems 
	GROUP BY order_num 
	HAVING SUM(quantity) >= 10 
	ORDER BY order_num ASC;

    输出：
    在这里插入图片描述

    分析：
    首先根据条件，筛选出订单总数不少于10的订单号，然后根据订单号进行排序。

  4. OrderItems表代表订单信息，包括字段：订单号order_num和item_price商品售出价格、quantity商品数量。
    编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。
    提示：总价：total_price = item_price 乘以 quantity

    输入：

    	SELECT order_num 
FROM orderitems 
	GROUP BY order_num 
	HAVING sum(item_price*quantity) >=1000 
	ORDER BY order_num;

    输出：
    在这里插入图片描述

    分析：
    根据订单号聚合，返回订单总价不小于1000 的所有订单号，总价 = item_price 乘以 quantity。

    其实题目已经暗示得很清楚了：根据订单号聚合，这不就摆明叫我们使用 group by 函数，对数据按照订单号进行分组吗？

    返回订单总价不小于1000 的所有订单号。 这是一个过滤条件，但是我们很多人这时候会踩坑， 直接这样写SQL语句:

    where sum(item_price*quantity)>=1000

    咋一看这好像没问题。但是别忘记了：where后面不能加聚合函数！！！！！

    那有没有别的办法呢？ 别忘记了我们的老大哥：having

    having也可以对结果进一步过滤，只不过having必须和group by联合使用。

    having sum(item_price*quantity)>=1000

    这样一来，就符合了MySQL的语法规则。

    对1、2两点进行整合，我们就可以写出完整的SQL语句啦。

  5. OrderItems表含有order_num订单号 ,将下面代码修改正确后执行:

    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(order_num) >= 3
ORDER BY items, order_num;

    输出:
    在这里插入图片描述

分析：
先执行GROUP BY order_num，对所有字段按照order_num 分组，
再执行HAVING items >= 3，对分组进行过滤，即查询出满足items大于等于3的记录，
再执行SELECT order_num, COUNT(order_num) AS items，
最后执行ORDER BY items, order_num;对记录进行排序，默认是升序。

10.使用子查询

  1. OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num 。
    使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
    注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

    输入：

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

    输出：
    在这里插入图片描述

    分析：本章节主要考察子查询的使用，所以我们使用子查询实现。
    在OrderItems表中查询出价格大于10的所有订单号，然后将订单号作为条件在Orders表中查询出所有的顾客ID。
    需要注意的是，子查询中查询出的是结果集，有多个结果，所以需要in关键字绑定条件，不能使用=。

  2. 表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；
    编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 “TNT2” 的产品，然后从 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 = 'TNT2') 
ORDER BY order_date;

    输出：
    在这里插入图片描述

    分析：本章节主要考察子查询的使用，所以我们使用子查询实现。
    在OrderItems表中查询出产品ID为 "TNT2"的所有订单号，然后将订单号作为条件在Orders表中查询出所有的顾客ID及订单日期，最后按照订单日期进行排序输出结果。
    需要注意的是，子查询中查询出的是结果集，有多个结果，所以需要in关键字绑定条件，不能使用=。

  3. 你想知道订购 "TNT2"产品的人员的邮箱，有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客id.
    返回购买 prod_id 为"TNT2"的产品的所有顾客的电子邮件（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 = 'TNT2') 
		);

    输出：
    在这里插入图片描述

    分析：本章节主要考察子查询的使用，所以我们使用子查询实现。
    提示信息中已经说的很明确了，本案例主要考察多层嵌套子循环的使用。在OrderItems表中查询出产品ID为 "TNT2"的所有订单号，然后将订单号作为条件在Orders表中查询出所有的顾客ID，最后将顾客ID作为条件在Customers表中查询出所有的电子邮箱。
    需要注意的是，子查询中查询出的是结果集，有多个结果，所以需要in关键字绑定条件，不能使用=。

  4. 我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。
    OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。
    编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。
    提示：你之前已经使用 SUM()计算订单总数。

    输入：

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

    输出：
    在这里插入图片描述

    分析：本章节主要考察子查询的使用，所以我们使用子查询实现。
    本案例主要考察子查询及别名的使用方式，这里使用子查询将查询结果作为输出的值，并在最后将结果按金额从大到小排序。

  5. Products 表中检索所有的产品名称：prod_name、产品id：prod_id；OrderItems代表订单商品表，订单产品：prod_id、售出数量：quantity；
    编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。

    输入：

 SELECT
  p.prod_name,
  (SELECT SUM(oi.quantity) FROM OrderItems oi WHERE oi.prod_id = p.prod_id) AS quant_sold
FROM
  Products p;

输出：

在这里插入图片描述

分析：本章节主要考察子查询的使用，所以我们使用子查询实现。
本案例主要考察将子查询作为计算字段使用。首先查询产品ID及所售产品总数，通过产品ID分组进行查询，在查询时要注意只查询在产品表中存在的产品，得到各自产品的所售产品总数；
然后查询产品名称，通过产品ID将俩张表进行关联，组合最后查询的结果。因为查询语句中包含俩张表中相同吗名称的字段，所以我们这里使用了绝对限定名。

11.联结表

  1. Customers 表有字段顾客名称cust_name、顾客id cust_id；Orders订单信息表，含有字段order_num订单号、cust_id顾客id；
    编写 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 Orders.cust_id=Customers.cust_id
ORDER BY cust_name,order_num;

    输出：
    在这里插入图片描述

    分析：本章节主要考察联结表的使用，所以我们使用联结实现。
    方法一与方法二得到的结果是一样的，也都使用了联结查询，区别在于一个是普通联结查询，一个是内联结。平时开发中，我们为了避免笛卡尔积的情况，建议使用内联结进行查询，也就是方法二，内联结在大多数情况具有更加高效的查询效率。

  2. Customers 表有字段，顾客名称：cust_name、顾客id：cust_id；Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id；OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price；
    要求除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

    输入：

    SELECT
    c.cust_name,
    o.order_num,
    SUM(oi.quantity * oi.item_price) AS OrderTotal
FROM
    Customers c
JOIN
    Orders o ON c.cust_id = o.cust_id
JOIN
    OrderItems oi ON o.order_num = oi.order_num
GROUP BY
    c.cust_name, o.order_num
ORDER BY
    c.cust_name ASC, o.order_num ASC;

    输出：
    在这里插入图片描述

    分析：本章节主要考察联结表的使用，所以我们使用联结实现。

    1. 使用 JOIN 语句连接 Customers、Orders 和 OrderItems 表，通过顾客 ID (cust_id) 和订单号 (order_num) 来关联这些表。
    2. 使用 SUM(oi.quantity * oi.item_price) 计算每个订单的总价。这里 oi.quantity * oi.item_price 计算出每个订单项的总价，SUM 函数则对同一订单的所有订单项进行求和，得到订单总价。
    3. 使用 GROUP BY 子句按顾客名称 (c.cust_name) 和订单号 (o.order_num) 对结果进行分组，这样每个顾客的每个订单只会出现一次，并附带有对应的总价。
    4. 最后，使用 ORDER BY 子句按顾客名称 (c.cust_name) 和订单号 (o.order_num) 对结果进行升序排序。

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

    输入：

    SELECT cust_id,order_date
FROM Orders o JOIN OrderItems oi ON o.order_num=oi.order_num
WHERE prod_id="TNT2"
ORDER BY order_date;

    输出：
    在这里插入图片描述

    分析：本章节主要考察联结表的使用，所以我们使用联结实现。
    首先，能将OrderItems表和Orders表关联起来的字段为order_num；
    根据题目要求，需要先使用子查询来查询OrderItems，查询条件的字段为prod_id，查询的结果集为order_num字段的；
    之后外层查询，查询Orders表，查询的条件为order_num字段，用order_num字段去匹配子查询的结果集；
    最后再排序。

  4. 有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客id；
    返回购买 prod_id 为TNT2的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

    输入：

    SELECT c.cust_email
FROM Customers c
JOIN Orders o ON c.cust_id = o.cust_id
JOIN OrderItems oi ON o.order_num = oi.order_num
WHERE oi.prod_id = 'TNT2';

    输出：
    在这里插入图片描述

    分析：本章节主要考察联结表的使用，所以我们使用联结实现。
    通过俩张表之前关联的字段进行联结查询即可。

  5. OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量；
    编写 SQL 语句，返回订单总价不小于1000 的客户名称(Customers表中的cust_name)和总额（OrderItems 表中的 单价*数量 计算得出）。
    提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

    输入：

    select
	c.cust_name ,
	ois.total_price
from
	customers c
inner join orders o on
	c.cust_id = o.cust_id
inner join 
	(
	select
		oi.order_num ,
		sum(oi.item_price * oi.quantity) as total_price
	from
		orderitems oi
	group by
		order_num
	having
		total_price >= 1000) as ois
on
	o.order_num = ois.order_num
order by
	ois.total_price asc;

    输出：
    在这里插入图片描述

    分析：本章节主要考察联结表的使用，所以我们使用联结实现。
    通过俩张表之前关联的字段进行联结查询即可。
    要注意groupby 和having是一对，不能用where；HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

12.创建高级联结

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

    输入：

    select
	c.cust_name ,
	o.order_num
from
	customers c
inner join orders o on
	c.cust_id = o.cust_id
order by
	c.cust_name ;

    输出：
    在这里插入图片描述

    分析：本章节主要考察高级联结表的使用，所以我们使用联结实现。
    使用俩张表的cust_id 字段进行关联，因为俩张表的字段名相同，为了避免歧义，使用绝对限定名。

  2. 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 USING(cust_id)
ORDER BY cust_name;

    输出：
    在这里插入图片描述

    分析：本章节主要考察高级联结表的使用，所以我们使用联结实现。
    这是 一个以cust_name为主表的外连接，这个可以采用左外连接，将Customers作为第一个表。并且存在未下订单的顾客，所以返回结果中存在null值。

  3. Products表为产品信息表含有字段prod_id产品id、prod_name产品名称；OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id ；
    使用联结查询 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

    输入：

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

    输出：
    在这里插入图片描述

    分析：本章节主要考察高级联结表的使用，所以我们使用联结实现。
    首先，产品名称与订单号是1对多的关系，即一个产品可以对应多个订单；查询结果中，产品名称需要全部显示，订单号唯一；
    用左外连接，为保证产品名称全部显示，应将Products作为左表。如果采用右外连接，为保证产品名称全部显示，应将Products作为连接字段中右边的表。

  4. Products表为产品信息表含有字段prod_id产品id、prod_name产品名称；OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id ；
    使用联结查询 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

    输入：

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

    输出：
    在这里插入图片描述

    分析：本章节主要考察高级联结表的使用，所以我们使用联结实现。
    left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取null。
    right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null。
    分组计算订单总数：count(order_num) as orders group by prod_id,prod_name
    左连接：对于没有购买产品，保留下来，总订单数为0
    因为Products表中有产品没有订单，所以求数量的时候不能使用count(),必须使用count(order_num) 使用count即使有null值，也会算一行

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

    输入：

    SELECT v.vend_id vend_id,COUNT(p.prod_id) prod_id
FROM Vendors v
LEFT JOIN Products p 
ON v.vend_id = p.vend_id
GROUP BY v.vend_id
ORDER BY v.vend_id;

    输出：
    在这里插入图片描述

    分析：本章节主要考察高级联结表的使用，所以我们使用联结实现。
    分组计算订单总数：count(prod_id) group by Vendors.vend_id
    左连接：对于Products中没有的vend_id，保留下来，总订单数为0，对于Products中有而Vendors中没有的商品自动过滤 left join Products using(vend_id)

13.组合查询

  1. 表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 的使用, 这里考虑两种结合两个 select 语句的思路:
    筛选条件：like用法。where quantity=100，where prod_id like ‘BNBG%’
    排序：放在最后进行排序，不能先排序在拼接。order by prod_id

  2. 表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量；
    从 OrderItems表中检索产品 id（prod_id）和 quantity。其中，数量为 100 的， id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
    注意：这次仅使用单个 SELECT 语句。

    输入：

    SELECT
    prod_id,
    quantity
FROM
    OrderItems
WHERE
    quantity = 100 OR prod_id LIKE "BNBG%"
ORDER BY
    prod_id;

    输出：
    在这里插入图片描述

    分析：与上题一样，只是这是要求使用单个 SELECT 语句。要求满足A条件或B条件的，使用OR关键字。

  3. Products表含有字段prod_name代表产品名称；Customers表代表顾客信息，cust_name代表顾客名称 ；
    编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

    输入：

    select prod_name from Products
union 
select cust_name as prod_name from Customers
order by prod_name

    输出：
    在这里插入图片描述

    分析：
    本题感觉就一个小点，使用union查询时用来连接的两表字段需要保持一致，因此将Customers表中的cust_name改成prod_name，再进行union查询即可。

  4. 表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;

    输入：

    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;

    输出：
    在这里插入图片描述

    分析：
    使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。
    并且在使用union关键字时，前半部分的查询语句不能使用分号结束，整个组合查询，只能存在一个分号，且在最后。

蟑螂恶霸不是恶霸
关注
专栏目录
牛客-SQL篇-SQL必知必会-09分组数据-分组数据-SQL87每个供应商成本最低的产品
SunshineLu2019的博客
11-23 176
【问题】编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。【示例结果】返回供应商id vend_id和对应供应商成本最低的产品cheapest_item。`vend_id` VARCHAR(255) NOT NULL COMMENT '供应商ID',有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id。
从零学会SQL：汇总分析
qq_35692953的博客
01-21 327
1.汇总分析 1.1 汇总函数 count(列名)： 求某列的行数【不包括空值】 count(*)包括空值 sum(列名)： 对某列求和 只能对数值类型的列计算 avg(列名)： 求某列数据的平均值 只能对数值类型的列计算 max(列名)：求某列数据的最大值 min(列名)：求某列数据的最小值 -- count函数 select count(教师姓名) from teacher; -- *包括空值 select count(*) from teacher; --
SQL 必知必会 50 题（11 - 15）
村雨遥
04-06 1242
牛客网 SQL 必知必会 50 题
SQL87 每个供应商成本最低的产品
llew的博客
02-21 661
SQL87 每个供应商成本最低的产品
作业：购物车实例
2301_76731971的博客
05-05 267
Orderltem 订单项：id;在eclipse中新建一个java项目，项目名称cart。Order 订单id;Product 产品:id;User 用户:id;在cart 数据库中创建表product。一个订单项，只会出现在一个订单里。产品和订单项的关系是：一对多。为product准备4条数据。一种产品，对应多条订单项。订单项和订单的关系：多对一。一个订单，只能属于一个用户。一条订单项，对应一种产品。订单和用户的关系：多对一。一个用户，可以下多个订单。一个订单里有多条订单项。5.Product类。
【牛客网SQL篇】SQL必知必会
随缘博客
05-31 1557
sql-teach-yourself 01 检索数据 SQL1 从 Customers 表中检索所有的 ID 题目： 编写 SQL 语句，从 Customers 表中检索所有的cust_id SELECT cust_id FROM Customers; SQL2 检索并列出已订购产品的清单 题目： 编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。 SELECT DISTINCT prod_id FROM OrderItems; SQL3 检索所
收获不止SQL优化
08-07
8.4　本章习题、总结与延伸 234 第9章　且慢，弄清索引之阻碍让SQL飞 235 9.1　索引的不足之处 235 9.1.1　索引的各种开销 236 9.1.2　索引使用失效 236 9.2　感受美好索引另一面 237 9.2.1　索引各种开销 237 9.2.2...
收获，不止SQL优化--抓住SQL的本质
12-21
8.4　本章习题、总结与延伸 234 第9章　且慢，弄清索引之阻碍让SQL飞 235 9.1　索引的不足之处 235 9.1.1　索引的各种开销 236 9.1.2　索引使用失效 236 9.2　感受美好索引另一面 237 9.2.1　索引各种开销 237 9.2.2...
Spark Scala版本 | 选择题汇总
希望每天都能进步一点点
01-03 8551
文章目录选择题总结 (89个题)第1章 大数据技术概述 (10个题)第2章 Scala 语言基础 (20个题)第3章 Spark的设计与运行原理 (10个题)第4章 Spark环境搭建和使用方法 (10个题)第5章 RDD编程 (10个题)第6章 Spark SQL (10个题)第7章 Spark Streaming (11个题)第8章 Spark MLlib (8个题) 选择题总结 (89个题) 第1章 大数据技术概述 (10个题) ‎1.1 大数据技术及其代表性的软件种类很多，不同的技术有其不同应用场
【大数据面试题】Spark-Core&amp；Spark-SQL
最新发布
2301_78234743的博客
08-20 199
题意给你一个整数和素数，让你求所有整数在模意义下的乘法逆元思路线性求乘法逆元假设我们已知 的逆元，我们现在要求 也就是 的逆元，已知 等于,那么说明 ，经。题意给你一个序列 ，已知序列 的周期是 ，要么要么，接着也给你两个数和，让你求 思路我们将提取出来，原式变成 ，那么我们只要能求解就能得到答案，因为的周期为，我。数据倾斜问题写个sql吧再写一个sql吧，写挺好啊，在写一个吧这些你是练习过原题吗，在写一个sql吧说一下数仓的分层，每层干了什么事？然后，在Solution类中，我们声明。
SQL61 检索并列出已订购产品的清单
llew的博客
02-15 234
SQL61 检索并列出已订购产品的清单
【SQL牛客网刷题】SQL必知必会50题
weixin_46861115的博客
09-05 3723
上次做这个还是一年前，这两天打算把所有的都刷一遍，做一个简单汇总
生成订单：三个表（Products，Orders，OrderItem）
weixin_30555515的博客
04-07 2664
1.有三个表（Product上，Orders，OrderItem） 分别创建对应的三个实体类 OrderItem中有外键Order_id 参考Orders中的id ；Product_id参考Product中的id；Orders中有外键User_id参考User中的id sql 代码： CREATE DATABASE day2017_04_05; USE day2017_04_05; ...
SQL必知必会练习题答案（第五版）
DanteBurning的博客
04-29 5113
《SQL必知必会-第五版》挑战题答案——不作商业用途，仅个人学习使用
编写SQL语句，从Orders表中检索顾客ID（cust_id）和订单号（order_num），并先按顾客ID对结果进行排序，再按订单日期倒序排列
xiaozhaoyouai的博客
02-11 2602
ORDER BY子句的默认排序顺序是升序排序（从A到Z），还可以使用ORDER BY子句进行降序排序（从Z到A），这里必须指定DESC关键字（DESCENDING的缩写）。
MySQL基础练习题（带答案）
flyskymood的博客
05-20 4422
文章目录前言📕往期知识点👓练习题（1）📌描述表数据代码👓练习题（2）📌描述表数据代码👓练习题（3）📌描述数据表代码👓练习题（4）📌描述表数据代码👓练习题（5）📌描述数据表代码📑题目答案 前言 MySQL的分组查询操作对在数据查询时起到了相当大的作用，能让我们在处理数据时更高效便捷的对数据的处理。 📕往期知识点 📕往期内容回顾 💡 python教程】保姆版教使用pymysql模块连接MySQL实现增删改查 💡 python+requests+BeautifulSoup实现对数据保存到mysql数据库 💡
第5课 高级数据过滤
weixin_43794683的博客
10-19 290
第5课 高级数据过滤 1.组合where子句 前面所讲的where筛选都只使用一个条件，在实际情况中，我们往往需要对数据进行多重条件筛选，如在销售记录中筛选出消费金额大于1千且性别为男性的用户。 要实现上述问题，就需要使用逻辑操作符将筛选条件组合起来。 （1）AND操作符 筛选出满足所有给定条件的数据 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id='DLL01' AND prod_price<=4; （2）OR操作符
计算机二级vb2019考试大纲,2019年计算机二级考试VB应用试题冲刺
weixin_34875640的博客
07-14 145
《2019年计算机二级考试VB应用试题冲刺》由会员分享，可在线阅读，更多相关《2019年计算机二级考试VB应用试题冲刺(5页珍藏版)》请在人人文库网上搜索。1、2019 年计算机二级考试 VB 应用试题冲刺 三、简单应用题 42在考生文件夹下，打开Ecommerce数据库，完成如下简单应用: (1) 使用报表向导建立一个简单报表。要求选择客户表 1”，字段布局 升序 Customer 中所有字段 ...
MySQL必知必会第四章
weixin_52643599的博客
08-20 448
1．编写SQL语句，从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9.49美元的产品。 select prod_id,prod_name from products where prod_price = 9.49; 2．编写SQL语句，从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9美元或更高的产品。 select prod_id,prod_name from products where prod_p
Cognos 8.4 安装配置教程：从安装到设置
Cognos 8.4 是IBM提供的一款企业级商业智能（BI）和性能管理软件，它为企业提供了数据分析、报告、仪表板和规划功能。本指南将详细介绍如何在Windows Server 2003环境中安装和配置Cognos 8.4 BI Server。 首先，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值