teradata sql alchemy
TRANSCRIPT
![Page 1: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/1.jpg)
TERADATA SQL
By
B M Kumar
![Page 2: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/2.jpg)
Teradata COE - Alchemy
Solutions
2
Agenda
• Basic RDBMS Concepts and Teradata SQL
• Teradata Extensions ( HELP & SHOW)
• Data Types and Conversions
• Logical & Conditional Expressions, Attribute & String Functions
• Data Definitions
• Data Manipulations
• Views and Macros
• Joins
• Sub queries
• Set Operators
• Aggregations, Totals and Subtotals
• Rankings, Samplings, and Built-In Functions
• BTEQ ( Basic Teradata Query)
![Page 3: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/3.jpg)
Teradata COE - Alchemy
Solutions
3
Basic RDBMS Concepts and Teradata SQL
![Page 4: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/4.jpg)
Teradata COE - Alchemy
Solutions
4
Basic RDBMS Concepts and Teradata SQL
![Page 5: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/5.jpg)
Teradata COE - Alchemy
Solutions
5
Basic RDBMS Concepts and Teradata SQL
![Page 6: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/6.jpg)
Teradata COE - Alchemy
Solutions
6
Basic RDBMS Concepts and Teradata SQL
![Page 7: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/7.jpg)
Teradata COE - Alchemy
Solutions
7
Basic RDBMS Concepts and Teradata SQL
![Page 8: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/8.jpg)
Teradata COE - Alchemy
Solutions
8
Basic RDBMS Concepts and Teradata SQL
![Page 9: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/9.jpg)
Teradata COE - Alchemy
Solutions
9
Basic RDBMS Concepts and Teradata SQL
![Page 10: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/10.jpg)
Teradata COE - Alchemy
Solutions
10
Basic RDBMS Concepts and Teradata SQL
![Page 11: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/11.jpg)
Teradata COE - Alchemy
Solutions
11
Basic RDBMS Concepts and Teradata SQL
![Page 12: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/12.jpg)
Teradata COE - Alchemy
Solutions
12
Basic RDBMS Concepts and Teradata SQL
![Page 13: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/13.jpg)
Teradata COE - Alchemy
Solutions
13
Basic RDBMS Concepts and Teradata SQL
![Page 14: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/14.jpg)
Teradata COE - Alchemy
Solutions
14
Basic RDBMS Concepts and Teradata SQL
![Page 15: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/15.jpg)
Teradata COE - Alchemy
Solutions
15
Basic RDBMS Concepts and Teradata SQL
![Page 16: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/16.jpg)
Teradata COE - Alchemy
Solutions
16
Basic RDBMS Concepts and Teradata SQL
![Page 17: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/17.jpg)
Teradata COE - Alchemy
Solutions
17
Basic RDBMS Concepts and Teradata SQL
![Page 18: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/18.jpg)
Teradata COE - Alchemy
Solutions
18
Basic RDBMS Concepts and Teradata SQL
![Page 19: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/19.jpg)
Teradata COE - Alchemy
Solutions
19
Basic RDBMS Concepts and Teradata SQL
![Page 20: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/20.jpg)
Teradata COE - Alchemy
Solutions
20
Basic RDBMS Concepts and Teradata SQL
![Page 21: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/21.jpg)
Teradata COE - Alchemy
Solutions
21
Teradata Extensions
![Page 22: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/22.jpg)
Teradata COE - Alchemy
Solutions
22
Teradata Extensions
![Page 23: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/23.jpg)
Teradata COE - Alchemy
Solutions
23
Teradata Extensions
![Page 24: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/24.jpg)
Teradata COE - Alchemy
Solutions
24
Teradata Extensions
![Page 25: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/25.jpg)
Teradata COE - Alchemy
Solutions
25
Teradata Extensions
![Page 26: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/26.jpg)
Teradata COE - Alchemy
Solutions
26
Teradata Extensions
![Page 27: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/27.jpg)
Teradata COE - Alchemy
Solutions
27
Teradata Extensions
![Page 28: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/28.jpg)
Teradata COE - Alchemy
Solutions
28
Teradata Extensions
![Page 29: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/29.jpg)
Teradata COE - Alchemy
Solutions
29
Data Types and Conversions
![Page 30: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/30.jpg)
Teradata COE - Alchemy
Solutions
30
Data Types and Conversions
![Page 31: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/31.jpg)
Teradata COE - Alchemy
Solutions
31
Data Types and Conversions
![Page 32: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/32.jpg)
Teradata COE - Alchemy
Solutions
32
Data Types and Conversions
![Page 33: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/33.jpg)
Teradata COE - Alchemy
Solutions
33
Data Types and Conversions
![Page 34: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/34.jpg)
Teradata COE - Alchemy
Solutions
34
Data Types and Conversions
![Page 35: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/35.jpg)
Teradata COE - Alchemy
Solutions
35
Data Types and Conversions
![Page 36: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/36.jpg)
Teradata COE - Alchemy
Solutions
36
Data Types and Conversions
![Page 37: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/37.jpg)
Teradata COE - Alchemy
Solutions
37
Data Types and Conversions
![Page 38: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/38.jpg)
Teradata COE - Alchemy
Solutions
38
Data Types and Conversions
![Page 39: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/39.jpg)
Teradata COE - Alchemy
Solutions
39
Data Types and Conversions
![Page 40: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/40.jpg)
Teradata COE - Alchemy
Solutions
40
Data Types and Conversions
![Page 41: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/41.jpg)
Teradata COE - Alchemy
Solutions
41
Data Types and Conversions
![Page 42: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/42.jpg)
Teradata COE - Alchemy
Solutions
42
Data Types and Conversions
![Page 43: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/43.jpg)
Teradata COE - Alchemy
Solutions
43
Data Types and Conversions
![Page 44: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/44.jpg)
Teradata COE - Alchemy
Solutions
44
Data Types and Conversions
![Page 45: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/45.jpg)
Teradata COE - Alchemy
Solutions
45
Data Types and Conversions
![Page 46: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/46.jpg)
Teradata COE - Alchemy
Solutions
46
Logical & Conditional ExpressionsAnd Output Attributes
![Page 47: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/47.jpg)
Teradata COE - Alchemy
Solutions
47
Logical and Conditional Expressions
![Page 48: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/48.jpg)
Teradata COE - Alchemy
Solutions
48
Logical and Conditional Expressions
![Page 49: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/49.jpg)
Teradata COE - Alchemy
Solutions
49
Logical and Conditional Expressions
![Page 50: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/50.jpg)
Teradata COE - Alchemy
Solutions
50
Logical and Conditional Expressions
![Page 51: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/51.jpg)
Teradata COE - Alchemy
Solutions
51
Logical and Conditional Expressions
![Page 52: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/52.jpg)
Teradata COE - Alchemy
Solutions
52
Logical and Conditional Expressions
![Page 53: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/53.jpg)
Teradata COE - Alchemy
Solutions
53
Logical and Conditional Expressions
![Page 54: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/54.jpg)
Teradata COE - Alchemy
Solutions
54
Logical and Conditional Expressions
![Page 55: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/55.jpg)
Teradata COE - Alchemy
Solutions
55
Logical and Conditional Expressions
![Page 56: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/56.jpg)
Teradata COE - Alchemy
Solutions
56
Logical and Conditional Expressions
![Page 57: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/57.jpg)
Teradata COE - Alchemy
Solutions
57
Logical and Conditional Expressions
![Page 58: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/58.jpg)
Teradata COE - Alchemy
Solutions
58
Logical and Conditional Expressions
![Page 59: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/59.jpg)
Teradata COE - Alchemy
Solutions
59
Output Attributes
![Page 60: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/60.jpg)
Teradata COE - Alchemy
Solutions
60
Output Attributes
![Page 61: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/61.jpg)
Teradata COE - Alchemy
Solutions
61
Output Attributes
![Page 62: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/62.jpg)
Teradata COE - Alchemy
Solutions
62
Output Attributes
![Page 63: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/63.jpg)
Teradata COE - Alchemy
Solutions
63
Output Attributes
![Page 64: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/64.jpg)
Teradata COE - Alchemy
Solutions
64
Output Attributes
![Page 65: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/65.jpg)
Teradata COE - Alchemy
Solutions
65
Output Attributes
![Page 66: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/66.jpg)
Teradata COE - Alchemy
Solutions
66
Output Attributes
![Page 67: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/67.jpg)
Teradata COE - Alchemy
Solutions
67
Output Attributes
![Page 68: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/68.jpg)
Teradata COE - Alchemy
Solutions
68
Output Attributes
![Page 69: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/69.jpg)
Teradata COE - Alchemy
Solutions
69
Output Attributes
![Page 70: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/70.jpg)
Teradata COE - Alchemy
Solutions
70
Output Attributes
![Page 71: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/71.jpg)
Teradata COE - Alchemy
Solutions
71
Data Definition
![Page 72: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/72.jpg)
Teradata COE - Alchemy
Solutions
72
Data Definition
![Page 73: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/73.jpg)
Teradata COE - Alchemy
Solutions
73
Data Definition
![Page 74: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/74.jpg)
Teradata COE - Alchemy
Solutions
74
Data Definition
![Page 75: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/75.jpg)
Teradata COE - Alchemy
Solutions
75
Data Definition
![Page 76: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/76.jpg)
Teradata COE - Alchemy
Solutions
76
Data Definition
![Page 77: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/77.jpg)
Teradata COE - Alchemy
Solutions
77
Data Definition
![Page 78: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/78.jpg)
Teradata COE - Alchemy
Solutions
78
Data Definition
![Page 79: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/79.jpg)
Teradata COE - Alchemy
Solutions
79
Data Definition
![Page 80: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/80.jpg)
Teradata COE - Alchemy
Solutions
80
Data Definition
![Page 81: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/81.jpg)
Teradata COE - Alchemy
Solutions
81
Data Definition
![Page 82: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/82.jpg)
Teradata COE - Alchemy
Solutions
82
Data Definition
![Page 83: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/83.jpg)
Teradata COE - Alchemy
Solutions
83
Data Definition
![Page 84: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/84.jpg)
Teradata COE - Alchemy
Solutions
84
Data Definition
![Page 85: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/85.jpg)
Teradata COE - Alchemy
Solutions
85
Data Definition
![Page 86: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/86.jpg)
Teradata COE - Alchemy
Solutions
86
Data Definition
![Page 87: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/87.jpg)
Teradata COE - Alchemy
Solutions
87
Data Definition
![Page 88: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/88.jpg)
Teradata COE - Alchemy
Solutions
88
Data Definition
![Page 89: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/89.jpg)
Teradata COE - Alchemy
Solutions
89
Data Definition
![Page 90: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/90.jpg)
Teradata COE - Alchemy
Solutions
90
Data Definition
![Page 91: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/91.jpg)
Teradata COE - Alchemy
Solutions
91
Data Manipulation
![Page 92: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/92.jpg)
Teradata COE - Alchemy
Solutions
92
Data Manipulation
![Page 93: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/93.jpg)
Teradata COE - Alchemy
Solutions
93
Data Manipulation
![Page 94: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/94.jpg)
Teradata COE - Alchemy
Solutions
94
Data Manipulation
![Page 95: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/95.jpg)
Teradata COE - Alchemy
Solutions
95
Data Manipulation
![Page 96: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/96.jpg)
Teradata COE - Alchemy
Solutions
96
Data Manipulation
![Page 97: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/97.jpg)
Teradata COE - Alchemy
Solutions
97
Data Manipulation
![Page 98: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/98.jpg)
Teradata COE - Alchemy
Solutions
98
Data Manipulation
![Page 99: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/99.jpg)
Teradata COE - Alchemy
Solutions
99
Data Manipulation
![Page 100: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/100.jpg)
Teradata COE - Alchemy
Solutions
100
Data Manipulation
![Page 101: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/101.jpg)
Teradata COE - Alchemy
Solutions
101
Data Manipulation
![Page 102: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/102.jpg)
Teradata COE - Alchemy
Solutions
102
Data Manipulation
![Page 103: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/103.jpg)
Teradata COE - Alchemy
Solutions
103
Views and Macros
![Page 104: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/104.jpg)
Teradata COE - Alchemy
Solutions
104
Views and Macros
![Page 105: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/105.jpg)
Teradata COE - Alchemy
Solutions
105
Views and Macros
![Page 106: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/106.jpg)
Teradata COE - Alchemy
Solutions
106
Views and Macros
![Page 107: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/107.jpg)
Teradata COE - Alchemy
Solutions
107
Views and Macros
![Page 108: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/108.jpg)
Teradata COE - Alchemy
Solutions
108
Views and Macros
![Page 109: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/109.jpg)
Teradata COE - Alchemy
Solutions
109
Views and Macros
![Page 110: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/110.jpg)
Teradata COE - Alchemy
Solutions
110
Views and Macros
![Page 111: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/111.jpg)
Teradata COE - Alchemy
Solutions
111
Views and Macros
![Page 112: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/112.jpg)
Teradata COE - Alchemy
Solutions
112
Views and Macros
![Page 113: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/113.jpg)
Teradata COE - Alchemy
Solutions
113
Views and Macros
![Page 114: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/114.jpg)
Teradata COE - Alchemy
Solutions
114
Views and Macros
![Page 115: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/115.jpg)
Teradata COE - Alchemy
Solutions
115
Views and Macros
![Page 116: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/116.jpg)
Teradata COE - Alchemy
Solutions
116
Views and Macros
![Page 117: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/117.jpg)
Teradata COE - Alchemy
Solutions
117
Views and Macros
![Page 118: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/118.jpg)
Teradata COE - Alchemy
Solutions
118
Views and Macros
![Page 119: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/119.jpg)
Teradata COE - Alchemy
Solutions
119
Views and Macros
![Page 120: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/120.jpg)
Teradata COE - Alchemy
Solutions
120
JOINS
![Page 121: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/121.jpg)
Teradata COE - Alchemy
Solutions
121
Joins
![Page 122: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/122.jpg)
Teradata COE - Alchemy
Solutions
122
Joins
![Page 123: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/123.jpg)
Teradata COE - Alchemy
Solutions
123
Joins
![Page 124: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/124.jpg)
Teradata COE - Alchemy
Solutions
124
Joins
![Page 125: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/125.jpg)
Teradata COE - Alchemy
Solutions
125
Joins
![Page 126: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/126.jpg)
Teradata COE - Alchemy
Solutions
126
Joins
![Page 127: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/127.jpg)
Teradata COE - Alchemy
Solutions
127
Joins
![Page 128: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/128.jpg)
Teradata COE - Alchemy
Solutions
128
Joins
![Page 129: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/129.jpg)
Teradata COE - Alchemy
Solutions
129
Joins
![Page 130: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/130.jpg)
Teradata COE - Alchemy
Solutions
130
Joins
![Page 131: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/131.jpg)
Teradata COE - Alchemy
Solutions
131
Joins
![Page 132: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/132.jpg)
Teradata COE - Alchemy
Solutions
132
Joins
![Page 133: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/133.jpg)
Teradata COE - Alchemy
Solutions
133
Joins
![Page 134: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/134.jpg)
Teradata COE - Alchemy
Solutions
134
Joins
![Page 135: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/135.jpg)
Teradata COE - Alchemy
Solutions
135
Joins
![Page 136: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/136.jpg)
Teradata COE - Alchemy
Solutions
136
Joins
![Page 137: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/137.jpg)
Teradata COE - Alchemy
Solutions
137
Joins
![Page 138: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/138.jpg)
Teradata COE - Alchemy
Solutions
138
Joins
![Page 139: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/139.jpg)
Teradata COE - Alchemy
Solutions
139
Joins
![Page 140: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/140.jpg)
Teradata COE - Alchemy
Solutions
140
Joins
![Page 141: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/141.jpg)
Teradata COE - Alchemy
Solutions
141
Joins
![Page 142: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/142.jpg)
Teradata COE - Alchemy
Solutions
142
Joins
![Page 143: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/143.jpg)
Teradata COE - Alchemy
Solutions
143
Sub Queries
![Page 144: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/144.jpg)
Teradata COE - Alchemy
Solutions
144
Sub Queries
![Page 145: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/145.jpg)
Teradata COE - Alchemy
Solutions
145
Sub Queries
![Page 146: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/146.jpg)
Teradata COE - Alchemy
Solutions
146
Sub Queries
![Page 147: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/147.jpg)
Teradata COE - Alchemy
Solutions
147
Sub Queries
![Page 148: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/148.jpg)
Teradata COE - Alchemy
Solutions
148
Sub Queries
![Page 149: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/149.jpg)
Teradata COE - Alchemy
Solutions
149
Set Operators
![Page 150: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/150.jpg)
Teradata COE - Alchemy
Solutions
150
Set Operators
![Page 151: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/151.jpg)
Teradata COE - Alchemy
Solutions
151
Set Operators
![Page 152: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/152.jpg)
Teradata COE - Alchemy
Solutions
152
Set Operators
![Page 153: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/153.jpg)
Teradata COE - Alchemy
Solutions
153
Set Operators
![Page 154: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/154.jpg)
Teradata COE - Alchemy
Solutions
154
Set Operators
![Page 155: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/155.jpg)
Teradata COE - Alchemy
Solutions
155
Set Operators
![Page 156: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/156.jpg)
Teradata COE - Alchemy
Solutions
156
Aggregations, Totals and Subtotals
![Page 157: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/157.jpg)
Teradata COE - Alchemy
Solutions
157
Aggregations, Totals and Subtotals
![Page 158: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/158.jpg)
Teradata COE - Alchemy
Solutions
158
Aggregations, Totals and Subtotals
![Page 159: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/159.jpg)
Teradata COE - Alchemy
Solutions
159
Aggregations, Totals and Subtotals
![Page 160: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/160.jpg)
Teradata COE - Alchemy
Solutions
160
Aggregations, Totals and Subtotals
![Page 161: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/161.jpg)
Teradata COE - Alchemy
Solutions
161
Aggregations, Totals and Subtotals
![Page 162: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/162.jpg)
Teradata COE - Alchemy
Solutions
162
Aggregations, Totals and Subtotals
![Page 163: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/163.jpg)
Teradata COE - Alchemy
Solutions
163
Aggregations, Totals and Subtotals
![Page 164: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/164.jpg)
Teradata COE - Alchemy
Solutions
164
Aggregations, Totals and Subtotals
![Page 165: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/165.jpg)
Teradata COE - Alchemy
Solutions
165
Aggregations, Totals and Subtotals
![Page 166: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/166.jpg)
Teradata COE - Alchemy
Solutions
166
Aggregations, Totals and Subtotals
![Page 167: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/167.jpg)
Teradata COE - Alchemy
Solutions
167
Aggregations, Totals and Subtotals
![Page 168: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/168.jpg)
Teradata COE - Alchemy
Solutions
168
Aggregations, Totals and Subtotals
![Page 169: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/169.jpg)
Teradata COE - Alchemy
Solutions
169
Aggregations, Totals and Subtotals
![Page 170: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/170.jpg)
Teradata COE - Alchemy
Solutions
170
Aggregations, Totals and Subtotals
![Page 171: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/171.jpg)
Teradata COE - Alchemy
Solutions
171
Rankings, Samplings, and Built-In Functions
![Page 172: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/172.jpg)
Teradata COE - Alchemy
Solutions
172
Rankings
![Page 173: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/173.jpg)
Teradata COE - Alchemy
Solutions
173
Rankings
![Page 174: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/174.jpg)
Teradata COE - Alchemy
Solutions
174
Rankings
![Page 175: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/175.jpg)
Teradata COE - Alchemy
Solutions
175
Rankings
![Page 176: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/176.jpg)
Teradata COE - Alchemy
Solutions
176
Rankings
![Page 177: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/177.jpg)
Teradata COE - Alchemy
Solutions
177
Rankings
![Page 178: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/178.jpg)
Teradata COE - Alchemy
Solutions
178
Rankings with Partition BYSELECT storeid ,prodid
,sales,RANK( ) OVER (PARTITION BY storeid, prodid
ORDER BY sales DESC) AS "Ranking"FROM salestblQUALIFY Ranking <= 3;storeid prodid sales Ranking----------- ------ ----------- -----------1001 A 100000.00 11001 C 60000.00 11001 D 35000.00 11001 F 150000.00 11002 A 40000.00 11002 C 35000.00 11002 D 25000.00 11003 A 30000.00 11003 B 65000.00 11003 C 20000.00 11003 D 50000.00 1 Things To Notice•An additional PARTITION BY clause is added. •In this case, the scope is sales by product within store. •Each product exists only once in each store. •Thus, each row gets a rank of 1. each store.
![Page 179: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/179.jpg)
Teradata COE - Alchemy
Solutions
179
TOP N Overview
The TOP N function provides the ability to produce:
•The top (or bottom) N rows of results based on specified criteria.
•The top (or bottom) N percentage of rows based on specified criteria.
•These results either with or without ties (more than one row has identical criteria
values).
•Sample rows arbitrarily without regard to rankings or order.
This improvement has been achieved by implementing the following new
syntax to the SQL lexicon:
TOP {decimal | integer} [PERCENT] [WITH TIES]
The following meaning is associated with these options:
•TOP 10 – Return the top ten rows according to criteria
•TOP 15 PERCENT – Return the top 15% of rows according to criteria
•TOP 10 WITH TIES – If more that one row has the same criteria value, return all
•TOP 15 PERCENT WITH TIES – If more than one row has the same criteria
value, return all
![Page 180: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/180.jpg)
Teradata COE - Alchemy
Solutions
180
TOP N• Show all department budgets ordered by budget amount descending. To
answer this query, the TOP N feature is not needed.
SELECT department_number , budget_amount
FROM department ORDER BY 2 DESC;
Note: Null sorts following the valid budget amounts.
department_number budget_amount
----------------- -------------
401 982300.00
403 932000.00
301 465600.00
100 400000.00
402 308000.00
501 308000.00
201 293800.00
302 226000.00
600 ?
![Page 181: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/181.jpg)
Teradata COE - Alchemy
Solutions
181
TOP NTOP N With and Without TIES
SELECT TOP 5
department_number
, budget_amount
FROM department
ORDER BY 2 DESC;
department_number budget_amount
----------------- -------------
401 982300.00
403 932000.00
301 465600.00
100 400000.00
501 308000.00
Things to notice:
ORDER BY defines the sequencing of the result set.
It therefore defines the ranking criteria.
To get the TOP highest amounts, you must use ORDER with DESC.
TOP N where N is an integer up to 18 digits in length.
SELECT TOP 5 WITH TIES
department_number
, budget_amount
FROM department
ORDER BY 2 DESC;
department_number budget_amount
----------------- -------------
401 982300.00
403 932000.00
301 465600.00
100 400000.00
501 308000.00
402 308000.00
Things to notice:
Even though TOP 5 is specified, six rows are returned.
Because there is a tie for the fifth position, both rows are returned.
This only occurs when WITH TIES is specified.
WITH TIES returns multiple tied rows when there is a tie for the 'last' position.
It will return all rows containing the 'tied' value, but it will only count it as one row.
Tied rows which are not in the last position, are each counted separately toward the N total.
![Page 182: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/182.jpg)
Teradata COE - Alchemy
Solutions
182
SAMPLEThe SAMPLE function allows a user to specify a sample of results returned by a specified SELECT statement. The sample
can be represented as either a percentage of the returned rows or as an absolute number.
Sampling is useful for many purposes, including generating samples of production data for purposes of testing out new
applications.
Stratified sampling:
the ability to generate different sized samples from different groupings of the data.
Consider the distribution of departments within the employee table.
SELECT department_number
,COUNT (*)
FROM employee
GROUP BY 1
ORDER BY 1;
• department_number Count(*)----------------- -----------
100 1
201 2
301 3
302 1
401 7
402 2
403 6
501 4
![Page 183: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/183.jpg)
Teradata COE - Alchemy
Solutions
183
SAMPLECreate five groupings of employees based on the range of department numbers.
SELECT CASE WHEN department_number BETWEEN 100 AND 199 THEN 'GROUP 1'
WHEN department_number BETWEEN 200 AND 299 THEN 'GROUP 2'
WHEN department_number BETWEEN 300 AND 399 THEN 'GROUP 3'
WHEN department_number BETWEEN 400 AND 499 THEN 'GROUP 4'
WHEN department_number BETWEEN 500 AND 599 THEN 'GROUP 5'
ELSE NULL END AS Group_Name ,COUNT (*) FROM employee
GROUP BY 1 ORDER BY 1;
Group_Name Count(*)
---------- -----------
GROUP 1 1
GROUP 2 2
GROUP 3 4
GROUP 4 15
GROUP 5 4
SELECT last_name (CHAR(10)) ,department_number (FORMAT '999') AS Deptno , SAMPLEID FROM employee SAMPLE
WHEN department_number BETWEEN 100 AND 199 THEN .25
WHEN department_number BETWEEN 200 AND 299 THEN .25
WHEN department_number BETWEEN 300 AND 399 THEN .25
WHEN department_number BETWEEN 400 AND 499 THEN .25
WHEN department_number BETWEEN 500 AND 599 THEN .25
END ORDER BY 2;
last_name Deptno SampleId
---------- ------ -----------
Short 201 2 (.25 * 2 = .50 ROUNDING TO 1)
Stein 301 3 (.25 * 4 = 1)
Phillips 401 4 (.25 * 15 = 3.75 ROUNDING TO 4)
Rogers 401 4
Villegas 403 4
Hopkins 403 4
Ratzlaff 501 5 (.25 * 4 = 1)
Things to notice:
•SAMPLEID reflects the sample pool into which each row falls.
•In some cases, the fractional amount is less than a full row, thus no rows are returned for that sampling.
•No rows are returned for SAMPLEID 1 because 25% of one row rounds to zero.
![Page 184: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/184.jpg)
Teradata COE - Alchemy
Solutions
184
Built-In Functions
![Page 185: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/185.jpg)
Teradata COE - Alchemy
Solutions
185
String Functions
![Page 186: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/186.jpg)
Teradata COE - Alchemy
Solutions
186
String Functions
![Page 187: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/187.jpg)
Teradata COE - Alchemy
Solutions
187
String Functions
![Page 188: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/188.jpg)
Teradata COE - Alchemy
Solutions
188
String Functions
![Page 189: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/189.jpg)
Teradata COE - Alchemy
Solutions
189
String Functions
![Page 190: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/190.jpg)
Teradata COE - Alchemy
Solutions
190
String Functions
![Page 191: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/191.jpg)
Teradata COE - Alchemy
Solutions
191
String Functions
![Page 192: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/192.jpg)
Teradata COE - Alchemy
Solutions
192
String Functions
![Page 193: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/193.jpg)
Teradata COE - Alchemy
Solutions
193
BTEQ (Basic Teradata Query)
![Page 194: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/194.jpg)
Teradata COE - Alchemy
Solutions
194
BTEQ
![Page 195: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/195.jpg)
Teradata COE - Alchemy
Solutions
195
BTEQ
![Page 196: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/196.jpg)
Teradata COE - Alchemy
Solutions
196
BTEQ
![Page 197: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/197.jpg)
Teradata COE - Alchemy
Solutions
197
BTEQ
![Page 198: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/198.jpg)
Teradata COE - Alchemy
Solutions
198
BTEQ
![Page 199: Teradata SQL Alchemy](https://reader035.vdocuments.us/reader035/viewer/2022081717/553db83a4a795932248b47aa/html5/thumbnails/199.jpg)
Teradata COE - Alchemy
Solutions
199
Questions