Teradata Basics
- Teradata - SubQueries
- Teradata - Joins
- Teradata - Primary Index
- Teradata - CASE & COALESCE
- Teradata - Aggregate Functions
- Teradata - Built-in Functions
- Teradata - Date/Time Functions
- Teradata - String Manipulation
- Teradata - SET Operators
- Logical & Conditional Operators
- Teradata - SELECT Statement
- Teradata - Data Manipulation
- Teradata - Tables
- Teradata - Data Types
- Teradata - Relational Concepts
- Teradata - Architecture
- Teradata - Installation
- Teradata - Introduction
Teradata Advanced
- Teradata - BTEQ
- Teradata - FastExport
- Teradata - MultiLoad
- Teradata - FastLoad
- Teradata - Performance Tuning
- Teradata - User Management
- Teradata - Data Protection
- Teradata - OLAP Functions
- Teradata - Partitioned Primary Index
- Teradata - JOIN Strategies
- Teradata - Stored Procedure
- Teradata - Macros
- Teradata - Views
- Teradata - Join Index
- Teradata - Hashing Algorithm
- Teradata - Explain
- Teradata - Compression
- Teradata - Statistics
- Teradata - Secondary Index
- Teradata - Space Concepts
- Teradata - Table Types
Teradata Useful Resources
- Teradata - Discussion
- Teradata - Useful Resources
- Teradata - Quick Guide
- Teradata - Questions & Answers
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Teradata - Table Types
Teradata supports the following table types to hold temporary data.
Derived Table
Volatile Table
Global Temporary Table
Derived Table
Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.
Example
The following example builds a derived table EmpSal with records of employees with salary greater than 75000.
SELECT Emp.EmployeeNo, Emp.FirstName, Empsal.NetPay FROM Employee Emp, (select EmployeeNo , NetPay from Salary where NetPay >= 75000) Empsal where Emp.EmployeeNo = Empsal.EmployeeNo;
When the above query is executed, it returns the employees with salary greater than 75000.
*** Query completed. One row found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName NetPay ----------- ------------------------------ ----------- 103 Peter 83000
Volatile Table
Volatile tables are created, used and dropped within a user session. Their definition is not stored in data dictionary. They hold intermediate data of the query which is frequently used. Following is the syntax.
Syntax
CREATE [SET|MULTISET] VOALTILE TABLE tablename <table definitions> <column definitions> <index definitions> ON COMMIT [DELETE|PRESERVE] ROWS
Example
CREATE VOLATILE TABLE dept_stat ( dept_no INTEGER, avg_salary INTEGER, max_salary INTEGER, min_salary INTEGER ) PRIMARY INDEX(dept_no) ON COMMIT PRESERVE ROWS;
When the above query is executed, it produces the following output.
*** Table has been created. *** Total elapsed time was 1 second.
Global Temporary Table
The definition of Global Temporary table is stored in data dictionary and they can be used by many users/sessions. But the data loaded into global temporary table is retained only during the session. You can materiapze up to 2000 global temporary tables per session. Following is the syntax.
Syntax
CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename <table definitions> <column definitions> <index definitions>
Example
CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( dept_no INTEGER, avg_salary INTEGER, max_salary INTEGER, min_salary INTEGER ) PRIMARY INDEX(dept_no);
When the above query is executed, it produces the following output.
*** Table has been created. *** Total elapsed time was 1 second.Advertisements