select
prod_id
,prod_price
,prod_price*0.9as 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'orderby order_date
SQL24 确定已售出产品的总数
selectsum(quantity)as items_ordered
from OrderItems
SQL25 确定已售出产品项 BR01 的总数
selectsum(quantity)as items_ordered
from OrderItems
where prod_id='BR01'
SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格
selectmax(prod_price)as max_price
from Products
where prod_price<=10
SQL27 返回每个订单号各有多少行数
select order_num,count(*) order_lines
from OrderItems
groupby order_num
orderby order_lines asc
SQL28 每个供应商成本最低的产品
select vend_id,min(prod_price)as cheapest_item
from Products
groupby vend_id
orderby cheapest_item asc
SQL29 确定最佳顾客
select order_num
from OrderItems
where quantity>=100orderby order_num asc
SQL30 确定最佳顾客的另一种方式(一)
select order_num,sum(item_price*quantity)as total_price
from OrderItems
groupby order_num
having total_price>=1000
SQL31 纠错3
SELECT order_num,COUNT(*)AS items
FROM OrderItems
GROUPBY order_num
HAVINGCOUNT(*)>=3ORDERBY 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'))