Playground  Resume

(WIP)Teradata 14 SQL

Started preparing Teradata 14 SQL exam.

Teradata SQL - V14.10.6

-- Prepare
drop table t1;
create table t1(c1 int, c2 int);
insert into t1 values (1, 1);
insert into t1 values (2, 2);
-- SET
-- invalid
select * from t1
union
select * from t1
order by c1,c2
;

-- valid
select * from t1
union
select * from t1
order by 1,2
;
/* Macro */
create macro select1 as
(select 1;);

replace macro select1 as
(select 1;);

show macro select1;
help macro select1;

exec select1;
drop macro select1;

replace macro multiselect as
(select 1;
 select 2;);
exec multiselect;
-- returns 2

replace macro selectorder as
(
select * from t1 order by 1 desc
;);
exec selectorder;

replace macro plus1(num integer) as
(select :num+1;);
exec plus1(99); -- 100
exec plus1(); -- null

replace macro plus1_optional(num# integer) as
(select :num#+1;);
exec plus1_optional(1); -- 2
exec plus1_optional(); -- null

replace macro createt1 as
(drop table t1; create table t1(id int););
-- Data definition not valid unless solitary

replace macro createt1 as
(create table t1(id int););
exec createt1;

-- Parametizing an In-List
replace macro param_list(list varchar(100)) as
(
select * from (select 'teradata' as c1) t
where c1 in (:list)
;);
exec param_list('''hello'', ''teradata'''); -- No rows
exec param_list('teradata'); -- teradata

replace macro param_list2(list varchar(100)) as
(
select * from (select 'teradata' as c1) t
where position(c1 in :list) > 0
;);
exec param_list2('hello teradata'); -- teradata
exec param_list2('teradata'); -- teradata

Teradata Advanced SQL


-- CTAS
create table t2 as t1 with data;
create table t2 as t1 with no data;
create table t2 as (select * from t1) with data;
create table t2 as (select * from t1) with no data;
-- References(Foreign Key) constraints and Triggers (which reference source table) are not copied

-- index
create table t1 (c1 int, c2 int) unique primary index(c1);
create table t2 as t1 with no data unique index(c2); -- primary index(c1) and unique index(c2)
create table t2 as t1 with no data unique primary index(c1) unique index(c2); -- unique primary index(c1) and unique index(c2)
show table t2;


-- CTAS with subqueries
-- * Table attributes (FALLBACK) are not copied.  
-- * Secondary index are not copied.  
-- * First column listed becomes a NUPI unless otherwise specified.  
create table t1 (c1 int, c2 int) unique primary index(c1) unique index (c2);
create table t2 as (select * from t1) with no data; -- primary index(c1)
create table t2 as (select c2, c1 from t1) with no data; -- primary index(c2)

-- change column attribues
drop table t1;
drop table t2;
create table t1 (c1 int, c2 int) unique primary index(c1) unique index (c2);
create table t2 (c1 default 0, c2 check (c2 > 0) ) as (select * from t1) with no data;
create table t2 (c1 not null default 0) as (select c1 from t1) with no data;
insert into t2 values ();  -- (0) are inserted

Deribed Table

with d1(c1, c2) as
(select * from t1)
select * from d1;

Volatile Table

-- Implicit transactions
-- Rows are deleted immediately after the inert for implicit transactions
-- Same for ANSI mode
create volatile table v1(id int) on commit delete rows;

bt;
insert into v1 values (1);
select * from v1; -- (1)
et;

select * from v1; -- No rows

-- Explicit transactions
create volatile table v1(id int) on commit preserve rows;

bt;
insert into v1 values (1);
select * from v1; -- (1)
et;

select * from v1; -- (1)

Recursive

with recursive all_trips(origin, destination, cost, depth) as
(
select origin, destination, cost, 0 from flights where origin='LAX'
union all
select all_trips.origin, flights.destination, all_trips.cost+flights.cost, all_trips.depth+1 from all_trips
join flights on all_trips.destination=flights.origin
and all_trips.depth<3
)
select * from all_trips order by 4
;

4. Global Temporary Table

5. Windows Aggregate Functions

Step Order

  1. where
  2. aggregation
  3. having
  4. olap (partiton by, order by rows)
  5. qualify [order by]
  6. RANDOM
  7. sample, top n
  8. order by
  9. format

6. Windows Aggregate Functions 2

Non-ANSI ANSI
csum sum over
msvg avg over
msum sum over
mdiff N/A
rank(column) rank over
quantile rank over

Cumulative SUM
sum(c1) over(partition by p1 order by o1 rows unbounded preceding)
Moving SUM
sum(c1) over(order by o1 rows 2 preceding)
Moving AVG
sum(c1) over(order by o1 rows between 2 preceding and 1 preceding)
Moving DIFF
c1 - min(c1) over(order by o1 rows between 7 preceding and 7 preceding) or mdiff(c1, 7, o1)

Remaining Window
sum(c1) over(order by o1 rows between current row and unbounded following)
Moving Window sum(c1) over(order by o1 rows between current row and 2 following)
Reset When sum(c1) over(order by o1 reset when c1 is null rows unbounded following)

Preceding includes the current row, Following excludes the current row 0 Preceding and 0 Following include the current row

7. RANK rank() over(partition by p1 order by o1)

row_number function is an ANSI standard function, disregards any tied value
row_number() over(order by o1 reset when r1 is null)

dense_rank() function is an ANSI standard function
rank() over(order by o1 with ties low)
rank() over(order by o1 with ties high)
rank() over(order by o1 with ties dense)
rank() over(order by o1 with ties avg)

percent_rank() is an ANSI standard function and is expressed as an approximate numeric ratio between 0.0 and 1.0

pecentile_cont(?)
percent_cont(0.3) within group (order by c2 (dec(8,2))) pecentile_disk
percent_disc(0.3) within group (order by c2 (dec(8,2)))

cume_dist = RankHigh / Num Rows in Group

first_value(c1 ignore nulls) over(partition by p1 order by o1 rows between 3 preceding and 1 following)
last_value(c1 respect nulls) over(partition by p1 order by o1 rows between 3 preceding and 1 following)

8. Extended Grouping Functions ROLLUP, CUBE, GROUPING SETS

-- rollup
select
 c1
,c2
,sum(c3)
from t1
group by rollup(1, 2)
order by 1 desc, 2 desc
;

-- grouping
select
 case grouping(c1)
  when 1 then 'total'
  else coalesce(c1, 'value is null') /* else or when 1*/
 end
,sum(c3)
from t1
group by rollup(c1)
;

-- two colulmns into one column
select
 c1
,c2
,sum(c3)
from t1
group by rollup((1, 2))
order by 1 desc, 2 desc
;

-- rollup summarizes from right-to-left
-- cube summarizes from right-to-left and left-to-right

-- cube
select
 c1
,c2
,sum(c3)
from t1
group by cube(1, 2)
order by 1 desc, 2 desc
;

-- cube and grouping
select
 case grouping(c1)
  when 1 then 'c1 total'
  else coalesce(c1, 'value is null')
 end
,case grouping(c2)
  when 1 then 'c2 total'
  else coalesce(c2, 'value is null')
 end
,sum(c3)
from t1
group by cube(c1, c2)
;

-- combine grouping in a cube
select
 c1
,c2
,c3
,sum(c4)
from t1
group by cube((1, 2), 3)
;

-- grouping sets
select
 c1
,c2
,sum(c3)
from t1
group by grouping sets(c1, c2)
;

-- adding grand totals
select
 c1
,c2
,sum(c3)
from t1
group by grouping sets(c1, c2, ())
;

group by cube(c1, c2)
group by grouping sets((c1, c2), c1, c2, ())
group by grouping sets(c1, ()) grouping sets(c2, ())

9. QUANTILE and WIDTH_BUCKET

-- quantile
select
 c1
from t1
qualify quantile(100, c2) = 20
;

-- aggregates
select
sum(c1)
from
( select c1 from t1 qualify quantile(100, c2) = 20 ) as t
;

-- qualify and group by
select
 c1
,quantile(100, c2)
from t1
group by 1
;

-- ordergin qualilfy
select
 c2
,quantile(100, c2 asc)
from t1
;

-- percent_rank
select
 c2
,percent_rank() over(order by c2)
from t1
;

-- percent_rank derives a value between 0.0 and 1.0
-- percentile is a number between 0 and 99

-- width_bucket(column, lower, upper, partition_count)
-- number of buckets created is always partition_count+3
-- 1. 0 bucket for all values less than the lower
-- 2. n+1 for all values greater than the upper
-- 3. null backet for null values
select
 c1
,width_bucket(c1, 1, 3, 10)
from t1
;

10. Correlated Subqueries NOT IN can have issues when nulls are involved
NOT EXISTS does not share those issues due to row-at-a-time logic

11. Scalar Subqueries NCSSQ: Inner query does not reference the outer table
CSSQ: Inner query does reference the outer table

select
 c1
,(select max(c2) from t1)
from t1
;

12. Date-Time Data Types The date format for the system is defined in the “DATEFORM” setting of DBS Control
You can change or override the DATE format settings in any of the following ways:

modify general 14=0 /* integerdate (YY/MM/DD) */
modify general 14=1 /* ansidate (YYYY-MM-DD) */

create user dlm...
 dateform=ansidate
         =integerdate

set session dateform=ansidate
                    =integerdate

select date(format '----')

SQL Assistant may be affected from several settings. including OOL -> Options -> Data Format

Centrury-Break setting effects the system’s interpretation of dates only when FORMAT uses YY
If you use Centrury-Break setting of 40 (YY/MM/DD), ‘40/12/31’ become 1940/12/31 and ‘39/12/31’ become ‘2039/12/31’.

Time(n) n=0-6 (default is 6)
HH BYTEINT (1 byte)
MI BYTEINT (1 byte)
SS DEC(8,6) (4 bytes)

Time(0) HH:MI:SS CHAR(8)
Time(6) HH:MI:SS.nnnnnn CHAR(15)

Time data types are imported and exported as CHARACTER data using FORMAT

-- setting timezone
modify general 16=n /* n=-12 to+13 */
modify general 17=n /* n=-59 to+59 */

create user dlm...
  timezone=local
          =null
          ='08:00'
          ='-04:30'

set time zone local
set time zone user
set time zone inteval '05:00' hour to minute

-- extract
select extract(timezone_hour from current_timestamp)
select extract(timezone_minute from current_timestamp)

13. Interval Data Types and Manipulations

select interval '05-13' year to month -- 6-01
select interval '03:12' hour to minute -- 3:12

select interval '02' year

select (date '2009-01-31' + interval '1' month) -- fail

select add_months(date '2009-01-31', 1) -- 2009-02-28

select (date '2009-01-31' - date '2010-02-15' year to month ) --  -1-01

select interval '1' year (interval month(4)) -- 12

14. Timestamps and Timezones

insert into zone_test values ('GMT', timestamp '2002-04-15 10:30:00');

set time zone interval -'08:00' hour to minute;
insert into zone_test values ('PST', timestamp '2002-04-15 10:30:00');

select * from zone_test;
--PST,2002-04-15 10:30:00-08:00
--GMT,2002-04-15 10:30:00+00:00

select loc_id, cast(timestamp_with_zone as timestamp(0)) from zone_test;
--GMT   ,2002-04-15 02:30:00
--PST   ,2002-04-15 10:30:00

select loc_id, timestamp_with_zone at local from zone_test;
--GMT   ,2002-04-15 02:30:00-08:00
--PST   ,2002-04-15 10:30:00-08:00

extract expressions

select extract(year from current_timestamp);
select extract(month from current_timestamp);
select extract(day from current_timestamp);
select extract(hour from current_timestamp);
select extract(minute from current_timestamp);
select extract(timezone_hour from current_timestamp);
select extract(timezone_minute from current_timestamp);

overlaps functions

select 'found' where (date '2017-01-01', date '2017-01-03') overlaps (date '2017-01-02', date '2017-01-04'); -- found
select 'found' where (date '2017-01-01', date '2017-01-03') overlaps (date '2017-01-04', date '2017-01-02'); -- found
select 'found' where (date '2017-01-01', date '2017-01-03') overlaps (date '2017-01-03', date '2017-01-04'); -- no rows
select 'found' where (date '2017-01-01', date '2017-01-03') overlaps (date '2017-01-02', null); -- found

select 'found' where (time '01:00:01', time '01:00:03') overlaps (time '01:00:02', time '01:00:04'); -- found
select 'found' where (time '01:00:01', time '01:00:03') overlaps (time '01:00:04', time '01:00:02'); -- found
select 'found' where (time '01:00:01', time '01:00:03') overlaps (time '01:00:03', time '01:00:04'); -- no rows
select 'found' where (time '01:00:01', time '01:00:03') overlaps (time '01:00:02', null); -- found

15. Advanced Date and Time formatting
Y4: 4-digit year
YY: 2-digit year
M4: Long month
M3: Short month
MM: 2-digit month
DD: 2-digit day of month D3: 3-digit day of year (Julian date)

date

select current_date (format 'YYYY-MM-DD')(varchar(50)); -- 2017-11-12
select cast(current_date as format 'YY-M4-D3')(char(50)); -- 17-November-316                                                                                     
select cast(current_date as format 'YY-M3-D3')(varchar(50)); -- 17-Nov-316
select cast(current_date as format 'YY-M3-D3-EEEE')(varchar(50)); -- 17-Nov-316-Sunday   
select cast(current_date as format 'YY-M3-D3-EEE')(varchar(50)); -- 17-Nov-316-Sun

time

select current_timestamp (format 'hh:mi:ss')(varchar(50)); -- 22:19:30
select current_timestamp (format 'hh:mi:ssbt')(varchar(50)); -- 10:19:40 PM
select current_timestamp (format 'hh:mi:ssds(f)')(varchar(50)); -- 22:19:49.290000
select current_timestamp (format 'hh:mi:ssds(2)')(varchar(50)); -- 22:20:05.03
select current_timestamp (format 'hh:mi:ssz')(varchar(50)); -- 22:20:14-05:00
select current_timestamp (format 'zhh:mi:ss')(varchar(50)); -- -05:0022:20:34
select current_timestamp (format 'hh:mi:zss')(varchar(50)); -- Invalid FORMAT string 'hh:mi:zss'.

– system calendar table select * from sys_calendar.calendar sample 10;

16. Other Formatting Options
number format

select 1234.567 (format '$$$,$$9.99')(varchar(15)); --  $1,234.57
select 1234.567 (format '$$,$$9.99')(varchar(15));  -- $1,234.57
select 1234.567 (format '$,$$9.99')(varchar(15));   -- ********
select .567 (format '$$,$$9.99')(varchar(15));      --     $0.57
select .567 (format '$$,$$$.99')(varchar(15));      --     $.57

format spec can contain a maximum of 30 characters
format phrase can describe up to 18 digit positions output string produced using a format phrase can have a maximum of 255 characters

select 1234.567 (format 'g$-(10)d9(2)')(varchar(15)); -- $      1,234.5
select 1234.567 (format 'u-z(i)b')(varchar(15));      -- $USD 1235
select 1234.567 (format '-z(i)bN')(varchar(15));      --  1235 US Dollar

17. SQL Functions

select last_day(cast('2017-01-01' as date)); -- 2017-01-31
select next_day(cast('2017-01-01' as date), 'monday'); -- 2017-01-02

select trunc(123.456, 2);  -- 123.450
select trunc(123.456, -2); -- 100.000

select trunc(cast('2017-01-31' as date), 'cc');    -- 2001-01-01
select trunc(cast('2017-01-31' as date), 'year');  -- 2017-01-01
select trunc(cast('2017-01-31' as date), 'month'); -- 2017-01-01

select round(45.678, -1); -- 50.000
select round(45.678,  1); -- 45.700

select regexp_substr('hello hallo', 'h(e|a)llo', 1, 2, 'c');       -- hallo
select regexp_replace('hello hallo', 'hallo', 'hello', 1, 1, 'c'); -- hello hello
select regexp_instr('hello hallo', 'h(e|a)llo', 1, 1, 1, 'c');     -- 6
select regexp_similar('hello', 'h(e|a)llo', 'c');     -- 1
select ltrim('abababc', 'ab'); -- c
select rtrim('xyzyzyz', 'yz'); -- x
select lpad('hello', 10, '*'); -- *****hello
select rpad('hello', 10, '*'); -- hello*****
select ngram('aaahitbbb', 'ccchitddd', 3); -- 1
select ngram('aaahitbbb', 'ccchitaaa', 3); -- 2
select nvp('entree:orange#entree:teradata', 'entree', '#', ':', 2); -- teradata
select oreplace('hello xxx', 'xxx', 'teradata');   -- hello teradata
select otranslate('Thin and Thick', 'Thk', 'Spe'); -- Spin and Spice
select initcap('hello'); -- Hello
select instr('hello', 'l', 1, 2); -- 4
select nvl(null, 99); -- 99
select nvl(1000, 99); -- 1000
select nvl2(1000, null, 99); -- null (1000 is not null, so return 2nd arg)
select nvl2(null, 1000, 99); -- 99   (null is null, so return 3rd arg)
select ceiling(123.5); -- 124.0
select ceiling(123.55); -- 124.00
select floor(123.5); -- 123.0
select floor(123.55); -- 123.00
select decode (1, 1, 'abc', 2, 'xyz'); -- abc
select decode (2, 1, 'abc', 2, 'xyz'); -- xyz
select greatest(1, 2); -- 2
select least(1, 2);    -- 1
select to_number('12e3', '99eeee'); -- 12000
select to_number('1,234.99', '9,999d99'); -- 1234.99
select to_char(50000, '$99,999.99'); --  $50,000.00
select to_char(date '2017-11-13', 'dd-mon-yyyy'); -- 13-nov-2017
select to_char(interval '120' month, 'mon'); -- +0010-00
select to_char(date '2017-11-13', 'daymonth-dd, yyyy'); -- monday   november -13, 2017
select to_date('January 15, 2017', 'month dd, yyyy'); -- 2017-01-15
select to_timestamp('January 15, 2017 11:00 A.M.', 'month dd, yyyy hh:mi A.M.'); -- 2017-01-15 11:00:00.000000
select to_timestamp(242525); -- 1970-01-03 14:22:05.000000
select to_timestamp_tz('January 15, 2017 11:00 A.M.', 'month dd, yyyy hh:mi A.M.'); -- 2017-01-15 11:00:00.000000-05:00
select to_yminterval('04-10'); --     4-10
select to_dsinterval('100 04:23:59'); --   100 04:23:59.000000
select numtodsinterval(100, 'hour'); --     4 04:00:00.000000
select numtoyminterval(40.25, 'year'); --    40-03
select numtoyminterval(13.25, 'month'); --     1-01

Tips

Backlog

References