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
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
Most 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 ;
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 Can Use the Column Number
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!
SORT BY Can Be Used Instead of ORDER BY
Rows typically come back to the report in random order. To order the result set, you usually use an ORDER BY, which completely orders the data. This is because the actual sorting is accomplished by using a single node. However, you can use a SORT BY statement. The SORT BY statement performs differently. If there is more than one node, sort by provides a partial sorting of the data by node, but not necessarily a 100% perfect sort.
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.
Using the SORT BY in DESC Mode
The SORT BY statement performs differently than an ORDER BY. If there is more than one reducer, sort by provides a partial sorting of the data by reducer, but not necessarily a 100% perfect sort.
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.
SORT BY Using Major and Minor Sorts
If you use an ORDER BY statement it completely orders the data because Hadoop uses a single reducer. However, you can use a SORT BY statement, which actually performs differently. If there is more than one reducer, sort by provides a partial sorting of the data by reducer, but not necessarily a 100% perfect sort. Be careful!
SORT BY Defaults to Ascending
Notice we did not use the keyword ASC on the minor sort. We don't need to because the default is ascending mode.
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.
How to ALIAS a Column Name
When you ALIAS a column, you give it a new name for the report header.
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 alias Sum has single quotes. 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. That is another reason to also put your commas in front of the columns, otherwise this technique won't work.