Laboratory Exercise 005 - Joins
Laboratory Exercise 005 - Joins
Laboratory Exercise 005 - Joins
Directions:
a. Use the following text file for table definitions:
TBL_1 LOCATIONS
TBL_2 DEPARTMENTS
TBL_3 EMPLOYEES
b. 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
c. 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.
d. Answer the questions ask in each numbers.
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?
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?
CS352-DBMS2-MAS Page 1
NAME: COURSE:
TITLE: Applying Join Condition USN ID:
MACHINE ACTIVITY NO. DATE:
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?
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?
CS352-DBMS2-MAS Page 2