【SQL牛客网刷题】SQL必知必会50题

（1）select prod_name,prod_price

from Products

where prod_price >= 3 and prod_price<=6

order by prod_price

（2）select prod_name,prod_price

from Products

where prod_price between 3 and 6

order by prod_price

知识点：在使用between的时候，要注意验证，他的两侧是否是 >= 还是不带=。

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

select order_num from OrderItems

group by order_num

having sum(quantity) >= 100

知识点：认为该题需要累加，分组后使用having进行结果集的筛选

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

select vend_name

from Vendors

where vend_country = 'USA' and vend_state = 'CA';

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

select order_num,prod_id,quantity

from OrderItems

where prod_id in('BR01', 'BR02', 'BR03') and quantity >= 100

知识点：in 和 and多列筛选

14. 返回所有价格在 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

15.纠错

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

知识点：order by 在where 后执行。

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

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

（1） select prod_name,prod_desc

from Products

where prod_desc like '%toy%'

（2）正则：select prod_name,prod_desc

from Products

where prod_desc REGEXP 'toy';

知识点：sql正则sql-正则表达式 - 知乎 (zhihu.com)

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

select prod_name,prod_desc

from Products

where prod_desc not like '%toy%'

order by prod_name

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

（1）like：

select prod_name,prod_desc from Products
where prod_desc like '%toy%' and prod_desc like '%carrots%'

（2）正则表达式：

select prod_name,prod_desc from Products
where prod_desc regexp "(.*toy.*carrots.*)|(.*carrots.*toy.*)"

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

select prod_name,prod_desc

from Products

where prod_desc like '%toy%carrots%'

20. 从 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

21. 从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）

select prod_id,prod_price,

round(prod_price*0.9,3) as sale_price

from Products

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

（1）left

select cust_id,cust_name,

upper (concat(left (cust_contact,2),left(cust_city,3))) as user_login

from Customers

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

知识点：转大写函数upper, left、right用法，字符串截取substring，拼接concat。

23. 返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

（1）select order_num,order_date

from Orders

where year(order_date) = 2020 and month(order_date) = 1

order by order_date

（2）select order_num,order_date
from Orders
where year(order_date)='2020' and month(order_date)= '01'

order by order_date

（3）select *
from Orders
where order_date between '2020-01-01' and '2020-01-31'
order by order_date

（4）select *
from Orders
where order_date like "%2020-01%"
order by order_date asc

（5）select *
from Orders
where order_date regexp('2020-01')
order by order_date

（6）select order_num,order_date
from Orders
where order_date >= '2020-01-01'
and order_date < '2020-02-01'
order by order_date;

（7）select

    order_num,

    order_date

    from Orders

where date_format(order_date, '%Y%m')='202001'

order by order_date

（8）select

order_num,

order_date

from Orders

where substr(order_date,1,7) = '2020-01'

order by order_date

知识点：日期函数

24，确定已售出产品的总数。

select sum(quantity) as items_ordered from OrderItems

25. 确定已售出产品项（prod_id）为"BR01"的总数。

select sum(quantity) as items_ordered

from OrderItems

where prod_id = 'BR01'

26. 确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。

select max(prod_price) as max_price

from Products

where prod_price <= 10

27. 返回每个订单号（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(*),count(列名)都可以，区别在于，count(列名)是统计非NULL的行数
2、order by最后执行，所以可以使用列别名
3、该题给的数据以order_num为主键，所给数据中有重复，所以要用group分组

28.返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。

（1）select vend_id,

min(prod_price) as cheapest_item

from Products

group by vend_id

order by cheapest_item

（2）SELECT

    vend_id,
    cheapest_item
FROM
    ( SELECT vend_id, prod_price AS cheapest_item, row_number ( ) over ( PARTITION BY vend_id ORDER BY prod_price ) AS tops FROM Products ) AS t1
WHERE
    tops = 1
ORDER BY
    cheapest_item;

知识点：窗口函数用来解决：排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励等相似问题，该题适用，通俗易懂的学会：SQL窗口函数 - 知乎 (zhihu.com)

29. 返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。

select order_num from OrderItems

group by order_num

having sum(quantity) >= 100

order by order_num

知识点：where不能使用聚合函数、having中可以使用聚合函数

30. 根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。

提示：总价 = item_price 乘以 quantity

select order_num,

sum(item_price * quantity) as total_price

from OrderItems

group by order_num

having total_price >=1000

order by order_num

31.改错

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. 使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。

注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

（1）select cust_id from Orders

where order_num in (select order_num

from OrderItems

where item_price>=10)

（2）多表联合

select b.cust_id from OrderItems a

join Orders b on a.order_num = b.order_num

where a.item_price >=10

知识点：子查询：SQL 子查询_w3cschool

33. 使用子查询来确定哪些订单（在 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)

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

多表联合

