sql查询练习题

（1-3 检索数据，4-7 排序检索数据，8-11 过滤数据，12-15 高级数据过滤，16-19 用通配符进行过滤，20-21 创建计算字段，22-23 使用函数处理数据，24-26 汇总数据，27-31 分组数据，32-36 使用子查询，37-41 联结表，42-46 创建高级联结，47-50 组合查询）

1、编写 SQL 语句，从 Customers 表中检索所有的cust_id

答案：select cust_id from Customers;

2、编写SQL 语句，从表OrderItems中检索并列出所有已订购商品（prod_id）的去重后的清单

答案：select distinct prod_id from OrderItems;

3、现在有Customers 表（表中含有列cust_id代表客户id，cust_name代表客户姓名）。需要编写 SQL语句，检索所有列

答案：select cust_id,cust_name from Customers;

4、有表Customers，cust_id代表客户id，cust_name代表客户姓名。从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。

答案：select cust_name from Customers order by cust_name desc;

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

答案：select cust_id,order_num from Orders order by cust_id,order_date desc;

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

答案：select quantity,item_price from OrderItems order by quantity

desc,item_price desc;

7、下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name逆序排列

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

答案：

SELECT vend_name 
FROM Vendors 
ORDER by vend_name DESC;

8、从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格（prod_price）为 9.49 美元的产品。

答案：select prod_id,prod_name from Products where prod_price=9.49;

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

答案：select prod_id,prod_name from Products where prod_price>=9;

10、编写 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;

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

答案：select distinct order_num from OrderItems where quantity>=100;

12、Vendors表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）。编写 SQL 语句，从 Vendors 表中检索供应商名称

（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA）

答案：select vend_name from Vendors where vend_country = 'USA' and vend_state = 'CA';

13、OrderItems 表包含了所有已订购的产品（有些已被订购多次）。编写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');

14、有表Products。编写 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;

15、修改正确下面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;

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

答案：select prod_name,prod_desc from Products where prod_desc like '%toy%';

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

答案：select prod_name,prod_desc from Products where prod_desc not like '%toy%' order by prod_name;

18、编写 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%';

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

答案：select prod_name,prod_desc from Products where prod_desc like '%toy%carrots%';

20、编写 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;

21、我们的示例商店正在进行打折促销，所有产品均降价 10%。Products表包含prod_id产品id、prod_price产品价格。编写 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;

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

答案：select

cust_id,cust_name,upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3))) as user_login from Customers;

23、Orders订单表。编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

答案：select order_num,order_date from Orders where order_date like '2020-01%' order by order_date;

24、OrderItems表代表售出的产品，quantity代表售出商品数量。编写 SQL 语句，确定已售出产品的总数。返回items_ordered列名，表示已售出商品的总数

答案：select sum(quantity) as items_ordered from OrderItems;

25、OrderItems表代表售出的产品，quantity代表售出商品数量，产品项为prod_id。返回items_ordered列名，表示已售出商品的总数，确定已售出产品项（prod_id）为"BR01"的总数

答案：select sum(quantity) as items_ordered from OrderItems where prod_id = 'BR01';

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

答案：select max(prod_price) as max_price from Products where prod_price <= 10;

27、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;

28、有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id。编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。返回供应商id vend_id和对应供应商成本最低的产品cheapest_item

答案：select vend_id,min(prod_price) as cheapest_item from Products group by vend_id order by cheapest_item;

29、OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。返回order_num订单号（where过滤行；having过滤分组）

答案：select distinct order_num from OrderItems group by order_num having sum(quantity)>=100 order by order_num;

30、OrderItems表代表订单信息，包括字段：订单号order_num和item_price商品售出价格、quantity商品数量。编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序（提示：总价 = item_price 乘以 quantity）

答案：select distinct order_num,sum(item_price * quantity) as total_price from OrderItems group by order_num having sum(quantity * item_price) >= 1000 order by order_num;

31、OrderItems表含有order_num订单号。将下面代码修改正确后执行，返回订单号order_num和出现的次数items

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 
ORDER BY items, order_num;

32、OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num。使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）

答案：select distinct cust_id from Orders where order_num in (select order_num from OrderItems where item_price >=10);

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

OrderItems表

Orders表

答案：select cust_id,order_date from Orders where order_num in (select order_num from OrderItems where prod_id = 'BR01');

34、你想知道订购 BR01 产品的日期，有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；

Customers表含有cust_email 顾客邮件和cust_id顾客id

OrderItems表

Orders表

Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email

返回购买 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'));

35、 我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。

Orders表订单号：order_num、顾客id：cust_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 group by order_num) 
as total_ordered from Orders order by total_ordered desc;
##############################################################################
select cust_id,
    (select total_ordered 
    from
        (select distinct order_num,round(sum(item_price*quantity)) as total_ordered
        from OrderItems
        group by order_num
        ) as t1
    where t1.order_num=Orders.order_num
    ) as total_ordered
from Orders
order by total_ordered desc;

36、Products 表中检索所有的产品名称：prod_name、产品id：prod_id

OrderItems代表订单商品表，订单产品：prod_id、售出数量：quantity

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

答案：

select prod_name,
(select sum(quantity)
from OrderItems
where OrderItems.prod_id=Products.prod_id
group by prod_id
 ) as quant_sold
from Products;

37、描述

Customers 表有字段顾客名称cust_name、顾客id cust_id

Orders订单信息表，含有字段order_num订单号、cust_id顾客id

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

答案：

1. select cust_name,order_num from Customers,Orders where Customers.cust_id=Orders.cust_id
   order by cust_name,order_num;

2. select cust_name,order_num from Customers inner join Orders on Customers.cust_id=Orders.cust_id
   order by cust_name,order_num;

38、描述

Customers 表有字段，顾客名称：cust_name、顾客id：cust_id

Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id

OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

除了返回顾客名称和订单号，返回 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 ) t join Orders o on t.order_num = o.order_num join Customers c on o.cust_id = c.cust_id order by cust_name,o.order_num;

39、描述

表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date

OrderItems表