SQL必知必会 （牛客）

SQL必知必会 （牛客）

01检索数据

--SQL60 从 Customers 表中检索所有的 ID
select cust_id from Customers ;

--SQL61 检索并列出已订购产品的清单
--检索并列出所有已订购商品（prod_id）的去重后的清单
select distinct prod_id from OrderItems;

--SQL62 检索所有列
select * from Customers;

02排序检索数据

--SQL63 检索顾客名称并且排序

--从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果
select cust_name from Customers order by cust_name DESC;

--SQL64 对顾客ID和日期排序
--从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。
select 
    cust_id,order_num 
    from 
Orders order by cust_id, order_date desc;

--SQL65 按照数量和价格排序
--显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。
select 
    quantity,item_price 
    from 
OrderItems
    order by quantity desc,item_price desc;
        
--SQL66 检查SQL语句
--下面的 SQL;语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name逆序排列
SELECT vend_name
    FROM Vendors 
    ORDER by vend_name DESC;

03过滤数据

--SQL67 返回固定价格的产品
--从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。
select prod_id,prod_name from Products where prod_price=9.49;

--SQL68 返回更高价格的产品
--从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。
select prod_id,prod_name from Products where prod_price >= 9;

--SQL69 返回产品并且按照价格排序
select 
    prod_name,prod_price 
    from 
Products 
    where 
    prod_price >=3 and prod_price <=6
    order by 
    prod_price;
        

--SQL70 返回更多的产品
--从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品。
select order_num from OrderItems
    group by order_num
    having sum(quantity)>=100;

04高级数据过滤

--SQL71 检索供应商名称
--从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA）
select vend_name 
    from Vendors 
    where vend_state = 'CA' 
    AND vend_country = 'USA';
    
--SQL72 检索并列出已订购产品的清单
--查找所有订购了数量至少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;
        
--SQL73 返回所有价格在 3美元到 6美元之间的产品的名称和价格
--返回所有价格在 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;
        

--SQL74 纠错2
SELECT vend_name 
    FROM Vendors 
    WHERE vend_country = 'USA' AND vend_state = 'CA'
    ORDER BY vend_name ;

05用通配符进行过滤

--SQL75 检索产品名称和描述（一）
--从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称
select prod_name,prod_desc 
    from Products
    where prod_desc like '%toy%';
        

--SQL76 检索产品名称和描述（二）
--从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。
select prod_name,prod_desc 
    from Products
    where prod_desc not like '%toy%'
    order by prod_name;
        
--SQL77 检索产品名称和描述（三）
--从 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%';
        

--SQL78 检索产品名称和描述（四）
--从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。
select prod_name,prod_desc 
    from Products
    where prod_desc like '%toy%' and prod_desc like '%carrots%' 
    order by prod_desc desc;

06创建计算字段

--SQL79 别名
--从 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;
        
                
--SQL80 打折
--我们的示例商店正在进行打折促销，所有产品均降价 10%。Products表包含prod_id产品id、prod_price产品价格
--【问题】编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。
--提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）
--【示例结果】返回产品id prod_id、产品价格prod_price、销售价格 sale_price

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

07使用函数处理数据

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

--1、字符串的截取：substring(字符串，起始位置，截取字符数）
--2 、 字符串的拼接 ： concat(字符串1 ， 字符串2 ， 字符串3, …)
--3 、 字母大写 ： upper(字符串）
select
  cust_id,
  cust_name,
  concat(
    upper(left(cust_name, 2)),
    upper(left(cust_city, 3))
  ) user_login
from
  Customers;
    
    

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

select
  order_num,
  order_date
from
  Orders
  where month(order_date) = 1 
  and year(order_date) = 2020
  order by order_date;

08汇总数据

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

select SUM(quantity)items_ordered from OrderItems;


--SQL84 确定已售出产品项 BR01 的总数
--OrderItems表代表售出的产品，quantity代表售出商品数量，产品项为prod_id。
--【问题】修改创建的语句，确定已售出产品项（prod_id）为"BR01"的总数。
--【示例结果】返回商品项已订购订单数

select SUM(quantity)items_ordered 
    from OrderItems
        where prod_id = 'BR01';



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

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

09分组数据

--SQL86 返回每个订单号各有多少行数
--OrderItems 表包含每个订单的每个产品
--编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines对结果进行升序排序。
--返回订单号order_num和对应订单号的行数order_lines

select order_num,COUNT(order_num) AS order_lines 
    from OrderItems 
    GROUP BY order_num
        order by order_lines;
        
        
        
--SQL87 每个供应商成本最低的产品
--有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;
                

--SQL88 返回订单数量总和不小于100的所有订单的订单号
--OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。
--请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。
--返回order_num订单号。

select order_num
    from OrderItems 
    WHERE quantity >= 100
        order by order_num;
                
                
--SQL89 计算总和
--OrderItems表代表订单信息，包括字段：订单号order_num和item_price商品售出价格、quantity商品数量。
--编写 SQL 语句，根据订单号聚合，返回订单总价不小于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;




--SQL90 纠错3
--OrderItems表含有order_num订单号
--返回订单号order_num和出现的次数items

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