Teradata SQL Cheat Sheet

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

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.

3 thoughts on “Teradata SQL Cheat Sheet”

  1. 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

    1. 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));

Leave a Reply

Your email address will not be published. Required fields are marked *