Retrieve Data using the SQL SELECT Statement
What the domain covers
This domain tests the SELECT statement as the foundation of Oracle SQL: choosing columns, creating expressions, naming output columns, eliminating duplicate rows, and understanding NULL behavior in arithmetic and concatenation.
Core rules
- The logical clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. SELECT is written first but evaluated after FROM and WHERE.
SELECT *returns every column; explicit column lists are safer and easier to read.- Arithmetic uses + - * / with multiplication and division before addition and subtraction; parentheses override.
- The concatenation operator is
||. NULL in arithmetic yields NULL; NULL in concatenation is treated as no characters, so 'A' || NULL returns 'A'. - Column aliases rename output. Unquoted aliases are case-insensitive and cannot contain spaces; quoted aliases preserve case and allow spaces.
- DISTINCT removes duplicate rows after projection and applies to the whole selected row, not one column.
Worked example
SELECT last_name || ', ' || salary * 12 AS annual_salary FROM employees;
Reasoning: concatenation combines last name, comma and space. Multiplication runs before concatenation, so salary is multiplied by 12 first. The alias annual_salary names the expression. If salary is NULL, the annual salary expression is NULL and the concatenated result ends after the comma and space.
Traps
Do not use a column alias in WHERE; use it in ORDER BY. DISTINCT is not a function and does not apply to only the next column. Quoted aliases must be referenced with matching case. NULL + 100 is NULL, but 'x' || NULL is 'x'.