Database Tutorial

Lab #10: Outer Join Select

Learn how to write SELECT statements with a query of data derived by an outer join against an external table and internally managed tables. Reinforce how you embed the queries to use sequence values as a pseudo column before inserting the results in INSERT statements. There are three queries that you must develop and insert their data sequentially. The sequential nature of the queries resolves around the independence of the first query, and the dependencies of the second and third queries. In other words, you must insert the records into the rental table before you can insert rows in the rental_item table, and you must insert records into the rental and rental_item tables before you can insert records into the transaction table.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

You learn how to map a denormalized import table to insert the data into three normalized tables. The first table in a series of insert statements has no dependency. The second table in a series of insert statement has a dependency on the first table and the third table on both the first and second tables.

The dependencies are:

• The first table is the rental table. You can insert into the rental table directly because the table has no sequential dependencies.
• The second table is the rental_item table. You can insert into the rental_item table only after you insert into the rental table.
• The third table is the transaction table. You can insert into the transaction table only after you insert into both the rental and rental_item tables.

You can use an INSERT statement when you know the values are unique and new but not when some of the data in the import table may already exist in the normalized tables. You use a MERGE statement when you import the data that may contain rows in the denormalized source table that already exist in the normalized tables.

This lab guarantees that the records in the denormalized source table are unique and new. This allows you to learn how to query the records and then insert the result set from the query into the normalized tables. After you accomplish this lab, you will use the queries inside MERGE statements in Lab #10.

Business Scenario

Business analysts design import approaches to ensure the data moves from import source to target database model without corrupting the database. Whether or not you import into a relational database or a NoSQL database like MongoDB, the goal is to import the data to target tables or collections without corrupting or duplicating the data.

The MERGE statement lets you insert data when it doesn’t exist and update it when it already exists. The MERGE statement lets you insert records when you provide a source file that excludes the surrogate key and update records when you include a surrogate key value. You write an outer join statement because it provides an intersection and relative complement.

The intersection include a match to the surrogate key. The relative complement excludes a match to the surrogate key. That means you:

• Insert records into the target tables when they are found in the relative complement with the target table.
• Update records in target table when they are found in the intersection with the target table.