Case Study Examples - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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 topic demonstrates, 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.