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 ;