1Z0-149: Retrieving Data Using the SQL SELECT Statement

What this domain tests

This domain covers the basic Oracle SELECT statement: choosing columns, filtering rows, naming output columns, using expressions, and eliminating duplicates. The exam expects precise syntax and an understanding of how NULL behaves in expressions.

Core rules

• SELECT list controls projection; FROM names the row source. SELECT * returns every column in table order.
• Column aliases may use AS (optional). An unquoted alias is stored in uppercase; a quoted alias preserves case and spaces and must be referenced with the same case later.
• DISTINCT applies to the whole selected row, not to one column.
• Arithmetic uses + - * /; parentheses change precedence. Any arithmetic involving NULL returns NULL.
• || concatenates strings. Oracle treats NULL as an empty string in concatenation, so 'A' || NULL returns A.
• String literals use single quotes. For literals containing quotes, use the alternative quoting syntax, for example q'[It's fine]'.
• DUAL is a one-row table used for expression tests.

Worked example

SELECT employee_id, salary * 12 AS annual_salary, first_name || ' ' || last_name AS full_name FROM employees WHERE department_id = 50;

The WHERE clause filters rows before the select list is evaluated for output. salary * 12 uses multiplication before concatenation; the concatenation combines three values and inserts a space. The alias annual_salary is available for ORDER BY, but not for WHERE.

Traps that cost marks

• Expecting a column alias to work in WHERE.
• Forgetting that DISTINCT with multiple columns compares the entire combination.
• Writing "annual_salary" in lowercase after defining an unquoted alias; it will not resolve.
• Assuming NULL concatenation returns NULL; it does not.
• Missing commas between select-list items.