SQL必知必会 - 课后题练习记录_本题目要求编写sql语句, 基于product表和shopproduct表查询在编号000b商店销售-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_57355007/article/details/123942703

第2课检索数据

1.编写 SQL 语句，从 Customers 表中检索所有的 ID（cust_id）。

SELECT cust_id FROM Customers;

2. OrderItems 表包含了所有已订购的产品（有些已被订购多次）。编写 SQL 语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示：最终应该显示 7 行。

SELECT DISTINCT prod_id FROM orderitems ;

3. 编写 SQL语句，检索 Customers 表中所有的列，再编写另外的 SELECT 语句，仅检索顾客的 ID。使用注释，注释掉一条 SELECT 语句，以便运行另一条 SELECT 语句。（当然，要测试这两个语句。）

SELECT * FROM customers; -- SELECT cust_id FROM customers;

标准答案：SELECT * # SELECT cust_id FROM Customers;

第3课排序检索数据

1. 编写 SQL 语句，从 Customers 中检索所有的顾客名称（cust_names），并按从 Z 到 A 的顺序显示结果。

SELECT cust_names FROM Customers ORDER BY cust_names DESC;

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

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

3. 显然，我们的虚拟商店更喜欢出售比较贵的物品，而且这类物品有很多。编写 SQL 语句，显示 OrderItems 表中的数量和价格（item_price），并按数量由多到少、价格由高到低排序。

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

4. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT vend_name, FROM Vendors ORDER vend_name DESC;

vend_name后面不应该加逗号；ORDER少了BY

第4课过滤数据

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_price>=9;

3. 结合第 3 课和第 4 课编写 SQL 语句，从 OrderItems 表中检索出所有不同订单号（order_num），其中包含 100 个或更多的产品。

标准答案：SELECT DISTINCT order_num FROM OrderItems WHERE quantity >=100;

4. 编写 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;

第5课高级数据过滤

1. 编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个加利福尼亚州）。提示：过滤器需要匹配字符串。

SELECT vend_name FROM Vendors

WHERE vend_name='USA' AND vend_name='CA%' ;

2. 编写 SQL 语句，查找所有至少订购了总量 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量，并按产品 ID 和数量进行过滤。提示：根据编写过滤器的方式，可能需要特别注意求值顺序。

SELECT order_num,prod_id,quantity FROM orderitems

WHERE quantity >=100 AND (prod_id='BR01' OR prod_id='BR02' OR prod_id='BR03');

标准答案：

--标准解法 1

SELECT order_num, prod_id, quantity FROM OrderItems 
      WHERE (prod_id='BR01' OR prod_id='BR02' OR prod_id='BR03') 
            AND quantity >=100;

--标准解法 2

SELECT order_num, prod_id, quantity 
FROM OrderItems 
WHERE prod_id IN ('BR01','BR02','BR03') AND quantity >=100;

3. 现在，我们回顾上一课的挑战题。编写 SQL 语句，返回所有价格在 3 美元到 6美元之间的产品的名称（prod_name）和价格（prod_price）。使用 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. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT vend_name FROM Vendors ORDER BY vend_name WHERE vend_country = 'USA' AND vend_state = 'CA';

ORDER BY应该位于语句的最后

第6课用通配符进行过滤

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

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

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

SELECT prod_name,prod_desc FROM Product WHERE NOT prod_desc LIKE '%toy%'

ORDER BY prod_name;

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

SELECT prod_name,prod_desc FROM Product

WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%'

4. 来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

SELECT prod_name,prod_desc FROM Product

WHERE prod_desc LIKE '%toy%carrots%';

第7课创建计算字段

1. 编写 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_ciy AS vcity

FROM vendors

ORDER BY vname;

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

第8课使用函数处理数据

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

SELECT cust_id,customer_name,

UPPER( LEFT(cust_contact,2) ) + UPPER( LEFT(cust_city,3) )

AS user_login

FROM customers;

标准答案：

-- DB2, PostgreSQL
SELECT cust_id, cust_name,
 UPPER(LEFT(cust_contact, 2)) || UPPER(LEFT(cust_city, 3)) AS
user_login
FROM customers;
-- Oracle, SQLite
SELECT cust_id, cust_name,
 UPPER(SUBSTR(cust_contact, 1, 2)) || UPPER(SUBSTR(cust_city, 1, 3)) AS
user_login
FROM customers;
-- MySQL
SELECT cust_id, cust_name,
 CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS
user_login
FROM customers;
-- SQL Server
SELECT cust_id, cust_name,
 UPPER(LEFT(cust_contact, 2)) + UPPER(LEFT(cust_city, 3)) AS
user_login
FROM customers;

2. 编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期排序。你应该能够根据目前已学的知识来解决此问题，但也可以开卷查阅 DBMS 文档。

SELECT order_num,order_date

FROM orders

WHERE DATEPART(yy, order_date) = 2020

AND DATEPART(mm, order_date) = 1

ORDER BY order_date;

标准答案：

