Tera-Tom Genius Series - Oracle SQL

Tom Coffing, Leona Coffing

• Publisher: Coffing Publishing
• Year: 2016
• ISBN:
  9781940540351
• Topic: Informatik
• Subtopic: Data-Warehousing

Oracle is one of the most popular database in the world. This incredible database is designed for on-premises systems and the cloud. Learning Oracle SQL is a great opportunity for you to access cutting edge technology. Tera-Tom Coffing and Leona Coffing have written a brilliant book that teaches readers the basics to advanced queries of Oracle SQL. This book is perfect for anyone who needs to write Oracle SQL. The book educates readers with over 600 pages of SQL examples and explanations, plus provides clever tricks and examples to provide an expert level of learning. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leona Coffing, Chief Financial Officer (CFO) of Coffing Data Warehousing bring a combined 40 years of experience of data warehouse knowledge to create this must have book.

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

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