Transcription
Teradata
TeradataAbout the TutorialTeradata is a popular Relational Database Management System (RDBMS) suitable for largedata warehousing applications. It is capable of handling large volumes of data and is highlyscalable. This tutorial provides a good understanding of Teradata Architecture, variousSQL commands, Indexing concepts and Utilities to import/export data.AudienceThis tutorial is designed for software professionals who are willing to learn Teradataconcepts and become a Teradata developer. By the end of this tutorial, you will havegained intermediate level of expertise in Teradata.PrerequisitesYou should have a basic understanding of Relational concepts and basic SQL. It will begood if you have worked with any other RDBMS product.Copyright & Disclaimer Copyright 2018 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I)Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.comi
TeradataTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer . iTable of Contents . iiPART 1: TERADATA BASICS . 11.Teradata - Introduction . 2What is Teradata? . 2History of Teradata . 2Features of Teradata . 22.Teradata – Installation . 4Installation Steps for Windows. 4Starting BTEQ. 83.Teradata – Architecture . 9Components of Teradata . 9Storage Architecture . 10Retrieval Architecture . 114.Teradata – Relational Concepts. 125.Teradata – Data Types . 146.Teradata – Tables . 16Table Types . 16Create Table . 16Alter Table . 18Drop Table . 197.Teradata – Data Manipulation . 20Insert Records . 20Insert from Another Table . 21Update Records . 22Delete Records . 238.Teradata – SELECT Statement . 24WHERE Clause . 25ORDER BY. 25GROUP BY . 269.Teradata – Logical & Conditional Operators . 27BETWEEN . 28IN . 28NOT IN . 2910. Teradata – SET Operators . 30UNION. 30UNION ALL . 31ii
TeradataINTERSECT . 32MINUS/EXCEPT . 3311. Teradata – String Manipulation . 3512. Teradata – Date/Time Functions . 36Date Storage . 36EXTRACT . 36INTERVAL . 3713. Teradata – Built-in Functions . 3914. Teradata – Aggregate Functions . 4015. Teradata – CASE & COALESCE . 42CASE Expression . 42COALESCE . 43NULLIF. 4416. Teradata – Primary Index . 45Unique Primary Index (UPI) . 45Non Unique Primary Index (NUPI) . 4617. Teradata – Joins . 47INNER JOIN . 47OUTER JOIN . 48CROSS JOIN . 5018. Teradata – SubQueries . 51PART 2: TERADATA ADVANCED . 5319. Teradata – Table Types . 54Derived Table. 54Volatile Table . 55Global Temporary Table . 5520. Teradata – Space Concepts . 57Permanent Space . 57Spool Space . 57Temp Space . 5721. Teradata – Secondary Index . 58Unique Secondary Index (USI) . 58Non Unique Secondary Index (NUSI) . 5822. Teradata – Statistics . 59Collecting Statistics . 59Viewing Statistics . 6023. Teradata – Compression . 6124. Teradata – EXPLAIN . 62iii
TeradataExamples of EXPLAIN . 62Full Table Scan (FTS) .
Installation Steps for Windows. Step 1: Download the required VM version from the link, radata-express-for-vmware-player. Step 2: Extract the file and specify the target folder. Step 3: Download the VMWare Workstation player from the link, https://my.vmware.com/web/vmware/downloads.
