CREATE TABLE  CUSTOMERS 
   (CUST_ID VARCHAR2(10), 
    FIRST_NAME VARCHAR2(15), 
    LAST_NAME VARCHAR2(15), 
    MIDDLE_NAME VARCHAR2(15), 
    STREET_ADDRESS VARCHAR2(30), 
    CITY VARCHAR2(15), 
    STATE VARCHAR2(20), 
    ZIP VARCHAR2(5),
    CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUST_ID)
   );

Insert into CUSTOMERS
   (CUST_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP)
 Values
   ('CUST001', 'PAUL', 'MULIN', '69821 SOUTH AVENUE', 
    'BOISE', 'ID', '83707');
Insert into CUSTOMERS
   (CUST_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP)
 Values
   ('CUST002', 'GRAVIN', 'LEWIS', '9153 MAIN STREET', 
    'AUSTIN', 'TX', '78710');
Insert into CUSTOMERS
   (CUST_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP)
 Values
   ('CUST003', 'MARK', 'MULLER', '114 EAST SAVANNAH', 
    'ATLANTA', 'GA', '30314');
Insert into CUSTOMERS
   (CUST_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP)
 Values
   ('CUST004', 'STACY', 'BARBER', '357 WHITE EAGLE AVE.', 
    'CLERMONT', 'FL', '34711');
Insert into CUSTOMERS
   (CUST_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP)
 Values
   ('CUST005', 'WENDY', 'BOVEL', '1008 GRAND AVENUE', 
    'MACON', 'GA', '31206');
COMMIT;
   
CREATE TABLE  FINANCING_PLANS
   (PLAN_ID VARCHAR2(10),
    INSTITUTION VARCHAR2(12), 
    PERCENTAGE VARCHAR2(12), 
    LOAN_TYPE VARCHAR2(12), 
    MAX_TERM INTEGER, 
    MIN_DOWN FLOAT,
    MAX_LOAN_AMT FLOAT,
    CONSTRAINT FINANCING_PLANS_PK PRIMARY KEY (PLAN_ID)
   );

Insert into FINANCING_PLANS
   (PLAN_ID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT)
 Values
   ('PLAN001', 'BANK', '12', 'CONSUMER', 10, 
    1500, 50000);
Insert into FINANCING_PLANS
   (PLAN_ID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT)
 Values
   ('PLAN002', 'BANK', '11.3', 'CONSUMER', 5, 
    2000, 20000);
Insert into FINANCING_PLANS
   (PLAN_ID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT)
 Values
   ('PLAN003', 'BANK', '15', 'CONSUMER', 15, 
    1500, 45000);
COMMIT;
   
CREATE TABLE  DEALERSHIPS
   (DEALER_ID VARCHAR2(10), 
    OPEN_DATE DATE, 
    PHONE VARCHAR2(15), 
    MANAGER VARCHAR2(30), 
    SQ_FT FLOAT, 
    LOCATION VARCHAR2(15),
    REGION VARCHAR2(10),
    CONSTRAINT DEALERSHIPS_PK PRIMARY KEY (DEALER_ID)
   );

Insert into DEALERSHIPS
   (DEALER_ID, OPEN_DATE, PHONE, MANAGER, SQ_FT, LOCATION, REGION)
 Values
   ('DEALER001', TO_DATE('07/18/2014 14:02:52', 'MM/DD/YYYY HH24:MI:SS'), '010-410-0010', 'TOMMIE SEYMOUR', 1000, 
    'CHICAGO', 'N');
Insert into DEALERSHIPS
   (DEALER_ID, OPEN_DATE, PHONE, MANAGER, SQ_FT, LOCATION, REGION)
 Values
   ('DEALER002', TO_DATE('07/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '800-555-1211', 'DAVID DAVIDSON', 500, 
    'BOSTON', 'NE');
Insert into DEALERSHIPS
   (DEALER_ID, OPEN_DATE, PHONE, MANAGER, SQ_FT, LOCATION, REGION)
 Values
   ('DEALER003', TO_DATE('07/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '800-555-8284', 'SEBASTIAN JONES', 800, 
    'SEATTLE', 'NW');
COMMIT;

CREATE TABLE  SALEPERSONS
   (SALESPERSON_ID VARCHAR2(10),
    TITLE VARCHAR2(12), 
    FIRST_NAME VARCHAR2(15), 
    LAST_NAME VARCHAR2(15), 
    MIDDLE_NAME VARCHAR2(15), 
    HIRE_DATE DATE,
	DEALER_ID VARCHAR2(10),
    CONSTRAINT SALEPERSONS_PK PRIMARY KEY (SALESPERSON_ID)
   );

Insert into SALEPERSONS
   (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID)
 Values
   ('SP001', 'MR', 'FALAH', 'KENNETH', TO_DATE('05/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DEALER001');
Insert into SALEPERSONS
   (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID)
 Values
   ('SP002', 'MR', 'SMITH', 'JENNIFER', TO_DATE('07/11/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DEALER002');
Insert into SALEPERSONS
   (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID)
 Values
   ('SP003', 'MR', 'MONTIASA', 'GREG', TO_DATE('07/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DEALER003');
COMMIT;

CREATE TABLE  DISTRIBUTORS
   (DIST_ID VARCHAR2(10), 
    STREET_ADDRESS VARCHAR2(30), 
    CITY VARCHAR2(15),
    STATE VARCHAR2(20), 
    ZIP VARCHAR2(5), 
    PHONE VARCHAR2(15),
    POC VARCHAR2(30),
    CONSTRAINT DISTRIBUTORS_PK PRIMARY KEY (DIST_ID)
   );

Insert into DISTRIBUTORS
   (DIST_ID, STREET_ADDRESS, CITY, STATE, ZIP, PHONE, POC)
 Values
   ('DIST001', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 
    '800-555-9743', 'RENEE SMITH');
COMMIT;


CREATE TABLE  ORDER_SHIPMENTS
   (OS_ID VARCHAR2(10), 
    DEALER_ID VARCHAR2(10), 
    DIST_ID VARCHAR2(10), 
    SHIP_DATE DATE,
    CONSTRAINT ORDER_SHIPMENTS_PK PRIMARY KEY (OS_ID)
    );

Insert into ORDER_SHIPMENTS
   (OS_ID, DEALER_ID, DIST_ID, SHIP_DATE)
 Values
   ('OS001', 'DEALER001', 'DIST001', TO_DATE('07/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

	
CREATE TABLE  TRADE_IN_VEHICLES
   (TRADE_ID VARCHAR2(10),
    SALE_ID VARCHAR2(10), 
    VIN VARCHAR2(10), 
    TRADE_IN_AMOUNT FLOAT, 
    CONSTRAINT TRADE_IN_VEHICLES_PK PRIMARY KEY (TRADE_ID)
   );

Insert into TRADE_IN_VEHICLES
   (TRADE_ID, SALE_ID, VIN, TRADE_IN_AMOUNT)
 Values
   ('TRADE001', 'SALE001', 'VIN00', 10000);
COMMIT;


CREATE TABLE  VEHICLES
   (VIN VARCHAR2(10),
    OS_ID VARCHAR2(10), 
    TRADE_ID VARCHAR2(10),
    TYPE VARCHAR2(12), 
    MODEL VARCHAR2(15), 
    MAKE VARCHAR2(15), 
    WHERE_FROM VARCHAR2(15),
    WHOLESALE_COST VARCHAR2(15),
    CONSTRAINT VEHICLES_PK PRIMARY KEY (VIN)
   );

Insert into VEHICLES
   (VIN, OS_ID, TRADE_ID, TYPE, MODEL, MAKE, WHERE_FROM, WHOLESALE_COST)
 Values
   ('VIN002', 'OS001', 'TRADE001', 'SEDAN', 'COROLLA', 
    'TOYOTA', 'JAPAN', '8000');
Insert into VEHICLES
   (VIN, OS_ID, TRADE_ID, TYPE, MODEL, MAKE, WHERE_FROM, WHOLESALE_COST)
 Values
   ('VIN001', 'OS001', 'TRADE001', 'SEDAN', 'LANCER', 
    'MITSUBISHI', 'JAPAN', '10000');
Insert into VEHICLES
   (VIN, OS_ID, TRADE_ID, TYPE, MODEL, MAKE, WHERE_FROM, WHOLESALE_COST)
 Values
   ('VIN003', 'OS001', 'TRADE001', 'SEDAN', 'TSX', 
    'ACURA', 'USA', '15000');
Insert into VEHICLES
   (VIN, OS_ID, TRADE_ID, TYPE, MODEL, MAKE, WHERE_FROM, WHOLESALE_COST)
 Values
   ('VIN004', 'OS001', 'TRADE001', 'ROADSTER', 'MIATA', 
    'MAZDA', 'GERMANY', '25000');
Insert into VEHICLES
   (VIN, OS_ID, TRADE_ID, TYPE, MODEL, MAKE, WHERE_FROM, WHOLESALE_COST)
 Values
   ('VIN005', 'OS001', 'TRADE001', 'SEDAN', 'CIVIC', 
    'HONDA', 'JAPAN', '12000');
COMMIT;


CREATE TABLE  SALES 
   (SALE_ID VARCHAR2(10), 
    CUST_ID VARCHAR2(10), 
    VIN VARCHAR2(10), 
    SALESPERSON_ID VARCHAR2(10), 
    VEHICLE_STATUS VARCHAR2(10), 
    SALE_DATE DATE,
    MILEAGE FLOAT,
	GROSS_SALE_PRICE FLOAT,
    CONSTRAINT SALE_PK PRIMARY KEY (SALE_ID, CUST_ID)
   );

Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE001', 'CUST001', 'VIN001', 'SP001', 'RUNNING', 
    TO_DATE('07/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 13200);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE002', 'CUST002', 'VIN001', 'SP001', 'RUNNING', 
    TO_DATE('07/16/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 15000, 10999);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE003', 'CUST001', 'VIN002', 'SP001', 'RUNNING', 
    TO_DATE('07/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20000, 10699);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE004', 'CUST001', 'VIN001', 'SP002', 'RUNNING', 
    TO_DATE('07/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 50000, 19298);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE005', 'CUST002', 'VIN002', 'SP001', 'RUNNING', 
    TO_DATE('07/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4000, 10229);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE006', 'CUST002', 'VIN003', 'SP002', 'RUNNING', 
    TO_DATE('07/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12000, 9730);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE007', 'CUST003', 'VIN002', 'SP003', 'RUNNING', 
    TO_DATE('07/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2000, 14555);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE008', 'CUST004', 'VIN004', 'SP002', 'RUNNING', 
    TO_DATE('07/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3500, 23359);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE009', 'CUST003', 'VIN003', 'SP003', 'RUNNING', 
    TO_DATE('07/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 45000, 9798);
Insert into SALES
   (SALE_ID, CUST_ID, VIN, SALESPERSON_ID, VEHICLE_STATUS, SALE_DATE, MILEAGE, GROSS_SALE_PRICE)
 Values
   ('SALE010', 'CUST004', 'VIN005', 'SP003', 'RUNNING', 
    TO_DATE('07/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 7891);
COMMIT;

   
CREATE TABLE  SALE_FINANCINGS
   (SALE_ID VARCHAR2(10),
    PLAN_ID VARCHAR2(10),
    DOWNPAY FLOAT, 
    LOAN_TERM FLOAT,
    CONSTRAINT SALE_FINANCING_PK PRIMARY KEY (SALE_ID, PLAN_ID)
   );

Insert into SALE_FINANCINGS
   (SALE_ID, PLAN_ID, DOWNPAY, LOAN_TERM)
 Values
   ('SALE001', 'PLAN001', 1000, 5);
COMMIT;
 

-- Verify table and data

SET SERVEROUTPUT ON FORMAT WRAPPED
SPOOL E:\Homework1Spool.txt

SELECT table_name FROM user_tables;

DESC CUSTOMERS;
DESC DEALERSHIPS;
DESC DISTRIBUTORS;
DESC FINANCING_PLANS;
DESC ORDER_SHIPMENTS;
DESC SALES;
DESC SALE_FINANCINGS; 
DESC VEHICLES;
DESC SALEPERSONS;
DESC TRADE_IN_VEHICLES;

set linesize 150 pagesize 100

SELECT * FROM CUSTOMERS;
SELECT * FROM DEALERSHIPS;
SELECT * FROM DISTRIBUTORS;
SELECT * FROM FINANCING_PLANS;
SELECT * FROM ORDER_SHIPMENTS;
SELECT * FROM SALES;
SELECT * FROM SALE_FINANCINGS;
SELECT * FROM VEHICLES;
SELECT * FROM SALEPERSONS;
SELECT * FROM TRADE_IN_VEHICLES;

SPOOL OFF