# Oracle SQL Fundamentals/Sorting Data

This lesson introduces sorting data using the ORDER BY clause and runtime ampersand statement substitution.

## Objectives and Skills

Objectives and skills for the sorting data portion of Oracle SQL Fundamentals I certification include:
* Restricting and Sorting Data
** Sort the rows that are retrieved by a query
** Use ampersand substitution to restrict and sort output at runtime

## Readings

* Read Wikipedia: Order by (SQL).
* Read Oracle: Using Substitution Variables.

## Multimedia

* YouTube: Oracle SQL Tutorial - Querying data - Part 4 - (ORDER BY clause)

## Activities

* Select HR data using the ORDER BY clause.
** Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
** Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME; to see all employees in order by last name.
** Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME, FIRST_NAME; to see all employees in order by last name and first name.
** Run the query SELECT * FROM EMPLOYEES ORDER BY SALARY DESC, LAST_NAME, FIRST_NAME; to see all employees in order by descending salary and then last name and first name.
** Run the query SELECT * FROM EMPLOYEES ORDER BY DEPARTMENT_ID NULLS FIRST; to see all employees in order by department ID, with no department ID listed first.
** Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 ORDER BY SALARY DESC, COMMISSION_PCT DESC; to see all employees in department 80 in order by descending salary and descending commission percentage.
* Select HR data using ampersand substitution.
** Run the query SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMPLOYEE_ID; to select a given employee at run time.
** Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '&STARTING_DATE' AND '&ENDING_DATE'; to select employees based on a hire date given at run time.
** Run the query SELECT * FROM EMPLOYEES WHERE &&COLUMN < &VALUE ORDER BY &COLUMN; to select employees based on a column and value given at run time.
* Select OE data using the ORDER BY clause.
** Select all data in the CUSTOMERS table.
** Select all customers in order by country, postal code, last name, and first name.
** Select all customers in order by account manager and descending credit limit, with customers with no account manager listed last.
** Select all customers in order by account manager and descending credit limit, but do not include customers with no account manager.
* Select OE data using ampersand substitution.
** Create a query to select a given customer at run time.
** Create a query to select customers within a given credit range entered at run time.
** Create a query to select and sort customers based on a column and value entered at run time.

## Lesson Summary

* By default, relational database systems may return data rows in any order, or more specifically, without any order.
* An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns.
* The sort criteria do not have to be included in the result set.
* The sort criteria can be expressions, including column names, user-defined functions, arithmetic operations, or CASE expressions.
* The basic ORDER BY clause syntax is SELECT <column(s)> FROM <table(s)> ORDER BY <column> [DESC] [NULLS FIRST], ....
* The DESC keyword will sort a given column in descending order.
* The NULLS FIRST will sort null values first. The SQL standard does not define define a default sort order for nulls, but Oracle defaults to NULLS LAST.
* When combined with a WHERE clause, the syntax is SELECT <column(s)> FROM <table(s)> WHERE predicate ORDER BY <columns>
* A substitution variable is preceded by one or two ampersands (&, &&).
* Oracle prompts for values for undefined variables preceded by an ampersand.
* Oracle prompts for values for undefined variables preceded by two ampersands and defines those values.
* Substitution variables may be undefined using the UNDEFINE keyword.
* SQL*Plus will echo before and after SQL statements to verify proper substitution. This echo may be disabled using the command SET VERIFY OFF.

## Assessments

* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement

## See Also

* Oracle: Database 2 Day Developers Guide - Selecting Table Data