Help Section
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
Help Section
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.
INSERTstatementSELECTstatementUPDATEstatement- Literal Fabrication
- Date Calculations
- Data Fabrication
- Aggregation
CASEOperator- Selective Aggregation
The following sections help you solve the logic sections of Step #4 in the lab:
Implementing Logic A: WHERE Clause
Instruction Details →
The logic involved in determining how to exclude rows from the result set involve examining when rows do and don’t exist in the data set.
| Table Name: PRICE | ||||||
|---|---|---|---|---|---|---|
| Distribution of Rows | ||||||
| Active Flag |
Rental Type |
New Release | Old Release | Total | ||
| <= 30 | > 30 | <= 30 | > 30 | |||
| Y | 1 | 3 | 21 | 24 | ||
| 3 | 3 | 21 | 24 | |||
| 5 | 3 | 21 | 24 | |||
| N | 1 | 21 | 21 | |||
| 3 | 21 | 21 | ||||
| 5 | 21 | 21 | ||||
| Total | 9 | 63 | 63 | 135 | ||
The logic represented in this problem is a standard business system. All items must maintain a price at the time of sale or rental, and prices change over time. Our simplified video store problem has only two prices for each type of rental. One price is the active price and the other the inactive price. All active prices have a start and end date values, but as a rule the active price has a null end date value. You need an active and inactive price for the three types of rentals – 1, 3, and 5 day rentals. Therefore, each row in the ITEM table requires:
- Three rows in the
PRICEtable when they’re within 30 days of their release date. - Six rows in the
PRICEtable when the current date is 31 days or more from their release date.
The following is illustrates the business and system logic that occurs after close of business on the 30th from the video’s release date. It shows how you inactivate the original row and how you add a new active row.
Without a WHERE clause, the sample fabrication query returns 144 rows. The WHERE clause should filter out those rows where the ACTIVE_FLAG is 'N' and the current date minus 30 is less than the item’s release date (a negation filter). The SELECT-list uses a CASE statements to determine the correct ACTIVE_FLAG and AMOUNT based on the relationship of other column values.
The WHERE clause requires that you determine how the following two things can exclude the 9 rows that can never occur.
- Whether the
active_flagcolumn is equal to a capital ‘N‘ or equal to a capital ‘Y‘. - Whether today is less than or equal to 30 days from the item release date with the following SQL formula:
(TRUNC(SYSDATE) - item.release_date) <= 30
or, whether today is greater than 30 days from the item release date with the following SQL formula:
(TRUNC(SYSDATE) - item.release_date) > 30
Implementing Logic B: Conditional Logic for the start_date column
Instruction Details →
The logic involved in determining how to calculate the start_date that will eventually become the start_date for each row in the price table.
The release date of an item is the start date of the new prices, while the start date for an old price is the release date plus 31. A DATE column is a date-time value in an Oracle database. The integer value is the date and the decimal value represents the hours, minutes, seconds, deca seconds, and hundredths of a second. As qualified in the Date data type tutorial, you can use the TRUNC() function to remove the decimal portion of a DATE data type.
You can calculate whether today is less than or equal to 30 days from the item release date with the following SQL formula:
(TRUNC(SYSDATE) - item.release_date) <= 30 |
You can calculate whether today is greater than 30 days from the item release date with the following SQL formula:
(TRUNC(SYSDATE) - item.release_date) > 30 |
You use the release_date as the value when today is less than or equal to 30 days. You use the release_date plus 31 as the value when today is more than 30 days from the release_date value in the item table and the active_flag value is ‘N‘ (or no).
Implementing Logic C: Conditional Logic for the end_date column
Instruction Details →
The logic involved in determining how to calculate the end_date that will eventually become the end_date for each row in the price table.
You calculate the end date one of two ways:
While there are three logical states, they can be solved by examining when you do set an end date. You would need to combine two logical comparisons in a CASE operator’s WHEN clause, like:
(TRUNC(SYSDATE) - item.release_date) > 30 |
and
active_flag = 'N' = 30 |
The CASE operator would exclude an ELSE condition because the end date would always be null and that’s what the case statement returns when a condition is not explicitly managed.
Implementing Logic D: Conditional Logic for the amount column
Instruction Details →
The logic involved in determining how to calculate the amount that will eventually become the amount for each row in the price table.
You determine the amount for the price table by checking whether the current day is more than 30 days from the start date and the active_flag value is ‘Y’ then the $1, $3, and $5 values apply when the rental_days value equals a 1, 3, and 5 day rental value. The 1, 3, or 5 day element becomes a nested CASE operator that matches rental_days column values.
You use the same nested CASE operator in the ELSE clause but assign the values $3, $10, and $15 to the 1, 3, and 5 day rental value fields respectively.
