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

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

Multimedia

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

Activities

 * 1) Select HR data using the ORDER BY clause.
 * 2) Run the query   to see all data in the EMPLOYEES table.
 * 3) Run the query   to see all employees in order by last name.
 * 4) Run the query   to see all employees in order by last name and first name.
 * 5) Run the query   to see all employees in order by descending salary and then last name and first name.
 * 6) Run the query   to see all employees in order by department ID, with no department ID listed first.
 * 7) Run the query   to see all employees in department 80 in order by descending salary and descending commission percentage.
 * 8) Select HR data using ampersand substitution.
 * 9) Run the query   to select a given employee at run time.
 * 10) Run the query   to select employees based on a hire date given at run time.
 * 11) Run the query   to select employees based on a column and value given at run time.
 * 12) Select OE data using the ORDER BY clause.
 * 13) Select all data in the CUSTOMERS table.
 * 14) Select all customers in order by country, postal code, last name, and first name.
 * 15) Select all customers in order by account manager and descending credit limit, with customers with no account manager listed last.
 * 16) Select all customers in order by account manager and descending credit limit, but do not include customers with no account manager.
 * 17) Select OE data using ampersand substitution.
 * 18) Create a query to select a given customer at run time.
 * 19) Create a query to select customers within a given credit range entered at run time.
 * 20) 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  FROM.

Assessments

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