牛客sql_SQL必知必会

SQL1 从 Customers 表中检索所有的 ID

select cust_id
from Customers

SQL2 检索并列出已订购产品的清单

select distinct prod_id
from OrderItems

SQL3 检索所有列

select cust_id,cust_name
from Customers 
where cust_id like 'a%'

SQL4 检索顾客名称并且排序

select cust_name
from Customers
order by cust_name desc

SQL5 对顾客ID和日期排序

select cust_id,order_num
from Orders
order by cust_id,order_date desc

SQL6 按照数量和价格排序

select quantity,item_price
from OrderItems
order by quantity desc,item_price desc

SQL7 检查SQL语句

SELECT vend_name 
FROM Vendors 
ORDER by vend_name DESC;

SQL8 返回固定价格的产品

select prod_id,prod_name
from Products
where prod_price='9.49'

SQL9 返回更高价格的产品

select prod_id,prod_name
from Products 
where prod_price>=9

SQL10 返回产品并且按照价格排序

select prod_name,prod_price
from Products 
where prod_price between 3 and 6
order by prod_price

SQL11 返回更多的产品

select distinct order_num
from OrderItems
where quantity>=100

SQL12 检索供应商名称

select vend_name
from Vendors 
where vend_country='USA' and vend_state='CA '

SQL13 检索并列出已订购产品的清单

select order_num,prod_id,quantity
from OrderItems 
where quantity>=100 and prod_id in ("BR01","BR02","BR03")

SQL14 返回所有价格在 3美元到 6美元之间的产品的名称和价格

select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price

SQL15 纠错2

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name 

SQL16 检索产品名称和描述（一）

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

SQL17 检索产品名称和描述（二）

select prod_name,prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_desc desc

SQL18 检索产品名称和描述（三）

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

SQL19 检索产品名称和描述（四）

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

SQL20 别名

select vend_id
	,vend_name vname
	,vend_address vaddress
	,vend_city  vcity
from Vendors
order by vname

SQL21 打折

select 
    prod_id
    ,prod_price
    ,prod_price*0.9 as sale_price
from Products 

SQL22 顾客登录名

select 
    cust_id
    ,cust_name
    ,upper(concat(left(cust_name,2),left(cust_city,3))) user_login
from Customers

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

select order_num,order_date
from Orders
where date_format(order_date,'%Y%m')='202001'
order by order_date 

SQL24 确定已售出产品的总数

select sum(quantity) as items_ordered
from OrderItems

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01' 

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

select max(prod_price) as max_price
from Products 
where prod_price<=10

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

select order_num,count(*) order_lines 
from OrderItems 
group by order_num
order by order_lines asc

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

select vend_id,min(prod_price) as cheapest_item 
from Products
group by vend_id
order by cheapest_item asc 

SQL29 确定最佳顾客

select order_num
from OrderItems
where quantity>=100
order by order_num asc

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

select order_num,sum(item_price*quantity) as total_price
from OrderItems
group by order_num
having total_price>=1000

SQL31 纠错3

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

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

select cust_id
from Orders
where order_num in (

select order_num
from OrderItems
where item_price>=10)

SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

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

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

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' 
    )
)

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

select cust_id,sum(item_price*quantity) as total_ordered
from OrderItems
join Orders using (order_num)
group by cust_id
order by total_ordered desc

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

select prod_name,sum(quantity) as quant_sold 
from Products 
left join OrderItems using(prod_id)
group by prod_name

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

select cust_name,order_num
from Customers 
join Orders using(cust_id)
order by cust_name,order_num

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

select cust_name,order_num,sum(quantity*item_price) as OrderTotal
from Customers 
join Orders using(cust_id)
join OrderItems using(order_num)
group by cust_name,order_num  
order by cust_name,order_num 

SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

select cust_id,order_date 
from OrderItems
join Orders using(order_num)
where prod_id ='BR01'
order by order_date

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

select cust_email
from OrderItems
join Orders using(order_num)
join Customers using(cust_id)
where prod_id ='BR01'

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

select cust_name,sum(item_price*quantity) as total_price
from OrderItems
join Orders using(order_num)
join Customers using(cust_id)
group by cust_name 
having total_price>=1000
order by cust_name

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

select cust_name,order_num 
from Customers
join Orders using(cust_id)
order by cust_name

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

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

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

select prod_name,order_num 
from Products
left join OrderItems using(prod_id)
order by prod_name

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

-- count(*) count(1) 计算null值
select prod_name,count(order_num) as orders
from Products
left join OrderItems using(prod_id)
group by prod_name
order by prod_name 

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

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  

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

select prod_id,quantity 
from OrderItems
where quantity=100
union all
select prod_id,quantity 
from OrderItems
where prod_id like 'BNBG%'

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

select prod_id,quantity 
from OrderItems
where quantity=100 or prod_id like 'BNBG%'

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

select prod_name as prod_name
from Products
union
select cust_name as prod_name
from Customers
order by prod_name

SQL50 纠错4

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; 

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