Oracle Database/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 query

• 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 query

• 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 runtime

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 ;