Teradata SQL Cheat Sheet | Free Download

I am sharing below some Teradata SQL queries which can quickly help you brush your SQL. I have tried to cover all the common SQL operations in Teradata and will keep on adding more. Hope this helps.

[DDL] CREATE / ALTER / DROP / RENAME TABLE : 19 Queries
SELECT : 38 Queries
Metadata/System Tables & Views : 11 Queries
[DML] INSERT / UPDATE / DELETE / MERGE : 12 Queries

FREE Download (pdf)

[DDL] CREATE / ALTER / DROP / RENAME TABLE

1 CREATE table with column list

create table test_datatype(
  col1 byteint, 
  col2 smallint, 
  col3 integer, 
  col4 bigint, 
  col5 char(10), 
  col6 varchar(10), 
  col7 date, 
  col8 timestamp, 
  col9 time, 
  col10 decimal(10, 2), 
  col11 number(10, 2), 
  col12 float
);

2 CREATE table with column list with PRIMARY INDEX

create table test_datatype2(
  col1 byteint, 
  col2 smallint, 
  col3 integer, 
  col4 bigint, 
  col5 char(10), 
  col6 varchar(10), 
  col7 date, 
  col8 timestamp, 
  col9 time, 
  col10 decimal(10, 2), 
  col11 number(10, 2), 
  col12 float
) primary index(col2);

3 CREATE table with column list with NO PRIMARY INDEX

create table test_datatype3(
  col1 byteint, 
  col2 smallint, 
  col3 integer, 
  col4 bigint, 
  col5 char(10), 
  col6 varchar(10), 
  col7 date, 
  col8 timestamp, 
  col9 time, 
  col10 decimal(10, 2), 
  col11 number(10, 2), 
  col12 float
) NO primary index;

4 CREATE table from other table with no-data

create table test_ctas1 as test_datatype with no data;

5 CREATE table from other table with no-data but different PRIMARY INDEX

create table test_ctas2 as test_datatype with no data primary index (col2);

6 CREATE table from other table with data

create table test_ctas3 as test_datatype with data;

7 CREATE table from other table with data but different PRIMARY INDEX

create table test_ctas4 as test_datatype with data primary index (col2);

8 CREATE table from SELECT with no data

create table test_ctas5 as (
  SELECT 
    w_warehouse_sk, 
    w_warehouse_id, 
    w_city 
  FROM 
    WAREHOUSE 
  WHERE 
    w_state = 'KS'
) with no data;

9 CREATE table from SELECT with no data but different PRIMARY INDEX

create table test_ctas6 as 
SELECT 
  w_warehouse_sk, 
  w_warehouse_id, 
  w_city 
FROM 
  WAREHOUSE 
WHERE 
  w_state = 'KS' with no data primary index (w_warehouse_id);

10 CREATE table from SELECT with data

create table test_ctas7 as (
  SELECT 
    w_warehouse_sk, 
    w_warehouse_id, 
    w_city 
  FROM 
    WAREHOUSE 
  WHERE 
    w_state = 'KS'
) with data;

11 CREATE table from SELECT with data but different PRIMARY INDEX

create table test_ctas8 as (
  SELECT 
    w_warehouse_sk, 
    w_warehouse_id, 
    w_city 
  FROM 
    WAREHOUSE 
  WHERE 
    w_state = 'KS'
) with data primary index (w_warehouse_id);

12 CREATE table from SELECT with no data by adding filter condition

create table test_ctas9 as (
  SELECT 
    w_warehouse_sk, 
    w_warehouse_id, 
    w_city 
  FROM 
    WAREHOUSE 
  WHERE 
    1 = 0
) with no data;

13 CREATE VOLATILE table

create VOLATILE table vt_datatype(
  col1 byteint, 
  col2 smallint, 
  col3 integer, 
  col4 bigint, 
  col5 char(10), 
  col6 varchar(10), 
  col7 date, 
  col8 timestamp, 
  col9 time, 
  col10 decimal(10, 2), 
  col11 number(10, 2), 
  col12 float
) primary index(col2) ON COMMIT PRESERVE ROWS;

14 CREATE GLOBAL TEMPORARY table

