Teradata SQL

Tom Coffing, Mike Larkins

• Publisher: Coffing Publishing
• Year: 2013
• 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 ...