CREATE TABLE LOCATIONS( LOCATION_ID NUMBER(4) PRIMARY KEY NOT NULL, STREET_ADDRESS VARCHAR(20), POSTAL_CODE VARCHAR(10), CITY VARCHAR(15) NOT NULL, STATE_PROVINCE VARCHAR(10), COUNTRY_ID CHAR(2)); INSERT INTO LOCATIONS VALUES(1400, '2014 JABBERWACKY RD',26192, 'SOUTHLAKE', 'TEXAS', 'US'); INSERT INTO LOCATIONS VALUES(1500, '2011 INTERIORS RD',99236, 'SAN FRANCISCO', 'CALIFORNIA', 'US'); INSERT INTO LOCATIONS VALUES(1700, '2004 CHARADE RD',98199, 'SEATLE', 'WASHINGTON', 'US'); INSERT INTO LOCATIONS VALUES(1800, '460 BLOOR ST.','ON M5S', 'TORONTO', 'ONTARIO', 'CA'); INSERT INTO LOCATIONS VALUES(2500, 'MAGDALEN CENTER','OX 9ZB', 'OXFORD', 'OXFORD', 'UK'); INSERT INTO LOCATIONS VALUES(2400, '365 SEATLE','ST 99W', 'OXFORD', 'OXFORD', 'US');

CREATE TABLE DEPARTMENTS( DEPARTMENT_ID NUMBER(4) PRIMARY KEY, DEPARTMENT_NAME VARCHAR(15) NOT NULL, MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4), CONSTRAINT fk_locid FOREIGN KEY (LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID)); INSERT INTO DEPARTMENTS VALUES(10,'ADMINISTRATION',200,1700); INSERT INTO DEPARTMENTS VALUES(20,'MARKETING',201,1800); INSERT INTO DEPARTMENTS VALUES(50,'SHIPPING',124,1500); INSERT INTO DEPARTMENTS VALUES(60,'IT',103,1400); INSERT INTO DEPARTMENTS VALUES(80,'SALES',149,2500); INSERT INTO DEPARTMENTS VALUES(90,'EXECUTIVE',100,1700); INSERT INTO DEPARTMENTS VALUES(100,'ACCOUNTING',205,1700); INSERT INTO DEPARTMENTS VALUES(190,'CONTRACTING',NULL,1700); INSERT INTO DEPARTMENTS VALUES(110,'HRD',205,1800); INSERT INTO DEPARTMENTS VALUES(1115,'CUSTOMER',NULL,2500);

CREATE TABLE EMPLOYEES( EMPLOYEE_ID NUMBER(6) PRIMARY KEY NOT NULL, FIRSTNAME VARCHAR(10), LASTNAME VARCHAR(10)NOT NULL, JOB_ID VARCHAR(10), SALARY NUMBER(8,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4)); INSERT INTO EMPLOYEES VALUES (100,'STEVEN','KING','AD_PRES',24000,NULL,90); INSERT INTO EMPLOYEES VALUES (101,'NENA','KOCHAR','AD_VP',17000,100,90); INSERT INTO EMPLOYEES VALUES (102,'LEX','DE HAAN','AD_VP',17000,100,90); INSERT INTO EMPLOYEES VALUES (103,'ALEXANDER','HUNOLD','IT_PROG',NULL,101,60); INSERT INTO EMPLOYEES VALUES (104,'BRUCE','ERNST','IT_PROG',6000,102,60); INSERT INTO EMPLOYEES VALUES (107,'DIANA','LORENTZ','IT_PROG',4200,103,60); INSERT INTO EMPLOYEES VALUES (124,'KEVIN','MOURGOS','ST_MAN',5800,100,50); INSERT INTO EMPLOYEES VALUES (141,'TRINA','RAJS','ST_CLERK',3500,124,50); INSERT INTO EMPLOYEES VALUES (142,'CURTIS','DAVIES','ST_CLERK',3100,124,50); INSERT INTO EMPLOYEES VALUES (143,'RANDALL','MATOS','ST_CLERK',2600,124,50); INSERT INTO EMPLOYEES VALUES (144,'PETER','VARGAS','ST_CLERK',2500,124,50); INSERT INTO EMPLOYEES VALUES (149,'ELENI','ZLOTKEY','SA_MAN',10500,100,80); INSERT INTO EMPLOYEES VALUES (174,'ELLEN','ABEL','SA_REP',11000,149,50); INSERT INTO EMPLOYEES VALUES (176,'JONATHAN','TAYLOR','SA_REP',8600,149,80); INSERT INTO EMPLOYEES VALUES (178,'KIMBERLEY','GRANT','SA_REP',7000,149,NULL); INSERT INTO EMPLOYEES VALUES (200, 'JENNIFER','WHALEN','AD_ASST',4400,101,10); INSERT INTO EMPLOYEES VALUES (201,'MICHAEL','HARTSTEIN','MK_MAN',13000,100,20); INSERT INTO EMPLOYEES VALUES (202,'PAT','FAY','MK_REP',6000,201,20); INSERT INTO EMPLOYEES VALUES (205,'SHELLEY','HIGGINS','AC_MGR',12000,101,110); INSERT INTO EMPLOYEES VALUES (206,'WILLIAM','GIETZ','AC_ACCOUNT',8300,205,110);

Directions:

1. Use the following text file for table definitions:

TBL_1 LOCATIONS

TBL_2 DEPARTMENTS

TBL_3 EMPLOYEES

2. Populate the three tables with following values found on the 3 text file:

TBL_1 LOCATIONS VALUES

TBL_2 DEPARTMENTS VALUES

TBL_3 EMPLOYEES VALUES

3. In each number, write the SQL statement that you use in order for you to come up with the right answer. Answer with no SQL statements will not be credited.
4. Answer the questions ask in each numbers.

1. Create a report that will display the following: DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID and POSTAL CODE. Use natural join to get the data from two table.

Question: What is the Postal Code of employee under the Marketing department?

2. Create a report that will display the following: concatenated column of LASTNAME and FIRSTNAME put a literal charter string in between 2 columns , rename the column as FULL NAME. Also get the DEPARTMENT_ID and LOCATION_ID. Use natural join to get the data from two table.

Questions: What is/are the full name of employee whose LOCATION_ID is equal to 1700?

3. Create a report that will display the following: STREET_ADDRESS, CITY, DEPARTMENT_NAME and LOATION_ID. Use using clause to get the data from two table.

Questions: What is the address of employee under the department_name IT?

4. Create a report that will display the following: FIRTSNAME, SALARY with additional 1000 in employees salary, rename this column as BONUS, then get the DEPARTMENT_NAME and DEPARTMENT_ID. Join the table using ON condition.

Question: How much is the total bonus that should be given to employee/s whose department name is equal to SHIPPING?

5. Create a report that will display the following: FIRTSNAME, SALARY with additional 1000 rename the column as BONUS, get the DEPARTMENT_NAME, DEPARTMENT_ID and CITY. Use ON condition to connect the three tables.

Questions: Whose employee have (2) two departments but of the same city?

6. Create a report that will display the following: DEPARTMENT_ID, LOCATION_ID and COUNTRY_ID. Use Inner join to connect the two tables.

Question: What are the country_id of all employees under department_id between 10 and 50?

7. Create a report that will display the following: DEPARTMENT_ID, LOCATION_ID, COUNTRY_ID and STREET ADDRESS. Use left outer join to connect the two tables.

Question: What are the department_id of all employees whose street address is equal to CHARADE RD?

8. Create a report that will display the following: LASTNAME, JOB_ID, SALARY, DEPARTMENT_ID and DEPARTMENT_NAME. Use Right Outer join to connect the two tables.

Question: How many department_name that is/are never been assigned to any employee? What is/are the department name?

9. Create a report that will display the following: LASTNAME, DEPARTMENT_NAME and STATE PROVINCE. Join the three tables using full outer join.

Question: By using Full Outer Join how many employee whose state province is equal to null or blank?

10. Create a report that will display the following: LASTNAME, CITY AND MANAGER_ID by combining the data from EMPLOYEES, DEPARTMENTS AND LOCATIONS.

Question: How many rows are returned? Why?