/* Write your PL/SQL query statement below */

-- create a table
CREATE TABLE SalesPerson (
    sales_id INT, 
    name VARCHAR,
    salary INT,
    commission_rate INT,
    hire_date DATE,
    CONSTRAINT PK_STUDENT PRIMARY KEY (sales_id)
);
-- insert some values
INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) VALUES (1, 'John', 100000, 6, 4/1/2006);
INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) VALUES (2, 'Amy', 12000, 5, 5/1/2010);
INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) VALUES (3, 'Mark', 65000, 12, 12/25/2008);
INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) VALUES (4, 'Pam', 25000, 25, 1/1/2005);
INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) VALUES (5, 'Alex', 5000, 10, 2/3/2007);

CREATE TABLE Company (
    com_id INT, 
    name VARCHAR,
    city VARCHAR,
    CONSTRAINT PK_STUDENT PRIMARY KEY (com_id)
);

INSERT INTO Company (com_id, name, city) VALUES (1, 'RED', 'Boston');
INSERT INTO Company (com_id, name, city) VALUES (2, 'ORANGE', 'New York');
INSERT INTO Company (com_id, name, city) VALUES (3, 'YELLOW', 'Boston');
INSERT INTO Company (com_id, name, city) VALUES (4, 'GREEN', 'Austin');

CREATE TABLE Orders (
    order_id INT, 
    order_date DATE,
    com_id INT,
    sales_id INT,
    amount INT,
    CONSTRAINT PK_STUDENT PRIMARY KEY (order_id)
);

INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) VALUES (1, 1/1/2014, 3, 4, 10000);
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) VALUES (2, 2/1/2014, 4, 5, 5000);
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) VALUES (3, 3/1/2014, 1, 1, 50000);
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) VALUES (4, 4/1/2014, 1, 4, 25000);