Chapter 15 - Basic SQL Functions

“A journey of a thousand miles begins with a single step”

- Lao-tzu

Introduction

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

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

Commas in the Front or in the 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 will produce the same answer set and have the same performance

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. 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 to do that in a few pages.

Use the Name or the Number in your ORDER BY Statement

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.

Two Examples of ORDER BY using Different Techniques

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.

Changing the ORDER BY to Descending Order

If you want the data sorted in descending order just place DESC at the end.

NULL Values sort First in Ascending Mode (Default)

The default for ORDER BY is Ascending mode (ASC). Notice that this places the Null Values at the beginning of the Answer Set.

NULL Values sort Last in Descending Mode (DESC)

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 vs. Minor Sorts

Major sort is how things are sorted, but a minor sort kicks in if there are Major Sort ties.

Multiple Sort Keys using Names vs. Numbers

  SELECT Employee_No
        ,Dept_No
        ,First_Name
        ,Last_Name
        ,Salary
  FROM Employee_Table
  ORDER BY Dept_No DESC
            ,Last_Name ASC
            ,Salary DESC;

  SELECT Employee_No
        ,Dept_No
        ,First_Name
        ,Last_Name
        ,Salary
  FROM Employee_Table
  ORDER BY 2 DESC,
            4,
            5 DESC;

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 of these Queries do the same thing. Once they sort Dept_No column in DESC order, they'll sort any ties by LAST_NAME. If any ties still occur, they'll sort by SALARY. Let me show you a real world example in the next slide!

Sorts are Alphabetical, NOT Logical

Change the query to Order BY Class_Code logically(FR, SO, JR, SR,?)

Using A CASE Statement to Sort Logically

We are using a CASE Statement to Order BY Class_Code logically(FR, SO, JR, SR,)

How to ALIAS a Column Name

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.

A Missing Comma can by Mistake become an Alias

Column names must be separated by commas. Notice in this example, there is a comma missing between Class_Code and Grade_Pt. This will result in only three columns appearing on your report, and one of them will be titled wrong.

The Title Command and Literal Data

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 which 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.

Comments using Double Dashes are Single Line Comments

Double dashes make a single line comment that will be ignored by the system.

Comments for Multi-Lines

Slash Asterisk starts a multi-line comment, and Asterisk Slash ends the comment.

Comments for Multi-Lines as Double Dashes per Line

You can make multi-line comments with double dashes on each line.

A Great Technique for Comments to Look for SQL Errors

Sometimes you get an error in your SQL, and it is difficult to find. When our first query ran, it produced an error. 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.