NIUKE SQL：必知必会

01 检索数据

SQL60 从 Customers 表中检索所有的 ID

【问题】编写 SQL 语句，从 Customers 表中检索所有的cust_id

SELECT cust_id FROM Customers

SQL61 检索并列出已订购产品的清单

【问题】编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。

SELECT DISTINCT prod_id FROM OrderItems

SQL62 检索所有列

【问题】需要编写 SQL语句，检索所有列

SELECT * FROM Customers

02 排序检索数据

SQL63 检索顾客名称并且排序

【问题】从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果

SELECT cust_name FROM Customers ORDER BY cust_name DESC

SQL64 对顾客ID和日期排序

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

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

SQL65 按照数量和价格排序

【问题】编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序

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

SQL66 检查SQL语句

【问题】返回结果根据vend_name逆序排列

SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC

03 过滤数据

SQL67 返回固定价格的产品

【问题】从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品

SELECT prod_id,prod_name FROM Products
WHERE prod_price=9.49

SQL68 返回更高价格的产品

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

SELECT prod_id,prod_name FROM Products
WHERE prod_price>=9

SQL69 返回产品并且按照价格排序

【问题】编写 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

SQL70 返回更多的产品

【问题】从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品

SELECT DISTINCT order_num FROM OrderItems
WHERE quantity>=100

04 高级数据过滤

SQL71 检索供应商名称

【问题】编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA）

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

SQL72 检索并列出已订购产品的清单

【问题】编写SQL 语句，查找所有订购了数量至少100 个的 BR01、BR02 或BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤

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

SQL73 返回所有价格在 3美元到 6美元之间的产品的名称和价格

【问题】编写 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

SQL74 纠错2

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

05 用通配符进行过滤

SQL75 检索产品名称和描述（一）

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

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

SQL76 检索产品名称和描述（二）

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

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

SQL77 检索产品名称和描述（三）

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

SELECT prod_name,prod_desc FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%'

SQL78 检索产品名称和描述（四）

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

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

连续百分号可以保证每个百分号之间的模糊查询互相为并列条件

 

06 创建计算字段

SQL79 别名

【问题】编写 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

SQL80 打折

【问题】编写 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

07 使用函数处理数据

SQL81 顾客登录名

【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名

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

SQL82 返回 2020 年 1 月的所有订单的订单号和订单日期

【问题】编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

SELECT order_num,order_date FROM Orders
WHERE MONTH(order_date)=1
ORDER BY order_date

08 汇总数据

SQL83 确定已售出产品的总数

【问题】编写 SQL 语句，确定已售出产品的总数

SELECT SUM(quantity) AS items_ordered FROM OrderItems

SQL84 确定已售出产品项 BR01 的总数

【问题】修改创建的语句，确定已售出产品项（prod_item）为"BR01"的总数

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

SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格

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

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

 

09 分组数据

SQL86 返回每个订单号各有多少行数

【问题】编写 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

SQL87 每个供应商成本最低的产品

【问题】编写 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

SQL88 返回订单数量总和不小于100的所有订单的订单号

【问题】请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序

SELECT order_num FROM OrderItems
WHERE quantity>=100
ORDER BY order_num	

SQL89 计算总和

【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序

SELECT order_num,SUM(item_price*quantity) AS total_price FROM OrderItems
GROUP BY order_num
HAVING total_price>=1000
ORDER BY order_num

SQL90 纠错3

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

10 使用子查询

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

【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序

SELECT cust_id FROM Orders 
    JOIN OrderItems ON OrderItems.order_num=Orders.order_num
WHERE item_price>=10

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

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

SELECT cust_id,order_date FROM Orders
    JOIN OrderItems ON OrderItems.order_num=Orders.order_num
WHERE prod_id="BR01" 
ORDER BY order_date

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

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

SELECT cust_email FROM Orders
    JOIN OrderItems ON OrderItems.order_num=Orders.order_num
    JOIN Customers ON Orders.cust_id=Customers.cust_id
WHERE prod_id='BR01'

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

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

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

SQL95 从 Products 表中检索所有的产品名称以及对应的销售总数

【问题】编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索

SELECT prod_name,SUM(quantity) AS quant_sold FROM Products
    JOIN OrderItems ON OrderItems.prod_id=Products.prod_id
GROUP BY prod_name

11 联结表

SQL96 返回顾客名称和相关订单号

【问题】编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNER JOIN

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

SQL97 返回顾客名称和相关订单号以及每个订单的总价

【问题】除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序

SELECT cust_name,
    Orders.order_num,
    (quantity*item_price) AS OrderTotal 
FROM Orders
    JOIN Customers ON Orders.cust_id=Customers.cust_id
    JOIN OrderItems ON OrderItems.order_num=Orders.order_num
ORDER BY cust_name,order_num

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

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

SELECT cust_id,order_date FROM Orders AS O
    JOIN OrderItems AS OS ON O.order_num=OS.order_num
WHERE  prod_id="BR01" 
ORDER BY order_date

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

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

SELECT cust_email FROM Orders
    JOIN OrderItems ON Orders.order_num=OrderItems.order_num
    JOIN Customers ON Customers.cust_id=Orders.cust_id
WHERE prod_id='BR01'

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

【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）

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

12 创建高级联结

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

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

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

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

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

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

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

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

SELECT prod_name,order_num FROM Products AS P
    LEFT JOIN OrderItems AS O ON P.prod_id=O.prod_id
ORDER BY prod_name

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

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

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

SQL105 列出供应商及其可供产品的数量

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

SELECT V.vend_id,COUNT(prod_id) AS prod_id FROM Vendors AS V
    LEFT JOIN Products AS P ON V.vend_id=P.vend_id
GROUP BY vend_id
ORDER BY vend_id

13 组合查询

SQL106 将两个 SELECT 语句结合起来（一）

【问题】将两个 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

SQL107 将两个 SELECT 语句结合起来（二）

【问题】将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序

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

SQL108 组合 Products 表中的产品名称和 Customers 表中的顾客名称

【问题】编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序

(SELECT prod_name FROM Products)
UNION ALL
(SELECT cust_name AS prod_name FROM Customers)
ORDER BY prod_name 

SQL109 纠错4

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语句之后

对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况