Database Tutorial

Lab #4: Insert Patterns

Learn how to write INSERT statements that include literal values, sequence values, and scalar subqueries.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

The lab teaches you how to work with sequential and non-sequential INSERT statements across a dependent series of tables. Also, the lab teaches you how to call an API, use subqueries and literal values in the VALUES clause of the INSERT statement; and how to use a derived table instead of a table or view in a query and how to perform a correlated UPDATE statement.

The detail learning objectives are:

• Learn how to write a set of sequential INSERT statements across a set of dependent tables.
• Learn how to write a set of non-sequential INSERT statement by leveraging queries to discover appropriate primary key values in an independent table that you can use as foreign key values in a related dependent table.
• Learn how to use the .NEXTVAL pseudo column in one table as a primary key value; and the .CURRVAL pseudo column in another table with a dependency on the former table as a foreign key value.
• Learn how to call a store procedure that acts like an API (Application Programming Interface) to a set of tables.
• Learn how to write a subquery as a derived table (or the result of a table subquery) inside the FROM clause.
• Learn how to correlate (or join) the results of a subquery (a correlated subquery) with an external UPDATE statement.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

• Literal values
• Pseudo columns for sequence values
• Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the preseed_oracle_store.sql script as a reference model for SQL syntax.

Overview

The lab is designed to teach you how to write queries. Some queries join data based on the equality of values between rows, and others simply filter the product of cross joins.

Equijoins

Equijoins tie rows of tables together by matching primary and foreign key values. That means that equijoins work by checking the equality of two values. Equijoins only work when the database design supports:

• A primary key column or set of columns that hold a list of unique values.
• A foreign key column or set of columns that hold a copy of the single column or a set of columns that acts as another row’s primary key. That primary key can exist in another table or in the same table, and the latter case occurs with a recursive relationship.

Non-equijoins

Non-equijoins work by using a cross join. A cross join matches one row in one table with every row in another table, then it repeats the process by matching the next row in the first table with every row in the same table until it runs out of rows in the first table.

The non-equijoin then filters the rows returned from the cross join. Non-equijoins typically work with range comparison operation that:

• Check whether one value is greater than or less than another value.
• Check whether one value is between two other values.

Filtering inequality statements also work inside inner joins. All non-equijoin filters are inside the WHERE clause of a query. This lab will use only a non-equijoin inside a three table inner join statement.