Chapter 11
Basic SQL Functions
“A journey of a thousand miles begins with a single step”
Table of Contents Chapter 11 – Basic SQL Functions
– SELECT * (All Columns) in a Table
– SELECT Specific Columns in a Table
– Using the Best Form for Writing SQL
– Place your Commas in front for better Debugging Capabilities
– Sort the Data with the ORDER BY Keyword
– ORDER BY Defaults to Ascending
– Use the Name or the Number in your ORDER BY Statement
– ORDER BY Defaults to Ascending
– Two Examples of ORDER BY using Different Techniques
– NULL Values sort First in Ascending Mode (Default)
– NULL Values sort Last in Descending Mode (DESC)
– Multiple Sort Keys using Names vs. Numbers
– Sorts are Alphabetical, NOT Logical
– Using A CASE Statement to Sort Logically
– A Missing Comma can by Mistake become an Alias
– The Title Command and Literal Data
– Comments using Double Dashes
– Comments using Double Dashes for Multiple Lines
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!
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 their own line.
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
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?
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
Rows typically come back to the report in random order, but we decided to use the ORDER BY statement. Now the data comes back ordered by Last_Name.
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.
ORDER BY Defaults to Ascending
If you want the data sorted in descending order just place DESC at the end.
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.
NULL Values sort First in Ascending Mode (Default)
SELECT *
FROM Student_Table
ORDER BY 5 ;
SELECT *
FROM Student_Table
ORDER BY Grade_Pt ;
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)
SELECT *
FROM Student_Table
ORDER BY 5 DESC ;
SELECT *
FROM Student_Table
ORDER BY Grade_Pt 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.
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 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!
Major Sort vs. Minor Sorts
Major sort is how things are sorted, but a minor sort kicks in if there are Major Sort ties.
Sorts are Alphabetical, NOT Logical
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, ?)
Using A CASE Statement to Sort Logically
How to ALIAS a Column Name
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.
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. What this will result in is only three columns appearing on your report with one being 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 and 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
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 using Double Dashes for Multiple Lines
Double Dashes in front of both lines comments both lines out and they're ignored.