SQL2009.4.14

//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