Oracle Programming/Restricting and Sorting Data

    SELECT *|{[DISTINCT] COLUMN|expr [[AS] alias],...}
      FROM TABLE
    [WHERE condition(s)]
    [ORDER BY {COLUMN, alias, expr, numeric_position} [ASC|DESC] [NULLS FIRST|NULLS LAST] ];

Limiting the rows that are retrieved by a queryEdit

  • Write queries that contain a WHERE clause to limit the output retrieved
    • Character strings and date values are enclosed with single quote
    • Character values are case-sensitive and date values are format-sensitive
    • The default date display format is DD-MON-YY
    • An alias cannot be used in the WHERE clause
    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE department_id = 90;
 
    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE last_name = 'King';
 
    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE hire_date = '30-JAN-96';
  • List the comparison operators and logical operators that are used in a WHERE clause
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to (can also use != or ^=)
BETWEEN ... AND ... Between two values (inclusive)
IN (set) Match any value in a list
LIKE Match a character pattern '%' - zero or many; '_' - one character
IS NULL is a null value
AND returns TRUE if both conditions are true
OR returns TRUE if either condition is true
NOT returns TRUE if the condition is false
    -- must specify the lower limit first
    SELECT last_name, salary
    FROM   hr.employees
    WHERE salary BETWEEN 4000 AND 5000;
 
    -- can also use on character value
    SELECT last_name, salary
    FROM   hr.employees
    WHERE last_name BETWEEN 'Abel' AND 'Bull'
    ORDER BY last_name;
 
    SELECT last_name, salary
    FROM   hr.employees
    WHERE salary IN (4000,6000,8000);
 
    -- last name start with 'A' and 2 characters at least
    SELECT last_name, salary
    FROM   hr.employees
    WHERE last_name LIKE 'A_%';
 
    -- hire date at year 1999
    SELECT last_name, salary, hire_date
    FROM   hr.employees
    WHERE hire_date LIKE '%99';    
 
    -- employee doesn't report to any manager 
    SELECT last_name, salary
    FROM   hr.employees
    WHERE manager_id IS NULL;
 
    -- use AND, OR, NOT operators
    SELECT last_name, job_id, salary
    FROM   hr.employees
    WHERE (job_id LIKE 'AD%' OR job_id LIKE 'IT%')
    AND salary > 5000
    AND NOT last_name = 'King';    
 
    -- use ESCAPE identifier 
    SELECT last_name, job_id
    FROM   hr.employees
    WHERE job_id LIKE 'A_\_P%' ESCAPE '\';
  • Describe the rules of precedence for comparison and logical operators
Precedence Operators Description
1 parenthesis Expression within parenthesis always evaluate first
2 /, * Division and Multiplication
3 +, - Addition and subtraction
4 || Concatenation
5 =, <, >, <=, >= Equality and inequality comparison
6 [NOT] LIKE, IS [NOT] NULL, [NOT] IN Pattern, null, and set comparison
7 [NOT] BETWEEN Range comparison
8 <>, !=, ^= Not equal to
9 NOT NOT logical condition
10 AND AND logical condition
11 OR OR logical condition

Sorting the rows that are retrieved by a queryEdit

  • Write queries that contain an ORDER BY clause sort the output of a SELECT statement
 * The default sort order is ascending  
 * Null values are displayed last for ascending sequences and first for descending sequence
 * You can also sort by a column that is not in the SELECT list
    SELECT employee_id, last_name, salary*12 annsal
    FROM   hr.employees
    ORDER BY annsal DESC ;
  • Sort output in descending and ascending order
    SELECT   last_name, job_id, salary, commission_pct, salary*commission_pct "Comm"
    FROM     hr.employees
    ORDER BY commission_pct NULLS FIRST, 2 DESC, salary, "Comm";

Using ampersand substitution to restrict and sort output at runtimeEdit

Use substitution variables to:

  • Temporarily store values with single-ampersand (&) and double-ampersand (&&) substitution

Use substitution variables to supplement the following:

  • WHERE conditions
  • ORDER BY clauses
  • Column expressions
  • Table names
  • Entire SELECT statements
    --any &column_name after the &&column_name will not prompt for value again
    SELECT   employee_id, last_name, job_id, &&column_name
    FROM     hr.employees
    ORDER BY &column_name ;
Last modified on 14 March 2012, at 13:51