Netezza SQL

Tom Coffing, Mike Larkins

• Publisher: Coffing Publishing
• Year: 2014
• eBook ISBN: 9781940540283
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

This book contains nearly 700 pages of brilliant Netezza examples ranging from simple commands to advanced procedures. Netezza SQL, like all others in the Genius Series, guides readers in a clear and logical manner using pictures and real-life examples. This book is a must-have for any Netezza environment.

Chapter 1 – Basic SQL Functions

"Kites rise highest against the wind – not with it."

Sir Winston Churchill

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

SELECT *
FROM Student_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!

Fully Qualifying a Database, Schema and Table

To refer to objects in other databases on the Netezza system, you must use three-level naming, which consists of the database (also referred to as catalog name), the schema (which is the name of the database owner), and the object (table, view or synonym). The last example (Sales..Dept) is a convenient way of specifying a fully qualified object name. The system supplies the schema name by internally inserting the current schema name.

SELECT Specific Columns in a Table

SELECT First_Name

,Last_Name

,Class_Code

,Grade_Pt

FROM Student_Table ;

Column names must be separated by commas. The next page will show perfect syntax, which will capitalize keywords and place each column on its own line.

Commas in the Front or Back?

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 above produce the same answer set and have the same performance.

Using Good Form

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. Let me show you an even better technique!

Using the Best Form for Writing SQL

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

Place your Commas in front for better Debugging Capabilities

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 by default. This is called the Major Sort!

ORDER BY Defaults to Ascending

When you use the ORDER BY statement, it will default to ascending order, but you can change that if you like. I will show you how in a few pages down.

Use the Name or the Number in your ORDER BY Statement

The ORDER BY can use a number to represent the sort column. The numbe...

Table of contents

• Cover
• The Tera-Tom Genius Series
• Tera-Tom- Author of over 50 Books
• The Best Query Tool Works on all Systems
• Copyright
• 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 – Review
• Chapter 5 – Aggregation Function
• Chapter 6 – Join Functions
• Chapter 7 – Date Functions
• Chapter 8 – OLAP Functions
• Chapter 9 – Temporary Tables
• Chapter 10 – Sub-query Functions
• Chapter 11 - Substrings and Positioning Functions
• Chapter 12 – Interrogating the Data
• Chapter 13 – View Functions
• Chapter 14 – Set Operators Functions
• Chapter 15 – Data Manipulations
• Chapter 16 – Tables, DDL, and Data Types
• Chapter 17 – Statistical Aggregate Functions
• Chapter 18 – Stored Procedure Functions
• Chapter 19 – Nexus Query Chameleon