Oracle SQL is one of the most sought-after skills in enterprise software development and database administration. The Oracle Database SQL certification exam (1Z0-071) tests your ability to write, optimize, and troubleshoot SQL queries against an Oracle Database—skills that are directly applicable on the job from day one. This free Oracle SQL practice test PDF gives you printable exam-style questions covering every tested domain so you can review on paper, away from a screen.
The questions in this PDF are structured to match the real 1Z0-071 exam format: scenario-based multiple-choice questions that require you to read SQL snippets, predict output, identify errors, and choose the best query for a given requirement. Work through the PDF question by question, write your answers, then check them against the explanations to understand exactly why each answer is correct.
The 1Z0-071 syllabus spans the full range of SQL capabilities in Oracle Database. Here is a detailed breakdown of every domain you need to master before exam day.
The foundation of every SQL exam: writing SELECT statements with column aliases, arithmetic operators, DISTINCT to eliminate duplicate rows, and the essential NULL-handling functions—NVL (substitute a value for NULL), NVL2 (returns different values depending on whether an expression is NULL or not), NULLIF (returns NULL if two expressions are equal), and COALESCE (returns the first non-NULL value in a list). Expect questions that ask you to predict query output when NULLs interact with arithmetic.
Master the WHERE clause operators: comparison operators (=, !=, <, >, <=, >=), BETWEEN (inclusive on both ends), IN (match any value in a list), LIKE (% matches any sequence, _ matches one character), and IS NULL (never use = NULL—it always returns false). Combine conditions with AND, OR, and NOT, understanding operator precedence. ORDER BY supports multiple columns, ASC/DESC per column, and NULLS FIRST/LAST control where NULLs appear in sorted output.
Character functions—UPPER, LOWER, INITCAP, SUBSTR, LENGTH, INSTR, REPLACE, TRIM—are heavily tested. Number functions include ROUND (rounds to n decimal places), TRUNC (truncates without rounding), MOD (returns the remainder), CEIL and FLOOR. Date functions: SYSDATE (current date and time), ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, and the conversion functions TO_DATE and TO_CHAR. Conversion functions TO_NUMBER, TO_DATE, and TO_CHAR handle explicit data type conversion—know the format model elements (YYYY, MM, DD, HH24, MI, SS).
COUNT, SUM, AVG, MAX, and MIN aggregate multiple rows into a single result. GROUP BY groups rows with the same values; every non-aggregate column in the SELECT list must appear in the GROUP BY clause. HAVING filters groups after aggregation—it is the WHERE clause for grouped data. The critical distinction: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Aggregate functions ignore NULLs except COUNT(*).
Oracle SQL supports ANSI-style joins. Inner join returns only matching rows. LEFT OUTER JOIN returns all rows from the left table plus matches from the right (NULLs for non-matches). RIGHT OUTER JOIN is the mirror image. FULL OUTER JOIN returns all rows from both tables. CROSS JOIN returns the Cartesian product. Natural join automatically matches columns with the same name—dangerous if multiple common columns exist. JOIN USING specifies exactly which column to match. Self join joins a table to itself using table aliases. Know when each join type produces NULLs and how to interpret output.
Single-row subqueries return one row and work with single-row operators (=, !=, <, >). Multiple-row subqueries return multiple rows and require IN, ANY, or ALL. Correlated subqueries reference columns from the outer query and execute once per outer row—understand performance implications. Subqueries can appear in the SELECT list (scalar subquery), FROM clause (inline view), or WHERE clause. Expect questions that require you to identify whether a subquery is correlated or non-correlated.
UNION returns distinct rows from both queries (removes duplicates). UNION ALL returns all rows including duplicates—faster than UNION. INTERSECT returns only rows that appear in both result sets. MINUS returns rows from the first query that don't appear in the second. Rules: both queries must have the same number of columns, and corresponding columns must have compatible data types. Column names in the result come from the first query.
DDL: CREATE TABLE (with inline constraint definitions), ALTER TABLE (ADD, MODIFY, DROP COLUMN, RENAME COLUMN), DROP TABLE, TRUNCATE TABLE (deletes all rows, no rollback, resets high-water mark). DML: INSERT (single-row and multi-row from subquery), UPDATE (with correlated subquery in SET clause), DELETE, and MERGE (upsert—combines INSERT and UPDATE). Data types: VARCHAR2 (variable-length character, up to 4000 bytes), NUMBER (precision and scale), DATE (includes time component), TIMESTAMP (fractional seconds), CLOB (character large object).
PRIMARY KEY (unique + NOT NULL, one per table), FOREIGN KEY (referential integrity—REFERENCES parent_table(column)), UNIQUE (allows one NULL), NOT NULL, and CHECK (must evaluate to TRUE or NULL). Constraints can be defined inline (column-level) or out-of-line (table-level). ENABLE/DISABLE and VALIDATE/NOVALIDATE combinations control whether constraints apply to existing and new data.
Transactions: COMMIT makes changes permanent; ROLLBACK undoes changes to the last COMMIT or SAVEPOINT; SAVEPOINT creates a rollback target within a transaction. DML statements don't auto-commit in Oracle SQL Developer—always commit explicitly. Views: simple views (single table, no group functions, no DISTINCT) are updatable; complex views are not. WITH CHECK OPTION prevents DML that would make rows invisible through the view. WITH READ ONLY blocks all DML. Sequences generate unique numbers via NEXTVAL and CURRVAL. B-tree indexes speed up equality and range searches; unique indexes enforce uniqueness. GRANT gives privileges (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE); REVOKE removes them. Roles bundle multiple privileges for easier management.
Ready to test yourself under timed exam conditions? Our Oracle SQL practice test platform delivers full-length 73-question timed exams alongside focused topic quizzes for every domain on the 1Z0-071 syllabus. Each question includes a detailed explanation showing the exact Oracle SQL syntax and the reasoning behind the correct answer. Combine the offline PDF drills with our online timed tests in the days before your exam for the best possible preparation.