Chapter 2 – The Basics of SQL
“As I would not be a slave, so I would not be a master.”
- Abraham Lincoln
Introduction
The Student_Table above will be used
in our early SQL Examples
“The man who has no imagination has no wings.”
– Muhammad Ali
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. This book is going to be so fun you will fly like a butterfly!
Setting Your Default Database
SQL Server allows you do set your default database. Above, we have set our default database to SQL_Class. If we run a query without specifying the database, then SQL Server will assume the database is SQL_Class.
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!
Fully Qualifying a Database, Schema and Table
To refer to objects in other databases on the SQL Server or Azure SQL Data Warehouse system, you must use three-level naming, which consists of the database, the schema (which is the name of the database owner), and the object (table or view etc.). The last example (SQL_Class..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 ;
This is a great way to show the columns you are selecting from the Table_Name.
Commas in the Front or Back?
Why is the example on the left better even though they are functionally equivalent? Errors are easier to spot and comments won't cause errors.
Place your Commas in front for better Debugging Capabilities
"A life filled with love may have some thorns, but a life empty of love will have no roses."
Anonymous
Having commas in front to separate column names makes it easier to debug. Remember our quote above. "A query filled with commas at the end just might fill you with thorns, but a query filled with commas in the front will allow you to always come up smelling like roses."
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.
Two Examples of ORDER BY using Different Techniques
Notice that the answer set is sorted in ascending order based on the column Grade_Pt. Also notice that Grade_Pt is the fifth column coming back on the report. That is why the SQL in both statements is ordering by Grade_Pt. Did you notice that the null value came back first? Nulls sort first in ascending order and last in descending order.
Changing the ORDER BY to Descending Order
Notice that the answer set is sorted in descending order based on the column Last_Name. Also, notice that Last_Name is the second column coming back on the report. We could have done an Order By 2. If you spell out the word DESCENDING the query will fail, so you must remember to just use DESC.
NULL Values sort First in Ascending Mode (Default)
Did you notice that the null value came back first? Nulls sort first in ascending order and last in descending order.
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 the first sort. There can only be one major sort. A minor sort kicks in if there are Major Sort ties. There can be zero or more minor sorts.
Multiple Sort Keys using Names vs. Numbers
These queries sort identically
In the example above, the Dept_No is the major sort and we have two minor sorts. The minor sorts are on the Salary and the Last_Name columns. Both Queries above have an equivalent Order by statement and sort exactly the same.
Sorts are Alphabetical, NOT Logical
SELECT * FROM Student_Table
ORDER BY Class_Code ;
This sorts alphabetically. Can you change the sort so the Freshman come first, followed by the Sophomores, Juniors, Seniors and then the Null?
Can you change the query to Order BY Class_Code logically (FR, SO, JR, SR, ?)?
Using A CASE Statement to Sort Logically
This is the way the pros do it.
An Order By That Uses an Expression
SELECT RTRIM(Last_Name) + ', ' + First_Name AS FullName
FROM Employee_Table
ORDER BY RTRIM(Last_Name) + ', ' + First_Name;
SELECT RTRIM(Last_Name) + ', ' + First_Name AS FullName
FROM Employee_Table
ORDER BY FullName;
FullName
Chambers, Mandee
Coffing, Billy
Harrison, Herbert
Jones, Squiggy
Larkins, Loraine
Reilly, William
Smith, John
Smythe, Richard
Strickling, Cletus
The above examples are equivalent. We actually used the FullName expression in the ORDER BY of the first example. The second example uses the alias FullName.
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 never need Double Quotes in SQL unless you are aliasing.
Aliasing a Column Name with Spaces or Reserved Words
When you ALIAS a column, you give it a new name for the report header. If your alias is a reserved word or has a space in it you can still use it, but you must use either double quotes, single quotes or brackets.
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 aliased wrong.
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
Double Dashes in front of both lines comments both lines out and they’re ignored.
A Great Technique for Comments to Look for SQL Errors
The query on the left had an error because the keyword Sum is reserved. We can test if this is the problem by commenting out that line in our SQL (example on the right). Now, our query works. We know the problem is on the line that we commented out. Once we put "Sum" (double quotes around the alias) it works. Use comments to help you debug.
sp_help at the Database Level
sp_help ;
The sp_help command lists the available objects in the current database. There is much more provided than just the objects, but each object will be displayed. Above, we ran the sp_help command while in the SQL_Class database.
sp_help at the Object Level
sp_help 'Customer_Table ;
Data_located_on_filegroup
PRIMARY
The sp_help Object_Name command lists a wide variety of information about an object. Above is an example of using the command on the Customer_Table. There are actually nine sets of data about the object such as: Name, creation date, owner, Columns, Identity columns, Row GUID columns, FileGroup location, Indexes, Constraints, Foreign Key references and View references. This information is essential when examining or troubleshooting a table.
Getting System Information
“Not all who wander are lost.”
– J. R. R. Tolkien
The query above uses SQL Server and Azure SQL Data Warehouse reserved names to get the User, their login, the host and the application names. When you can get system information you are never lost!
Getting Additional System Information
The query above uses SQL Server and Azure SQL Data Warehouse reserved names to get additional information on the system.