Charles Blake Knight
Relations
CUSTOMER(Cust#, Company_Name, Contact_Fname, Contact_Lname, Contact_Email, Phone#, Address, City, Zip, State)
SUPPLIER(Supplier#, Company_Name, Contact_Fname, Contact_Lname, Contact_Email, Phone#, Address, City, Zip, State)
BRANCH(Branch#, Branch_Name, Branch_Address, Branch_City, Branch_State, Branch_Zip)
SHIPPING(Shipping#, Shipping_Company, Branch_Shipping, Dest_Address, Ship_Date, Arrival_Date, Is_Signature_Required)
PRODUCT(Product#, Prod_Name, Package_Type, Unit_Price, Current_Cost, Projected_Cost, Qty_Available, Reorder_Qty, Supplier#, Branch_Located)
ORDERS(Order#, Cust#, Ordered_by_Name, Date_Ordered, Special_Requests, Date_Needed, Est_Shipment_Date, Quote_Verified, Is_Completed, Has_Multi_Shipments)
ORDER_DETAIL(Order#, Product#, Qty_Ordered, Is_Backordered, Shipping#)
RECEIVABLES_INVOICES(Invoice#,Product#, Order#, Invoice_Date, Payment_Terms, Freight_Terms, Product_Amount, Tax_Amount, Freight_Amount, Date_Paid)
======
SQL to create database
-- Drop tables if they exist...
drop table RECEIVABLES_INVOICES;
drop table ORDER_DETAIL;
drop table ORDERS;
drop table PRODUCT;
drop table SHIPPING;
drop table BRANCH;
drop table SUPPLIER;
drop table CUSTOMER;
-- Create tables
create table CUSTOMER
(cust# char(10) not null,
company_name varchar(30) not null,
contact_fname varchar(20),
contact_lname varchar(20),
contact_email varchar(60),
phone# varchar(14), --enough for 10 digits + an extension or possibly an international number
address varchar(50) not null,
city varchar(30) not null,
zip char(5) not null,
state char(2) not null,
primary key (cust#));
create table SUPPLIER
(supplier# char(10) not null,
company_name varchar(30) not null,
contact_fname varchar(20),
contact_lname varchar(20),
contact_email varchar(60),
phone# varchar(14), --enough for 10 digits + an extension or possibly an international number
address varchar(50) not null,
city varchar(30) not null,
zip char(5) not null,
state char(2) not null,
primary key (supplier#));
create table BRANCH
(branch# char(5) not null,
branch_name varchar(20) not null,
branch_address varchar(50) not null,
branch_city varchar(30) not null,
branch_state char(2) not null,
branch_zip char(5) not null,
primary key (branch#));
create table SHIPPING
(shipping# varchar(40) not null,
shipping_company varchar(30) not null,
branch_shipping char(5) not null,
dest_address varchar(80) not null,
ship_date date not null,
arrival_date date,
is_signature_required number(1) not null,
primary key (shipping#),
foreign key (branch_shipping) references branch(branch#));
create table PRODUCT
(product# char(12) not null,
prod_name varchar(30) not null,
package_type varchar(10) not null,
unit_price number(*,2) not null,
current_cost number not null,
projected_cost number not null,
qty_available number not null,
reorder_qty number,
supplier# char(10) not null,
branch_located char(5) not null,
primary key (product#),
foreign key (supplier#) references supplier(supplier#),
foreign key (branch_located) references branch(branch#));
create table ORDERS
(order# char(10) not null,
cust# char(10) not null,
order_by_name varchar(30) not null,
date_ordered date not null,
special_requests varchar(512),
date_needed date not null,
est_shipment_date date,
quote_verified number(1) not null,
is_completed number(1) not null,
has_multi_shipments number(1) not null,
primary key (order#),
foreign key (cust#) references customer(cust#)
);
create table ORDER_DETAIL
(order# char(10) not null,
product# char(12) not null,
qty_ordered number not null,
is_backordered number(1) not null,
shipping# varchar(40),
primary key (order#, product#),
foreign key (product#) references product(product#),
foreign key (shipping#) references shipping(shipping#)
);
create table RECEIVABLES_INVOICES
(invoice# char(12) not null,
product# char(12) not null,
order# char(10) not null,
invoice_date date not null,
payment_terms varchar(6) not null,
freight_terms varchar(6) not null,
product_amount number not null,
tax_amount number(*, 2) not null,
freight_amount number(*, 2) not null,
date_paid date,
primary key (invoice#),
foreign key (product#) references product(product#),
foreign key (order#) references orders(order#));
======
SQL Queries
1) Retrieve the names of customers that have more orders than the average.
select company_name
from customer,
(select count(order#) CNT_ORDERS, cust# from orders group by cust#) cnt_temp
(select avg(order#) AVG_ORDERS, cust# from orders group by cust#) avg_temp
where avg_temp.cust# = customer.cust#
and cnt_temp.cust# = customer.cust#
and CNT_ORDERS > AVG_ORDERS;
2) Get a list of customers have received products but have not paid their invoices.
select c.Company_Name
from customer c,
shipping s,
receivables_invoices i,
orders o
where i.order# = o.order#
and o.cust# = c.cust#
and i.date_paid = null
and s.arrival_date > null;
3) Find customers that always require a signature when their product is delivered.
select c.company_name
from customer c
where not exists
((select order_detail.order#
from shipping, order_detail
where shipping.shipping# = order_detail.shipping#
and shipping.is_signature_required = 1)
minus
(select o.order#
from orders o
where c.cust# = o.cust#));
4) Find the most popular product for each customer.
select p.product#, c.company_name
from product p,
customer c,
(select max(NUM_ORDERED) FAV_PROD, product#, cust# from
(select count(product#) NUM_ORDERED, product#, cust#
from orders, order_detail
where orders.order# = order_detail.order#
group by cust#, product#)
group by cust#, product#) o_tmp
where o_tmp.cust# = c.cust#
and p.product# = o_tmp.product#;
5) Find which branch is holding the most products.
select b.branch_name
from branch b,
(select max(NUM_PRODS), branch_located
from (select count(product#) NUM_PRODS, branch_located from product group by branch_located)
group by branch_located) max_tmp
where max_tmp.branch_located = b.branch#;