"Kites rise highest against the wind – not with it."
Sir Winston Churchill
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 *
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!
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 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 (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.
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!
Why is the example on the right better even though they are functionally equivalent?
Having commas in front to separate column names makes it easier to debug
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!
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.
The ORDER BY can use a number to represent the sort column. The number 2 represents the second column on the report. This is also going to default to ascending.
You have the option of using a number instead of the column name. The columns number is represented by what position it is in the SELECT statement, not the table. If you use an * in your Select Statement, then the columns number is represented by the position it is in the table. The two above queries are the same.
If you want the data sorted in descending order, just place DESC at the end.
(The default for ORDER BY is Ascending mode (ASC). Notice that this places the Null Values at the beginning of the Answer Set.
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 how things are sorted, but a minor sort kicks in if there are Major Sort ties.
These queries sort identically.
Queries can have a multiple columns in the ORDER BY. The first column in an ORDER BY is called the MAJOR SORT. Those after it are MINOR SORTS.
Both these Queries do the same thing. Once they sort Dept_No column in DESC order, they'll sort any ties by LAST_NAME, and then if any ties still occur, they'll sort by SALARY. Let me show you a real world example in the next slide!
SELECT * FROM Student_Table
ORDER BY Class_Code ;
This sorts alphabetically, but Sophomores (SO) logically come after Freshman
Change the query to Order BY Class_Code logically (FR, SO, JR, SR, ?)
We are using a CASE Statement to Order BY Class_Code logically (FR, SO, JR, SR, ?)
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 titled wrong.
LIMIT { ALL | <integer-number> }
The following is an example using LIMIT:
SELECT *
FROM Employee_table
LIMIT 3;
NPS offers a unique capability in its SQL to limit the number of rows returned from the SPU data. It is a LIMIT clause and is normally added at end of a valid SELECT statement with the above example and syntax.
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.
You can make multi-line comments with double dashes on each line.
Sometimes you get an error in your SQL, and it is difficult to find. When our first query ran, it produced an error and we were not sure if our Class_Code was the error. So, we commented that line out and ran our SQL again. Everything ran perfectly the next time, so we knew the Class_Code line must have been the error. What was the error? The alias Sum is a reserved word. Comments can be used to test lines for errors.
18.224.59.192