Teradata DBA Assistant

Upgrade your skills with our Video Modules and become a proficient Teradata DBA ready to excel in any organization.

Teradata Query Building (Writing Teradata SQL Query)

1. How to Write Teradata SQL SELECT

The SQL SELECT statement is essential in retrieving data from tables within a database. This fundamental SQL command enables you to choose specific columns to retrieve and set criteria for the data to meet.

Explore the examples taken from the Dictionary Database DBC. This query will retrieve all tables in the database dbc, filtering by databasekind to only include databases with DBKIND='D'.

SELECT * FROM DBC.DATABASESV

WHERE DBKIND='D'

ORDER BY CREATETIMESTAMP DESC;

SELECT * FROM DBC.DATABASESV

WHERE DBKIND='D' AND CAST(CREATETIMESTAMP AS DATE) > '2023-01-01'

ORDER BY CREATETIMESTAMP DESC;

Explore the examples utilized from the Dictionary Database DBC. The query will retrieve all tables in the database dbc, filtered by databasekind 'D' and databases created after January 1, 2023.

SELECT * FROM DBC.DATABASESV

WHERE DBKIND='D' AND CAST(CREATETIMESTAMP AS DATE) > '2023-01-01'

ORDER BY CREATETIMESTAMP DESC;

The SELECT keyword is essential for fetching data from a database. It is followed by the names of the columns you wish to retrieve. You can specify multiple columns separated by commas or use * to select all columns. The FROM clause specifies the table from which data is retrieved. Refine your query using clauses like WHERE, ORDER BY, GROUP BY, and HAVING.

This video tutorial is about Teradata DDL Data Definition Language How the tables are created in Teradata database for storing data.

CREATE DATABASE PRACTICE_DB

FROM DBC AS PERM= 2000000000;

DATABASE PRACTICE_DB;

CREATE MULTISET TABLE DEPARTMENT ( DEPT_ID INTEGER, DEPT_NAME VARCHAR(30) )UNIQUE PRIMARY INDEX (DEPT_ID);

CREATE MULTISET TABLE EMPLOYEE ( EMPLOYEE_ID INTEGER, EMPLOYEE_NAME VARCHAR(50), DOB DATE format 'YYYY-MM-DD',

DEPT_ID INTEGER,

FOREIGN KEY (DEPT_ID) REFERENCES WITH CHECK OPTION PRACTICE_DB.DEPARTMENT(DEPT_ID) )UNIQUE PRIMARY INDEX (EMPLOYEE_ID);

INSERT INTO DEPARTMENT(DEPT_ID,DEPT_NAME)VALUES(33,'IT');

INSERT INTO EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_NAME,DOB,DEPT_ID) VALUES (333,'JOSUF','1992-04-11',22);

SELECT * FROM DEPARTMENT;

SELECT * FROM EMPLOYEE;

2. How to Create Teradata Database and How to Create Teradata Table

Before proceeding knowing difference between SET and MULTISET table is important.

SET tables enforce uniqueness of rows based on the entire row. They are useful when you want to ensure that each row in the table is unique. Teradata will reject duplicate rows in SET tables. It scans the complete table before inserting each row. It is always slower and recommended not to have SET table of big size.

MULTISET tables, on the other hand, allow duplicate rows and are more flexible in scenarios where duplicate rows may occur and need to be retained. It is recommended to define tables as MULTISET and control duplication through primary index.

3. Creating Table using Teradata BTEQ scripting

Teradata BTEQ is a widely-used scripting language by ETL teams for extracting, transforming, and loading data into the Teradata database from files.

Learn how to extract results from multiple tables as a single dataset in normalized form. As a Teradata DBA, you may often need to generate reports by combining data from various tables.

Utilize Joins to fulfill these reporting requirements effectively.

DATABASE PRACTICE_DB; SELECT * FROM DBC.TablesV WHERE DATABASENAME='PRACTICE_DB';

SELECT * FROM DEPARTMENT;

SELECT * FROM EMPLOYEE;

SELECT DEPT.DEPT_NAME, EMP.EMPLOYEE_NAME,EMP.DOB

FROM DEPARTMENT DEPT, EMPLOYEE EMP;

SELECT DEPT.DEPT_NAME, EMP.EMPLOYEE_NAME,EMP.DOB

FROM

DEPARTMENT DEPT INNER JOIN EMPLOYEE EMP ON (DEPT.DEPT_ID=EMP.DEPT_ID);

SELECT DEPT.DEPT_NAME, EMP.EMPLOYEE_NAME,EMP.DOB

FROM

DEPARTMENT DEPT LEFT JOIN EMPLOYEE EMP ON (DEPT.DEPT_ID=EMP.DEPT_ID); 

4. How to retrieve data from multiple tables using SQL JOIN in a SELECT statement

SQL utilizes joins to merge rows from different tables by linking them through related columns. The main join types are INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, Natural Join, and Non-Equi Join, facilitating efficient data retrieval across various tables.

5. Group By Clause

SQL employs the GROUP BY clause to group rows that share the same values into summary rows, typically in combination with aggregate functions like SUM, COUNT, AVG, MAX, or MIN. This is frequently used to calculate subsets of data or apply aggregate functions to groups of rows.

6. Create Table AS

The CREATE TABLE AS (CTAS) statement in Teradata serves the purpose of creating a fresh table based on the outcome of a SELECT statement. This functionality streamlines the process of table creation and data population by utilizing data from an existing table or query output.

7. Create Tables

A detailed overview of Creating Teradata Table into Teradata Database System

8. DDLs (Data Definition Language)

DDL, or Data Definition Language, is a subset of SQL (Structured Query Language) that is essential for defining, modifying, and overseeing the structure of database objects like tables, views, indexes, and schemas. Through DDL statements, you can create, alter, and drop database objects. The CREATE statement is used to create new database objects, ALTER for modifying existing ones, DROP for removing objects, and TRUNCATE to clear all rows from a table while keeping the structure intact. Database administrators (DBAs) and developers commonly use DDL statements to design and manage the database schema, encompassing tables, indexes, constraints, and more. It's crucial to handle DDL statements with care, especially in production environments, as they directly impact the database's structure.

9. Deploying Views To Teradata Database System

In Teradata, a view is a database object that represents a subset of data from one or more tables. It's essentially a virtual table that can be queried like a regular table, but its contents are defined by a query. Views are useful for several reasons: Data Abstraction, Security, Simplified Queries, and Performance Optimization. Creating a view in Teradata involves writing a SELECT statement that defines the data to include and assigning a name to the view, allowing users to query it as if it were a table. There are Database to Database access is granted to function views normally. Let's watch this video