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 Wikipedia: 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.
   1. Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
   2. Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME; to see all employees in order by last name.
   3. Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME, FIRST_NAME; to see all employees in order by last name and first name.
   4. 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.
   5. 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.
   6. 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.
2. Select HR data using ampersand substitution.
   1. Run the query SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMPLOYEE_ID; to select a given employee at run time.
   2. 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.
   3. 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.
3. Select OE data using the ORDER BY clause.
   1. Select all data in the CUSTOMERS table.
   2. Select all customers in order by country, postal code, last name, and first name.
   3. Select all customers in order by account manager and descending credit limit, with customers with no account manager listed last.
   4. Select all customers in order by account manager and descending credit limit, but do not include customers with no account manager.
4. Select OE data using ampersand substitution.
   1. Create a query to select a given customer at run time.
   2. Create a query to select customers within a given credit range entered at run time.
   3. 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

References

← Restricting Data

Oracle SQL Fundamentals

Single-Row Functions →