The Sql Quick Reference Guide

The SQL Quick Reference Guide Simplicity by Design The Select Command The WHERE Clause Comparators: AND/OR Combining Comparators: AND/OR Combining Multiple AND Comparators or Multiple OR Comparators IS NULL Clause and IS NOT NULL Clause NOT Clause IN and NOT IN

= ANY, NOT = ALL BETWEEN LIKE Clause Aliasing Columns ALIAS using NAMED ORDER BY Clause DISTINCT

Help, Show, Explain, User Information Functions HELP SHOW User Information Functions Default Database Command Account SELECT Database Command SESSION

Data Conversions CAST – ANSI Implied Cast – Teradata FORMAT Tricking the ODBC to Format CASESPECIFIC NOT CASESPECIFIC LOWER and UPPER

Aggregation Functions SUM Function AVERAGE Function (AVG) MINIMUM Function (MIN) MAXIMUM Function (MAX) COUNT Function (COUNT) HAVING Clause Subquery Multiple Subqueries Quantifiers Correlated Subquery Exists

Joins Original Join Syntax ANSI INNER Join Syntax (Newer Syntax) Cartesian Product Join ANSI OUTER JOIN Syntax CROSS JOIN Syntax

Date and Time Processing INTEGERDATE Formula Selecting the DATE ADD_MONTHS Function EXTRACT Function TIME and TIMESTAMP System Calendar Columns

Character String Processing

CHARACTERS Function TRIM Function SUBSTRING and SUBSTR Functions POSITION and INDEX Functions INDEX Function String and Column Concatenation

OLAP Functions Cumulative Sum Function (CSUM) Moving Sum Function (MSUM) Moving Average Function (MAVG) Moving Difference Function (MDIFF) QUANTILE Function RANK Function SAMPLE Function and SAMPLEID RANDOM Function

Set Operator Commands INTERSECT UNION EXCEPT MINUS

Data Manipulation Language Commands INSERT Inserting NULL and Default Values INSERT / SELECT UPDATE UPDATE with a Subquery UPDATE with a Join DELETE Delete with a Join Delete with a Subquery UPSERT UPSERT for TPUMP and MULTILOAD

DATA INTERROGATION NULLIFZERO NULLIF ZEROIFNULL COALESCE CASE Basic Syntax Advanced Syntax Horizontal Reporting Nested CASE

Views Create View Syntax DROP Views REPLACE Views LOCKING FOR ACCESS

MACROS CREATE Macro EXECUTE Macro

REPLACE Macro DROP Macro

Transaction Processing Locking Modifiers Lock Types Lock Syntax Transaction Modes Transaction Mode Types Setting the Transaction Mode Teradata Transaction Mode (BT/ET) Commit Work Abort/Rollback Rollback Work ROLLBACK WORK ;

Reporting Totals and Subtotals Totals (WITH) Subtotals (WITH..BY) Multiple WITH..Bys

Data Definition Language Create Table Multiple Column Primary Index SET | MULTISET Table FALLBACK Permanent Journal Add Column Attributes Column and Table Constraints CREATE TABLE with copying an existing table ALTER Table DROP Table RENAME Table Secondary Indexes Join Indexes Collect Statistics HELP STATISTICS DROP STATISTICS CREATE DATABASE CREATE USER DROP DATABASE or USER Hashing Functions HASHROW HASHBUCKET HASHAMP HASHBAKAMP

Temporary Tables Derived Tables Volatile Temporary Tables Global Temporary Tables CREATE GLOBAL TEMPORARY TABLE DROP TEMPORARY TABLE

Triggers BEFORE Triggers AFTER Triggers INSTEAD OF Triggers

Sequencing Triggers

APPENDIX A: Current Reserved Words Current Reserved Words Future Reserved Words

APPENDIX B: Data Control Language (DCL) Data Control Language (DCL) Statements Privileges GRANT Statement REVOKE Statement GIVE Statement

Appendix C: Data Types and Format Symbols Format Symbols

Appendix D: Mathematical Functions Appendix E: Qualifying and Table Aliasing Qualifying Column Names Creating an Alias for a Table

Appendix F: Stored Procedure Syntax