lab3 solution.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.   select c.customername
     from mcd_customerlist c
     where not exists
       (select foodno 
        from
	(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 '%周佶平%'))
	)customer_food 
        where 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 '%周佶平%'
        and c.customerno in 
          (select o1.customerno
           from mcd_order o1);

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.   select c.customername,customer_city.city_count
     from mcd_customerlist c,
     (select o.customerno,count(distinct cs.locatedcity) as city_count
      from mcd_order o,mcd_chainstorelist cs
      where cs.storeno=o.storeno
      group by o.customerno
      having count(distinct cs.locatedcity)=1) customer_city
     where c.customerno=customer_city.customerno;

6.   select c.customername,season_consume.sum_price
     from mcd_customerlist c,
     (select customerno,sum(price*consume.foodnumber) as sum_price
       from 
       (select o.customerno,f.foodname,f.price,sum(oi.orderednumber) as foodnumber
        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
       ) consume
       group by customerno
     ) season_consume
     where c.customerno=season_consume.customerno
     and season_consume.sum_price >=all
       (select season_consume2.sum_price
        from 
	(select customerno,sum(price*consume2.foodnumber) as sum_price
       from 
       (select o.customerno,f.foodname,f.price,sum(oi.orderednumber) as foodnumber
        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
       ) consume2
       group by customerno
       ) season_consume2);



       
7.   select distinct sales1.storename,sales1.city,sales1.qua
     from 
     (select '可乐',cs.storename,cs.locatedcity as city,sum(oi.orderednumber) as qua
       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) sales1,
     (select '美年达',cs.storename,cs.locatedcity as city,sum(oi.orderednumber) as qua
       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) sales2
     where sales1.qua=sales2.qua
       and sales1.storename!=sales2.storename
       and sales1.city!=sales2.city;
     


8.   select c.customername,avg(sum_spend)
     from mcd_customerlist c,
     (select customerno,orderno,sum(price*orderednum) as sum_spend
       from
       (select o.customerno,o.orderno,f.foodname,f.price,sum(oi.orderednumber) as orderednum
       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) each_spend
       group by customerno,orderno) spend
     where c.customerno=spend.customerno
     group by customername
     having avg(sum_spend)>500;
     

9.   select * 
     from
     (select rownum as rank,p.*
       from 
       (select sales_condition.storeno,cs.storename,sum(sales_condition.price*sales_condition.orderednum) as sumsum
        from mcd_chainstorelist cs,
	(select cs.storeno,f.foodname,f.price,sum(oi.orderednumber) as orderednum
         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) sales_condition
        where cs.storeno=sales_condition.storeno
        group by sales_condition.storeno,cs.storename
        order by sumsum desc) p)
     where rank<=5;

     
     

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