两天完成牛客网sql必知必会50题（附链接）

sql必知必会50题链接直达

SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

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

select max(prod_price) max_price
from Products
where prod_price <= 10
1
2
3

SQL27 返回每个订单号各有多少行数

编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines对结果进行升序排序。

select order_num, count(*) order_lines
from OrderItems
group by order_num
order by order_lines
1
2
3
4

SQL28 每个供应商成本最低的产品

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

select vend_id, min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item
1
2
3
4

SQL29 返回订单数量总和不小于100的所有订单的订单号

请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。

select order_num
from OrderItems
where quantity >= 100
order by order_num
1
2
3
4

SQL30 计算总和

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

提示：总价 = item_price 乘以 quantity

select order_num, sum(item_price*quantity) total_price
from OrderItems
group by order_num
having sum(item_price*quantity)>=1000
order by order_num
1
2
3
4
5

SQL31 纠错3

将下面代码修改正确后执行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;
1
2
3
4
5

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;
1
2
3
4
5

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

使用子查询，返回购买价格为 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
)
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8

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'
    )
)
1
2
3
4
5
6
7
8
9
10
11

SQL35 返回每个顾客不同订单的总金额

编写 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 total_ordered
from Orders
order by total_ordered desc
1
2
3
4
5
6
7

SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数

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

select prod_name, (
    select sum(quantity)
    from OrderItems
    where Products.prod_id = OrderItems.prod_id
    )
from Products
1
2
3
4
5
6

SQL37 返回顾客名称和相关订单号

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

select cust_name, order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by cust_name, order_num
1
2
3
4

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
1
2
3
4

SQL38 返回顾客名称和相关订单号以及每个订单的总价

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

select cust_name, o.order_num, sum(quantity*item_price) OrderTotal
from Orders o
inner join Customers c on c.cust_id = o.cust_id
inner join OrderItems oi on oi.order_num = o.order_num
group by cust_name, o.order_num
order by cust_name, o.order_num
1
2
3
4
5
6

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
1
2
3
4
5
6
7
8

select cust_id, order_date
from Orders o
inner join OrderItems oi using(order_num)
where prod_id = 'BR01'
order by order_date
1
2
3
4
5

SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

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

提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。

select cust_email
from Orders o
inner join OrderItems oi on oi.order_num = o.order_num
inner join Customers c using(cust_id)
where prod_id = 'BR01'
1
2
3
4
5

SQL41 确定最佳顾客的另一种方式（二）

编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

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

select cust_name, sum(item_price*quantity) total_price
from Orders o
inner join Customers c using(cust_id)
inner join OrderItems oi using(order_num)
group by cust_name
having total_price >= 1000
order by total_price
1
2
3
4
5
6
7

having 和 group by 组合着使用，where是聚合前筛选，having是用了sum之后筛选

当有三张表进行连接时，可以将中间的表作为主表，然后inner join依次进行和主表连接

SQL42 检索每个顾客的名称和所有的订单号（一）

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

select cust_name, order_num
from Customers c
inner join Orders o using(cust_id)
order by cust_name
1
2
3
4

SQL43 检索每个顾客的名称和所有的订单号（二）

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

select cust_name, order_num
from Customers left join Orders using(cust_id)
order by cust_name
1
2
3

SQL44 返回产品名称和与之相关的订单号

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

select prod_name, order_num
from Products left join OrderItems using(prod_id)
union 
select prod_name, order_num
from Products right join OrderItems using(prod_id)
order by prod_name
1
2
3
4
5
6

SQL45 返回产品名称和每一项产品的总订单数

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

select prod_name, count(order_num) orders
from Products left join OrderItems using(prod_id)
group by prod_name
order by prod_name
1
2
3
4

SQL46 列出供应商及其可供产品的数量

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

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

select v.vend_id, count(prod_id) prod_id
from Vendors v 
left join Products p using(vend_id)
group by v.vend_id
order by v.vend_id
1
2
3
4
5

SQL47 将两个 SELECT 语句结合起来（一）

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

select prod_id, quantity
from OrderItems
where prod_id like "BNBG%"
union
select prod_id, quantity
from OrderItems
where quantity = 100
order by prod_id
1
2
3
4
5
6
7
8

SQL48 将两个 SELECT 语句结合起来（二）

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

select prod_id, quantity
from OrderItems
where prod_id like "BNBG%" or quantity = 100
order by prod_id
1
2
3
4

SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

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

select prod_name
from Products
union all
select cust_name prod_name
from Customers
order by prod_name
1
2
3
4
5
6

#错误的
select prod_name, cust_name as prod_name
from Products, Customers
order by prod_name
### Execution Error
### SQL_ERROR_INFO: "Column 'prod_name' in order clause is ambiguous"
1
2
3
4
5
6

SQL50 纠错4

修正下面错误的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;
1
2
3
4
5
6
7
8

【示例结果】

返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客email：cust_email

cust_name	cust_contact	cust_email
cust1	8695193	cust1@cust.com
cust10	8695192	cust10@cust.com
cust2	8695194	cust2@cust.com

【示例解析】

返回住在"IL"和"MI"的顾客信息，最后根据顾客名称升序排序。

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;
1
2
3
4
5
6
7
8

使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。