cse311

CREATE TABLE Employees(
Employee_id NUMBER(6) PRIMARY KEY,
first_name varchar2(20),
Last_name VARCHAR2(25) NOT NULL,
Email VARCHAR2(25) NOT NULL,
Phone_number VARCHAR2(15),
Hire_Date DATE NOT NULL,
Job_id Varchar2(10) NOT NULL,
Salary NUMBER(8,2),
Commission_pct NUMBER(2,2),
Manager_id NUMBER(6),
Department_id NUMBER(4));


CREATE TABLE Departments(
DEPARTMENT_ID NUMBER(4) NOT NULL,
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);

CREATE TABLE Locations(
LOCATION_ID NUMBER(4) NOT NULL,
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30) NOT NULL,
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2)
);

INSERT ALL
  INTO Employees VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', 'JUN-17-2006', 'AD_PRESS', 24000, NULL, NULL, 90)
  INTO Employees VALUES (101, 'Neena', 'Kochar', 'NKOCHAR', '515.123.4568', 'SEP-21-2008', 'AD_VP', 17000, NULL, 100, 90)
  INTO Employees VALUES (102, 'Lex', 'De Haan', 'DEHAAN', '515.123.4569', 'JAN-13-2009', 'AD_VP', 17000, NULL, 100, 90)
SELECT * FROM dual;




INSERT ALL
    INTO Departments VALUES( …… )
    INTO Departments VALUES( …… )
    …..
SELECT * FROM dual;


//

INSERT ALL
    INTO Departments VALUES( 10, 'Administration', 200, 1700)
        INTO Departments VALUES( 20, 'Marketing', 201, 1800)
        INTO Departments VALUES( 50, 'Shipping', 124, 1500)
        INTO Departments VALUES( 60, 'IT', 103, 1400)
        INTO Departments VALUES( 80, 'Sales', 149, 2500)
        INTO Departments VALUES( 90, 'Executive', 100, 1700)
        INTO Departments VALUES( 110,'Accounting', 205, 1700)
        INTO Departments VALUES( 190,'Contracting', NULL, 1700 )
SELECT * FROM dual;


INSERT ALL
    INTO Locations VALUES( …… )
    INTO Locations VALUES( …… )
    …….
SELECT * FROM dual;



SELECT 'Mr/s ' || last_name  ||  ' earns a salary of ' ||salary|| ' with a job id ' || job_id AS "Employee Summary" FROM employees