CREATE TABLE customers(  
                       customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,  
                       first_name VARCHAR2(10) NOT NULL,  
                       last_name VARCHAR2(10) NOT NULL,  
                       dob DATE,  
                       phone VARCHAR2(12)  
                       )

CREATE TABLE product_types(  
                           product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,  
                           name VARCHAR2(10) NOT NULL  
                           )

CREATE TABLE products(  
                      product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,  
                      product_type_id INTEGER CONSTRAINT products_fk_product_types REFERENCES product_types(product_type_id),  
                      name VARCHAR2(30) NOT NULL,  
                      description VARCHAR2(50),  
                      PRICE number(5,2)  
                      )

CREATE TABLE purchases(  
                       product_id INTEGER CONSTRAINT purchases_fk_products REFERENCES products(product_id),  
                       customer_id INTEGER CONSTRAINT purchases_fk_customers REFERENCES customers(customer_id),  
                       quantity INTEGER NOT NULL,  
                       CONSTRAINT purchases_pk PRIMARY KEY(product_id,customer_id)  
                       )

CREATE TABLE employees(  
                       employee_id INTEGER CONSTRAINT employees_pk PRIMARY KEY,  
                       manager_id INTEGER,  
                       first_name VARCHAR(10) NOT NULL,  
                       last_name VARCHAR2(10) NOT NULL,  
                       title VARCHAR2(20),  
                       SALARY number(6,0)  
                       )

CREATE TABLE salary_grades(  
                           salary_grade_id INTEGER CONSTRAINT salary_grade_pk PRIMARY KEY,  
                           low_salary NUMBER(6,0),  
                           high_salary NUMBER(6,0)  
                           )

INSERT INTO customers VALUES(1,'John','Brown','01-JAN-65','800-555-1211')

INSERT INTO customers VALUES(2,'Cynthia','Green','05-FEB-68','800-555-1212')

INSERT INTO customers VALUES(3,'Steve','White','16-MAR-71','800-555-1213')

INSERT INTO customers VALUES(4,'Gail','Black',NULL,'800-555-1214')

INSERT INTO customers VALUES(5,'Doreen','Blue','20-MAY-70',NULL)

INSERT INTO customers VALUES(6,'Fred','Brown','01-JAN-1970','800-555-1215')

INSERT INTO product_types VALUES(1,'Book')

INSERT INTO product_types VALUES(2,'Video')

INSERT INTO product_types VALUES(3,'DVD')

INSERT INTO product_types VALUES(4,'CD')

INSERT INTO product_types VALUES(5,'Magazine')

INSERT INTO products VALUES(1,1,'Modern SCIENCE','a DESCRIPTION OF MODERN SCIENCE',19.95)

INSERT INTO products VALUES(2,1,'cHEMISTRY','Introduction to Chemistry',30)

INSERT INTO products VALUES(3,2,'Supernova','A star explodes',25.99)

INSERT INTO products VALUES(4,2,'Tank War','Action movie about a future war',13.95)

INSERT INTO purchases VALUES(1,1,1)

INSERT INTO purchases VALUES(2,1,3)

INSERT INTO purchases VALUES(1,4,1)

INSERT INTO purchases VALUES(2,2,1)

INSERT INTO purchases VALUES(1,3,1)

INSERT INTO employees VALUES(1,NULL,'James','Smith','CEO',800000)

INSERT INTO employees VALUES(2,1,'Ron','Johnson','Sales Manager',600000)

INSERT INTO employees VALUES(3,2,'Fres','Hobbs','Salesperson',150000)

INSERT INTO employees VALUES(4,2,'Susan','Jones','Salesperson',500000)

INSERT INTO salary_grades VALUES(1,1,250000)

INSERT INTO salary_grades VALUES(2,250001,500000)

INSERT INTO salary_grades VALUES(3,500001,750000)

INSERT INTO salary_grades VALUES(4,750001,999999)