sql-teach-yourself
01 检索数据
-
题目: 编写 SQL 语句,从 Customers 表中检索所有的cust_id
SELECT cust_id FROM Customers;
-
题目: 编写SQL 语句,检索并列出所有已订购商品(prod_id)的去重后的清单。
SELECT DISTINCT prod_id FROM OrderItems;
-
题目: 需要编写 SQL语句,检索所有列。
SELECT cust_id, cust_name FROM Customers;
02 排序检索数据
-
题目: 从 Customers 中检索所有的顾客名称(cust_name),并按从 Z 到 A 的顺序显示结果。
SELECT cust_name FROM Customers ORDER BY cust_name DESC;
-
题目: 编写 SQL 语句,从 Orders 表中检索顾客 ID(cust_id)和订单号(order_num),并先按顾客 ID 对结果进行排序,再按订单日期倒序排列。
SELECT cust_id, order_num FROM Orders ORDER BY cust_id, order_date DESC;
-
题目: 编写 SQL 语句,显示 OrderItems 表中的数量(quantity)和价格(item_price),并按数量由多到少、价格由高到低排序。
SELECT quantity, item_price FROM OrderItems ORDER BY quantity DESC, item_price DESC;
-
题目: 下面的 SQL 语句有问题吗?尝试将它改正确,使之能够正确运行,并且返回结果根据vend_name逆序排列
SELECT vend_name, FROM Vendors ORDER vend_name DESC;
改正:
SELECT vend_name FROM Vendors ORDER BY vend_name DESC;
03 过滤数据
-
题目: 从 Products 表中检索产品 ID(prod_id)和产品名称(prod_name),只返回价格为 9.49 美元的产品。
SELECT prod_id, prod_name FROM Products WHERE prod_price = 9.49;
-
题目: 编写 SQL 语句,从 Products 表中检索产品 ID(prod_id)和产品名称(prod_name),只返回价格为 9 美元或更高的产品。
SELECT prod_id, prod_name FROM Products WHERE prod_price >= 9;
-
题目: 编写 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;
-
题目: 从 OrderItems 表中检索出所有不同且不重复的订单号(order_num),其中每个订单都要包含 100 个或更多的产品。
SELECT DISTINCT order_num FROM OrderItems WHERE quantity >= 100;
04 高级数据过滤
-
题目: 编写 SQL 语句,从 Vendors 表中检索供应商名称(vend_name),仅返回加利福尼亚州的供应商(这需要按国家[USA]和州[CA]进行过滤,没准其他国家也存在一个CA)
SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA';
-
题目: 编写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' );
-
SQL14 返回所有价格在 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;
-
题目: 修改正确下面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;
05 用通配符进行过滤
-
题目: 编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中包含 toy 一词的产品名称
SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%';
-
题目: 编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中未出现 toy 一词的产品,最后按”产品名称“对结果进行排序。
SELECT prod_name, prod_desc FROM Products WHERE prod_desc NOT LIKE '%toy%' ORDER BY prod_name;
-
题目: 编写 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%';
-
题目: 编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示:只需要用带有三个 % 符号的 LIKE 即可。
SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%'
06 创建计算字段
-
题目: 编写 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 vend_name;
-
题目: 编写 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 使用函数处理数据
-
题目: 编写 SQL 语句,返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login),其中登录名全部为大写字母,并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成。提示:需要使用函数、拼接和别名。
SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_name, 1, 2),SUBSTRING(cust_city, 1, 3))) AS user_login FROM Customers;
-
SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期
题目: 编写 SQL 语句,返回 2020 年 1 月的所有订单的订单号(order_num)和订单日期(order_date),并按订单日期升序排序
SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' # WHERE DATE_FORMAT(order_date, '%Y-%m')='2020-01' # WHERE YEAR(order_date) = '2020' and MONTH(order_date) = '1' ORDER BY order_date;
08 汇总数据
-
题目: 编写 SQL 语句,确定已售出产品的总数。
SELECT SUM(quantity) AS items_ordered FROM OrderItems;
-
题目: 修改创建的语句,确定已售出产品项(prod_id)为"BR01"的总数。
SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';
-
SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格
**题目:**编写 SQL 语句,确定 Products 表中价格不超过 10 美元的最贵产品的价格(prod_price)。将计算所得的字段命名为 max_price。
SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price <= 10;
09 分组数据
-
题目: 编写 SQL 语句,返回每个订单号(order_num)各有多少行数(order_lines),并按 order_lines对结果进行升序排序。
SELECT order_num, COUNT(*) AS order_lines FROM OrderItems GROUP BY order_num ORDER BY order_lines;
-
题目: 编写 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;
-
题目: 请编写 SQL 语句,返回订单数量总和不小于100的所有订单号,最后结果按照订单号升序排序。
SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;
-
题目: 编写 SQL 语句,根据订单号聚合,返回订单总价不小于1000 的所有订单号,最后的结果按订单号进行升序排序。
提示:总价 = item_price 乘以 quantity
SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING SUM(item_price * quantity) >= 1000 ORDER BY 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(*) >= 3;
010 使用子查询
-
题目: 使用子查询,返回购买价格为 10 美元或以上产品的顾客列表,结果无需排序。
注意:你需要使用 OrderItems 表查找匹配的订单号(order_num),然后使用Order 表检索这些匹配订单的顾客 ID(cust_id)。SELECT cust_id FROM Orders JOIN OrderItems ON Orders.order_num = OrderItems.order_num WHERE OrderItems.item_price >= 10;
-
SQL33 确定哪些订单购买了 prod_id 为 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;
-
SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件(一)
题目: 返回购买 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' ) );
-
题目: 编写 SQL语句,返回顾客 ID(Orders 表中的 cust_id),并使用子查询返回total_ordered 以便返回每个顾客的订单总数,将结果按金额从大到小排序。
SELECT cust_id, SUM(total_ordered) AS total_ordered FROM ( SELECT order_num, SUM(item_price * quantity) AS total_ordered FROM OrderItems GROUP BY order_num )a JOIN Orders as o ON a.order_num = o.order_num GROUP BY cust_id ORDER BY total_ordered DESC;
-
SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数
题目: 编写 SQL 语句,从 Products 表中检索所有的产品名称(prod_name),以及名为 quant_sold 的计算列,其中包含所售产品的总数(在 OrderItems 表上使用子查询和 SUM(quantity)检索)。
SELECT prod_name, ( SELECT SUM(quantity) FROM OrderItems WHERE OrderItems.prod_id = Products.prod_id ) AS quant_sold FROM Products;
11 联结表
-
题目: 编写 SQL 语句,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法,一个使用简单的等联结语法,另外一个使用 INNER JOIN。
SELECT cust_name, order_num FROM Customers c INNER JOIN Orders o ON c.cust_id = o.cust_id ORDER BY cust_name, order_num;
-
题目: 除了返回顾客名称和订单号,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),添加第三列 OrderTotal,其中包含每个订单的总价,并按顾客名称再按订单号对结果进行升序排序。
SELECT cust_name, o.order_num, OrderTotal FROM ( SELECT order_num, SUM(quantity * item_price) OrderTotal FROM OrderItems GROUP BY order_num ) oi JOIN Orders o ON oi.order_num = o.order_num JOIN Customers c ON o.cust_id = c.cust_id ORDER BY cust_name,o.order_num;
-
SQL39 确定哪些订单购买了 prod_id 为 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;
-
SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
题目: 返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件(Customers 表中的 cust_email),结果无需排序。
提示:涉及到 SELECT 语句,最内层的从 OrderItems 表返回 order_num,中间的从 Customers 表返回 cust_id,但是必须使用 INNER JOIN 语法。
SELECT cust_email FROM Customers c INNER JOIN Orders o ON c.cust_id = o.cust_id INNER JOIN OrderItems oi ON oi.order_num = o.order_num WHERE oi.prod_id = 'BR01 ';
-
题目: 编写 SQL 语句,返回订单总价不小于1000 的客户名称和总额(OrderItems 表中的order_num)。
提示:需要计算总和(item_price 乘以 quantity)。按总额对结果进行排序,请使用INNER JOIN 语法。
SELECT c.cust_name, oi.total_price FROM ( SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 )oi INNER JOIN Orders o ON o.order_num = oi.order_num INNER JOIN Customers c ON c.cust_id = o.cust_id ORDER BY oi.total_price;
12 创建高级联结
-
题目: 使用 INNER JOIN 编写 SQL语句,检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),最后根据顾客姓名cust_name升序返回。
SELECT cust_name, order_num FROM Customers c INNER JOIN Orders o ON c.cust_id = o.cust_id ORDER BY c.cust_name;
-
题目: 检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名cust_name升序返回。
语法: 即使他们没有下过订单,右表中未匹配到的字段用NULL表示,可以采用左连接。
SELECT cust_name, order_num FROM Customers c LEFT JOIN Orders o ON c.cust_id = o.cust_id ORDER BY c.cust_name;
-
题目: 使用 OUTER JOIN 联结 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;
-
**题目:**使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和每一项产品的总订单数(不是订单号),并按产品名称升序排序。
语法: 是在连接查询中用来代替对应的on关键字的,进行条件匹配。
SELECT prod_name, COUNT(order_num) AS orders FROM Products LEFT JOIN OrderItems USING(prod_id) GROUP BY prod_name ORDER BY prod_name;
-
题目: 列出供应商(Vendors 表中的 vend_id)及其可供产品的数量,包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量,最后根据vend_id 升序排序。
注意:vend_id 列会显示在多个表中,因此在每次引用它时都需要完全限定它。
SELECT vend_id, COUNT(prod_id) AS prod_id FROM Vendors LEFT JOIN Products USING(vend_id) GROUP BY vend_id ORDER BY vend_id;
13 组合查询
-
题目: 将两个 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;
-
题目: 将两个 SELECT 语句结合起来,以便从 OrderItems表中检索产品 id(prod_id)和 quantity。其中,一个 SELECT 语句过滤数量为 100 的行,另一个 SELECT 语句过滤 id 以 BNBG 开头的产品,最后按产品 id 对结果进行升序排序。
注意:这次仅使用单个 SELECT 语句。SELECT prod_id, quantity FROM OrderItems WHERE quantity = 100 OR prod_id LIKE "BNBG%" ORDER BY prod_id;
-
SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名
题目: 编写 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;
-
**题目:**修正下面错误的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;语法: 使用了union子句,所以order by 子句只能使用一次且只能在最后一个 select 中使用。
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;