select cust_email from Customers c

inner join Orders b on b.cust_id = c.cust_id

inner join OrderItems a on a.order_num = b.order_num

where prod_id = 'BR01'

知识点：这道题，多表链接更简单一点。
多表链接，当没有特定的主表，基本上可以使用 inner join内链接。

inner join OrderItems using(order_num)

using可代替on

35.返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

（1）子查询：select cust_id, total_ordered
from Orders as a, (
    select order_num, sum(item_price*quantity) as total_ordered
    from OrderItems
    group by order_num) as b
where a.order_num = b.order_num
order by total_ordered desc

（2）多表联合

select t.cust_id,SUM(quantity*item_price) as total_ordered

from Orders t inner join OrderItems t1 on t.order_num=t1.order_num

group by cust_id

ORDER BY total_ordered DESC

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

select prod_name, SUM(quantity) as quant_sold

from OrderItems

join Products on Products.prod_id = OrderItems .prod_id

group by prod_name

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

（1）inner join

select cust_name,order_num from Orders

inner join Customers on Customers.cust_id = Orders.cust_id

order by cust_name,order_num

（2）select cust_name,order_num

from Customers,Orders

where Customers.cust_id=Orders.cust_id

order by cust_name,order_num;

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

select cust_name,

       t1.order_num,

       sum(quantity*item_price) as OrderTotal

from Customers t

inner join Orders t1 on t.cust_id = t1.cust_id

inner join OrderItems t2 on t1.order_num = t2.order_num

group by cust_name,t1.order_num

order by cust_name,t1.order_num

（2）select t1.cust_name,

       t2.order_num,

       (quantity * item_price) OrderTotal

from Customers t1,

     Orders t2,

     OrderItems t3

where t1.cust_id=t2.cust_id and t2.order_num=t3.order_num

group by cust_name,t1.order_num

order by cust_name,OrderTotal

知识点：因为order_num在多个表出现，所以在引用列的时候，需要注明该列来自哪个表。

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

(1)select cust_id,order_date

from Orders

where order_num in (select order_num from OrderItems

where prod_id = "BR01"

)

order by order_date

（2）select cust_id ,order_date from Orders

inner join OrderItems on OrderItems.order_num = Orders.order_num

where prod_id = "BR01"

order by order_date

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

select cust_email from Customers

inner join Orders on Orders.cust_id = Customers.cust_id

inner join OrderItems on OrderItems.order_num = Orders.order_num

where prod_id = 'BR01'

41. 返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

select cust_name,

sum(item_price * quantity) as total_price

from OrderItems

inner join Orders on OrderItems.order_num = Orders.order_num

inner join Customers on Customers.cust_id = Orders.cust_id

group by cust_name

having total_price >= 1000

order by total_price

知识点：先建新表再筛选

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

select cust_name,order_num from Customers

inner join Orders on Orders.cust_id = Customers.cust_id

order by cust_name

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

select cust_name,order_num from Customers

left join Orders on Orders.cust_id = Customers.cust_id

order by cust_name

知识点：主表放左边

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

select prod_name, order_num from Products

left join OrderItems on OrderItems.prod_id = Products.prod_id

order by prod_name

知识点：表的联结分为内连接（inner join）和外连接（outer join），其中外连接又分三种，左连接（left outer join）、右连接（right outer join）和全连接（full outer join）；我们平常在写全连接的时候会把'outer'省略。

A inner join B 取交集。
A left join B 取 A 全部，B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。

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

select prod_name,count(order_num) as orders from Products

left join OrderItems on OrderItems.prod_id = Products.prod_id

group by prod_name

order by prod_name

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

注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

select t1.vend_id,count(t2.prod_id) as prod_id

from Vendors t1

left join Products t2 on t1.vend_id = t2.vend_id

group by t1.vend_id

order by t1.vend_id

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

select prod_id,quantity from OrderItems where quantity =100

union all

select prod_id,quantity from OrderItems where prod_id like "BNBG%"

order by prod_id

知识点：union--连接表，对行操作。

union--将两个表做行拼接，同时自动删除重复的行。

union all---将两个表做行拼接，保留重复的行。

48. 将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
注意：这次仅使用单个 SELECT 语句。

Ps，什么破题

（1）select prod_id, quantity

from OrderItems

where quantity = 100 or prod_id like 'BNBG%'

order by prod_id;

（2）子查询：SELECT *

FROM

   (select

        prod_id,

        quantity

    from

        OrderItems

    where

        quantity = 100

    union all

    select

        prod_id,

        quantity

    from

        OrderItems

    where

        prOD_id like 'BNBG%') A

ORDER BY

prod_id asc

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

select prod_name

from Products

union all

select cust_name

from Customers

order by prod_name

50. 改错

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;

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

知识点：在组合查询中，排序只能放在所有语句的最后，且出现一次。