create GLOBAL TEMPORARY table gt_datatype(
  col1 byteint, 
  col2 smallint, 
  col3 integer, 
  col4 bigint, 
  col5 char(10), 
  col6 varchar(10), 
  col7 date, 
  col8 timestamp, 
  col9 time, 
  col10 decimal(10, 2), 
  col11 number(10, 2), 
  col12 float
) primary index(col2);

15 ADD new column in the table

ALTER table 
  warehouse 
ADD 
  w_warehouse_new varchar(30), 
ADD 
  w_warehouse_new2 varchar(30);

16 DROP existing column from the table

ALTER table 
  warehouse 
DROP 
  w_warehouse_new, 
DROP 
  w_warehouse_new2;

17 RENAME existing column to new name

ALTER table 
  warehouse RENAME w_warehouse_name to w_wh_nm;

18 RENAME table to new name

RENAME TABLE warehouse to warehouse_new;

19 DROP table from database

DROP 
  TABLE warehouse;

SELECT

20 SELECT all columns and all rows from table

SELECT 
  * 
from 
  WAREHOUSE;

21 SELECT few columns and all rows from table

SELECT 
  w_warehouse_sk, 
  w_warehouse_id, 
  w_city 
FROM 
  WAREHOUSE;

22 SELECT all columns and few rows from table

SELECT 
  * 
from 
  WAREHOUSE sample 10;

23 SELECT all columns and few rows from table – another approach

SELECT 
  Top 10 * 
from 
  WAREHOUSE;

24 SELECT all columns and FILTER rows from table

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state = 'KS';

25 SELECT all columns and FILTER rows , multiple conditions (AND) from table

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state = 'TN' 
  and w_city = 'Fairview';

26 SELECT all columns and FILTER rows , multiple conditions (OR) from table

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state = 'TN' 
  OR w_state = 'VA';

27 SELECT all columns and FILTER rows , multiple conditions (OR) on same column using IN

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state IN ('TN', 'VA');

28 SELECT DISTINCT rows from table

SELECT 
  DISTINCT * 
from 
  WAREHOUSE;

29 SELECT DISTINCT column rows from table

SELECT 
  DISTINCT w_warehouse_sk, 
  w_warehouse_id, 
  w_city 
FROM 
  WAREHOUSE;

30 SORT output rows

SELECT 
  w_warehouse_sk, 
  w_warehouse_id, 
  w_city 
FROM 
  WAREHOUSE 
ORDER BY 
  w_warehouse_id asc, 
  w_city desc;

31 SORT output rows by column position

SELECT 
  w_warehouse_sk, 
  w_warehouse_id, 
  w_city 
FROM 
  WAREHOUSE 
ORDER BY 
  2 asc, 
  3 desc;

32 NULL check for column value

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state IS NOT NULL 
  and w_city is NULL;

33 Fetch MAX, MIN, COUNT, AVG,SUM for measures in table

SELECT 
  MAX(w_warehouse_sq_ft), 
  MIN(w_warehouse_sq_ft), 
  COUNT(w_warehouse_sq_ft), 
  AVG(w_warehouse_sq_ft), 
  SUM(w_warehouse_sq_ft) 
FROM 
  WAREHOUSE;

34 Check total records in the table

SELECT 
  COUNT(*) 
FROM 
  WAREHOUSE;

35 Check total distinct records in the table

SELECT 
  COUNT(*) 
FROM 
  (
    select 
      distinct * 
    from 
      WAREHOUSE
  ) tb1;

36 Check for string pattern in column values

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state like 'T % ';

37 Check for multiple string pattern in column values

SELECT 
  * 
FROM 
  WAREHOUSE 
WHERE 
  w_state like any ('T % ', 'V % ');

38 Check for rows BETWEEN range values

select 
  * 
from 
  date_dim 
where 
  d_date between '2019 - 01 - 01' 
  and '2019 - 01 - 31';

39 Create ALIAS name for columns

SELECT 
  w_warehouse_sk as w_sk, 
  w_warehouse_id as w_id, 
  w_city as w_city 
FROM 
  WAREHOUSE;

40 Create ALIAS name for tables

SELECT 
  tb1.w_warehouse_sk, 
  tb1.w_warehouse_id, 
  tb1.w_city 
