Case Study Examples - Teradata Database

Case Study Examples

Introduction

These examples represent actual cases from a Teradata customer site. The examples are changed slightly to avoid disclosing any business critical information, but the basic syntax and counts remain accurate.

The example EXPLAIN reports are altered slightly for clarity, replacing site-specific aliases with generic database names. The EXPLAIN reports do not reflect the current EXPLAIN text features. This does not detract from their value as an instructional tool.

Defining the Business Question

Before writing a complex query, you must understand the business question it is supposed to answer. The following text is a simple explanation of the business question being asked in the case study.

What are the customer numbers for all the customers in the customer table (which contains over 18 million rows)

AND

Who resides in district K

AND

Who has a service_type of ABC

OR

Who has a service_type of XYZ

AND

What is their monthly_revenue for July 1997 (using the revenue table, which contains over 234 million rows)

AND

If the monthly_revenue for a customer is unknown (no revenue rows are found), then report the customer number (from customer.cust_num) with a null (represented by a QUESTION MARK (?) character) to represent its monthly_revenue.

This sounds simple, but when you analyze your answer sets carefully, you might find them to be incorrect. As the following missteps (see “First Attempt” on page 285, “Second Attempt” on page 288, and “Third Attempt” on page 290) demonstrate, the results are correct, but the outer join queries that produce them are not asking the proper questions. This becomes more apparent as the examples are developed and the results analyzed.

The study presents two example queries for determining the reasonableness of the answers of this study, three missteps along the path to getting the correct answer, and a properly coded query that returns the correct answer for the original question that was asked.