Iacademy oracle 3

Delete this text and type your name here

This file will become rather large due to your screen shots. I encourage you to compress this file (zip) before submitting it.

Lab 4 : 40 Total Points Possible

You will need to log into Apex at https://iacademy.oracle.com in order to complete this assignment.

Sections 3-4 Programming with SQL

Section 3 Objectives : Executing Database Joins

Create and execute database joins

Use table aliases in SQL statements
Vocabulary :

Directions: Identify the vocabulary word for each definition below. (1 Point each)

1. A join that returns the unmatched rows as well as matched rows.

Answer:

2. Performs a join on two tables, retrieves all the rows in the left table even if there is no match in the right table.

Answer:

3. A join of two or more tables that return only matched rows.

Answer:

Try It / Solve It :

In order to be successful with this lab, y ou must review the ‘Oracle Student Tables’ file located under ‘Videos and Resources’ (multiple tabs across bottom of worksheet). This file will help you to understand the data that is being manipulated in the SQL statements .

I am providing a ‘copy’ (first 2 0 rows) of the ‘Results from Apex’ for each question. Hopefully, this will help you see if you are on track with your results. At the top of y our SQL window, you MUST increase the number of rows displayed to 20 so that I am able to see the first 20 rows of your results . You will need to provide the SQL statement you used to produce your results .

DBMS Output

11141129549203

11143605700225

1. Create a query using a LEFT OUTER JOIN that will display the first name, last name, and department name for all employees including those employees not assigned to a department. You will be using the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points)

Select Statement You Used :

My example of correct r esults from Apex (copy) :

Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)

Your Screenshot below:

DBMS Output

2. Create a query using a RIGHT OUTER JOIN that will display the first name, last name, and department id for all employees including those departments that do not have an employee assigned to them. You will be gathering data from the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points)

Select Statement You Used :

My example of correct r esults from Apex (copy) :

DBMS Output

DBMS Output

DBMS Output

Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)

Your Screenshot below:

3. Create a query using a FULL OUTER JOIN that will display the first name, last name, and department id for all employees including those departments that do not have an employee assigned to them and those employees not assigned to a department. You will be gathering data from the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points)