Teradata SQL

Tom Coffing, Mike Larkins

• Publisher: Coffing Publishing
• Year: 2013
• eBook ISBN: 9781940540153
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

This book includes over 1000 examples of all aspects of SQL starting at the most basic level and going to the most advanced level with real examples that work and explain exactly what is going on. The building block approach that continues to take things a step deeper at a time makes this a perfect SQL guide for everyone.

Chapter 1 - Basic SQL Functions

“A journey of a thousand miles begins with a single step.”
- Lao-tzu

Introduction

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.

SELECT * (All Columns) in a Table

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 Specific Columns in a Table

Column names must be separated by commas. Notice that only the columns requested come back on the report, not all columns. Also, notice that the order of the columns in the SQL is the same order on the report.

Using the Best Form for Writing SQL

SELECT	First_Name,
	Last_Name,
	Class_Code,
	Grade_Pt
FROM	Student_Table;

SELECT	First_Name,
	,Last_Name,
	,Class_Code,
	,Grade_Pt
FROM	Student_Table;

Can you spot the difference between these two examples?

Why is the example on the right better, even though they are functionally equivalent?

Commas in the Front or in the Back?

SELECT	First_Name,
	,Last_Name,
	,Class_Code,
	,Grade_Pt
FROM	Student_Table;

SELECT	First_Name,
	Last_Name,
	Class_Code,
	Grade_Pt
FROM	Student_Table;

Commas in the front (example 1) is Tera-Tom's recommendation to writing, but the next page is an even better example for a company standard. Both queries will produce the same answer set and have the same performance

Place your Commas in front for better Debugging Capabilities

SELECT	First_Name
	,Last_Name
	,Class_Code
	,Grade_Pt
FROM	Student_Table;

Successful

Having commas in front to separate column names makes it easier to debug.

Sort the Data with the ORDER BY Keyword

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!

ORDER BY Defaults to Ascending

SELECT *
FROM Student_Table
ORDER BY Last_Name;

When you use the ORDER BY statement, it will default to ascending order. But you can ...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• About Tom Coffing
• About Michael Larkins
• Contents
• Chapter 1 - Basic SQL Functions
• Chapter 2 - The WHERE Clause
• Chapter 3 - Distinct Vs. Group By
• Chapter 4 - The TOP Command
• Chapter 5 – Review
• Chapter 6 - HELP and SHOW
• Chapter 7 - Aggregation Function
• Chapter 8 - Join Functions
• Chapter 9 - Date Functions
• Chapter 10 - Format Functions
• Chapter 11 - OLAP Functions
• Chapter 12 - The Quantile Function
• Chapter 13 - Temporary Tables
• Chapter 14 - Sub-query Functions
• Chapter 15 - Substrings and Positioning Functions
• Chapter 16 - Interrogating the Data
• Chapter 17 - View Functions
• Chapter 18 - Macro Functions
• Chapter 19 - Set Operators Functions
• Chapter 20 – Creating Tables, Secondary Indexes, and Join Indexes
• Chapter 21 - Data Manipulation Language (DML)
• Chapter 22 - Stored Procedure Functions
• Chapter 23 - Trigger Functions
• Chapter 24 - Math Functions
• Chapter 25 - Sample
• Chapter 26 - Statistical Aggregate Functions
• Chapter 27 - Explain
• Chapter 28 - Collect Statistics
• Chapter 29 - Hashing Functions
• Chapter 30 - BTEQ – Batch Teradata Query
• Chapter 31 – Top SQL Commands Cheat Sheet
• Back Cover