-- create the tables
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 VARCHAR2(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 sample data into customers table

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  1, 'Jan', 'Nikiel', '1965/01/01', '800-555-1211'
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  2, 'Lidia', 'Stal', '1968/02/05', '800-555-1212'
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  3, 'Stefan', 'Br�z', '1971/03/16', '800-555-1213'
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  4, 'Gra�yna', 'Cynk', NULL, '800-555-1214'
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  5, 'Jadwiga', 'Mosi�dz', '1970/05/20', NULL
);


-- insert sample data into product_types table

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  1, 'Ksi��ka'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  2, 'VHS'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  3, 'DVD'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  4, 'CD'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  5, 'Czasopismo'
);

-- commit the transaction
COMMIT;

-- insert sample data into products table

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  1, 1, 'Nauka wsp�?czesna', 'Opis wsp�?czesnej nauki', 19.95
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  2, 1, 'Chemia', 'Wprowadzenie do chemii', 30.00
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  3, 2, 'Supernowa', 'Eksplozja gwiazdy', 25.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  4, 2, 'Wojny czo?g�w', 'Film akcji o nadchodz�cej wojnie', 13.95
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  5, 2, 'Z Files', 'Serial o tajemniczych zjawiskach', 49.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  6, 2, '2412: Powr�t', 'Powr�t obcych', 14.95
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
)
VALUES (
  7, 3, 'Space Force 9', 'Przygody bohater�w', 13.49
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  8, 3, 'Z innej planety', 'Obcy z innej planety l�duje na Ziemi', 12.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  9, 4, 'Muzyka klasyczna', 'Nalepsze dzie?a muzyki klasycznej', 10.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  10, 4, 'Pop 3', 'Najlepsze utwory popowe', 15.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  11, 4, 'Tw�rczy wrzask', 'Album debiutancki', 14.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  12, NULL, 'Pierwsza linia', 'Najwi�ksze hity', 13.49
);

-- commit the transaction
COMMIT;

-- insert sample data into purchases table

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  1, 1, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  2, 1, 3
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  1, 4, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  2, 2, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  1, 3, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  1, 2, 2
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  2, 3, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  2, 4, 1
);

INSERT INTO purchases (
  product_id, customer_id, quantity
) VALUES (
  3, 3, 1
);

-- commit the transaction
COMMIT;

-- insert sample data into employees table

INSERT INTO employees (
  employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
  1, NULL, 'Jan', 'Kowalski', 'CEO', 800000
);

INSERT INTO employees (
  employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
  2, 1, 'Roman', 'Jo?wierz', 'Kierownik sprzeda�y', 600000
);

INSERT INTO employees (
  employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
  3, 2, 'Fryderyk', 'Helc', 'Sprzedawca', 150000
);

INSERT INTO employees (
  employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
  4, 2, 'Zofia', 'Nowak', 'Sprzedawca', 500000
);

-- commit the transaction
COMMIT;

-- insert sample data into salary_grade table

INSERT INTO salary_grades (
  salary_grade_id, low_salary, high_salary
) VALUES (
  1, 1, 250000
);

INSERT INTO salary_grades (
  salary_grade_id, low_salary, high_salary
) VALUES (
  2, 250001, 500000
);

INSERT INTO salary_grades (
  salary_grade_id, low_salary, high_salary
) VALUES (
  3, 500001, 750000
);

INSERT INTO salary_grades (
  salary_grade_id, low_salary, high_salary
) VALUES (
  4, 750001, 999999
);

-- commit the transaction
COMMIT;