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.