Teradata Database SQL Fundamentals - …

1 Teradata DatabaseSQL FundamentalsRelease 2010 The product or products described in this book are licensed products of Teradata Corporation or its , BYNET, DBC/1012, DecisionCast, DecisionFlow, DecisionPoint, Eye logo design, InfoWise, Meta Warehouse, MyCommerce, SeeChain, SeeCommerce, SeeRisk, Teradata Decision Experts, Teradata Source Experts, WebAnalyst, and You ve Never Seen Your Business Like This Before are trademarks or registered trademarks of Teradata Corporation or its and SCSIS elect are trademarks or registered trademarks of Adaptec, Opteron and Opteron are trademarks of Advanced Micro Devices, and NetVault are trademarks or registered trademarks of BakBone Software, , PowerPath, SRDF, and Symmetrix are registered trademarks of EMC is a trademark of GoldenGate Software, and HP are registered trademarks of Hewlett-Packard , Pentium, and XEON are registered trademarks of Intel , CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines is a registered trademark of Linus and Engenio are registered trademarks of LSI , Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other and SUSE are registered trademarks of Novell, Inc.

2 , in the United States and other and SANbox are trademarks or registered trademarks of QLogic and SAS/C are trademarks or registered trademarks of SAS Institute is a registered trademark of SPARC International, Microsystems, Solaris, Sun, and Sun Java are trademarks or registered trademarks of Sun Microsystems, Inc., in the United States and other , NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other is a collective membership mark and a service mark of Unicode, is a registered trademark of The Open Group in the United States and other product and company names mentioned herein may be the trademarks of their respective INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN AS-IS BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT.

3 SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. IN NO EVENT WILL Teradata CORPORATION BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document.

4 Please e-mail: comments or materials (collectively referred to as Feedback ) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating 2000 - 2010 by Teradata Corporation. All Rights Fundamentals3 PrefacePurposeSQL Fundamentals describes basic Teradata SQL concepts, including data handling, SQL data definition, control, and manipulation, and the SQL lexicon. Use this book with the other books in the SQL book administrators, Database administrators, security administrators, application programmers, Teradata field engineers, end users, and other technical personnel responsible for designing, maintaining, and using Teradata Database will find this book SQL users can also see simplified statement, data type, function, and expression descriptions in SQL Quick Software ReleaseThis book supports Teradata Database If you are not familiar with Teradata Database , you will find it useful to read Introduction to Te r a d a t a before reading this book.

5 You should be familiar with basic relational Database management technology. This book is not an SQL to This BookReleaseDescriptionTeradata Database 2010 Updated Appendix E with changes to SQL to This Book4 SQL FundamentalsTeradata Database 2009 Deleted concurrent transaction limit from Appendix C. Deleted system maximum for replication groups from Appendix Database 2009 Provided correct system maximum for replication Database 2009 Added material to support No Primary Index (NoPI) tables Added material to support Period and Geospatial data types Changed material on UDFs to include Java UDFs and dynamic UDTs Changed material on stored procedure access rights and default Database Added new material to support changes to triggers Modified material on archiving and restoring views, macros, stored procedures, and triggers Updated list of DDL and DCL statements Removed restrictions regarding compression on foreign key columns and the USI primary key columns for soft and batch referential integrity Updated ANSI SQL:2003 references to ANSI SQL.

6 2008, including reserved and nonreserved words Updated Appendix E with changes to SQL syntaxTeradata Database 2007 Changed QUERY_BAND from a reserved word to a nonreserved wordTeradata Database 2007 Changed topic on index hash mapping to reflect increased hash bucket size of 20 bits (1048576 hash buckets) Changed the rules on naming objects in Chapter 2 in support of the UNICODE Data Dictionary feature Changed the description of profiles to include the option to specify whether to restrict a password string from containing certain words Added material to support mutilevel partitioned primary indexes Added material to support error logging tables for errors that occur during INSERT .. SELECT and MERGE operations Removed material on Express Logon Updated Appendix E with the following: New statements for the online archive feature: LOGGING ONLINE ARCHIVE ON and LOGGING ONLINE ARCHIVE OFF New statements for error logging tables: CREATE ERROR TABLE and DROP ERROR TABLE LOGGING ERRORS option for INSERT.

