
rem query 1

select unique firstname, lastname 
from customers c, reciepts r
where r.customer = c.cid and
      r.saledate = '08-OCT-07';


rem query 2

select unique r.saledate
from customers c, reciepts r
where r.customer = c.cid and 
      c.firstname='DAVID' and c.lastname='CALLENDAR'
order by saledate;

rem query 3

select r.saledate, r.rnumber, sum(g.price) as Total
from customers c, reciepts r, items i, goods g
where r.customer = c.cid and r.rnumber = i.reciept and
      i.item = g.gid and 
      c.firstname='DAVID' and c.lastname='CALLENDAR' 
group by r.saledate, r.rnumber       
order by saledate;


rem query 4

select MAX(Total) AS Max_Total
from ( select SUM(g.Price) AS Total
       from customers c, reciepts r, items i, goods g 
       where r.customer = c.cid and r.rnumber = i.reciept and
             i.item = g.gid
       group by saledate) t;


rem query 5

select *
from ( select saledate, count(DISTINCT rnumber) AS NumPurchases, 
              SUM(g.Price) AS Total
       from customers c, reciepts r, items i, goods g 
       where r.customer = c.cid and r.rnumber = i.reciept and
             i.item = g.gid
       group by saledate) t
where Total = (select MAX(Total) AS Max_Total
               from (select SUM(g.Price) AS Total
                    from customers c, reciepts r, items i, goods  g 
                    where r.customer = c.cid and 
	                  r.rnumber = i.reciept and
	                  i.item = g.gid
                    group by saledate) t) 
;

rem query 6

select saledate
from reciepts r, items i, goods g
where r.rnumber = i.reciept and i.item = g.gid and
      g.flavor = 'Chocolate' and food= 'Croissant'
order by saledate; 


rem query 7

(select saledate from reciepts)
minus
(select unique saledate
from reciepts r, items i, goods g
where r.rnumber = i.reciept and i.item = g.gid and
      g.flavor = 'Apricot' and food= 'Tart')
order by saledate; 

rem query 8
 
select * from
  (select food, flavor, COUNT(*) as CNT
  from reciepts r, items i, goods g
  where r.rnumber = i.reciept and i.item = g.gid
  group by food, flavor) t
  where CNT = (select MAX(CNT)
               from
               (select food, flavor, COUNT(*) as CNT
                from reciepts r, items i, goods g
                where r.rnumber = i.reciept and i.item = g.gid
                group by food, flavor)
             );

rem query 9

select firstname, lastname, count(*) as NumPurchases
from customers c, reciepts r
where c.cid = r.customer
group by c.cid, c.firstname, c.lastname
having NumPurchases > 15
order by NumPurchases;


rem query 10

select gg.flavor, gg.food, gg.price
from  reciepts rr, items ii, goods gg
where rr.rnumber = ii.reciept and ii.item = gg.gid
and  rr.rnumber =  (select rnumber
                    from reciepts r, items i, goods g
                    where r.rnumber = i.reciept and i.item = g.gid
                    group by r.rnumber
                    having sum(g.price) = (select MAX (amount)
                                          from (select SUM(price) as amount
                                                from reciepts r1, items i1, goods g1
                                                where r1.rnumber = i1.reciept and i1.item = g1.gid
                                                group by rnumber
                                                ) t
                                           )
                   )
;  


