Skip to content Skip to main navigation Skip to footer

Anatomy of a SQL Statement

Objectives

This tutorial covers the following objectives:

  • Match projection and selection with their correct capabilities
  • Create a basic SELECT statement
  • Use the correct syntax to display all rows in a table
  • Use the correct syntax to select specific columns in a table, modify the way data is displayed, and perform calculations using arithmetic expressions and operators
  • Formulate queries using correct operator precedence to display desired results
  • Define a null value
  • Demonstrate the effect null values create in arithmetic expressions
  • Construct a query using a column alias

SELECT Keyword

  • SELECT is one of the most important, if not the most important, keyword in SQL.
  • You use SELECT to retrieve information from the database. When you learn how to use SELECT, you’ve opened the door to the database.
  • Imagine a database containing information about movies such as title, genre, studio, producer, release date, series, country, language, rating, running time, and so on.
  • What if you only wanted the titles of movies created in Tunisia?
  • The SELECT statement allows you to search for specific data.
  • The SELECT statement retrieves information from the database.
  • The syntax for a SELECT statement is as follows:
SELECT <column_name(s)>
FROM <table_name>;
  • In its simplest form, a SELECT statement must include the following:
  • A SELECT clause, which specifies the columns to be displayed
  • A FROM clause, which specifies the table containing the columns listed in the SELECT clause

Conventions

  • Throughout this course, the following will be used:
SELECT last_name
FROM employees 
  • A keyword refers to an individual SQL command.
  • For example, SELECT and FROM are keywords.
  • A clause is a part of a SQL statement.
  • For example, SELECT last_name is a clause.
  • A statement is a combination of two or more clauses.
  • For example, SELECT last_name FROM employees is a statement.

Capabilities of SELECT Statements

  • Projection: Used to choose columns in a table
  • Selection: Used to choose rows in a table
Capabilities of SELECT Statements

Projection and Selection

Projection and Selection

Selecting All Columns

  • You can display all of the columns of data in a table by using an asterisk symbol (*) instead of a column name in the SELECT clause.
  • In the example shown, all of the columns in the countries table are selected.
SELECT *
FROM countries;
Relational-Databases
  • You can also display all the columns in a table by listing them individually.
SELECT country_id, country_name, region_id
FROM countries;

Sometimes doing a SELECT * FROM table can take a long time to return the data. It all depends on the number of rows stored in that particular table. Remember, Oracle tables can store millions of rows of data.

By the way, all examples on the tutorial have a semicolon after each statement. Oracle Application Express does not require this syntax, but other Oracle SQL Interfaces do, so we have included them in the course-ware.

Projecting Specific Columns

  • If you want to PROJECT only specific columns from a table to be displayed, simply list each of the column names you want and separate each name with a comma in the SELECT clause.
SELECT location_id, city, state_province
FROM locations;
Projecting Specific Columns

Using Arithmetic Operators

  • Using a few simple rules and guidelines, you can construct SQL statements that are both easy to read and easy to edit.
  • Knowing the rules will make learning SQL easy.
  • You may need to modify the way in which data is displayed, perform calculations, or look at what-if scenarios.
  • For example, “What if every employee was given a 5% raise?
  • How would that affect our yearly profit figures?”
  • These types of calculations are all possible using arithmetic expressions.
  • You are already familiar with arithmetic expressions in mathematics:
    • add (+), subtract (-) , multiply (*) and divide (/)
  • Note that this example does not create new columns in the tables or change the actual data values.
  • The results of the calculations will appear only in the output.
  • The example shown uses the addition operator to calculate a salary increase of 300 for all employees and displays a new SALARY + 300 column in the output.
SELECT last_name, salary, salary + 300
FROM employees;
  • Putting in blank spaces before and after an arithmetic operator will not affect the output.

