MySQL - OJ 练习（23道）

SQL60 从 Customers 表中检索所有的 ID

select 
    cust_id 
from 
    Customers;

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

select 
    distinct prod_id 
from 
    OrderItems;

SQL62 检索所有列

select 
    cust_id, 
    cust_name
from 
    Customers;

SQL63 检索顾客名称并且排序

select 
    cust_name 
from 
    Customers 
order by 
    cust_name desc;

SQL64 对顾客ID和日期排序 

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

SQL65 按照数量和价格排序

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

SQL66 检查SQL语句

select 
    vend_name
from 
    Vendors
order by 
    vend_name desc;

SQL67 返回固定价格的产品

select
    prod_id, prod_name
from 
    Products
where 
    prod_price = 9.49;

SQL68 返回更高价格的产品

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

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

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

SQL70 返回更多的产品

select
    distinct order_num
from
    OrderItems
where 
    quantity >= 100;

SQL71 检索供应商名称

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

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

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

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

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

SQL74 纠错2

select 
    vend_name
from 
    Vendors
WHERE 
    vend_country = 'USA' 
AND 
    vend_state = 'CA'
ORDER BY 
    vend_name ;

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

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

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

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

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

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

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

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

SQL79 别名

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

SQL80 打折

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

SQL81 顾客登录名

-- 其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成
-- 取字符串的前 n 个字符
-- substr(字段, 1, n);
-- 大写
-- upper(...)
-- 连接字符串
-- concat(...)
select
    cust_id,
    cust_name,
    upper(
        concat (
            substr (cust_contact, 1, 2),
            substr (cust_city, 1, 3)
        )
    ) user_login
from
    Customers;

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

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

-- 第二种方法

select 
    order_num,
    order_date
from   
    Orders
where 
    order_date between '2020-01-01 00:00:00' and '2020-01-31 23:59:59'
order by 
    order_date;