“As I would not be a slave, so I would not be a master.”
– Abraham Lincoln
Exam No | Exam Name |
TE0-141 | Teradata 14 Basics |
TE0-142 | Teradata 14 SQL |
TE0-143 | Teradata 14 Physical Design and Implementation |
TE0-144 | Teradata 14 Database Administration |
TE0-145 | Teradata 14 Solutions Development |
TE0-146 | Teradata 14 Enterprise Architecture |
TE0-147 | Teradata 14 Comprehensive Mastery Exam |
V14 Certification Title | Exams Required For Certification |
Teradata 14 Certified Professional | TE0-141 |
Teradata 14 Certified Technical Specialist | TE0-141, TE0-142 , TE0-143 |
Teradata 14 Certified Database Administrator | TE0-141, TE0-142, TE0-143, TE0-144 |
Teradata 14 Certified Solutions Developer | TE0-141, TE0-142, TE0-143, TE0-145 |
Teradata 14 Certified Enterprise Architect | TE0-141, TE0-142, TE0-143, TE0-145, TE0-146 |
Teradata 14 Certified Master | Pass all seven exams |
All exams must be taken and passed in the order listed. Teradata V12 Masters can take one test to achieve a V14 Masters Upgrade and that is TE0-147. Go to www.prometric.com/Teradata to register for an exam.
The Student_Table above will be used
in our early SQL Examples
This is a pictorial of the Student_Table which we will use to present some basic examples of SQL and get some hands-on experience with querying this table. This book attempts to show you the table, show you the query, and show you the result set.
Mostly every SQL statement will consist of a SELECT and a FROM. You SELECT the columns you want to see on your report and an Asterisk (*) means you want to see all columns in the table on the returning answer set!
SELECT First_Name
,Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table ;
This is a great way to show the columns you are selecting from the Table_Name.
Why is the example on the left better even though they are functionally equivalent? Errors are easier to spot and comments won’t cause errors.
"A life filled with love may have some thorns,
but a life empty of love will have no roses."
Anonymous
Having commas in front to separate column names makes it easier to debug. Remember our quote above. "A query filled with commas at the end just might fill you with thorns, but a query filled with commas in the front will allow you to always come up smelling like roses."
Rows typically come back to the report in random order. To order the result set, you must use an ORDER BY. When you order by a column, it will order in ASCENDING order. This is called the Major Sort!
Rows typically come back to the report in random order, but we decided to use the ORDER BY statement. Now, the data comes back ordered by Last_Name.
The ORDER BY can use a number to represent the sort column. The number 2 represents the second column on the report.
Notice that the answer set is sorted in ascending order based on the column Grade_Pt. Also notice that Grade_Pt is the fifth column coming back on the report. That is why the SQL in both statements is ordering by Grade_Pt. Did you notice that the null value came back first? Nulls sort first in ascending order and last in descending order.
Notice that the answer set is sorted in ascending order based on the column Grade_Pt. Also notice that Grade_Pt is the fifth column coming back on the report. That is why the SQL in both statements is ordering by Grade_Pt. Did you notice that the null value came back first? Nulls sort first in ascending order and last in descending order.
Did you notice that the null value came back first? Nulls sort first in ascending order and last in descending order.
You can ORDER BY in descending order by putting a DESC after the column name or its corresponding number. Null Values will sort Last in DESC order.
Major sort is the first sort. There can only be one major sort. A minor sort kicks in if there are Major Sort ties. There can be zero or more minor sorts.
In the example above, the Dept_No is the major sort and we have two minor sorts. The minor sorts are on the Salary and the Last_Name columns. Both Queries above have an equivalent Order by statement and sort exactly the same.
SELECT * FROM Student_Table
ORDER BY Class_Code ;
This sorts alphabetically. Can you change the sort so the Freshman come first,
followed by the Sophomores, Juniors, Seniors and then the Null?
Can you change the query to Order BY Class_Code logically (FR, SO, JR, SR, ?)?
This is the way the pros do it.
ALIAS Rules!
1) AS is optional
2) Use Double Quotes when Spaces are in the Alias name
3) Use Double Quotes when the Alias is a reserved word
When you ALIAS a column, you give it a new name for the report header. You should always reference the column using the ALIAS everywhere else in the query. You never need Double Quotes in SQL unless you are Aliasing.
Column names must be separated by commas. Notice in this example, there is a comma missing between Class_Code and Grade_Pt. What this will result in is only three columns appearing on your report with one being aliased wrong.
A Literal Value brings back the Literal Value! Also notice that the word ‘Character’ is stacked over the ‘Data’ portion of the heading for the second column using the Nexus Query Chameleon. So, as an alternative, a TITLE can be used instead of an alias and allows the user to include spaces in the output title.
The difference between an ALIAS and a TITLE is that the ALIAS can be used in the SQL again such as in the ORDER BY or WHERE statements, but a TITLE is only good for the report heading. Notice that Title uses Single Quotes not double quotes.
Double dashes make a single line comment that will be ignored by the system.
Slash Asterisk starts a multi-line comment and Asterisk Slash ends the comment.
Double Dashes in front of both lines comments both lines out and they’re ignored.
The query on the left had an error because the keyword Sum is reserved. We can test if this is the problem by commenting out that line in our SQL (example on the right). Now, our query works. We know the problem is on the line that we commented out. Once we put "Sum" (double quotes around the alias) it works. Use comments to help you debug.