FROM 
  WAREHOUSE tb1;

41 Use COALESCE to set value when NULL exists

SELECT 
  w_warehouse_sk as w_sk, 
  w_warehouse_id as w_id, 
  COALESCE(w_city, 'Not Available') as w_city 
FROM 
  WAREHOUSE;

42 Use CASE to create new derived table

SELECT 
  w_warehouse_sk as w_sk, 
  w_warehouse_id as w_id, 
  w_city as w_city, 
  CASE when w_state = 'California' then 'CA' when w_state = 'Florida' then 'FL' when w_state = 'Michigan' then 'MI' when w_state = 'New Jersey' then 'NJ' when w_state = 'New York' then 'NY' when w_state = 'Texas' then 'TX' when w_state = 'Virginia' then 'VA' else 'Other States' END as w_state_abbr 
FROM 
  WAREHOUSE;

43 INNER JOIN

select 
  tb2.d_date, 
  tb1.ss_quantity, 
  tb1.ss_wholesale_cost, 
  tb1.ss_list_price, 
  tb1.ss_sales_price 
from 
  store_sales tb1 
  INNER JOIN date_dim tb2 on tb1.ss_sold_date_sk = tb2.d_date_sk 
where 
  tb2.d_date between '2020 - 01 - 01' 
  and '2020 - 12 - 31';

44 LEFT OUTER JOIN

select 
  tb2.d_date, 
  tb1.ss_quantity, 
  tb1.ss_wholesale_cost, 
  tb1.ss_list_price, 
  tb1.ss_sales_price 
from 
  store_sales tb1 
  LEFT OUTER JOIN date_dim tb2 on tb1.ss_sold_date_sk = tb2.d_date_sk 
where 
  tb2.d_date between '2020 - 01 - 01' 
  and '2020 - 12 - 31';

45 RIGHT OUTER JOIN

select 
  tb2.d_date, 
  tb1.ss_quantity, 
  tb1.ss_wholesale_cost, 
  tb1.ss_list_price, 
  tb1.ss_sales_price 
from 
  store_sales tb1 
  RIGHT OUTER JOIN date_dim tb2 on tb1.ss_sold_date_sk = tb2.d_date_sk 
where 
  tb2.d_date between '2020 - 01 - 01' 
  and '2020 - 12 - 31';

46 FULL OUTER JOIN

select 
  tb2.d_date, 
  tb1.ss_quantity, 
  tb1.ss_wholesale_cost, 
  tb1.ss_list_price, 
  tb1.ss_sales_price 
from 
  store_sales tb1 FULL 
  OUTER JOIN date_dim tb2 on tb1.ss_sold_date_sk = tb2.d_date_sk 
where 
  tb2.d_date between '2020 - 01 - 01' 
  and '2020 - 12 - 31';

47 SELF JOIN

select 
  tb1.w_warehouse_sk, 
  tb1.w_warehouse_name 
from 
  warehouse tb1, 
  warehouse tb2 
where 
  tb1.w_warehouse_id = tb2.w_warehouse_id 
  and tb1.w_state = tb2.w_state 
  and tb1.w_warehouse_sk = 1;

48 LEFT OUTER JOIN to find rows which does not exists in LEFT table

select 
  tb1.w_warehouse_name, 
  _tb1.w_state, 
  tb1.w_city 
from 
  warehouse tb1 
  left outer join inventory tb2 on tb1.w_warehouse_sk = tb2.inv_warehouse_sk 
where 
  tb2.inv_warehouse_sk is null;

49 LEFT OUTER JOIN to find rows which does exists in LEFT table (same as INNER JOIN)

select 
  tb1.w_warehouse_name, 
  _tb1.w_state, 
  tb1.w_city 
from 
  warehouse tb1 
  left outer join inventory tb2 on tb1.w_warehouse_sk = tb2.inv_warehouse_sk 
where 
  tb2.inv_warehouse_sk is not null;

50 ROW NUMBER olap function

select 
  w_warehouse_name, 
  w_state, 
  w_city, 
  row_number() over(
    partition by w_zip 
    order by 
      w_warehouse_sq_ft desc
  ) as w_warehouse_no 
from 
  warehouse;

51 RANK olap function

select 
  w_warehouse_name, 
  w_state, 
  w_city, 
  rank() over(
    partition by w_zip 
    order by 
      w_warehouse_sq_ft desc
  ) as w_warehouse_no 
from 
  warehouse;

52 UNION set operator

SELECT 
  w_warehouse_sk as w_sk, 
  w_warehouse_id as w_id, 
  w_city as w_city 
FROM 
  WAREHOUSE 
where 
  w_state = 'Virginia' 
UNION 
SELECT 
  w_warehouse_sk as w_sk, 
  w_warehouse_id as w_id, 
  w_city as w_city 
FROM 
  WAREHOUSE 
where 
  w_state = 'Texas';

53 SELECT today's date

SELECT 
  CURRENT_DATE;

54 Add 5 days to date

SELECT 
  CURRENT_DATE + INTERVAL '5' DAY;

55 Add 5 months to date

SELECT 
  CURRENT_DATE + INTERVAL '5' MONTH;

56 Add 5 years to date

SELECT 
  CURRENT_DATE + INTERVAL '5' YEAR;

57 Subtract 2 dates

SELECT 
  cast('2019 - 12 - 31' as date) – cast('2019 - 04 - 01' as date);

Metadata/System Tables & Views

58 Check for tablename in any database

select 
  * 
from 
  dbc.tablesV 
where 
  tablename = 'warehouse';

59 Check for tablename in specific database

select 
  * 
from 
  dbc.tablesV 
where 
  tablename = 'warehouse' 
  and databasename = 'tpcds';

60 Check for all tables in specific database

select 
  * 
from 
  dbc.tablesV 
where 
  databasename = 'tpcds' 
  and tablekind in ('T', 'O');

61 Check for all objects in specific database

HELP database tpcds;

62 Check table structure

SHOW table warehouse;

63 Check for columns in any table

HELP table warehouse;

64 Check for columns in any table

select 
  * 
from 
  dbc.columnsV 
where 
  databasename = 'tpcds' 
  and tablename = 'warehouse' 
order by 
  columnid;

65 Check for tablesize in Teradata

select 
  databasename, 
  tablename, 
  sum(currentperm) as total_size_bytes 
from 
  dbc.tablesize 
where 
  tablename = 'warehouse' 
  and databasename = 'tpcds' 
group by 
  databasename, 
  tablename;

66 Check for all VIEWS in specific database

select 
  * 
from 
  dbc.tablesV 
where 
  databasename = 'tpcds' 
  and tablekind = 'V';

67 Check for performance metrics of session

select 
  * 
from 
  dbc.dbqlogtbl 
where 
  sessionid = (
    select 
      session
  );

68 Explain plan for the Query

EXPLAIN 
select 
  tb2.d_date, 
  tb1.ss_quantity, 
  tb1.ss_wholesale_cost, 
  tb1.ss_list_price, 
  tb1.ss_sales_price 
from 
  store_sales tb1 
  INNER JOIN date_dim tb2 on tb1.ss_sold_date_sk = tb2.d_date_sk 
where 
  tb2.d_date between '2020 - 01 - 01' 
  and '2020 - 12 - 31';

[ DML ] INSERT / UPDATE / DELETE / MERGE

69 INSERT static records into table

INSERT into employee(
  Name, City, County, State, Zip, Country
) 
values 
  (
    'Mark', 'Paris', 'NA', 
    'NA', 75000, 'France'
  );

70 INSERT records into table from another table

INSERT into employee(
  Name, City, County, State, Zip, Country
) 
SELECT 
  Name, 
  City, 
  County, 
  State, 
  Zip, 
  Country 
from 
  stg_employee 
where 
  zip = 75000 
  and country = 'France';

71 UPDATE table records from static values

UPDATE 
  employee 
set 
  Country = 'France', 
  City = 'Paris';

72 UPDATE table records from static values with some condition

UPDATE 
  employee 
set 
  Country = 'France', 
  City = 'Paris' 
where 
  zip = 75000;

73 UPDATE table records from another table

UPDATE 
  tb1 
from 
  employee tb1, 
  dim_add tb2 
