Relational Concepts and Basic SELECT Retrieval
The model behind the syntax
A relational table holds rows over named columns. A primary key enforces uniqueness and non-nullness; a foreign key references a parent key and enforces referential integrity. NULL means "unknown", not zero and not blank - this single fact drives many exam traps.
Clause order versus evaluation order
Write clauses as SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. Oracle evaluates logically as FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That explains why a column alias is legal in ORDER BY but illegal in WHERE.
A bare SELECT 1 is not valid in Oracle; projection without a table uses FROM dual. DISTINCT de-duplicates the entire selected row, never just the first column. Arithmetic involving NULL returns NULL, while concatenation (||) treats NULL as an empty string - so 'a' || NULL is 'a'. Multiplication and division bind tighter than addition and subtraction; use parentheses.
Aliases: an unquoted alias is folded to uppercase and may not contain spaces; a double-quoted alias preserves case and spaces exactly. Always alias computed columns.
Worked example
SELECT last_name || ' earns ' || (salary * 12) AS "Annual Pay" FROM employees WHERE department_id = 50;
Here parentheses guarantee the multiplication happens before concatenation, and the quoted alias keeps mixed case and the space in the heading.
Rule of thumb
Anything you want to filter on must exist before the WHERE clause is evaluated - so never reference a SELECT alias there.