-- DB2, MariaDB, MySQL
SELECT order_num, order_date
FROM Orders
WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1
ORDER BY order_date;
-- Oracle, PostgreSQL
SELECT order_num, order_date
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020 AND EXTRACT(month FROM
order_date) = 1
ORDER BY order_date;
-- PostgreSQL
SELECT order_num, order_date
FROM Orders
WHERE DATE_PART('year', order_date) = 2020
 AND DATE_PART('month', order_date) = 1
ORDER BY order_num;
-- SQL Server
SELECT order_num, order_date
FROM Orders
WHERE DATEPART(yy, order_date) = 2020 AND DATEPART(mm, order_date) = 1
ORDER BY order_date;
-- SQLite
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020'
 AND strftime('%m', order_date) = '01';

第 9 课汇总数据

1. 编写 SQL 语句，确定已售出产品的总数（使用 OrderItems 中的 quantity 列）。

SELECT SUM(quantity) AS sum_prod

FROM orderitems;

2. 修改刚刚创建的语句，确定已售出产品项（prod_item）BR01 的总数。

SELECT SUM(quantity) AS sum_prod

FROM orderitems

WHERE prod_item = 'BRO1';

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

SELECT MAX(prod_price) AS max_price

FROM products

WHERE prod_price<=10;

第 10 课分组数据

1. OrderItems 表包含每个订单的每个产品。编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行排序。

SELECT order_num,

COUNT(*) AS order_lines

FROM orderitems

GROUP BY order_num

ORDER BY order_lines;

2. 编写 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;

3. 确定最佳顾客非常重要，请编写 SQL 语句，返回至少含 100 项的所有订单的订单号（OrderItems 表中的 order_num）。

SELECT order_num FROM OrderItems

GROUP BY order_num

HAVING SUM(quantity) >= 100

ORDER BY order_num;

4. 确定最佳顾客的另一种方式是看他们花了多少钱。编写 SQL 语句，返回总价至少为 1000 的所有订单的订单号（OrderItems 表中的 order_num）。提示：需要计算总和（item_price 乘以 quantity）。按订单号对结果进行排序。

SELECT order_num,

item_price*quantity AS sum_items

FROM orderitems

GROUP BY order_num

HAVING sum_items>=1000

ORDER BY order_num;

5. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。） SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY items HAVING COUNT(*) >= 3 ORDER BY items, order_num;

count(*)是对所有的总计数，不能跟在group by后面

标准答案：GROUP BY 项错误。GROUP BY 必须是实际列，而不是用于执行汇总计算的列。

允许使用 GROUP BY order_num。

第11 课使用子查询

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

SELECT cust_id

FROM order

WHERE order_num IN (SELECT order_num

FROM orderitems

WHERE prod_price>=10 );

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

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

SELECT cust_id,order_date

FROM orders

WHERE order_num IN( SELECTorder_num

FROM orderitems

WHERE prod_id='BR01')

ORDER BY order_date;

3. 现在我们让它更具挑战性。在上一个挑战题，返回购买 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( SELECTorder_num

FROM orderitems

WHERE prod_id='BR01'));

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

SELECT cust_id,

（ SELECT SUM(prod_price*quantity) FROM orderitems

WHERE orders.order_num = orderitem.order_num）AS total_ordered

FROM orders

ORDER BY DESC total_ordered;

5. 再来。编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_ name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。

SELECT prod_name,

(SELECT SUM(quantity) FROM orderitems

WHERE products.prod_id = orderitems.prod_id)

AS quant_sold

FROM products;

12 联结多个表

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

2. 我们来让上一题变得更有用些。除了返回顾客名称和订单号，添加第三列 OrderTotal，其中包含每个订单的总价。有两种方法可以执行此操作：使用 OrderItems 表的子查询来创建 OrderTotal 列，或者将 OrderItems 表与现有表联结并使用聚合函数。提示：请注意需要使用完全限定列名的地方。

1. SELECT cust_name, order_num,

( SELECT Sum(item_price*quantity) FROM OrderItems

WHERE Orders.order_num=OrderItems.order_num) AS OrderTotal

FROM Customers, Orders

WHERE Customers.cust_id = Orders.cust_id

ORDER BY cust_name, order_num;

2.SELECT cust_name,Orders.order_num,SUM(item_price*quantity) AS ordertotal

FROM Customers,Orders, OrderItems

WHERE Customers.cust_id = Orders.cust_id

AND Orders.order_num = OrderItems.order_num

GROUP BY cust_name, Orders.order_num

ORDER BY cust_name, order_num;

3. 我们重新看一下第 11 课的挑战题 2。编写 SQL 语句，检索订购产品 BR01 的日期，这一次使用联结和简单的等联结语法。输出应该与第 11 课的输出相同。

SELECT cust_id, order_date

FROM Orders, OrderItems

WHERE Orders.order_num = OrderItems.order_num

AND prod_id = 'BR01'

ORDER BY order_date;

4. 很有趣，我们再试一次。重新创建为第 11 课挑战题 3 编写的 SQL 语句，这次使用 ANSI 的 INNER JOIN 语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它，需要两个 INNER JOIN 语句，每个语句的格式类似于本课讲到的 INNER JOIN 示例，而且不要忘记 WHERE 子句可以通过 prod_id 进行过滤。

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