1.OrderItems表包含每个订单的每个产品。编写SQL语句,返回每个订单号(order_num)各有多少行数(order_lines),并按order_lines对结果进行排序。
--1
select order_num,count(order_num) as order_lines
from orders
group by order_num
order by order_lines
2.编写SQL语句,返回名为cheapest_item的字段,该字段包含每个供应商成本最低的产品(使用Products表中的prod_price),然后从最低成本到最高成本对结果进行排序。
--2
select vend_id,min(prod_price) as cheapest_item
from products
group by vend_id
order by cheapest_item
3.确定最佳顾客非常重要,请编写SQL语句,返回至少含100项的所有订单的订单号(OrderItems表中的order_num)。
--3
select order_num ,sum(quantity)
from orderitems
where quantity>=100
group by order_num
这条语句中的sum(quantity)加不加无所谓,只是让结果容易理解
4.确定最佳顾客的另一种方式是看他们花了多少钱。编写SQL语句,返回总价至少为1000的所有订单的订单号(OrderItems表中的order_num)。提示:需要计算总和(item_price乘以quantity)。按订单号对结果进行排序。
--4
select order_num ,sum(item_price*quantity) as total
from OrderItems
group by order_num
having sum(item_price*quantity)>1000
(1)处于select子句中的sum(item_price*quantity) 并不是必须的
(2)在having子句中不能用别名total替代sum(item_price*quantity),否则会出错
5.下面的SQL语句有问题吗?
GROUP BY后边跟的应该是order_num等一类实际的列或表达式的列,否则会出错
最后需要特别注意几个关键词的顺序
select,from,where,group by,having,order by