O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Tera-Tom on Teradata SQL V12-V13

Book Description

Want to know everything about Teradata SQL? This is the number one selling book on Teradata of all time. The clear and simple explanations covering all topics will vault you to an expert level in no time.

Table of Contents

  1. Cover
  2. Copyright
  3. Chapter 1 - Teradata Parallel Architecture
    1. Teradata Introduction
    2. Teradata Architecture
      1. Teradata Components
        1. Parsing Engine Processor (PEP or PE)
        2. Access Module Processor (AMP)
        3. Message Passing Layer (BYNET)
    3. A Teradata Database
      1. CREATE / MODIFY DATABASE Parameters
    4. Teradata Users
      1. { CREATE | MODIFY } DATABASE or USER (in common)
      2. { CREATE | MODIFY } USER (only)
    5. Symbols Used in this Book
    6. DATABASE Command
    7. Use of an Index
      1. Primary Index
      2. Secondary Index
    8. Determining the Release of Your Teradata System:
    9. Teradata Limits
  4. Chapter 2 - Fundamental SQL Using SELECT
    1. Fundamental Structured Query Language (SQL)
    2. Basic SELECT Command
    3. WHERE Clause
    4. Compound Comparisons ( AND / OR )
    5. Impact of NULL on Compound Comparisons
    6. Using NOT in SQL Comparisons
    7. Multiple Value Search (IN)
      1. Using NOT IN
    8. Using Quantifiers Versus IN
    9. Multiple Value Range Search (BETWEEN)
    10. Character String Search (LIKE)
    11. Derived Columns
    12. Creating a Column Alias Name
      1. AS
      2. NAMED
      3. Naming conventions
      4. Breaking Conventions
    13. ORDER BY
    14. TOP Rows Option
    15. DISTINCT Function
  5. Chapter 3 - On-line HELP and SHOW Commands
    1. HELP commands
      1. SET SESSION command
    2. SHOW commands
    3. EXPLAIN
    4. Adding Comments
      1. ANSI Comment
      2. Teradata Comment
    5. User Information Functions
      1. ACCOUNT Function
      2. DATABASE Function
      3. SESSION Function
  6. Chapter 4 - Data Conversions
    1. Data Conversions
    2. Data Types
    3. CAST
    4. Implied CAST
    5. Formatted Data
    6. Formatted Data for Day-Month-Year
      1. Tricking the ODBC to Allow Formatted Data
    7. TITLE Attribute for Data Columns
    8. Transaction Modes
    9. Case Sensitivity of Data
    10. CASESPECIFIC
    11. LOWER Function
    12. UPPER Function
  7. Chapter 5 - Aggregation
    1. Aggregate Processing
      1. Math Aggregates
        1. The SUM Function
        2. The AVG Function
        3. The MIN Function
        4. The MAX Function
        5. The COUNT Function
      2. Aggregates and Derived Data
    2. GROUP BY
    3. Limiting Output Values Using HAVING
      1. V12 GROUP BY Options
        1. GROUP BY GROUPING SETS
        2. GROUP BY ROLLUP
        3. GROUP BY CUBE
    4. Statistical Aggregates
      1. The KURTOSIS Function
      2. The SKEW Function
      3. The STDDEV_POP Function
      4. The STDDEV_SAMP Function
      5. The VAR_POP Function
      6. The VAR_SAMP Function
      7. The CORR Function
      8. The COVAR Function
      9. The REGR_INTERCEPT Function
      10. The REGR_SLOPE Function
      11. Using GROUP BY
      12. Use of HAVING
    5. Using the DISTINCT Function with Aggregates
    6. Aggregates and Very Large Data Bases (VLDB)
      1. Potential of Execution Error
      2. GROUP BY versus DISTINCT
    7. Performance Opportunities
  8. Chapter 6 – Subquery Processing
    1. Subquery
      1. Using NOT IN
      2. Using Quantifiers
    2. Qualifying Table Names and Creating a Table Alias
      1. Qualifying Column Names
      2. Creating an Alias for a Table
    3. Correlated Subquery Processing
      1. Correlated Subquery To Find Duplicate Values
    4. EXISTS
  9. Chapter 7 – Join Processing
    1. Join Processing
    2. Original Join Syntax
    3. Product Join
    4. Newer ANSI Join Syntax
      1. INNER JOIN
      2. OUTER JOIN
        1. LEFT OUTER JOIN
        2. RIGHT OUTER JOIN
        3. FULL OUTER JOIN
        4. CROSS JOIN
      3. Self Join
      4. Alternative JOIN / ON Coding
    5. Adding Residual Conditions to a Join
      1. INNER JOIN
      2. OUTER JOIN
    6. OUTER JOIN Hints
    7. Parallel Join Processing
    8. Join Index Processing
  10. Chapter 8 – Date and Time Processing
    1. ANSI Standard DATE Reference
    2. INTEGERDATE
    3. ANSIDATE
    4. DATEFORM
      1. System Level Definition
      2. User Level Definition
      3. Session Level Declaration
    5. DATE Processing
      1. ADD_MONTHS
    6. ANSI TIME
    7. EXTRACT
    8. Implied Extract of Day, Month and Year
    9. ANSI TIMESTAMP
    10. TIME ZONES
      1. Setting TIME ZONES
      2. Using TIME ZONES
      3. Normalizing TIME ZONES
    11. DATE and TIME Intervals
      1. Using Intervals
      2. INTERVAL Arithmetic with DATE and TIME
      3. CAST Using Intervals
    12. OVERLAPS
    13. System Calendar
  11. Chapter 9 – Character String Processing
    1. Transforming Character Data
    2. CHARACTERS Function
    3. CHARACTER_LENGTH Function
    4. OCTET_LENGTH Function
    5. TRIM
    6. SUBSTRING
    7. SUBSTR
    8. SUBSTRING and Numeric Data
    9. POSITION
    10. INDEX
    11. SUBSTRING and POSITION or INDEX Used Together
    12. Concatenation of Character Strings
  12. Chapter 10 – OLAP Functions
    1. On-Line Analytical Processing (OLAP) Functions
    2. OLAP Functions
    3. Cumulative Sum Using the CSUM Function
      1. Cumulative Sum with Reset Capabilities
        1. Using CSUM and GROUP BY
      2. Generating Sequential Numbers with CSUM
    4. Moving Sum Using the MSUM Function
      1. Moving Sum with Reset Capabilities
        1. Using MSUM and GROUP BY
    5. Moving Average Using the MAVG Function
      1. Moving Average with Reset Capabilities
        1. Using MAVG and GROUP BY
    6. Moving Difference Using the MDIFF Function
      1. Moving Difference with Reset Capabilities
        1. Using MDIFF and GROUP BY
    7. Cumulative and Moving SUM Using SUM / OVER
      1. Cumulative Sum with Reset Capabilities
        1. SUM Using SUM / OVER and PARTITION BY
    8. Moving Average Using AVG / OVER
      1. Moving Average with Reset Capabilities
        1. Using AVG and OVER / ROWS and PARTITION BY
    9. Moving Linear Regression Using the MLINREG Function
    10. Categorizing Data Using the QUANTILE Function
      1. QUALIFY to Find Products in the top Partitions
    11. Ranking Data using RANK
      1. QUALIFY to Find Top Best or Bottom Worse
      2. RANK with Reset Capabilities
        1. Using RANK with GROUP BY
    12. Ranking Data using RANK / OVER
      1. QUALIFY to Find Top Best or Bottom Worse
      2. RANK with Reset Capabilities
        1. Using RANK/OVER with PARTITION BY
    13. Internal RANK operations
    14. Percentage of Total Rows Using PERCENT_RANK / OVER
      1. Percent Rank with Reset Capabilities
        1. Using PERCENT_RANK OVER and PARTITION BY
    15. Counting of the Total Rows Using COUNT / OVER
      1. COUNT OVER with Reset Capabilities
        1. Using COUNT OVER and PARTITION BY
    16. Finding the Largest Value Using MAX / OVER
    17. Finding the Smallest Value Using MIN / OVER
    18. Numbering of the Rows Using ROW_NUMBER / OVER
    19. Sampling Rows using the SAMPLE Function
    20. RANDOM Number Generator Function
  13. Chapter 11 – SET Operators
    1. Set Operators
    2. Considerations for Using Set Operators
    3. INTERSECT
    4. UNION
    5. EXCEPT
    6. MINUS
    7. Using Multiple Set Operators in a Single Request
  14. Chapter 12 – Data Manipulation
    1. Data Maintenance
      1. Considerations for Data Maintenance
      2. Safeguards
    2. INSERT Command
      1. Using Null for DEFAULT VALUES
    3. INSERT / SELECT Command
      1. Fast Path INSERT / SELECT
    4. UPDATE Command
      1. Fast Path UPDATE
    5. DELETE Command
      1. Fast Path DELETE
    6. UPSERT
    7. MERGE
    8. ANSI Vs Teradata Transactions
    9. Performance Issues With Data Maintenance
      1. Impact of FALLBACK on Row Modification
      2. Impact of PERMANENT JOURNAL Logging on Row Modification
      3. Impact of Primary Index on Row Modification
      4. Impact of Secondary Indices on Row Modification
  15. Chapter 13 – Data Interrogation
    1. Data Interrogation
    2. NULLIFZERO
    3. NULLIF
    4. ZEROIFNULL
    5. COALESCE
    6. CASE
      1. Flexible Comparisons within CASE
      2. Comparison Operators within CASE
      3. CASE for Horizontal Reporting
      4. Nested CASE Expressions
      5. CASE used with the other DML
    7. Using CASE to avoid a join
  16. Chapter 14 – View Processing
    1. Views
      1. Reasons to Use Views
      2. Considerations for Creating Views
    2. Creating and Using VIEWS
    3. TOP Command allows ORDER BY in the View
    4. Deleting Views
    5. Modifying Views
    6. Modifying Rows Using Views
      1. DML Restrictions when using Views
      2. INSERT using Views
      3. UPDATE or DELETE using Views
      4. WITH CHECK OPTION
    7. Locking and Views
  17. Chapter 15 – Macro Processing
    1. Macros
    2. CREATE MACRO
    3. REPLACE MACRO
    4. EXECUTE Macro
    5. DROP MACRO
    6. Generating SQL from a Macro
  18. Chapter 16 – Transaction Processing
    1. What is a Transaction
    2. Locking
    3. Transaction Modes
    4. Comparison Chart
    5. Setting the Transaction Mode
    6. Teradata Mode Transactions
    7. ANSI Mode Transactions
    8. Aborting Teradata Transactions
    9. Aborting ANSI Transactions
  19. Chapter 17 – Reporting Totals and Subtotals
    1. Totals and Subtotals
    2. Totals (WITH)
    3. Subtotals (WITH…BY)
      1. Multiple Subtotals on a Single Break
      2. Multiple Subtotal Breaks
  20. Chapter 18 – Data Definition Language
    1. Creating Tables
    2. Table Considerations
      1. Maximum Columns per Table
    3. Table Naming Conventions
    4. CREATE TABLE
      1. Column Data Types
      2. Specifying the Database in a CREATE TABLE Statement
    5. PRIMARY INDEX considerations
      1. CREATE TABLE AS
      2. Table Type Specifications of SET VS MULTISET
        1. SET and MULTISET Tables
    6. Protection Features
      1. FALLBACK
      2. Permanent Journal
        1. BEFORE Journal
        2. AFTER Journal
    7. Internal Storage Options
      1. DATABLOCKSIZE
      2. FREESPACE PERCENTAGE
    8. QUEUE Tables
    9. Partitioned Primary Index (PPI)
    10. SQL for Partitioned Primary Index (PPI)
      1. Adding and Dropping Partitions
    11. Column Attributes
    12. Constraints
      1. UNIQUE Constraint
      2. CHECK Constraint
      3. Referential Integrity (RI) Constraint
      4. Defining Constraints at the Column level
      5. Defining Constraints at the Table Level
    13. Utilizing Default Values for a Table
    14. Secondary Indices
    15. CREATE TABLE to Copy an existing table
    16. Altering a Table
      1. Modifying the Primary Index Partitioning
      2. Revalidating the Primary Index
    17. Dropping a Table
      1. Dropping a Table versus Deleting Rows
    18. Renaming a Table
    19. Creating Secondary via CREATE INDEX
    20. Join Index
    21. Collecting Statistics
    22. Hashing Functions
      1. HASHROW
      2. HASHBUCKET
      3. HASHAMP
      4. HASHBAKAMP
  21. Chapter 19 – Temporary Tables
    1. Temporary Tables
    2. Temporary Table Choices
    3. Derived Tables
      1. Derived Tables Using a Non-Recursive WITH
      2. Derived Tables Using a Recursive WITH
      3. FROM TABLE UDF Tables
    4. Volatile Temporary Tables
    5. Global Temporary Tables
      1. GLOBAL Temporary Table Examples
    6. General Practices for Temporary use Tables
  22. Chapter 20 – Trigger Processing
    1. Triggers
      1. Terminology
      2. Logic Flow
    2. CREATE TRIGGER Syntax
      1. Row Trigger
      2. Statement Trigger
      3. ‘BEFORE’ Trigger
      4. ‘AFTER’ Trigger
      5. ‘INSTEAD OF’ Trigger
    3. Cascading Triggers
    4. Sequencing Triggers
  23. Chapter 21 – Stored Procedures
    1. Teradata Stored Procedures
    2. CREATE PROCEDURE
    3. Stored Procedural Language (SPL) Statements
      1. BEGIN / END Statements
    4. Establishing Variables and Data Values
      1. DECLARE Statement to Define Variables
      2. SET to Assign a Data Value as a Variable
      3. Status Variables
      4. Assigning a Data Value as a Parameter
    5. Additional SPL Statements
      1. CALL Statement
      2. CASE / END CASE Statement
      3. IF / END IF Statement
      4. LOOP / END LOOP Statements
      5. LEAVE Statement
      6. REPEAT / END REPEAT Statement
      7. WHILE / END WHILE Statement
      8. FOR / END FOR Statements
      9. ITERATE Statement
    6. Using a Cursor
      1. DECLARE CURSOR Statement
      2. OPEN CURSOR Statement
      3. CLOSE CURSOR Statement
      4. FETCH Statement
    7. Exception Handling
      1. DECLARE HANDLER Statement
      2. PRINT Statement
    8. DML Statements
    9. Using Column and Alias Names
    10. Comments and Stored Procedures
      1. Commenting in a Stored Procedure
      2. Commenting on a Stored Procedure
    11. On-line HELP for Stored Procedures
      1. HELP on a Stored Procedure
      2. HELP on Stored Procedure Language (SPL)
    12. REPLACE PROCEDURE
    13. ALTER PROCEDURE
    14. DROP PROCEDURE
    15. RENAME PROCEDURE
    16. SHOW PROCEDURE
    17. Dynamic SQL
    18. Considerations When Using Stored Procedures
    19. Compiling a Procedure
      1. Temporary Directory Usage
  24. Chapter 22 – Temporal Tables
    1. Temporal Tables
    2. There are three types of Temporal Tables:
    3. Valid Time Temporal Table
    4. Temporal Table Quiz
    5. Transaction Time Temporal Table
    6. Bi Temporal Tables
    7. SQL for Temporal Tables
  25. Here are your Temporal Table SQL Keywords designed to help query Temporal Tables.
    1. CURRENT
    2. AS OF
    3. SEQUENCED
    4. NONSEQUENCED