//author 满晨晨
//time 2009 4 14上午
avg（）返回某列的平均值
count（）返回满足条件的记录个数
max（)某列的最大值
min（）某列的最小值
sun（）返回某一列的所有数值的和
--select sum(quantity*item_price) as total from orderitems where order_num='20008'
--select sum(quantity*item_price) as total from orderitems
select sum(quantity*item_price) as total from orderitems oi,orders o where o.order_num=oi.order_num
and order_date between to_date('2004/1/1','yyyy/mm/dd') and to_date('2004/12/31','yyyy/mm/dd') 计算2004年的总收入
order in（select order from ）嵌套查询
substr(to_char(order_date,'yyyy/mm/dd'),1,4)='2004' 以第1个开头到第4个为2004年
distinct 强制性要求返回值里不得有重复的数据
统计客户都来自于哪些国家
select distinct vend_country from vendors 有重复的国家
select count(vend_country) from vendors 统计国家个数
select count(distinct vend_country) from vendors 除去重复的统计国家个数
------------------------------------------------------------------------------------------------------------------------
--select sum(quantity*item_price) as total from orderitems where order_num='20008'
--select sum(quantity*item_price) as total from orderitems
--select sum(quantity*item_price) as total from orderitems oi,orders o where o.order_num=oi.order_num
--and order_date between to_date('2004/1/1','yyyy/mm/dd') and to_date('2004/12/31','yyyy/mm/dd')
--select * from orders where order_date between to_date('2004/1/1','yyyy/mm/dd') and to_date('2004/12/31','yyyy/mm/dd')
select sum(quantity) as quantity from orderitems oi,orders o where o.order_num=oi.order_num
and substr(to_char(order_date,'yyyy/mm/dd'),1,7)='2004/02'
select sum(quantity) as quantity from orderitems oi,orders o where o.order_num=oi.order_num
and substr(to_char(order_date,'yyyy/mm/dd'),1,10)='2004/02/03'
select sum(quantity*item_price) as total from orderitems oi,orders o where o.order_num=oi.order_num
and substr(to_char(order_date,'yyyy/mm/dd'),1,7)='2004/02'
select sum(quantity * item_price) as total
from orderitems oi, orders o
where o.order_num = oi.order_num and
substr(to_char(order_date, 'yyyy/mm/dd'), 1, 10) = '2004/02/03'
select sum(quantity) as quantity
from orderitems oi, orders o
where o.order_num = oi.order_num and
substr(to_char(order_date, 'yyyy/mm/dd'), 1, 4) = '2004'
select avg(prod_price) avgp, max(prod_price) maxp, min(prod_price) minp
from products
where vend_id = 'BRS01'
select count(cust_id) from customers where cust_email is not null
select distinct vend_country from vendors
select count( distinct vend_country) from vendors
select cust_id from orders group by cust_id having count(order_num) >= 2
--统计订单中货物多于3件的订单 ，并按照订单中货物有多到少排序
select order_num ,sum(quantity)from orderitems
group by order_num
having sum(quantity)>3
order by sum(quantity) asc
--统计订单中货物种类少于3种 ，并按照订单中货物种类有多到少，订单编号降序排序
select order_num ,count(prod_id)from orderitems
group by order_num
having count(pro_id)<3
order by count(prod_id) desc ,order_num desc
--查询出订购了prod_id为RGAN01产品的cust_id
select cust_id
from orders
where order_num IN
(select order_num from orderitems where prod_id = 'RGAN01')
--要求查询出更详细的顾客信息，例如获得cust_id,cust_contact
select cust_id, cust_contact
from customers
where cust_id in
(select cust_id
from orders
where order_num IN
(select order_num from orderitems where prod_id = 'RGAN01')
)
------------------------------------------------------------------------------------------------------------------------
where
group by
having
order by