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.

S# Description Query
[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 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);
14 CREATE GLOBAL TEMPORARY table create GLOBAL TEMPORARY 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;
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
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.

1 Comment

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

Leave a Reply

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