lab3.txt

database xiangguan... shujuku

1.   select distinct fl.foodname 
     from mcd_customerlist c,mcd_orderitems oi,mcd_order o,mcd_foodlist fl
     where fl.foodno=oi.foodno
       and oi.orderno=o.orderno
       and o.customerno=c.customerno
       and c.customername like '%A%';
 
or:  select fl.foodname 
     from mcd_foodlist fl
     where fl.foodno in 
       (select oi.foodno
        from mcd_orderitems oi
        where oi.orderno in
          (select o.orderno 
           from mcd_order o
           where o.customerno in
           (select c.customerno 
            from mcd_customerlist c
            where c.customername like '%A%')));


2.   create view customer_food(foodno) as
       select oi.foodno 
          from mcd_orderitems oi
          where oi.orderno in
            (select o.orderno 
             from mcd_order o
             where o.customerno in
              (select c.customerno 
               from mcd_customerlist c
               where c.customername like '%A%'));
     select c.customername
     from mcd_customerlist c
     where not exists
       (select cf.foodno 
        from customer_food cf
        where cf.foodno not in
            (select distinct oi2.foodno
             from mcd_orderitems oi2,mcd_order o
             where oi2.orderno=o.orderno
               and o.customerno=c.customerno))
        and c.customername not like '%A%'
        and c.customerno in 
          (select o1.customerno
           from mcd_order o1);
     drop view customer_food;

3.   select c.customername 
     from mcd_customerlist c
     where not exists
       (select c.customerno
        from mcd_chainstorelist cs,mcd_order o
        where o.storeno=cs.storeno
          and c.customerno=o.customerno
          and cs.locatedcity not like '%BEIJING%')
     and c.customerno in 
       (select o1.customerno
        from mcd_order o1);

4.   select c.customername
     from mcd_customerlist c
     where c.customerno not in
       (select o.customerno
        from mcd_order o,mcd_chainstorelist cs
        where o.storeno=cs.storeno
          and cs.locatedcity like '%WUXI%');

5.   create view customer_city(customerno,city_count) as
     select o.customerno,count(distinct cs.locatedcity)
      from mcd_order o,mcd_chainstorelist cs
      where cs.storeno=o.storeno
      group by o.customerno
      having count(distinct cs.locatedcity)=1;
     select c.customername,city_count
     from mcd_customerlist c,customer_city
     where c.customerno=customer_city.customerno;
     drop view customer_city;

6.   create view consume(customerno,foodname,price,foodnumber) as
       select o.customerno,f.foodname,f.price,sum(oi.orderednumber)
       from mcd_order o,mcd_orderitems oi,mcd_foodlist f
       where o.orderno=oi.orderno
         and oi.foodno=f.foodno
	 and extract(year from o.orderdate)=2003
	 and extract(month from o.orderdate) between 1 and 3
       group by o.customerno,f.foodname,f.price;
     create view season_consume(customerno,sum_price) as
       select customerno,sum(price*foodnumber)
       from consume
       group by customerno;
     select c.customername,sc.sum_price
     from season_consume sc,mcd_customerlist c
     where c.customerno=sc.customerno
     and sc.sum_price >=all
       (select sc2.sum_price
        from season_consume sc2);
     drop view consume;
     drop view season_consume;
       
7.   create view sales1(name,storename,city,qua) as
       select '可乐',cs.storename,cs.locatedcity,sum(oi.orderednumber)
       from mcd_chainstorelist cs,mcd_orderitems oi,mcd_order o,mcd_foodlist f
       where oi.orderno=o.orderno
         and o.storeno=cs.storeno
         and oi.foodno=f.foodno
         and f.foodname like '%可乐%'
       group by cs.locatedcity,cs.storename;
     create view sales2(name,storename,city,qua) as
       select '美年达',cs.storename,cs.locatedcity,sum(oi.orderednumber)
       from mcd_chainstorelist cs,mcd_orderitems oi,mcd_order o,mcd_foodlist f
       where oi.orderno=o.orderno
         and o.storeno=cs.storeno
         and oi.foodno=f.foodno
         and f.foodname like '%美年达%'
       group by cs.locatedcity,cs.storename;
     select distinct s1.storename,s1.city,s1.qua
     from sales1 s1,sales2 s2
     where s1.qua=s2.qua
       and s1.storename!=s2.storename
       and s1.city!=s2.city;
     drop view sales1;
     drop view sales2;

8.   create view each_spend(customerno,orderno,foodname,price,orderednumber) as
       select o.customerno,o.orderno,f.foodname,f.price,sum(oi.orderednumber)
       from mcd_order o,mcd_orderitems oi,mcd_foodlist f
       where o.orderno=oi.orderno
         and oi.foodno=f.foodno
       group by o.customerno,o.orderno,f.foodname,f.price;
     create view spend(customerno,orderno,sum_spend) as
       select es.customerno,es.orderno,sum(es.price*es.orderednumber)
       from each_spend es
       group by es.customerno,es.orderno;
     select c.customername,avg(sum_spend)
     from mcd_customerlist c,spend s
     where c.customerno=s.customerno
     group by customername
     having avg(sum_spend)>500;
     drop view each_spend;
     drop view spend;

9.   create view sales_condition(storeno,foodname,price,orderednumber) as
       select cs.storeno,f.foodname,f.price,sum(oi.orderednumber)
       from mcd_chainstorelist cs,mcd_order o,mcd_foodlist f,mcd_orderitems oi
       where cs.locatedcity like '%BEIJING%'
         and o.storeno=cs.storeno
         and o.orderno=oi.orderno
         and oi.foodno=f.foodno
       group by cs.storeno,f.foodname,f.price;
     create view profits(rank,storeno,storename,profit) as
       select rownum,p.*
       from 
       (select sc.storeno,cs.storename,sum(sc.price*orderednumber)
        from sales_condition sc,mcd_chainstorelist cs
        where cs.storeno=sc.storeno
        group by sc.storeno,cs.storename
        order by sum(sc.price*orderednumber) desc) p;
     select * 
     from profits 
     where rank<=5;
     drop view sales_condition;
     drop view profits;
     

10.  create view store_order(storeno,city,ordernumber) as
       select o.storeno,cs.locatedcity,count(o.orderno)
       from mcd_order o,mcd_chainstorelist cs
       where o.storeno=cs.storeno
       group by o.storeno,cs.locatedcity
       order by count(o.orderno) desc;
     select cs.storename,so.city
     from mcd_chainstorelist cs,store_order so
     where cs.storeno=so.storeno
       and so.ordernumber >=all 
       (select ordernumber
        from store_order);
     drop view store_order;










     ----------------------------drop---------------------------
     drop view customer_food;
     drop view customer_city;
     drop view consume;
     drop view season_consume;
     drop view each_spend;
     drop view spend;
     drop view sales_condition;
     drop view profits;
     drop view store_order;