SQL必知必会习题练习
《SQL必知必会》习题练习答案
使⽤的都是MySQL语句，⾃⼰留存做笔记⽤的，不⼀定对（不过⾃⼰测试没问题才会粘过来_(:з」∠)_）
最近更新0923：第⼗课 分组依据
第⼆课检索数据
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语句。（当然，要测试这两个语句。）
/*lect * from customers;*/
SELECT cust_id FROM customers;
第三课排序检索数据
破损1．编写SQL语句，从Customers中检索所有的顾客名称（cust_names），并按从Z到A的顺序显⽰结果。
SELECT cust_name FROM customers
ORDER BY`cust_name`DESC;
2．编写SQL语句，从Orders表中检索顾客ID（cust_id）和订单号（order_num），并先按顾客ID对结果进⾏排序，再按订单⽇期倒序排列。
SELECT cust_id,order_num
FROM orders
ORDER BY1,order_date DESC;
3．显然，我们的虚拟商店更喜欢出售⽐较贵的物品，⽽且这类物品有很多。编写SQL语句，显⽰OrderItems表中的数量和价格
（item_price），并按数量由多到少、价格由⾼到低排序。
SELECT`quantity`,`item_price`
FROM`orderitems`
ORDER BY quantity DESC,item_price DESC;
4．下⾯的SQL语句有问题吗？（尝试在不运⾏的情况下指出。）
会报错
①order后⾯缺了个by
vend_name后⾯没有逗号
第四课过滤数据
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），然后按价格对结果进⾏排序。（本题有多种解决⽅案，我们在下⼀课再讨论，不过你可以使⽤⽬前已学的知识来解决它。）
#①使⽤and操作符进⾏连接
SELECT prod_name,prod_price
FROM products
WHERE prod_price >=3AND prod_price <=6经典土味情话
ORDER BY prod_price;
#②使⽤between-and
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN3AND6
ORDER BY prod_price;
第五课⾼级数据过滤
1．编写SQL语句，从Vendors表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进⾏过滤，没准其他国家也存在⼀个加利福尼亚州）。提⽰：过滤器需要匹配字符串。
SELECT vend_name FROM vendors
WHERE`vend_country`='USA'AND`vend_state`='CA';
2．编写SQL语句，查找所有⾄少订购了总量100个的BR01、BR02或BR03的订单。你需要返回OrderItems表的订单号
（order_num）、产品ID（prod_id）和数量，并按产品ID和数量进⾏过滤。提⽰：根据编写过滤器的⽅式，可能需要特别注意求值顺序。#①使⽤IN操作符
SELECT order_num,prod_id,quantity
FROM orderitems
WHERE quantity >=100
AND prod_id IN('BR01','BR02','BR03');
#②只使⽤and/or操作符
SELECT order_num,prod_id,quantity
FROM orderitems
WHERE quantity >=100
AND(prod_id ='BR01'OR prod_id ='BR02'OR prod_id ='BR03');心声作文
3．现在，我们回顾上⼀课的挑战题。编写SQL语句，返回所有价格在3美元到6美元之间的产品的名称（prod_name）和价格
（prod_price）。使⽤AND，然后按价格对结果进⾏排序。
SELECT prod_name,prod_price
FROM products
WHERE prod_price >=3AND prod_price <=6
ORDER BY prod_price;
4．下⾯的SQL语句有问题吗？（尝试在不运⾏的情况下指出。）
有问题，order by应该在where⼦句的后⾯
第六课⽤通配符进⾏固定
1．编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含toy⼀词的产品。
SELECT prod_name,prod_desc
FROM products
WHERE prod_name LIKE'%toy%';
2．反过来再来⼀次。编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现toy⼀词的产品。这次，按产品名称对结果进⾏排序。
SELECT prod_name,prod_desc
FROM products
WHERE prod_name NOT LIKE'%toy%'
order by prod_name;
where not prod_name like '%toy%'也是正确的语法
3．编写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%';
4．来个⽐较棘⼿的。我没有特别向你展⽰这个语法，⽽是想看看你根据⽬前已学的知识是否可以找到答案。编写SQL语句，从Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现toy和carrots的产品。提⽰：只需要⽤带有三个%符号的LIKE即可。
SELECT prod_name,prod_desc
FROM products
WHERE prod_desc LIKE'%toy%carrots%';
第七课创建计算字段
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 vname,
vend_city vcity,
vend_address vaddress
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.9AS sale_price
FROM products;
第⼋课使⽤函数处理数据
1．我们的商店已经上线了，正在创建顾客账户。所有⽤户都需要登录名，默认登录名是其名称和所在城市的组合。编写SQL语句，返回顾客ID（cust_id）、顾客名称（customer_name）和登录名（ur_login），其中登录名全部为⼤写字母，并由顾客联系⼈的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。例如，我的登录名是BEOAK（Ben Forta，居住在OakPark）。提⽰：需要使⽤函数、拼接和别名。
SELECT cust_id,cust_name,
商场保安
UPPER(CONCAT(SUBSTR(`cust_contact`,1,2),SUBSTR(`cust_city`,1,3)))AS ur_login
FROM`customers`;
ps：使⽤left函数更⽅便⼀点
2．编写SQL语句，返回2020年1⽉的所有订单的订单号（order_num）和订单⽇期（order_date），并按订单⽇期排序。你应该能够根据⽬前已学的知识来解决此问题，但也可以开卷查阅DBMS⽂档。
SELECT order_num,order_date
FROM`orders`
WHERE YEAR(order_date)=2020AND MONTH(order_date)=01
ORDER BY order_date;
ps：⽉份可以写成01也可以写成1，不⽤加引号。
第九课汇总数据
1．编写SQL语句，确定已售出产品的总数（使⽤OrderItems中的quantity列）。
SELECT SUM(quantity)
FROM`orderitems`;
2．修改刚刚创建的语句，确定已售出产品项（prod_item）BR01的总数。
SELECT SUM(quantity)
FROM`orderitems`
WHERE prod_id ='br01';
3．编写SQL语句，确定Products表中价格不超过10美元的最贵产品的价格（prod_price）。将计算所得的字段命名为max_price。
船舶工程技术
SELECT MAX(prod_price)AS max_price
FROM products
WHERE prod_price <=10;
第⼗课分组依据
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;
count后⾯使⽤*也⾏，因为前⾯lect的字段即为要计算的字段
2．编写SQL语句，返回名为cheapest_item的字段，该字段包含每个供应商成本最低的产品（使⽤Products表中的prod_price），然后从最低成本到最⾼成本对结果进⾏排序。
SELECT vend_id,MIN(prod_price)AS cheapest_item
FROM products
GROUP BY vend_id
ORDER BY prod_price;
最后的分组也可以直接少⽤别称cheapest_item
3．确定最佳顾客⾮常重要，请编写SQL语句，返回⾄少含100项的所有订单的订单号（OrderItems表中的order_num）。
SELECT order_num FROM orderitems
GROUP BY order_num
HAVING SUM(quantity)>=100;
4．确定最佳顾客的另⼀种⽅式是看他们花了多少钱。编写SQL语句，返回总价⾄少为1000的所有订单的订单号（OrderItems表中的order_num）。提⽰：需要计算总和（item_price乘以quantity）。按订单号对结果进⾏排序。
SELECT order_num,SUM(item_price*quantity)AS总价
FROM orderitems
GROUP BY order_num
HAVING SUM(item_price*quantity)>=1000;
5．下⾯的SQL语句有问题吗？（尝试在不运⾏的情况下指出。）
应该是group by order_num
group by 后⾯需要是实际的列或表达式列，不是⽤聚集函数计算以后的列。
第⼗⼀课使⽤⼦查询
1．使⽤⼦查询，返回购买价格为10美元或以上产品的顾客列表。你需要使⽤OrderItems表查找匹配的订单号（order_num），然后使⽤Order表检索这些匹配订单的顾客ID（cust_id）。
#刚开始没看见orders表⾥就有cust_id字段，多嵌套了⼀层
SELECT cu.cust_id,cust_name
FROM customers cu
WHERE cu.cust_id IN(
SELECT cust_id FROM orders
WHERE order_num IN(
SELECT order_num FROM orderitems
WHERE item_price>=10));
#更改如下
SELECT cust_id
FROM orders
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(
SELECT order_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。
