CSE 311 Database

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)
);

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));

INSERT ALL
INTO Locations VALUES(1400, '2014 Jabberwocky Rd','26192','Southlake', 'Texas', 'US')
INTO Locations VALUES(1500, '2011 Interiors Blvd','99236','South San Francisco', 'California', 'US')
INTO Locations VALUES(1700, '2004 Charade Rd','98199', 'Seattle','Washington', 'US')
INTO Locations VALUES(1800, '460 Bloor St. W.','ON M5S 1X8', 'Toronto','Ontario', 'CA')
INTO Locations VALUES(2500, 'Magdalen Centre- The Oxford Sc. Park','OX9 9ZB', 'OXford','Oxford', 'UK')
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 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)
INTO Employees VALUES(103, 'Alexander','Hunold', 'AHUNOLD','590.423.4567', 'JAN-03-2008', 'IT_PROG',9000,NULL, 102, 60)
INTO Employees VALUES(104, 'Bruce','Ernst', 'BERNST','590.423.4568', 'MAY-21-2009', 'IT_PROG',6000,NULL, 103, 60)
INTO Employees VALUES(107, 'Diana','Lorentz', 'DLORENTZ','590.423.5567', 'FEB-07-2008', 'IT_PROG',4200,NULL, 103, 60)
INTO Employees VALUES(124, 'Kevin','Mourgos', 'KMORGOS','650.123.5234', 'NOV-16-2012', 'ST_MAN',5800,NULL, 100, 50)
INTO Employees VALUES(141, 'Treena','Rajs', 'RRAJS','650.121.5234', 'OCT-17-2004', 'ST_CLERK',3500,NULL, 124, 50)
INTO Employees VALUES(142, 'Curtis','Davies', 'CDAVIES','121.123.5234', 'JAN-29-2007', 'ST_CLERK',3100,NULL, 124, 50)
INTO Employees VALUES(143, 'Randall','Matos', 'RMATOS','121.123.5234', 'MAR-15-2008', 'ST_CLERK',2600,NULL, 124, 50)
INTO Employees VALUES(144, 'Peter','Vargas', 'PVARGAS','121.123.5234', 'JUL-09-2008', 'ST_CLERK',2500,NULL, 124, 50)
INTO Employees VALUES(149, 'Eleni','Zlotkey', 'EZLOTKEY','44.1344.429018', 'JAN-29-2014', 'SA_MAN',10500,.2, 100, 80)
INTO Employees VALUES(174, 'Ellen','Abel', 'EABEL','44.1644.429017', 'MAY-11-2004', 'SA_REP',11000,.3, 149, 80)
INTO Employees VALUES(176, 'Jnathon','Taylor', 'JTAILOR','44.1644.429021', 'MAR-24-2008', 'SA_MAN',8600,.2, 149, 80)
INTO Employees VALUES(178, 'Kimberely','Grant', 'KGRANT','44.1644.429023', 'MAY-24-2009', 'SA_MAN',7000,.15, 149, NULL)
INTO Employees VALUES(200, 'Jennifer','Whalem', 'JWHALEN','515.123.4444', 'SEP-17-2003', 'ADD_ASST',4400,NULL, 101, 10)
INTO Employees VALUES(201, 'Michael','Hartstein', 'MHARSTEIN','515.123.5555', 'FEB-17-2008', 'MK_MAN',13000,NULL, 100, 20)
INTO Employees VALUES(202, 'Pat','Fay','PFAY','603.123.6666', 'AUG-17-2010', 'MK_REP',6000,NULL, 201, 20)
INTO Employees VALUES(205, 'Shelley','Higgins', 'SHIGGINS','515.123.8050', 'JUN-07-2007', 'AC_MGR',12000,NULL, 101, 110)
INTO Employees VALUES(206, 'William','Gietz', 'WGIETZ','515.123.8181', 'JUN-07-2007', 'AC_ACCOUNT',8300,NULL, 205, 110)
SELECT * FROM dual;

CREATE TABLE Jobs(
job_id VARCHAR2(10) PRIMARY KEY,
job_title VARCHAR2(35),
min_salary NUMBER(6),
max_salary NUMBER(6));

INSERT ALL
INTO JOBS VALUES('AD_PRES', 'President',20000,40000)
INTO JOBS VALUES('AD_VP', 'Administration Vice President',15000, 30000)
INTO JOBS VALUES('AD_ASST', 'Administration Assistant',3000, 6000)
INTO JOBS VALUES('AC_MGR', 'Account Manager',8200, 16000)
INTO JOBS VALUES('AC_ACCOUNT', 'Public Accountant',4200,9000)
INTO JOBS VALUES('SA_MAN', 'Sales Manager',10000, 20000)
INTO JOBS VALUES('SA_REP', 'Sales Representative',6000, 12000)
INTO JOBS VALUES('ST_MAN', 'Stock Manager',5500, 8500)
INTO JOBS VALUES('ST_CLERK', 'Stock Clerk',2000, 5000)
INTO JOBS VALUES('IT_PROG', 'Programmer',4000, 10000)
INTO JOBS VALUES('MK_MAN', 'Marketing Manager',9000, 15000)
INTO JOBS VALUES('MK_REP', 'Marketing Representative',4000, 9000)
SELECT * FROM dual;

SELECT E.EMPLOYEE_ID, E.SALARY, J.JOB_TITLE, E.HIRE_DATE, E.JOB_ID
FROM EMPLOYEES E
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, J.JOB_TITLE, E.HIRE_DATE, E.JOB_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.SALARY NOT BETWEEN 5000 AND 10000;

SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.HIRE_DATE, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE E.HIRE_DATE < 'JAN-01-2010';

SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS "EMPLOYEE NAME", E.SALARY AS "EMP_SALARY",
M.FIRST_NAME || ' ' || M.LAST_NAME AS "MANAGER NAME", M.SALARY AS "MGR_SALARY",
EJ.JOB_TITLE AS "EJOB_TITLE", MJ.JOB_TITLE AS "MJOB_TITLE"

FROM EMPLOYEES E
JOIN EMPLOYEES M
ON E.MANAGER_ID = M.EMPLOYEE_ID
JOIN JOBS EJ
ON E.JOB_ID = EJ.JOB_ID
JOIN JOBS MJ
ON MJ.JOB_ID = M.JOB_ID;

SELECT ' Mr/s ' || LAST_NAME || ' earns a salary of ' || SALARY || ' with a job id ' || JOB_ID FROM Employees;

SELECT ' Mr/s ' || LAST_NAME || ' earns a salary of ' || (SALARY+500)*100 || ' with a job id ' || JOB_ID AS "Employee Summary" FROM Employees;