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语句。(当然,要测试这两个语句。)

/*select * 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 BY 1,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 >= 3 AND prod_price <= 6
ORDER BY prod_price;
#②使用between-and
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6
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 >= 3 AND 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.9 AS sale_price
FROM products;

第八课 使用函数处理数据

1.我们的商店已经上线了,正在创建顾客账户。所有用户都需要登录名,默认登录名是其名称和所在城市的组合。编写SQL语句,返回顾客ID(cust_id)、顾客名称(customer_name)和登录名(user_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 user_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) = 2020 AND 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后面使用*也行,因为前面select的字段即为要计算的字段

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。

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')
		ORDER BY order_date);

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

SELECT cust_id,
	(SELECT SUM(item_price*quantity) FROM orderitems 
	WHERE orderitems.`order_num`=orders.`order_num`)  AS price
FROM orders
order by price;

也可以使用连接查询

SELECT cust_id,SUM(item_price*quantity) AS price
FROM orders
INNER JOIN orderitems
ON orders.`order_num`=orderitems.`order_num`
GROUP BY orders.order_num
ORDER BY price;

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;

也可以使用连接查询

SELECT prod_name,SUM(quantity)
FROM products
LEFT JOIN orderitems
ON products.`prod_id` = orderitems.`prod_id`
GROUP BY products.`prod_id`;
  • 8
    点赞
  • 47
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
### 回答1: 《SQL必知必会》是一本由Ben Forta所著的经典SQL学习教材,在CSDN上有很高的推荐度和用户评价。 这本书以通俗易懂的语言介绍了SQL(Structured Query Language)的基础概念和使用方法。从最基础的SELECT语句开始,逐渐深入地介绍了SQL的各种操作和技巧,包括过滤、排序、统计、连接、子查询等。 这本书的优点之一是注重实践。作者通过大量的实例和练习题,让读者能够真正动手操作,加深对SQL的理解和熟练度。书中的练习题循序渐进,既能帮助初学者理解知识点,又能挑战更高层次的读者。 同时,本书也非常注重SQL语句的规范和性能优化。作者强调了编写高效SQL语句的重要性,教授了避免冗余查询、选择合适的索引等技巧,帮助读者提高查询速度和数据库的性能。 此外,《SQL必知必会》还介绍了一些数据库管理的基本知识,如创建和管理表、编写存储过程、触发器等。这些知识使读者能够更好地理解数据库系统和整个开发流程。 总的来说,《SQL必知必会》是一本很好的SQL入门教材,适合想要学习SQL的初学者和希望加强SQL技能的读者。它的简洁明了的教学风格和丰富的实例操作,使读者能够迅速入门并掌握SQL的基本知识和技巧。无论是在工作中还是面试中,掌握好SQL技能都是非常重要的,因此《SQL必知必会》是一本不可或缺的参考书籍。 ### 回答2: 《SQL 必知必会》是由Ben Forta所写的一本经典的SQL学习指南。本书以简单易懂的语言、通俗易懂的案例,全面而系统地介绍了SQL的基础知识和常用操作。 本书首先从SQL的起源和发展历程开始,介绍了SQL的作用、应用领域以及其与其他数据库语言的关系。接着,本书详细讲解了SQL的几个基本组成部分,包括数据定义语言(DDL)、数据查询语言(DQL)、数据操作语言(DML)和数据控制语言(DCL)。 在学习SQL语句的过程中,作者通过大量的实例和图表来解释SQL的各种语法和用法。读者可以通过跟着书中的案例一步一步实践,加深对SQL的理解和掌握。此外,书中还介绍了常见的SQL函数、约束、事务等内容,帮助读者进一步提高SQL的应用能力。 《SQL 必知必会》还对数据库的设计和优化进行了一定的介绍,使读者能够更好地理解数据库的运行原理和优化思路。此外,本书还提供了一些常见的数据库错误和解决方案,帮助读者避免常见的问题和错误。 总的来说,本书从入门到进阶都有涉及,内容详实且易懂。不仅适合初学者入门,也适合有一定SQL基础的开发者进一步提高。无论是学习数据库的基础知识,还是使用SQL解决实际问题,本书都是一本不可多得的学习资料。无论是从事数据分析、数据挖掘,还是数据库开发、管理等方面的人员,都应该拥有这本书,作为自己学习和工作中的重要参考书籍。 ### 回答3: 《SQL必知必会》是一本由柯特·戈维(Ben Forta)所著的SQL入门经典教材,已广为程序员和数据库爱好者所熟悉。该书系统地介绍了SQL的基本概念、语法和应用,并提供了大量的实例和练习题供读者巩固学习。 《SQL必知必会》主要分为四个部分:查询基础,过滤数据,数据操作和高级特性。首先,它详细介绍了SQL语言的基础知识,包括数据库的概念、表的创建和插入数据等。然后,它讲解了如何使用查询语句来过滤和排序数据,以及使用运算符和函数来处理数据。接着,它介绍了如何更新、插入和删除表中的数据,以及如何创建和修改表结构。最后,它介绍了SQL语言的高级特性,如多表查询、子查询、视图和索引等。 《SQL必知必会》在教学方法上非常简洁明了,注重实践。每个概念和语法都通过实例进行讲解,并提供了大量的练习题供读者巩固学习。此外,书中还提供了SQL语句的常见错误和解决方法,帮助读者更好地理解和应用SQL语言。 总之,《SQL必知必会》作为一本SQL入门经典教材,非常适合初学者学习和掌握SQL语言。无论是作为学习资料还是作为参考手册,它都能帮助读者快速入门并实际应用SQL语言进行数据库操作。读者通过学习本书可以牢固掌握SQL的基础知识,并能够运用SQL语言进行数据库的查询、更新和管理。
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值