Master Teradata- A Complete Course on Teradata

Learn the basics of Teradata, a scalable relational database with parallel processing and open architecture, including data distribution across processors and BI tool compatibility.

Develop a foundational understanding of database concepts, relational database management theory, and RDBMS knowledge, and prepare to work with data, including 3D data sets.

Trace the history of Teradata from a 1979 Brentwood garage to 130 terabytes across 176 nodes by 2002, including 1984 Wells Fargo shipping and 2006 index and compression innovations.

Explore Teradata’s massively parallel processing and shared-nothing architecture, enabling linear scalability across thousands of nodes. Discover the built-in optimizer and robust utilities for automatic data distribution, import, and export.

Discover Teradata's massively parallel processing architecture, featuring nodes with OS, memory, RDBMS software, and storage, a parsing engine, and MP X processors that store, retrieve, and manage data.

Explore relational concepts in RDBMS, including the structure of tables with rows and columns, and keys such as primary keys and foreign keys that link tables.

Explore Teradata datatypes, including integer, decimal, numeric, real and double precision, fixed and variable character types, and date and time formats with fractional seconds.

Trace the lifecycle of a Teradata query from SQL submission as a parcel through session setup, security checks, optimization, dispatch, parallel execution, and final result delivery.

Install Teradata by downloading Studio Express, creating an account, extracting files, launching a virtual machine, and configuring the local database connection profile.

Explore how relational tables store data as rows and columns, compare default and global temporary tables with session lifetimes, and distinguish set versus multiset handling of duplicates.

Create a table within a Teradata database using the create table command, detailing database creation steps, space considerations including spool space, and defining table columns.

Explore the alter table command to modify an existing table by adding or dropping columns and changing column attributes, with syntax examples on a sample school table.

Learn how to use the drop table command in Teradata, understand the generic syntax, and verify that a dropped table no longer exists in the database.

Learn to insert records with insert into, including inserting from another table via select, specify column lists, manage values and NULLs, and ensure data type compatibility.

Learn how to update records using the data statement, including set and where conditions, and see how omitting a condition can affect rows.

Learn to delete records from a table using delete statement, with syntax like delete from table where condition, and remove a row by roll number 15 in the school.students example.

Learn how to use the select statement to retrieve records from a table, selecting specific columns like first name and last name, or all columns with select * from school.student.

Learn to use the where clause to filter rows and write select statements in Teradata, including from, where, and simple conditions, to retrieve specific data.

Master Teradata teaches how to use the order by clause to sort query results in ascending or descending order, with examples on selecting from a table.

The group by clause groups rows by department number and counts occurrences for each group, demonstrated with a select query using group by department number.

This lecture explains logical and conditional operators in Teradata, including and, or, not, and comparison operators such as between and in, with example queries to filter data.

Explore set operators and union, compare them with joins, and learn how union combines results from multiple select statements with matching columns, with order by only in the final select.

Explore how union all combines results from multiple queries, including duplicates. Compare it with union and learn how to implement a union all statement in practice.

Explore the sql intersect operator, showing how to use select and from with conditions to return records that exist in both queries, illustrated through a data set example.

Explore the minus and except set operators in Teradata to subtract rows between queries. Learn how to implement the minus statement, select specific columns from a table, and apply conditions.

Master Teradata string manipulation by using key functions such as substring, concatenate, index, trim, and case conversions to extract, join, and format strings.

Explore date and time functions in Teradata, including extract, current_date, and arithmetic with intervals, and learn how dates are stored as integers.

Discover built-in Teradata functions, including date and time helpers such as current date, current time, and current timestamp, and learn to query the database name with select statements.

Master Teradata covers aggregate functions such as count, max, min, and avg, demonstrated through select statements on school.marks to compute records, maximum, minimum, and average values.

Explore case expressions in Teradata, using when, then, and else to evaluate conditions, implement logic in select statements, and transform department data with conditional results.

Define a primary index to specify where data is, choose between unique primary index (UPI) and non-unique primary index, and recreate a table with the selected primary index.

