1Z0-071 Exam Prep
Chapter 2
Volume 1
The SELECT statement is the foundation of every SQL query on the exam. Chapter 2 looks straightforward but it contains several rules that catch candidates — particularly around column aliases, the behaviour of DISTINCT with multiple columns, arithmetic on NULL, and which clauses are mandatory versus optional.
This post covers the SELECT mechanics the exam actually tests, not just the basic syntax you already know.
In this post
- SELECT statement structure and mandatory clauses
- Selecting all columns vs specific columns
- Column aliases — AS and without AS
- Aliases with spaces or special characters
- Arithmetic expressions in SELECT
- String concatenation with ||
- DISTINCT — removing duplicate rows
- DISTINCT with multiple columns
- Operator precedence in expressions
- SQL statement rules — case, whitespace, termination
- Quick reference
- Practice questions
01 — SELECT statement structure and mandatory clauses
A SELECT statement has six possible clauses. Only two are mandatory. The exam tests which clauses are required and what order they must appear in.
| Clause | Required? | Purpose |
|---|---|---|
SELECT | Yes | Columns or expressions to retrieve |
FROM | Yes | Table(s) to retrieve from |
WHERE | No | Filter rows before grouping |
GROUP BY | No | Collapse rows into groups |
HAVING | No | Filter groups after grouping |
ORDER BY | No | Sort the final result |
-- Minimum valid SELECT: SELECT and FROM only SELECT last_name, salary FROM employees; -- Full clause order — must always appear in this sequence SELECT department_id, AVG(salary) FROM employees WHERE salary > 5000 GROUP BY department_id HAVING AVG(salary) > 8000 ORDER BY department_id; -- Swapping clause order (e.g. ORDER BY before WHERE) raises an error
Clause order is fixed: The clauses must appear in the sequence shown above. Writing
ORDER BY before WHERE, or HAVING before GROUP BY, raises a syntax error. The exam will present incorrectly ordered clauses as wrong answer options.
02 — Selecting all columns vs specific columns
-- Select all columns using * SELECT * FROM employees; -- Returns all columns in the order they were defined in CREATE TABLE -- Column order in the result matches the table definition order -- Select specific columns — in any order you choose SELECT last_name, employee_id, salary FROM employees; -- Columns appear in the order listed in SELECT — not table definition order -- Same column can appear more than once SELECT last_name, salary, salary * 12 FROM employees; -- salary appears twice — as the raw value and as annual salary
SELECT * in production: Using
SELECT * is valid but not recommended in applications because adding a column to the table changes what the query returns. The exam accepts SELECT * as valid syntax and tests what it returns — all columns in table definition order.
03 — Column aliases — AS and without AS
A column alias gives a different name to a column or expression in the query result. The AS keyword is optional — both forms are valid and both appear on the exam.
-- Using AS (recommended for clarity) SELECT last_name AS name, salary * 12 AS annual_salary, department_id AS dept FROM employees; -- Without AS — space between column and alias is enough SELECT last_name name, salary * 12 annual_salary, department_id dept FROM employees; -- Both forms produce identical results
Aliases are NOT available in WHERE or HAVING: A column alias defined in SELECT cannot be used in the WHERE clause or HAVING clause of the same query — those clauses execute before SELECT. The alias IS available in ORDER BY because that executes after SELECT. This rule is tested repeatedly across many exam topics.
04 — Aliases with spaces or special characters
If a column alias contains spaces, special characters, or needs to preserve mixed case, it must be wrapped in double quotes. This is one of the more specific syntax rules the exam tests.
-- Simple alias — no quotes needed SELECT salary * 12 AS annual_salary FROM employees; -- Alias with a space — double quotes required SELECT salary * 12 AS "Annual Salary" FROM employees; -- Result column header: Annual Salary (with capital letters preserved) -- Alias with special characters — double quotes required SELECT salary AS "Salary ($)" FROM employees; -- Preserve exact case — double quotes required SELECT last_name AS "lastName" FROM employees; -- Without quotes: stored and displayed as LASTNME (uppercase) -- With quotes: stored and displayed as lastName (exact case) -- WRONG: single quotes cannot be used for aliases SELECT salary AS 'Annual Salary' FROM employees; -- Error: single quotes are for string literals, not aliases
Double quotes for aliases, single quotes for strings: Single quotes delimit string literals (
'King'). Double quotes delimit identifiers like aliases ("Annual Salary"). Using single quotes for an alias raises an error. The exam regularly swaps these to test whether you know the difference.
05 — Arithmetic expressions in SELECT
The SELECT list can contain arithmetic expressions on numeric and date columns. The standard operators work as expected — with one important rule about NULL.
-- Arithmetic on numeric columns SELECT last_name, salary, salary * 12 AS annual_sal, salary * 12 + NVL(commission_pct, 0) * salary * 12 AS total_annual FROM employees; -- NULL in arithmetic always produces NULL SELECT salary + commission_pct FROM employees; -- Returns NULL for any employee where commission_pct IS NULL -- Use NVL to substitute a default before arithmetic
| Operator | Meaning | Priority |
|---|---|---|
* / | Multiply, divide | Higher (evaluated first) |
+ - | Add, subtract | Lower |
Parentheses () | Override precedence | Highest — always evaluated first |
-- Operator precedence in expressions salary + 100 * 12 -- = salary + 1200 (multiplication first) (salary + 100) * 12 -- = salary*12 + 1200 (parentheses override)
06 — String concatenation with ||
The concatenation operator || joins two or more strings. It can combine column values, string literals, and function results into a single output string.
-- Basic concatenation SELECT first_name || ' ' || last_name AS full_name FROM employees; -- Returns: 'Steven King', 'Neena Kochhar' etc. -- Concatenating with other data types SELECT last_name || ' earns ' || salary AS info FROM employees; -- Oracle implicitly converts salary (NUMBER) to VARCHAR2 for || -- Returns: 'King earns 24000' -- NULL in concatenation: Oracle treats it as empty string SELECT 'Hello' || NULL || ' World' FROM dual; -- Returns: 'Hello World' (NULL treated as empty string in ||) -- This is Oracle-specific — ANSI SQL returns NULL
Implicit type conversion in ||: Oracle automatically converts numbers and dates to strings when they appear on either side of
||. You do not need TO_CHAR for basic concatenation — though using it gives you control over the format.
07 — DISTINCT — removing duplicate rows
DISTINCT eliminates duplicate rows from the query result. It applies to the entire row — not just one column — and it appears immediately after the SELECT keyword.
-- Without DISTINCT: all 107 rows returned, many duplicate department_ids SELECT department_id FROM employees; -- With DISTINCT: only unique department_id values returned SELECT DISTINCT department_id FROM employees; -- Returns one row per unique department_id -- UNIQUE is a synonym for DISTINCT in Oracle SELECT UNIQUE department_id FROM employees; -- Identical result — both keywords mean the same thing
UNIQUE keyword: Oracle accepts both DISTINCT and UNIQUE as synonyms for removing duplicates from a SELECT result. Other databases do not support UNIQUE this way. The exam occasionally uses UNIQUE to test whether candidates recognise it as valid Oracle syntax.
08 — DISTINCT with multiple columns
When DISTINCT is used with multiple columns, the deduplication applies to the combination of all selected columns — not to each column individually. This is the most commonly misunderstood behaviour of DISTINCT.
-- DISTINCT applies to the full combination of (department_id, job_id) SELECT DISTINCT department_id, job_id FROM employees; -- Returns one row per unique (department_id, job_id) pair -- If dept 50 has 3 different job_ids, it contributes 3 rows -- NOT one row per department_id — the combination is what is distinct -- Example data to illustrate: -- dept 50 / IT_PROG → 1 row -- dept 50 / SA_REP → 1 row (same dept, different job = different combination) -- dept 50 / SA_REP → duplicate, removed -- dept 80 / SA_REP → 1 row (different dept)
DISTINCT is not per-column:
SELECT DISTINCT dept_id, job_id does not give you distinct dept_ids with distinct job_ids separately. It gives you distinct pairs. The exam presents scenarios where candidates confuse this and select the wrong row count.
09 — Operator precedence in expressions
Oracle evaluates expressions in a defined order. Understanding precedence is needed to predict the result of arithmetic and concatenation expressions without parentheses.
-- Arithmetic precedence: * and / before + and - SELECT 2 + 3 * 4 FROM dual; -- Returns: 14 (3*4=12, then +2) SELECT (2 + 3) * 4 FROM dual; -- Returns: 20 (2+3=5, then *4) -- Multiple operators at same level: left to right SELECT 10 - 3 - 2 FROM dual; -- Returns: 5 ((10-3)-2 = 7-2 = 5) SELECT 12 / 4 * 3 FROM dual; -- Returns: 9 ((12/4)*3 = 3*3 = 9) -- Real-world example: salary calculation SELECT salary + 500 * 12 FROM employees; -- Adds 6000 to salary — NOT (salary+500)*12 -- To get (salary+500)*12: SELECT (salary + 500) * 12 FROM employees
10 — SQL statement rules — case, whitespace, termination
The exam tests basic SQL syntax rules that are easy to overlook because most tools are forgiving.
| Rule | Detail | Example |
|---|---|---|
| Keywords are case-insensitive | SELECT, select, Select all work | select * from EMPLOYEES is valid |
| Column and table names are case-insensitive | EMPLOYEES, employees, Employees all refer to the same table | FROM employees same as FROM EMPLOYEES |
| String literals are case-sensitive | 'King' ≠ 'king' ≠ 'KING' | WHERE last_name = 'king' does not match 'King' |
| Whitespace is flexible | Extra spaces, tabs, and newlines are ignored | Splitting a query across lines is fine |
| Semicolon terminates the statement | Required in SQL*Plus; optional in some tools | SELECT * FROM employees; |
| Comments | Single line: -- Multi-line: /* */ | -- this is a comment |
-- All of these are valid and identical SELECT last_name, salary FROM employees WHERE department_id = 50; select LAST_NAME, SALARY from EMPLOYEES where DEPARTMENT_ID = 50; -- These are NOT identical — string literals are case-sensitive WHERE last_name = 'King' -- finds 'King' WHERE last_name = 'king' -- finds nothing (no employee named 'king') WHERE last_name = 'KING' -- finds nothing (stored as 'King')
11 — Quick reference
| Rule | Detail |
|---|---|
| Only SELECT and FROM are mandatory | WHERE, GROUP BY, HAVING, ORDER BY are all optional |
| Clause order is fixed | SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY |
| AS keyword is optional for aliases | Both salary annual_sal and salary AS annual_sal work |
| Double quotes for aliases with spaces | "Annual Salary" — single quotes raise an error |
| Column alias usable in ORDER BY only | Not in WHERE, GROUP BY, or HAVING |
| NULL in arithmetic = NULL | Any arithmetic with NULL produces NULL |
| NULL in || = empty string (Oracle) | 'A' || NULL returns 'A' in Oracle |
| DISTINCT applies to entire row | Not per-column — deduplicates the full combination |
| UNIQUE = DISTINCT in Oracle SELECT | Both keywords are accepted |
| * and / before + and - | Use parentheses to override precedence |
| Keywords case-insensitive | select = SELECT = Select |
| String literals case-sensitive | 'King' ≠ 'king' |
12 — Practice questions
Q1 — A developer writes this query. What is wrong with it?
SELECT last_name, salary * 12 AS annual_sal FROM employees WHERE annual_sal > 60000;Q2 — The EMPLOYEES table has 107 rows across 12 departments and 19 different job types. How many rows does this return?
SELECT DISTINCT department_id, job_id FROM employees;Q3 — Which of these column alias definitions is invalid Oracle syntax?
Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 2 covers the full SELECT statement — clause structure, aliases, expressions, DISTINCT, concatenation, and all the syntax rules the exam tests. Instant PDF download.
Get the full guide — $25 →
Chapter 2 covers the full SELECT statement — clause structure, aliases, expressions, DISTINCT, concatenation, and all the syntax rules the exam tests. Instant PDF download.
No comments:
Post a Comment