Oracle SQL 1Z0-071 Exam Day Review: 50 Rules You Must Know Before You Sit the Test

1Z0-071 Exam Prep All 19 Chapters Final Review

This post is a single-page exam-day review — 50 rules pulled from across the entire exam that are worth reading through the night before you sit the test.

These are not definitions. They are the specific rules, edge cases, and distinctions that questions are built around. Each one has appeared as an exam trap in at least one chapter of this series.

NULL — the rules that appear in every topic

1

Any comparison with NULL returns UNKNOWN — not TRUE, not FALSE. UNKNOWN rows are excluded from WHERE results silently.

2

NOT IN with a NULL in the list always returns zero rows. The condition expands to a series of != comparisons joined by AND — the != NULL produces UNKNOWN and kills every row.

3

= NULL and != NULL are valid syntax but always return zero rows. IS NULL and IS NOT NULL are the only correct tests.

4

Aggregate functions except COUNT(*) skip NULL rows. AVG(col) divides by the count of non-null rows — giving a higher average than if NULLs were treated as zero.

5

NULL in arithmetic always produces NULL. But 'A' || NULL returns 'A' in Oracle — concatenation treats NULL as empty string. This is Oracle-specific, not ANSI standard.

6

ORDER BY ASC: NULLs appear last. ORDER BY DESC: NULLs appear first. Override with NULLS FIRST or NULLS LAST.

7

NULL in SET operators: UNION and INTERSECT treat NULL as equal to NULL for duplicate elimination — unlike comparison operators where NULL = NULL is UNKNOWN.

SELECT, filtering and sorting

8

Only SELECT and FROM are mandatory. WHERE, GROUP BY, HAVING, ORDER BY are all optional. Clause order is fixed — writing them out of sequence raises a syntax error.

9

Column alias defined in SELECT is only available in ORDER BY — not in WHERE, GROUP BY, or HAVING. Those clauses run before SELECT evaluates the aliases.

10

Double quotes for aliases with spaces ("Annual Salary"). Single quotes are for string literals only — using them for an alias raises an error.

11

DISTINCT applies to the entire row — not per column. SELECT DISTINCT dept_id, job_id returns distinct combinations, not distinct dept_ids.

12

BETWEEN is inclusive — both boundary values are returned. Lower bound must come first — BETWEEN 10000 AND 5000 returns zero rows.

13

AND has higher precedence than OR. A OR B AND C evaluates as A OR (B AND C). Use parentheses to override.

14

String comparisons are case sensitive. WHERE last_name = 'king' does not match 'King'. SQL keywords and object names are case-insensitive — string literals are not.

15

LIKE: % matches zero or more characters — 'S%' matches the single character 'S'. _ matches exactly one character. Both are case sensitive.

GROUP BY, HAVING and aggregates

16

Every non-aggregated column in SELECT must appear in GROUP BY. ORA-00979 if you include a column in SELECT that is not in GROUP BY and not wrapped in an aggregate.

17

You can GROUP BY a column that is not in the SELECT list — that direction is valid. Only the reverse direction (SELECT but not GROUP BY) is blocked.

18

Aggregate functions are not allowed in WHERE — ORA-00934. They belong in HAVING. You CAN use non-aggregate conditions in HAVING — it is valid but less efficient than WHERE.

19

Nested aggregates like MAX(AVG(salary)) are only valid when GROUP BY is present. Without GROUP BY: ORA-00978.

JOINs

20

INNER JOIN drops rows with no match on either side. An employee with NULL department_id is excluded from an INNER JOIN result.

21

NATURAL JOIN and USING: the join column cannot have a table alias. e.department_id in a query using USING (department_id) raises ORA-25154.

22

Legacy (+) operator: goes on the optional side (the side that can be NULL). Cannot replicate FULL OUTER JOIN with (+).

23

Self-join requires two different aliases. Without aliases Oracle cannot distinguish which instance of the table each column reference means.

24

CROSS JOIN (or comma-separated tables with no WHERE) produces a Cartesian product: every row from table A paired with every row from table B.

Subqueries

25

Single-row operators (= != >) with a subquery that returns multiple rows raise ORA-01427. Use IN or ANY to handle multiple rows.

26

NOT IN with a subquery that returns NULL gives zero rows. Add WHERE col IS NOT NULL inside the subquery, or use NOT EXISTS instead.

27

A scalar subquery returning zero rows returns NULL — not an error. ORA-01427 only fires for more than one row.

28

EXISTS only cares whether any row exists — the SELECT list inside EXISTS doesn't matter. SELECT 1, SELECT *, and SELECT 'X' are all equivalent inside EXISTS.

29

WITH clause: named query blocks are defined once and can be reused. They exist only for the duration of that one statement — not stored permanently.

SET operators

30

Both SELECT statements in a SET operation must have the same number of columns with compatible types. Column names come from the first SELECT only.

31

ORDER BY goes once at the very end of the entire SET statement — individual SELECTs cannot have their own ORDER BY.

32

INTERSECT has higher precedence than UNION and MINUS. In a chain without parentheses, INTERSECT is evaluated first.

33

UNION ALL is the only SET operator with a predictable row count — it is always the sum of both query row counts. UNION, INTERSECT, and MINUS depend on how many duplicates exist.

DDL and constraints

34

NOT NULL can only be defined at the column level — not as a table-level constraint. It also cannot be named with a CONSTRAINT clause in the same way.

35

Composite PRIMARY KEY must be table-level. Defining PRIMARY KEY inline on two separate columns creates two single-column PKs — which is invalid since a table can only have one PK.

36

UNIQUE allows multiple NULL values. PRIMARY KEY allows no NULLs. NULL is never considered a duplicate for UNIQUE constraint purposes.

37

CHECK constraint: NULL passes CHECK. The condition evaluates to UNKNOWN for NULL, and UNKNOWN is not rejected. Add NOT NULL separately to prevent NULLs.

38

CTAS (CREATE TABLE AS SELECT) copies column names, data types, and NOT NULL constraints. PRIMARY KEY, UNIQUE, FK, and CHECK are not copied.

39

Oracle has ON DELETE CASCADE and ON DELETE SET NULL for foreign keys. ON UPDATE does not exist in Oracle — any answer with ON UPDATE CASCADE is wrong.

40

ALTER TABLE MODIFY — cannot change data type if column has data. Cannot decrease column size if existing values would not fit. Cannot add NOT NULL if any row has NULL.

DML and transactions

41

INSERT with a subquery: no VALUES keyword. The SELECT replaces the VALUES clause entirely. INSERT INTO t VALUES (SELECT ...) is a syntax error.

42

Explicit NULL overrides DEFAULT. VALUES (..., NULL) stores NULL — the DEFAULT is only used when the column is omitted from the INSERT column list entirely.

43

TRUNCATE is DDL — cannot be rolled back. DELETE is DML — can be rolled back until COMMIT. TRUNCATE does not fire row triggers. DELETE does.

44

Any DDL statement causes an implicit COMMIT of all pending DML before it executes. A subsequent ROLLBACK has nothing to undo.

45

Multi-row DML is all or nothing. One constraint violation in a 1000-row INSERT rejects all 1000 rows — not just the offending row.

Views, sequences and data dictionary

46

WITH CHECK OPTION blocks INSERT/UPDATE that would make the row invisible through the view. WITH READ ONLY blocks ALL DML. Neither blocks SELECT.

47

ROLLBACK does not restore sequence values. Calling NEXTVAL and rolling back the transaction permanently consumes that value — the next NEXTVAL skips it.

48

CURRVAL before NEXTVAL raises ORA-08002. NEXTVAL must be called at least once in the session before CURRVAL is accessible.

49

Data dictionary object names are stored in UPPERCASE. WHERE table_name = 'employees' returns zero rows — must be 'EMPLOYEES'.

50

Foreign key constraint type in USER_CONSTRAINTS is 'R' (Referential) — not 'F'. NOT NULL is stored as type 'C' (Check). Constraint types: P, U, R, C.

Final tip: The 1Z0-071 exam tests precision, not general knowledge. The questions are designed to find the candidates who know the exact rule, not just the concept. The 50 rules above are where those precision marks live. Read through them once more before you go in — they take about 10 minutes and cover most of the edge cases the exam exploits.

The three traps that catch the most candidates: NULL in NOT IN (rule 2), DDL causes implicit COMMIT (rule 44), and ROWNUM assigned before ORDER BY (covered in Post 18). If you know these three cold, you will pick up marks that most candidates drop.

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Every rule covered in this series is backed by full chapter explanations and exam-style practice sets in the guide. If the posts gave you the map, the guide gives you the drills. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.