Chapter 1 – 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, the query and the result set.

Setting Your Default SCHEMA

Oracle 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 Oracle will assume the database is SQL_Class. Oracle refers to this as a schema.

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

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 last? Nulls sort last in ascending order and first 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)

You can ORDER BY in descending order by putting a DESC after the column name or its corresponding number. Null Values will sort first in DESC order.

NULL Values sort First 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 first 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

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

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

Formatting Number

Oracle gives you many options for formatting numbers. The next page will show an example.

Formatting Number Examples

Above you can seen an example of formatted numbers using the TO_CHAR command.

Formatting Dates

Oracle gives you many options for formatting dates. The next page will show an example.

Formatting Date Example

Above you can seen an example of formatted dates using the TO_CHAR command.