Precedence in Arithmetic Operators

  • Precedence is the order in which Oracle evaluates different operators in the same expression.
  • When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence.
  • Oracle evaluates operators with equal precedence from left to right within an expression.
  • Arithmetic operators perform the mathematical operations of Multiplication, Division, Addition, and Subtraction.
  • If these operators appear together in an expression, multiplication and division are evaluated first.
  • So the order is: * / + -.
  • An easy way to remember their operator precedence is the mnemonic device: My Dear Aunt Sally
  • If operators within an expression are of the same priority, then evaluation is done from left to right.
  • You can always use parentheses to force the expression within parentheses to be evaluated first.
  • In the example tables shown below, note the differences in the output between the query that used parentheses and the one that didn’t.
Precedence in Arithmetic Operators

The example on the left uses Operator Precedence to determine the order in which the operations are performed. As * (multiplication) has higher precedence, the salary is first multiplied by 12, then 100 is added to the result of the multiplication. So for employee King (salary 24000), 12*24000 is calculated = 288000, then 100 is added to give the answer 288100.

The example on the right uses parenthesis to force the addition to be carried out first, the result of the addition is then multiplied by 12. So King has 24000+100 = 24100, this is then multiplied by 12 to give the answer 289200.

NULL Values

  • In SQL, NULL is an interesting word.
  • To understand NULL, you have to know what NULL is and what NULL is not.
  • NULL is a value that is unavailable, unassigned, unknown, or inapplicable.
  • NULL is not the same as a zero or a space.
  • In SQL, a zero is a number, and a space is a character.
  • Sometimes, you don’t know the value for a column.
  • In a database, you can store unknowns in your databases.
  • Relational databases use a placeholder called NULL or null to represent these unknown values.
  • If any column value in an arithmetic expression is null, the result is null or unknown.
  • If you try to divide by null, the result is null or unknown.
  • However, if you try to divide by zero, you get an error.
NULL Values
SELECT last_name, job_id, salary, commission_pct, salary*commission_pct
FROM employees;
NULL Values

In the example, a row with a null value in the commission_pct column will return a null value for the salary*commission_pct column in this query. NULL values are displayed in APEX using the – (dash) symbol.

Aliases

  • An Alias is a way of renaming a column heading in the output.
  • Without aliases, when the result of a SQL statement is displayed, the name of the columns displayed will be the same as the column names in the table or a name showing an arithmetic operation such as 12*(SALARY + 100).
  • You probably want your output to display a name that is easier to understand, a more “friendly” name.
  • Column aliases let you rename columns in the output.
  • There are several rules when using column aliases to format output.
  • A column alias:
    • Renames a column heading
    • Is useful with calculations
    • Immediately follows the column name
    • May have the optional AS keyword between the column name and alias
    • Requires double quotation marks if the alias contains spaces or special characters, or is case-sensitive

Using Column Aliases

  • The syntax for aliases is:
SELECT * |column|expr [ AS alias], .....
FROM table;
  • Examples:
SELECT last_name AS name,
commission_pct AS comm
FROM employees;
Using Column Aliases
SELECT last_name "Name",
salary*12 "Annual Salary"
FROM employees;
Using Column Aliases

The first example uses the AS keyword, but has no “double quotes “, so the aliases are displayed in the default UPPER case. The second example though omits AS, but as the intended alias is more than one word, uses the double quotes. Using the optional AS keyword makes SQL statements easier to read.

Terminology

Key terms used in this tutorial included:

  • Arithmetic expression
  • Arithmetic operator
  • Clause
  • Column
  • Column alias
  • From clause
  • NULL
  • Projection
  • Select clause
  • Selection
  • Select statement
  • Statement
  • WHERE Clause
  • * (Asterisk)

Summary

In this tutorial, you should have learned how to:

  • Match projection and selection with their correct capabilities
  • Create a basic SELECT statement
  • Use the correct syntax to display all rows in a table
  • Use the correct syntax to select specific columns in a table, modify the way data is displayed, and perform calculations using arithmetic expressions and operators
  • Formulate queries using correct operator precedence to display desired results
  • Define a null value
  • Demonstrate the effect null values create in arithmetic expressions
  • Construct a query using a column alias

Was This Article Helpful?

1
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *