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

Lab Description

You re-enforce skills writing INSERT statements, learn how to use a correlated UPDATE statement to set all empty column values in a table, and learn how to use the CASE operator to perform conditional calculations.

The lab has four parts, which you find after clicking on the Lab Instructions link. The steps are:

• Step 1: Add two new rows for the active_flag column in the price table to the common_lookup table.
• Step 2: Add six new rows in the common_lookup table:
  1. Add three new rows for the price_type column in the price table to the common_lookup table.
  2. Add three new rows for the rental_item_type column in the rental_item table to the common_lookup table.

• Step 3: Update the rental_item_type column of the rental_item table with values derived from a correlated subquery.
• Step 4: Write a query that uses negation logic and uses three CASE operators in the SELECT-list; and you should resolve the query in the following sequence:
1. Solve the WHERE clause first by returning 135 rows from query, which you can do with the following SELECT-list:

   COLUMN row_count FORMAT 99999 HEADING "Row|Count" SELECT COUNT(*) AS row_count FROM item i CROSS JOIN (subquery) CROSS JOIN (subquery) JOIN (subquery) WHERE cl.common_lookup_table = 'PRICE' AND cl.common_lookup_column = 'PRICE_TYPE' AND NOT ( ... implement logic "A" ...) ORDER BY 1, 2, 3;

   COLUMN row_count FORMAT 99999 HEADING "Row|Count" SELECT COUNT(*) AS row_count FROM item i CROSS JOIN (subquery) CROSS JOIN (subquery) JOIN (subquery) WHERE cl.common_lookup_table = 'PRICE' AND cl.common_lookup_column = 'PRICE_TYPE' AND NOT ( ... implement logic "A" ...) ORDER BY 1, 2, 3;

   It should return:

   Row Count ----- 135

   Row Count ----- 135

2. Solve the CASE operator logic for the start_date column.
3. Solve the CASE operator logic for the end_date column.
4. Solve the CASE operator logic for the amount column.