-- set echo on
--
-- 2.3 (a) Get the names of parts that cost less than 20.00.
-- OK
select pname
from   parts
where  price < 20.00;
--
-- 2.3 (b) Get the names and cities of employees who have taken
--         orders for parts costing more than 20.00.
-- OK
select distinct e.ename,z.city
from   zipcodes z, employees e, orders o, odetails od, parts p
where  z.zip = e.zip and
       e.eno = o.eno and
       o.ono = od.ono and
       od.pno = p.pno and
       p.price > 20.00;
--
-- 2.3 (c) Get the pairs of customer number values of customers who
--         have the same zip code
-- OK
select c1.cno, c2.cno
from   customers c1, customers c2
where  c1.zip = c2.zip and
       c1.cno < c2.cno;
--
-- 2.3 (d) Get the names of customers who have ordered parts from
--         employees living in Wichita.
-- OK
select distinct c.cname
from   customers c, orders o, employees e, zipcodes z
where  c.cno = o.cno and
       o.eno = e.eno and
       e.zip = z.zip and
       z.city = 'Wichita';
--
-- 2.3 (e) Get the names of customers who have ordered parts ONLY
--         from employees living in Wichita.
-- OK
select distinct cname
from   customers, orders
where  customers.cno = orders.cno and
       customers.cno not in
          (select o.cno
           from   orders o, employees e, zipcodes z
           where  o.eno=e.eno and
                  e.zip = z.zip and
                  z.city <> 'Wichita');
--
-- 2.3 (f) Get the names of customers who have ordered ALL parts
--         costing less than 20.00.
-- OK
select c.cname
from   customers c
where  not exists (
         select pno
         from   parts p
         where  p.price < 20.00 and
                not exists (
                   select *
                   from   orders o, odetails od
                   where  o.ono = od.ono and
                          o.cno = c.cno and
                          od.pno = p.pno));
--
-- 2.3 (g) Get the names of employees along with their total sales
--         for the year 2095 (consider only orders shipped in 2095).
-- OK
select distinct e.ename, sum(p.price*od.qty) TOTAL_SALES
from   employees e, orders o, odetails od, parts p
where  e.eno = o.eno and
       o.ono = od.ono and
       od.pno = p.pno and
       to_char(shipped,'YYYY') = '2095'
group by e.eno, e.ename;
--
-- 2.3 (h) Get the numbers and names of employees who have never
--         made a sale to a customer living in the same zipcode 
--         as the employee
-- OK
select eno,ename
from   employees
where  eno not in (
         select e.eno
         from   employees e, orders o, customers c
         where  e.zip = c.zip and
                e.eno = o.eno and
                c.cno = o.cno);
--
-- 2.3 (i) Get the names of customers who have placed the highest
--         number of orders
select cname
from   customers c, orders o
where  c.cno = o.cno
group by c.cno,cname
having count(o.ono) >=all (select count(ono)
                           from   orders
                           group by cno);
--
-- 2.3 (j) Get the names of customers who have placed the most
--         expensive orders.
-- OK
select c.cname
from   customers c, orders o, odetails od, parts p
where  c.cno = o.cno and
       o.ono = od.ono and
       od.pno = p.pno
group by o.ono, c.cno, c.cname
having sum(p.price*od.qty) >=all (
             select sum(p1.price*od1.qty)
             from   odetails od1, parts p1
             where  od1.pno = p1.pno
             group by od1.ono);
--
-- 2.3 (k) Get the names of parts that have been ordered
--         the most (in terms of quantity ordered and not 
--         number of orders).
-- OK
select p.pname
from   parts p, odetails od
where  p.pno = od.pno
group by p.pno, p.pname
having sum(od.qty) >=all (select sum(od1.qty)
                          from   parts p1, odetails od1
                          where  p1.pno = od1.pno
                          group by p1.pno);
--
-- 2.3 (l) Get the names of parts along with the number of orders
--         they appear in, sorted in decreasing order of the
--         number of orders
-- OK
select p.pname, count(o.ono) total
from   orders o, odetails od, parts p
where  o.ono = od.ono and
       od.pno = p.pno
group by od.pno, p.pname
order by total desc;
--
-- 2.3 (m) Get the average waiting time for all orders in number of
--         days. The waiting time for an order is defined as the 
--         difference between the shipped date and the received date. 
--         Note: the dates should be truncated to 12:00 AM so that the 
--         difference is always a whole number of days.
--         DO NOT consider any orders that are not yet shipped.
--         It would be interesting to consider to consider such orders though!
-- OK
select avg(trunc(shipped)-trunc(received)) WAITING_TIME
from   orders
where  shipped is not null;
--
-- 2.3 (n) Get the names of customers who had to wait the longest for
--         their orders to be shipped.
--         DO NOT consider any orders that are not yet shipped.
--         It would be interesting to consider to consider such orders though!
-- OK
select cname
from   customers c, orders o
where  c.cno = o.cno and
       shipped is not null and 
       trunc(shipped) - trunc(received) >=all
           (select trunc(shipped)-trunc(received)
            from   orders
            where  shipped is not null);
--
-- 2.3 (o) For all orders more than $100.00, get the order number and
--         the waiting time for the order.
--         DO NOT consider any orders that are not yet shipped.
--         It would be interesting to consider to consider such orders though!
-- OK
select ono, trunc(shipped)-trunc(received) WAITING_TIME
from   orders
where  shipped is not null and
       ono in (select o.ono
               from   orders o, odetails od, parts p
               where  o.ono = od.ono and
                      od.pno = p.pno
               group by o.ono
               having sum(od.qty*p.price) > 100.00);
--
-- set echo off