Scribd
Upload
70 views2 pages

DP 6 3 Solution W

The document provides definitions and examples of SQL join types, specifically focusing on inner and outer joins. It includes vocabulary terms such as FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and INNER JOIN, along with SQL query solutions for various scenarios involving employee and department data. The solutions demonstrate how to retrieve data using different types of joins to include unmatched rows from either table.

Uploaded by

internet.user9922
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
70 views2 pages

DP 6 3 Solution W

The document provides definitions and examples of SQL join types, specifically focusing on inner and outer joins. It includes vocabulary terms such as FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and INNER JOIN, along with SQL query solutions for various scenarios involving employee and department data. The solutions demonstrate how to retrieve data using different types of joins to include unmatched rows from either table.

Uploaded by

internet.user9922
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Database Programming with

SQL 6-3: Inner versus Outer


Joins Practice Solutions
Vocabulary

Directions: Identify the vocabulary word for each definition below.

FULL OUTER JOIN Performs a join on two tables, retrieves all the rows in the
Left table, even if there is no match in the Right table. It
also retrieves all the rows in the Right table, even if there
is no match in the Left table.
Outer join A join that returns the unmatched rows as well as
matched rows
LEFT OUTER JOIN Performs a join on two tables, retrieves all the rows in the
Left table even if there is no match in the Right table
RIGHT OUTER Performs a join on two tables, retrieves all the rows in the
JOIN Right table even if there is no match in the Left table
Inner join A join of two or more tables that returns only matched
rows

Try It / Solve It

Use the Oracle database for problems 1-7.

1. Return the first name, last name, and department name for all employees including
those employees not assigned to a department.

Solution:
SELECT e.first_name, e.last_name, d.department_id
FROM employees e
LEFT OUTER JOIN departments d
ON( e.department_id = d.department_id);

2. Return the first name, last name, and department name for all employees including
those departments that do not have an employee assigned to them.

Solution:
SELECT e.first_name, e.last_name, d.department_id
FROM employees e
RIGHT OUTER JOIN departments d
ON( e.department_id = d.department_id);

3. Return the first name, last name, and department name for all employees including those
departments that do not have an employee assigned to them and those employees not
assigned to a department.

2
Solution:
SELECT e.first_name, e.last_name, d.department_id
FROM employees e
FULL OUTER JOIN departments d
ON( e.department_id = d.department_id);

4. Create a query of the DJs on Demand database to return the first name, last name, event
date, and description of the event the client held. Include all the clients even if they have not
had an event scheduled.

Solution:
SELECT c.first_name, c.last_name, e.event_date, e.description
FROM d_clients c LEFT OUTER JOIN d_events e
ON(c.client_number = e.client_number);

5. Using the Global Fast Foods database, show the shift description and shift assignment date
even if there is no date assigned for each shift description.

Solution:
SELECT s.description, a.shift_assign_date
FROM f_shifts s LEFT OUTER JOIN f_shift_assignments a ON (s.code = a.code);

You might also like