set 
  Country = tb2.Country, 
  City = tb2.City 
where 
  tb1.zip = tb2.zip;

74 UPDATE table records from another derived table

UPDATE 
  tb1 
from 
  employee tb1, 
  (
    select 
      country, 
      city, 
      zip 
    from 
      dim_add 
    where 
      zip between 75000 
      and 75020
  ) tb2 
set 
  Country = tb2.Country, 
  City = tb2.City 
where 
  tb1.zip = tb2.zip;

75 UPSERT table records

UPDATE 
  employee 
set 
  Country = 'France', 
  City = 'Paris' 
where 
  zip = 75000 ELSE INSERT into employee(
    Name, City, County, State, Zip, Country
  ) 
values 
  (
    'Mark', 'Paris', 'NA', 
    'NA', 75000, 'France'
  );

76 MERGE table records from Source table

MERGE into employee tb1 using stg_employee tb2 on tb1.zip = tb2.zip WHEN MATCHED THEN 
UPDATE 
SET 
  Country = tb2.Country, 
  City = tb2.City WHEN NOT MATCHED THEN INSERT 
VALUES 
  (
    tb2.Name, tb2.City, tb2.County, tb2.State, 
    tb2.Zip, tb2.Country
  );

77 DELETE entire table

DELETE from 
  employee;

78 DELETE few records from table

DELETE from 
  employee 
where 
  zip = 75000;

79 DELETE few records from table by looking into another table

DELETE FROM 
  employee 
where 
  zip NOT IN (
    select 
      zip 
    from 
      stg_employee
  );

80 DELETE few records from table by looking into another table

DELETE FROM 
  employee 
where 
  zip = stg_employee.zip;

If you want me to add few more SQL scenarios, feel free to leave comments and I will include those SQL scenarios too.

John

March 14, 2023 at 12:09 am

Hi, Thank you for this. I have made ANKI flashcards from these would you mind if I shared them?

Osic

March 31, 2022 at 2:39 am

I guess, but I am not sure, there is a typo like below:
———————————————————–
75 UPSERT table records -> 75 UPDATE table records
———————————————————–

Aniket

October 1, 2021 at 12:28 pm

Thank you so much for sharing this wonderful content. I am fresher and have just transitioned from Campus to Corporate life and have been given Data Integration domain where I will be required to work on Teradata SQL Assistant and Informatica Powercenter. Please share more of your experiences with us, it would be very helpful.

Prerana

June 3, 2021 at 3:56 pm

This was very helpful. Please make more of this sort.

Abhishek

May 23, 2021 at 8:39 pm

The best place to visit again and again.
This will help most of freshers to learn and impress people around them.
I am happy to see such pages exist on web.

Raj
May 24, 2021 at 9:31 am

Thank you Abhishek for the appreciation.
Happy to help.

Sonia

December 12, 2019 at 3:45 am

I need the years 2019 and 2020 to look like '2019-20' in a integer field which I cannot change the data type. Someone suggested using trim and substring. Can you please help me. Thanks

TestUser
June 16, 2020 at 2:21 pm

Use left cast and right cast for two dates and concatenate two dates into one.
E.g. select concat(left(cast('2019-6-16' as varchar(23)), 5),right(cast('8-17-2020' as varchar(25)), 2));

Bhargav

September 24, 2019 at 6:44 pm

need interview based teradata sql queries and all utilities with an example,.

Aditya on How to pass parameters to BTEQ scriptJuly 30, 2024
Getting warning RDBMS crash or session reset. recovery in progress. Will this recover my instance or i need to create…
Pavani on Teradata combine rows into single valueJanuary 25, 2024
This is best solution I have come across. Can we only concatenate distinct values ? Thanks in advance.
Raj on TD Import Script GeneratorJune 22, 2023
Hi Chris, This is default behavior of FASTLOAD script. Whenever it fails, it locks the table. The reason is to…
Chris on TD Import Script GeneratorJune 21, 2023
The fastload script appears to work but then after it completes I get an error saying that the tables are…
John on Teradata SQL Cheat Sheet | Free DownloadMarch 14, 2023
Hi, Thank you for this. I have made ANKI flashcards from these would you mind if I shared them?