7 SELECT and MERGE New syntax for CREATE PROCEDURE and REPLACE PROCEDURE to support C and C++ external stored procedures that use CLIv2 to execute SQL, Java external stored procedures, and dynamic result setsReleaseDescriptionPrefaceAdditional InformationSQL Fundamentals5 Additional InformationTo maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please e-mail: Updated Appendix E with the following: New SET QUERY_BAND statement New syntax for ALTER TABLE, CREATE TABLE, and CREATE JOIN INDEX to support multilevel partitioned primary the Teradata Information Products Publishing Library site to: View or download a manual:1 Under Online Publications, select General your search criteria and click Search. Download a documentation CD-ROM:1 Under Online Publications, select General the Title or Keyword field, enter CD-ROM, and click Search. Order printed manuals:Under Print & CD Publications, select How to Teradata home page provides links to numerous sources of information about Teradata .

8 Links include: Executive reports, case studies of customer experiences with Teradata , and thought leadership Technical information, solutions, and expert advice Press releases, mentions and media Customer Education designs, develops and delivers education that builds skills and capabilities for our customers, enabling them to maximize their Teradata Teradata @ Your Service to access Orange Books, technical alerts, and knowledge repositories, view and join forums, and download software Developer Exchange provides articles on using Teradata products, technical discussion forums, and code to Microsoft Windows and Linux6 SQL FundamentalsReferences to Microsoft Windows and LinuxThis book refers to Microsoft Windows and Linux. For Teradata Database , these references mean: Windows is Microsoft Windows Server 2003 64-bit. Linux is SUSE Linux Enterprise Server 9 and SUSE Linux Enterprise Server Fundamentals7 Table of ContentsPreface.

9 3 Purpose ..3 Audience ..3 Supported Software Release ..3 Prerequisites ..3 Changes to This Book ..3 Additional Information ..5 References to Microsoft Windows and Linux ..6 Chapter 1: Objects .. 13 Databases and Users .. 13 Tables .. 14 Global Temporary Tables .. 18 Volatile Tables .. 23 Columns.. 25 Data Types .. 27 Keys .. 32 Indexes .. 33 Primary Indexes .. 38 Secondary Indexes.. 42 Join Indexes .. 47 Hash Indexes .. 50 Referential Integrity .. 53 Views .. 59 Triggers .. 60 Macros .. 62 Stored Procedures .. 65 External Stored Procedures .. 69 User-Defined Functions .. 69 Profiles .. 70 Table of Contents8 SQL FundamentalsRoles ..72 User-Defined Types ..74 Chapter 2: Basic SQL Syntax and Lexicon ..75 Structure of an SQL Statement ..75 SQL Lexicon Characters ..76 Keywords ..77 Expressions ..78 Names ..79 Name Validation on Systems Enabled with Japanese Language Support ..83 Object Name Translation and Storage.

10 87 Object Name Comparisons ..88 Finding the Internal Hexadecimal Representation for Object Names ..89 Specifying Names in a Logon String ..90 Standard Form for Data in Teradata Database ..91 Unqualified Object Names ..93 Default Database ..95 Literals ..97 NULL Keyword as a Literal ..101 Operators ..102 Functions ..104 Delimiters ..104 Separators ..106 Comments ..106 Terminators ..108 Null Statements ..110 Chapter 3: SQL Data Definition, Control, and Manipulation .111 SQL Functional Families and Binding Styles ..111 Embedded SQL ..112 Data Definition Language ..113 Altering Table Structure and Definition ..115 Dropping and Renaming Objects ..116 Data Control Language ..118 Table of ContentsSQL Fundamentals9 Data Manipulation Language .. 119 Subqueries .. 123 Recursive Queries .. 124 Query and Workload Analysis Statements .. 128 Help and Database Object Definition Tools.. 129 Chapter 4: SQL Data Handling.. 133 Invoking SQL Statements.