Database Tutorial

Lab #8: Insert data from queries

Learn how to use an INSERT statement with a query of other data and an embedded sequence value. Reinforce how to alter a table’s column to add a NOT NULL constraint, update a column with a correlated UPDATE statement, learn how to use a correlated UPDATE statement to set an initial value in a newly added column.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

You learn how to match the SELECT-list sequence of values to the column-list of the price table. You re-enforce how to change a null allowed column into a NOT NULL allowed column by adding a column constraint. You re-enforce a skill learned in the prior lab by using a correlated UPDATE statement to populate a newly added column with correct values.

Specifically, you should learn the following:

• Learn how to use an INSERT statement with a subquery rather than a VALUES clause.
• Learn how to use the ALTER TABLE to change a nullable column into a NOT NULL constrained, or mandatory, column.
• Learn how to write a correlated UPDATE statement with the BETWEEN operator.

Business Scenario

Business analysts may create new tables to support an existing or new business requirement. The data for these tables often exists in one or more existing tables. You need to leverage queries to find existing data before directly inserting it into the new table.

Overview

The lab is designed to teach you how to write an INSERT statement with a subquery and how to fix remaining issues from Labs #6 and #7, like populating new columns with data and adding NOT NULL constraints to those columns after populating them with data.