Explore how joins combine records from multiple tables using inner, left, right, full outer, cross, and self-join types. Learn join syntax with on conditions and aliases for selecting columns.

Explore subqueries in Teradata, using inner and outer queries to fetch related records. Learn to apply aggregation functions like max to identify top records and return corresponding student data.

Explore permanent journal concepts in Teradata, including capturing table snapshots before and after changes, rollback and undo support, and table level snapshot options for data integrity.

Explore two recovery coverage types: downtime recovery with a table fallback and transaction journals that capture the before image to enable rollback.

Master Teradata locking mechanisms that guard data integrity as multiple users access tables. Learn exclusive, write, read, and access locks at table and row levels, plus hash-based deadlock prevention.

The lecture explains how read and write locks are applied to table data, how gatekeepers manage access to prevent deadlocks, and how hashing and explain plans reveal locking behavior.

Explore how explain plan analyzes query execution in Teradata, revealing access paths, joins, confidence levels, and time estimates to identify performance issues before running queries.

Explore three Teradata table types, including derived tables and global temporary tables, which store intermediate query results within a session, with creation syntax and column and index definitions.

Explore space concepts in Teradata, including space, spoiler space, and d.m. space. Understand how maximum space is allocated, inherited from the parent, and used by global temporary tables.

Learn how secondary indexes in Teradata differ from primary indexes, including their storage in tables, not involved in data distribution, and when to use unique and non-unique secondary indexes.

Discover how the Teradata optimizer uses statistics to determine an execution strategy. Learn to collect and view table statistics, including sample and summary statistics, on tables like the student table.

Learn how multivalue compression (MVC) uses column-level, dictionary-based compression to replace repeated values with bit patterns, cutting storage and I/O while weighing compression overhead.

Explore block-level compression in Teradata and its roughly 60 percent space reduction. Learn how compressed data blocks decompress on access and how to toggle with set queries.

Explore ELC compression, a column-level technique for compressing data on columns such as Unicode, and see how compression and decompression occur during data block creation and access.

Discover how Teradata hashing uses a primary index and a hash map with buckets to locate records, assign a uniqueness id on collisions, and sort data by hash.

Explore join indexes that store columns from tables to speed up joins. Define partial or full replication with a different primary index, and note how the parsing engine decides usage.

Learn how to create, replace, and drop views in Teradata, retrieve and modify data through views, and use views to secure access and simplify multi-table queries.

Learn how to create and execute macros in Teradata by grouping SQL statements into reusable macro definitions, supporting parameters and a single-transaction execution.

Explore join strategies such as merge join, hash join, and nested join, and learn how join conditions, indexes, and distribution affect performance.

Explore partitioned primary index (PPI): integrity, partition expressions, and hash rules that improve query performance, and note unpartitioned primary index (UPI) advantages for fast scans and quick data updates.

Explore olap functions in Teradata, mastering aggregate and rank functions, running totals and percentages, and learn partitioned by and over clause syntax for ordered data.

Learn how Teradata protects data from transaction failures using before images and rollback, with foldback fallbacks across nodes, fallback options, and mirror disk, ensuring data availability and automatic synchronization.

Create Teradata users with names, passwords, and storage quotas. Grant and revoke privileges on database objects using the syntax for privileges such as select, update, and references.

Analyze query performance with explain plan, collect statistics on join and filter columns, and index and partition keys; ensure data type compatibility, remove unnecessary order by, and manage spool space.

Explore BTEQ, a command-driven utility for interacting with Teradata systems, enabling batch or interactive work, script-based job submission with error checking, conditional logic, and data import/export.

Load large datasets into Teradata with fastload by meeting prerequisites, including an empty target table, using a two-phase process with work and target tables, and handling errors and duplicates.

Master Teradata multiload enables fast high-volume data loading with update, delete, and insert operations. It supports import from multiple sources and processing with checkpoints and rollback, without enforcing referential integrity.

Master Teradata FastExport demonstrates exporting data from tables or views to a file using multi-session, 64-block transfers, up to 15 concurrent jobs, supports multiple select statements, and no conditional logic.