100+ Free Oracle 1Z0-071 Practice Questions

Retrieving, Restricting and Sorting Data

SELECT statements, column expressions and aliases, restricting rows with WHERE using comparison, logical, BETWEEN, IN, LIKE and IS NULL operators, sorting with ORDER BY, and using DISTINCT, concatenation and the DUAL table.

Single-Row, Conversion and Conditional Functions

Character functions (UPPER, LOWER, SUBSTR, INSTR, LENGTH, LPAD, TRIM, REPLACE), number functions (ROUND, TRUNC, MOD), date functions and arithmetic, TO_CHAR/TO_NUMBER/TO_DATE conversion, NVL, NVL2 and COALESCE, and CASE, DECODE and NULLIF.

Aggregating Data with Group Functions

COUNT, SUM, AVG, MIN and MAX, how aggregate functions ignore NULLs, grouping rows with GROUP BY, filtering groups with HAVING, and the rules on what columns may appear in a grouped SELECT.

Joins and Subqueries

Equijoins, non-equijoins, self-joins and outer joins; ANSI NATURAL, USING, ON, CROSS and FULL/LEFT/RIGHT OUTER joins; single-row and multiple-row subqueries; correlated subqueries; and the IN, EXISTS, ANY and ALL operators.

Set Operators

Combining queries with UNION, UNION ALL, INTERSECT and MINUS, the requirement for matching column counts and compatible data types, NULL handling and duplicate removal, and how ORDER BY applies to combined results.

DML and Transaction Control

INSERT, UPDATE, DELETE and MERGE, multitable inserts, default values and subquery-driven DML, and transaction control with COMMIT, ROLLBACK, SAVEPOINT, read consistency and row locking.

DDL, Constraints and Schema Objects

CREATE and ALTER TABLE, Oracle data types, PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK constraints, views (including WITH CHECK OPTION and WITH READ ONLY), sequences, indexes, synonyms and querying data dictionary views.