---------------------------------------------------------------
-- Mail Order Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
--            by R. Sunderraman
---------------------------------------------------------------
drop table zipcodes cascade constraints;
create table zipcodes (
  zip      number(5),
  city     varchar2(30),
  primary key (zip));

drop table employees cascade constraints;
create table employees (
  eno      number(4) not null primary key, 
  ename    varchar2(30),
  zip      number(5) references zipcodes,
  hdate    date);

drop table parts cascade constraints;
create table parts(
  pno      number(5) not null primary key,
  pname    varchar2(30),
  qoh      integer check(qoh >= 0),
  price    number(6,2) check(price >= 0.0),
  olevel   integer);

drop table customers cascade constraints;
create table customers (
  cno      number(5) not null primary key,
  cname    varchar2(30),
  street   varchar2(30),
  zip      number(5) references zipcodes,
  phone    char(12));
 
drop table orders cascade constraints;
create table orders (
  ono      number(5) not null primary key,
  cno      number(5) references customers,
  eno      number(4) references employees,
  received date,
  shipped  date);

drop table odetails cascade constraints;
create table odetails (
  ono      number(5) not null references orders,
  pno      number(5) not null references parts,
  qty      integer check(qty > 0),
  primary key (ono,pno));

select pno, pname
from   parts;

select pno, pname
from   parts
where  price < 20.00;

select distinct pno
from   odetails;

select ono, cno
from   orders, customers
where  orders.cno = customers.cno;

select c1.cno, c2.cno
from   customers c1, customers c2
where  c1.zip = c2.zip and c1.cno < c2.cno;

select distinct cname
from   orders, customers
where  orders.cno=customers.cno and
       eno in (select eno
               from   employees, zipcodes
               where  employees.zip = zipcodes.zip and
                      city = 'Fort Dodge');

select distinct cname
from   customers
where  cno in (select cno
               from   orders
               where  ono in (select ono
                              from   odetails
                              where  pno in (select pno
                                             from   parts
                                             where  color = 'Red')));

select cno
from   orders
where  exists (select ono
               from   odetails
               where  odetails.ono = orders.ono and
                      odetails.pno = 10506) and
       exists (select ono
               from   odetails
               where  odetails.ono = orders.ono and
                      odetails.pno = 10507) and

select c.cname
from   customers c
where  not exists (select e.eno
                   from   employees e, zipcodes z
                   where  z.city = 'Wichita' and
                          e.zip = z.zip and
                          not exists (select x.ono
                                      from   orders x       
                                      where  x.cno = c.cno and
                                             x.eno = e.eno));


