Database Tutorial

Lab #7: SELECT-list Decision Making

Learn how to use the CASE operator to perform calculations in the SELECT-list of a query, re-enforce skills on how you use the INSERT statement, and learn how to update column values through correlated UPDATE statements.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

The lab is designed re-enforce skills writing INSERT statements, learn how to use a correlated UPDATE statement to set column values in a table, and learn how to use the CASE operator to perform conditional calculations any column of the SELECT-list of a SELECT statement.

Specifically, you should learn the following:

• Learn how to use the INSERT statement to add eight new rows in the common_lookup table.
• Learn how to use a correlated UPDATE statement to set the value of the rental_item_type column of the rental_item table.
• Learn how to write an exclusionary WHERE clause with parentheses for order of operation control.
• Learn how to write a SELECT-list statement that uses the CASE operator in several columns that perform conditional evaluation.

Business Scenario

Business analysts change existing table definitions to meet ever changing design needs that support day-to-day and reporting business requirements. Changes after initial design and implementation require you to add data to tables.

Changes in how database tables are organized after the entry of data becomes much more complex to maintain. You often need to add columns and migrate data from one table to another. Some of the changes requiring adding rows to tables with the INSERT statement. Other changes require you to add or change only the values of columns, which you do with the UPDATE statement.

Sometimes you can select the data to insert or update from existing tables. That process often requires you to use Cartesian Products (or CROSS JOINs) to add literal values as columns to the queries SELECT-list. You do that through data fabrication, and often use the literal values to perform computational logic, or decision making, in the SELECT-list. All non-aggregated columns in the SELECT-list act independently, which means you need to ensure the logic for each column follows the same pattern.

The patterns are typically two fold. Each column asks an affirmative logic question or a negation logic question. Typically, all columns ask affirmative or negation question, but sometimes one column may ask an affirmative question while another asks a negation question. Provided that all the columns return a consistent set of results for each row the logic works.

Overview

The lab is designed re-enforce skills writing INSERT statements, learn how to use a correlated UPDATE statement to set column values in a table, and learn how to use the CASE operator to perform conditional calculations any column of the SELECT-list of a SELECT statement.

The following reading and articles are important preparation for this lab. Please make sure you read them and understand the concepts and techniques in them.

• INSERT statement
• SELECT statement
• UPDATE statement
• Date Calculations
• Literal Fabrication
• Data Fabrication
• Aggregation
• CASE Operator
• Selective Aggregation

You will do the following four steps in the lab:

1. You will add three rows into the common_lookup table to support columns in the price and rental_item tables.
2. You will add six rows into the common_lookup table:
   • You can add three rows into the common_lookup table to support the price_type column in the price table.
   • You can add three rows into the common_lookup table to support the rental_item_type column in the rental_item table.

3. You will update the rental_item_type column in the rental_item table.
4. You will write a query that uses CASE operators to perform conditional operations in the SELECT-list and a negation filter in the WHERE clause.