Oracle Intermediate

1Z0-071

Oracle Database SQL

The Oracle Database SQL (1Z0-071) certification validates foundational knowledge of SQL language for working with Oracle Database. This exam is designed for database administrators, developers, and business intelligence professionals who need to demonstrate proficiency in writing SQL queries to retrieve, manipulate, and manage data stored in Oracle databases.

The exam covers five key domains: Relational Database Concepts (understanding tables, relationships, and normalization), DML and DDL (Data Manipulation Language and Data Definition Language statements), Single-Row and Group Functions (using built-in SQL functions for data transformation and aggregation), Joins and Subqueries (combining data from multiple tables using various join types and nested queries), and Set Operators and Data Management (using UNION, INTERSECT, MINUS, and managing transactions).

This certification is ideal for anyone working with Oracle Database who needs to demonstrate SQL proficiency, including application developers building database-driven applications, data analysts performing complex queries, and database administrators managing database objects. The 1Z0-071 exam was last updated in 2024 and validates skills applicable to Oracle Database 12c through 23ai.

Updated 2024 Database

Ερωτήσεις

Δοκιμαστικά Τεστ

63%

Βαθμολογία Επιτυχίας

Προβολές

Συνολικές Προσπάθειες

Μ.Ο. Βαθμολογίας

Ποσοστό Επιτυχίας

Συζητήσεις

Δοκιμαστικά Τεστ Κάρτες Μελέτης Θέματα Εξέτασης Σύνοψη

€5.00

1Z0-071 Practice Exam 1

Comprehensive Oracle Database SQL practice exam covering relational database concepts, DML/DDL statements, single-row and group functions, joins and subqueries, and set operators across 63 questions.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

€5.00

1Z0-071 Practice Exam 2

Oracle Database SQL practice exam 2 with 63 scenario-based questions covering relational concepts, DML/DDL operations, functions, joins, subqueries, and data management.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

€5.00

1Z0-071 Practice Exam 3

Oracle Database SQL practice exam 3 featuring 63 scenario-based questions on relational concepts, SQL statements, functions, joins, subqueries, and database administration.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

€5.00

1Z0-071 Practice Exam 4

Oracle Database SQL practice exam 4 with 63 challenging questions covering relational theory, SQL operations, built-in functions, table joins, subqueries, and database object management.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

€5.00

1Z0-071 Practice Exam 5

Oracle Database SQL practice exam 5 with 63 comprehensive questions covering relational database principles, SQL data manipulation and definition, built-in functions, joins, subqueries, and data management operations.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

€5.00

1Z0-071 Practice Exam 6

Expert-level Oracle Database SQL practice exam with 63 tricky questions designed to test edge-case knowledge of SQL syntax, function behavior, join mechanics, and database administration.

63 Q 120 λεπτά 63%

Δοκιμαστική Οδήγηση

Ξεκλείδωμα Όλου του Περιεχομένου για 1Z0-071

6 Δοκιμαστικά Τεστ + Κάρτες Μελέτης — πρόσβαση 3 μηνών

€39.99 €26.99 Εξοικονόμηση 30%

ή περιλαμβάνεται στη Μηνιαία συνδρομή / Πακέτο Περιεχομένου

1Z0-071 Cheat Sheet

Σύντομος οδηγός αναφοράς - 6 ενότητες

Δωρεάν Πρόσβαση

Oracle Database SQL (1Z0-071)

The 1Z0-071 exam validates your ability to write SQL statements to retrieve, manipulate, and manage data in Oracle Database environments. This certification is a foundational requirement for the Oracle Database SQL Certified Associate credential and is also a prerequisite for the Oracle Database Administration certifications. The exam covers the full spectrum of SQL capabilities including single-table and multi-table queries, data manipulation language (DML), data definition language (DDL), transaction control, subqueries, set operators, schema object management, and the use of built-in functions for data conversion and manipulation. Candidates are expected to demonstrate proficiency with SELECT statements, JOIN operations, grouping and aggregation, hierarchical queries, and the management of tables, views, sequences, indexes, and synonyms. This exam is essential for database developers, administrators, analysts, and anyone who works directly with Oracle Database SQL on a regular basis.

Exam Details

Exam Code	1Z0-071
Duration	120 minutes
Number of Questions	63 questions
Passing Score	63%
Cost	$245 USD
Validity	Lifetime (does not expire)
Question Types	Multiple choice, multiple select
Testing Options	Pearson VUE testing center or online proctored
Recommended Experience	6+ months working with Oracle SQL
Certification Level	Associate

Domain Weights

Domain	Weight
Domain 1: Relational Database Concepts	10%
Domain 2: Retrieving Data Using SELECT	20%
Domain 3: Restricting, Sorting & Using Functions	25%
Domain 4: Joins, Subqueries & Set Operators	25%
Domain 5: DML, DDL & Schema Objects	20%

Study Tips

Joins, subqueries, and functions together account for 50% of the exam; practice writing complex multi-table queries with various JOIN types, correlated and non-correlated subqueries, and nested function calls
Understand the difference between implicit and explicit JOIN syntax; Oracle supports both ANSI SQL syntax (JOIN...ON) and traditional Oracle syntax (WHERE clause joins); the exam heavily favors ANSI syntax
Know all single-row functions thoroughly including NVL, NVL2, NULLIF, COALESCE, DECODE, CASE, TO_CHAR, TO_DATE, TO_NUMBER, and string functions like SUBSTR, INSTR, REPLACE, TRIM, and LPAD/RPAD
Pay close attention to NULL handling in SQL; understand how NULLs behave in comparisons, arithmetic, GROUP BY, ORDER BY, and aggregate functions since these are frequent exam traps
Practice creating and managing schema objects (tables, views, sequences, indexes, synonyms) and understand the difference between DDL (auto-commit) and DML (requires explicit commit)
Use Oracle Live SQL (livesql.oracle.com) to practice without installing anything locally; it provides a free browser-based Oracle Database environment
Review hierarchical queries with CONNECT BY, START WITH, LEVEL, and SYS_CONNECT_BY_PATH as these appear regularly on the exam

Exam Day Checklist

Arrive 15 minutes early at the testing center or start online proctoring check-in 30 minutes before scheduled time
Bring two valid forms of identification (one with photo) for testing center exams
You have 120 minutes for 63 questions, giving approximately 1.9 minutes per question
Read SQL code blocks carefully; watch for subtle syntax errors, missing commas, wrong aliases, and incorrect column references
You need 63% to pass; flag questions with complex SQL output analysis and return to them after completing easier questions
Results are available immediately after completing the exam; your score report shows performance by domain

SELECT Statement Components

Clause	Purpose	Required
SELECT	Specifies columns or expressions to return; supports column aliases using AS keyword or double quotes for aliases with spaces; DISTINCT eliminates duplicate rows; * selects all columns from all tables	Yes
FROM	Identifies the table(s) or view(s) to query; supports table aliases for readability; can include inline views (subqueries in the FROM clause); DUAL is a special one-row, one-column table for evaluating expressions	Yes
WHERE	Filters rows before grouping; supports comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), BETWEEN, IN, LIKE, IS NULL, IS NOT NULL; evaluated row by row	No
GROUP BY	Groups rows sharing common values; every non-aggregate column in SELECT must appear in GROUP BY; supports ROLLUP, CUBE, and GROUPING SETS for generating subtotals and grand totals	No
HAVING	Filters groups after aggregation; similar to WHERE but operates on grouped results; typically used with aggregate functions like COUNT, SUM, AVG, MIN, MAX	No
ORDER BY	Sorts the result set; ASC (ascending, default) or DESC (descending); can reference column names, aliases, or positional numbers; NULLS FIRST or NULLS LAST controls NULL positioning	No

Exam Tip: The logical order of SQL execution is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This means column aliases defined in SELECT cannot be used in WHERE or GROUP BY but can be used in ORDER BY.

WHERE Clause Operators

Operator	Syntax	Description
BETWEEN	col BETWEEN low AND high	Inclusive range check; equivalent to col >= low AND col <= high; works with numbers, dates, and strings
IN	col IN (val1, val2, ...)	Matches any value in the list; equivalent to multiple OR conditions; can also use a subquery instead of a value list
LIKE	col LIKE pattern	Pattern matching; % matches zero or more characters; _ matches exactly one character; ESCAPE clause defines an escape character for literal % or _
IS NULL	col IS NULL	Tests for NULL values; = NULL never works because NULL is not equal to anything including itself; always use IS NULL or IS NOT NULL
ANY / ALL	col > ANY (subquery)	ANY matches if condition is true for at least one value; ALL matches if condition is true for every value in the subquery result set
EXISTS	EXISTS (subquery)	Returns TRUE if subquery returns at least one row; commonly used with correlated subqueries; more efficient than IN for large datasets

Column Aliases & Expressions

Feature	Syntax Example	Notes
Simple Alias	SELECT salary * 12 annual_sal	Space between expression and alias; alias is case-insensitive and stored uppercase in results
AS Keyword	SELECT salary * 12 AS annual_sal	Optional AS keyword improves readability; functionally identical to without AS
Quoted Alias	SELECT salary * 12 "Annual Salary"	Double quotes preserve case and allow spaces; required when alias contains special characters or is a reserved word
Concatenation	SELECT first_name \|\| ' ' \|\| last_name	Double pipe (\|\|) concatenates strings; if any operand is NULL the result includes the other operands (Oracle treats empty string as NULL)
Literal Strings	SELECT 'Name: ' \|\| name	Single quotes delimit string literals; use two single quotes ('') to include a literal apostrophe; alternative quoting q'[ ]' avoids escaping

Substitution Variables & DEFINE

Feature	Description
&variable	Single ampersand prompts for a value each time the statement is executed; the value is substituted directly into the SQL text before execution; must enclose in quotes if substituting a string or date value
&&variable	Double ampersand prompts once and reuses the value for all subsequent references in the same session; the variable persists until UNDEFINE is used or the session ends
DEFINE	DEFINE var = value pre-sets a substitution variable without prompting; useful for scripts that run without user interaction; UNDEFINE removes the variable
SET VERIFY	SET VERIFY ON shows old and new values after substitution; SET VERIFY OFF suppresses this display; helpful for debugging substitution variable issues

Single-Row Character Functions

Function	Syntax	Description
UPPER / LOWER / INITCAP	UPPER(str), LOWER(str), INITCAP(str)	Case conversion; UPPER converts all to uppercase, LOWER to lowercase, INITCAP capitalizes the first letter of each word
SUBSTR	SUBSTR(str, start, [length])	Extracts substring; position is 1-based; negative start counts from end; if length omitted, returns to end of string
INSTR	INSTR(str, search, [start], [nth])	Returns position of substring; returns 0 if not found; optional start position and occurrence number; negative start searches backward
LENGTH	LENGTH(str)	Returns number of characters; returns NULL if str is NULL; counts all characters including spaces
REPLACE	REPLACE(str, search, [replace])	Replaces all occurrences of search with replace string; if replace is omitted, removes the search string; case-sensitive matching
TRIM / LTRIM / RTRIM	TRIM([LEADING\|TRAILING\|BOTH] char FROM str)	Removes specified characters; TRIM removes from both ends by default; LTRIM/RTRIM accept a set of characters to remove from left/right sides
LPAD / RPAD	LPAD(str, length, [pad_str])	Pads string to specified length; LPAD adds padding to left, RPAD to right; defaults to padding with spaces; truncates if string exceeds length

Single-Row Numeric Functions

Function	Description	Example Result
ROUND(n, d)	Rounds number to d decimal places; negative d rounds to left of decimal point	ROUND(45.926, 2) = 45.93; ROUND(45.926, -1) = 50
TRUNC(n, d)	Truncates number to d decimal places without rounding; negative d truncates to left of decimal	TRUNC(45.926, 2) = 45.92; TRUNC(45.926, -1) = 40
MOD(m, n)	Returns the remainder of m divided by n; useful for determining odd/even or cyclical patterns	MOD(10, 3) = 1; MOD(10, 2) = 0
CEIL(n) / FLOOR(n)	CEIL returns the smallest integer >= n; FLOOR returns the largest integer <= n	CEIL(2.1) = 3; FLOOR(2.9) = 2
ABS(n)	Returns the absolute (positive) value of n	ABS(-15) = 15

Date Functions

Function	Description
SYSDATE	Returns current database server date and time as a DATE datatype; does not require parentheses; reflects the timezone of the database server OS
CURRENT_DATE	Returns current date in the session timezone; differs from SYSDATE when session timezone differs from server timezone
MONTHS_BETWEEN(d1, d2)	Returns the number of months between two dates; result can be fractional; positive if d1 > d2, negative if d1 < d2
ADD_MONTHS(d, n)	Adds n months to date d; if starting date is last day of month, result is last day of target month; handles 28/29/30/31-day months automatically
NEXT_DAY(d, day)	Returns the next occurrence of the specified day of the week after date d; day can be a name ('FRIDAY') or abbreviation; NLS-dependent
LAST_DAY(d)	Returns the last day of the month containing date d; useful for month-end calculations and reporting periods
ROUND(d) / TRUNC(d)	ROUND rounds date to nearest day/month/year; TRUNC truncates time component; both accept format model ('MONTH', 'YEAR', 'DD')
EXTRACT	EXTRACT(YEAR\|MONTH\|DAY FROM date); returns numeric component; also works with TIMESTAMP and INTERVAL data types

Exam Tip: Date arithmetic in Oracle: date + number = date (adds days), date - date = number (difference in days), date + number/24 = date (adds hours). You cannot add two dates together.

Conversion Functions

Function	Description	Common Format Models
TO_CHAR(date, fmt)	Converts date to formatted string; format models are case-sensitive for output	YYYY, MM, DD, HH24, MI, SS, DAY, MONTH, MON, DY, Q (quarter)
TO_CHAR(number, fmt)	Converts number to formatted string; controls decimal places, grouping, currency	9 (digit placeholder), 0 (leading zeros), $ (dollar sign), L (local currency), , (comma), . (decimal)
TO_DATE(str, fmt)	Converts string to DATE datatype using specified format model; format must match the string exactly	Must match input string format; fx modifier enforces exact matching
TO_NUMBER(str, fmt)	Converts string to NUMBER datatype; format model must match the string representation including currency and grouping characters	Same number format models as TO_CHAR

NULL-Handling Functions

Function	Syntax	Description
NVL	NVL(expr1, expr2)	Returns expr2 if expr1 is NULL; otherwise returns expr1; both expressions must be the same datatype or implicitly convertible
NVL2	NVL2(expr1, expr2, expr3)	If expr1 is NOT NULL returns expr2; if expr1 IS NULL returns expr3; three-argument version of NVL providing different results for null and non-null cases
NULLIF	NULLIF(expr1, expr2)	Returns NULL if expr1 equals expr2; otherwise returns expr1; useful for avoiding division by zero: salary / NULLIF(commission, 0)
COALESCE	COALESCE(expr1, expr2, ..., exprN)	Returns the first non-NULL expression; accepts any number of arguments; more flexible than NVL for multiple fallback values; ANSI SQL standard

Exam Tip: NVL always evaluates both arguments. NVL2 evaluates the first argument to determine which of the other two to evaluate and return. COALESCE uses short-circuit evaluation and stops at the first non-NULL value.

JOIN Types

Join Type	ANSI Syntax	Description
INNER JOIN	FROM t1 JOIN t2 ON t1.col = t2.col	Returns only rows with matching values in both tables; the most common join type; rows without matches in either table are excluded from the result set
LEFT OUTER JOIN	FROM t1 LEFT [OUTER] JOIN t2 ON ...	Returns all rows from the left table and matching rows from the right table; non-matching right-side columns return NULL; equivalent to Oracle (+) on the right side in traditional syntax
RIGHT OUTER JOIN	FROM t1 RIGHT [OUTER] JOIN t2 ON ...	Returns all rows from the right table and matching rows from the left table; non-matching left-side columns return NULL; equivalent to Oracle (+) on the left side
FULL OUTER JOIN	FROM t1 FULL [OUTER] JOIN t2 ON ...	Returns all rows from both tables; NULLs fill in where no match exists on either side; combines the results of both LEFT and RIGHT outer joins
CROSS JOIN	FROM t1 CROSS JOIN t2	Cartesian product; every row in t1 paired with every row in t2; no ON clause; result has t1_rows * t2_rows rows; rarely intentional in production code
NATURAL JOIN	FROM t1 NATURAL JOIN t2	Automatically joins on all columns with the same name in both tables; no ON clause allowed; dangerous if tables share unintended column names
USING Clause	FROM t1 JOIN t2 USING (col)	Joins on specified shared column name(s); column must exist in both tables with the same name; joined column appears once in results without table qualifier
Self Join	FROM t1 a JOIN t1 b ON a.col = b.col	Joins a table to itself; requires table aliases to distinguish the two copies; commonly used for hierarchical data like employees and managers

Exam Tip: The USING clause column must NOT be qualified with a table alias in SELECT, WHERE, or any other clause. Writing e.DEPARTMENT_ID when USING (DEPARTMENT_ID) is specified causes an error.

Subquery Types

Type	Description	Usage
Single-Row Subquery	Returns exactly one row; use with single-row operators (=, <>, >, <, >=, <=); will error if more than one row returned	WHERE salary > (SELECT AVG(salary) FROM employees)
Multiple-Row Subquery	Returns zero or more rows; must use multi-row operators (IN, ANY, ALL, EXISTS); cannot use single-row operators	WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)
Multiple-Column Subquery	Returns multiple columns; used for pairwise or non-pairwise comparisons; outer query compares multiple columns simultaneously	WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id)
Correlated Subquery	References a column from the outer query; re-executed for each row of the outer query; typically used with EXISTS or in scalar contexts	WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id)
Scalar Subquery	Returns a single value (one row, one column); can be used in SELECT list, WHERE clause, or CASE expression; returns NULL if no rows match	SELECT name, (SELECT dept_name FROM departments d WHERE d.id = e.dept_id) FROM employees e
Inline View	Subquery in the FROM clause acting as a temporary table; must have an alias; can be joined with other tables; useful for pre-aggregating data before joining	FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) dept_avg

Set Operators

Operator	Description	Duplicates
UNION	Combines results of two queries; column count and datatypes must match between queries; sorts result to eliminate duplicates	Removed
UNION ALL	Combines results without removing duplicates; faster than UNION because no sorting required; preferred when duplicates are acceptable or impossible	Kept
INTERSECT	Returns only rows that appear in both query results; like a logical AND between result sets	Removed
MINUS	Returns rows from the first query that do not appear in the second query; order matters (A MINUS B is different from B MINUS A)	Removed

Exam Tip: Column names in the result set come from the FIRST SELECT statement. ORDER BY can only appear at the very end of the compound query, not within individual SELECT statements. UNION ALL is the only set operator that preserves duplicates.

Aggregate Functions

Function	Description	NULL Handling
COUNT(*)	Counts all rows including those with NULLs; COUNT(col) counts non-NULL values only; COUNT(DISTINCT col) counts unique non-NULL values	COUNT(*) includes NULLs; COUNT(col) ignores NULLs
SUM(col)	Returns the total of all non-NULL values; returns NULL if all values are NULL; works only with numeric columns	Ignores NULLs
AVG(col)	Returns the average of non-NULL values; does not include NULL rows in the denominator; use NVL to include NULLs as zero	Ignores NULLs (changes denominator)
MIN(col) / MAX(col)	Returns the minimum/maximum value; works with numbers, strings (alphabetical), and dates; returns NULL if all values are NULL	Ignores NULLs
LISTAGG	Concatenates values from multiple rows into a single string with a specified delimiter; WITHIN GROUP (ORDER BY) controls the ordering of concatenated values	Ignores NULLs

Data Manipulation Language (DML)

Statement	Syntax	Description
INSERT	INSERT INTO table (cols) VALUES (vals)	Adds new rows to a table; column list is optional if providing values for all columns in table order; can use subquery instead of VALUES for multi-row insert; INSERT ALL for multi-table insert
UPDATE	UPDATE table SET col = val WHERE ...	Modifies existing rows; SET clause can update multiple columns; WHERE clause filters rows to update; omitting WHERE updates all rows; can use subquery in SET or WHERE
DELETE	DELETE FROM table WHERE ...	Removes rows from a table; WHERE clause filters rows to delete; omitting WHERE deletes all rows; generates undo/redo data; can be rolled back
MERGE	MERGE INTO target USING source ON (...) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...	Upsert operation; conditionally inserts or updates rows based on whether a match exists; combines INSERT and UPDATE in a single atomic statement; optional DELETE clause in WHEN MATCHED

Multi-Table INSERT

Type	Description
INSERT ALL ... SELECT	Unconditional multi-table insert; inserts into all specified target tables for every row from the source subquery; each INTO clause specifies a different target table with its own column list and values
INSERT ALL WHEN ... THEN	Conditional multi-table insert; evaluates all WHEN conditions for each source row; a row can be inserted into multiple tables if multiple conditions are true; ELSE clause handles unmatched rows
INSERT FIRST WHEN ... THEN	Conditional multi-table insert with short-circuit evaluation; inserts into the first matching WHEN clause only and skips remaining conditions; each source row goes to at most one target table

Exam Tip: INSERT ALL evaluates ALL conditions and may insert a single source row into multiple target tables. INSERT FIRST stops at the first matching condition. Both require a trailing SELECT subquery as the data source.

Transaction Control

Statement	Description
COMMIT	Makes all pending DML changes permanent; releases all locks held by the transaction; other sessions can now see the committed changes; cannot be undone after execution
ROLLBACK	Undoes all pending DML changes since the last COMMIT or SAVEPOINT; releases locks; data reverts to its state before the transaction began
SAVEPOINT name	Creates a named marker within a transaction; allows partial rollback using ROLLBACK TO SAVEPOINT name; changes before the savepoint remain pending; multiple savepoints can exist in one transaction
ROLLBACK TO SAVEPOINT	Undoes changes made after the named savepoint; changes before the savepoint are retained but still uncommitted; the savepoint itself is retained after rollback

Data Definition Language (DDL)

Statement	Description
CREATE TABLE	Creates a new table with specified columns, datatypes, and constraints; can use CREATE TABLE AS SELECT (CTAS) to create and populate from a query; CTAS copies data and NOT NULL constraints only (not other constraints)
ALTER TABLE	Modifies table structure; ADD (new columns/constraints), MODIFY (change datatype/default/nullability), DROP (remove columns/constraints), RENAME (column or table); cannot modify a column to a smaller size if existing data exceeds new size
DROP TABLE	Removes table and all its data; moves to recyclebin by default (recoverable with FLASHBACK TABLE); DROP TABLE ... PURGE permanently removes without recyclebin; cascades to dependent indexes
TRUNCATE TABLE	Removes all rows permanently; DDL statement (auto-commits, cannot be rolled back); faster than DELETE because it does not generate row-level undo; resets high water mark; does not fire triggers
RENAME	RENAME old_name TO new_name; renames tables, views, sequences, or synonyms; updates dependent views and synonyms automatically

Exam Tip: All DDL statements perform an implicit COMMIT before and after execution. This means any pending DML changes are automatically committed when you execute any DDL statement. TRUNCATE is DDL (not DML) and cannot be rolled back.

Oracle Datatypes

Datatype	Description	Max Size
VARCHAR2(n)	Variable-length character string; stores only actual data (no padding); most common string datatype; size n is required	4000 bytes (32767 with MAX_STRING_SIZE=EXTENDED)
CHAR(n)	Fixed-length character string; pads with spaces to the declared length; default size is 1 if not specified; comparison behavior includes trailing spaces	2000 bytes
NUMBER(p, s)	Numeric datatype with precision p and scale s; p is total digits, s is decimal digits; NUMBER without p and s stores any numeric value; NUMBER(5,2) stores -999.99 to 999.99	Precision 1-38
DATE	Stores date and time (year, month, day, hour, minute, second); default display format controlled by NLS_DATE_FORMAT; occupies 7 bytes of storage	Jan 1, 4712 BC to Dec 31, 9999 AD
TIMESTAMP	Extends DATE with fractional seconds precision; TIMESTAMP WITH TIME ZONE stores timezone offset; TIMESTAMP WITH LOCAL TIME ZONE converts to session timezone on retrieval	Up to 9 digits of fractional seconds
CLOB / BLOB	CLOB stores large character data; BLOB stores large binary data (images, documents); both use locators for efficient storage and access	Up to 128 TB

Constraints

Constraint	Description	Key Behavior
PRIMARY KEY	Uniquely identifies each row; combination of NOT NULL and UNIQUE; only one per table; automatically creates a unique index; can be composite (multiple columns)	No NULLs, no duplicates
FOREIGN KEY	Establishes referential integrity between tables; references a PRIMARY KEY or UNIQUE constraint in another table; allows NULLs by default; ON DELETE CASCADE or ON DELETE SET NULL for parent deletion behavior	Values must exist in parent table or be NULL
UNIQUE	Ensures all values in a column are distinct; allows multiple NULLs (NULLs are not considered duplicates); automatically creates a unique index; multiple UNIQUE constraints per table allowed	No duplicates, NULLs allowed
NOT NULL	Prevents NULL values in a column; can only be defined at column level (not table level); can be added or removed using ALTER TABLE MODIFY	Column must always have a value
CHECK	Enforces a boolean condition on column values; condition cannot reference other rows, SYSDATE, CURRVAL, NEXTVAL, ROWNUM, or subqueries; allows NULLs (NULL satisfies any CHECK constraint)	Values must satisfy the condition

Exam Tip: NOT NULL can only be defined at the column level, never at the table level. All other constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) can be defined at either column level or table level. Composite constraints must be defined at the table level.

Views

Feature	Description
Simple View	Based on a single table; does not contain functions, GROUP BY, or DISTINCT; allows DML operations (INSERT, UPDATE, DELETE) on the underlying table through the view; columns map directly to base table columns
Complex View	Based on multiple tables (joins) or contains functions, GROUP BY, DISTINCT, or expressions; generally does not allow DML; some DML possible with INSTEAD OF triggers
CREATE OR REPLACE VIEW	Creates or redefines a view without dropping and recreating; preserves existing grants on the view; replaces the query definition while maintaining the object
WITH CHECK OPTION	Prevents DML operations that would produce rows not visible through the view; ensures that inserted or updated rows satisfy the view's WHERE clause condition
WITH READ ONLY	Prevents all DML operations through the view; stronger restriction than WITH CHECK OPTION; makes the view read-only regardless of its complexity

Sequences, Indexes & Synonyms

Object	Description	Key Points
Sequence	Generates unique sequential numbers; independent of tables; NEXTVAL generates next number, CURRVAL returns current; supports INCREMENT BY, START WITH, MAXVALUE, MINVALUE, CYCLE, CACHE options	Gaps are normal (rollbacks, cache); CURRVAL requires prior NEXTVAL in session; can be used as default column value in 12c+
Index	Database object that speeds up data retrieval; B-tree index (default) for equality and range searches; bitmap index for low-cardinality columns; function-based index on expressions; automatically created for PRIMARY KEY and UNIQUE constraints	Speeds reads, slows writes; optimizer decides usage; DROP INDEX to remove; invisible indexes for testing
Synonym	Alias for a database object (table, view, sequence, procedure); simplifies access to objects in other schemas; private synonyms belong to a schema, public synonyms are available to all users	CREATE [PUBLIC] SYNONYM name FOR owner.object; does not bypass security; just provides alternate name

Hierarchical Queries

Clause / Function	Description
START WITH	Specifies the root row(s) of the hierarchy; condition identifies the top of the tree; multiple roots possible if condition matches multiple rows
CONNECT BY PRIOR	Defines the parent-child relationship; PRIOR refers to the parent row; CONNECT BY PRIOR employee_id = manager_id walks from parent to children; NOCYCLE handles circular references
LEVEL	Pseudocolumn returning the depth of the current row in the hierarchy; root is LEVEL 1; useful for indentation: LPAD(' ', 2*LEVEL) \|\| name
SYS_CONNECT_BY_PATH	Returns the full path from root to current node as a string; SYS_CONNECT_BY_PATH(name, '/') produces /CEO/VP/Manager format; delimiter must not appear in column values
CONNECT_BY_ROOT	Returns the value of a column from the root row of the current path; useful for grouping results by their hierarchical root
CONNECT_BY_ISLEAF	Returns 1 if current row is a leaf node (has no children) and 0 otherwise; useful for identifying the bottom of the hierarchy

Exam Tip: The position of PRIOR determines the traversal direction. CONNECT BY PRIOR child = parent traverses top-down (root to leaves). CONNECT BY child = PRIOR parent traverses bottom-up (leaves to root). WHERE filters after the hierarchy is built; CONNECT BY condition filters during tree construction.

CASE & DECODE Expressions

Expression	Syntax	Description
Simple CASE	CASE expr WHEN val1 THEN result1 ... ELSE default END	Compares an expression to a set of values; returns the result for the first match; ELSE is optional (returns NULL if omitted and no match); ANSI SQL standard
Searched CASE	CASE WHEN condition1 THEN result1 ... ELSE default END	Evaluates independent boolean conditions; more flexible than simple CASE; supports complex expressions with different columns; conditions evaluated in order, first TRUE wins
DECODE	DECODE(expr, search1, result1, search2, result2, ..., default)	Oracle-specific function; compares expression to search values; returns matching result or default; treats NULL = NULL as TRUE (unlike CASE which requires IS NULL); not ANSI standard

Exam Tip: A critical difference: DECODE considers two NULLs as equal, but CASE treats NULL comparison as UNKNOWN. DECODE(col, NULL, 'Yes', 'No') returns 'Yes' when col IS NULL, but CASE col WHEN NULL THEN 'Yes' ELSE 'No' END always returns 'No' because NULL = NULL is UNKNOWN.

1Z0-071

Oracle Database SQL

1Z0-071 Practice Exam 1

1Z0-071 Practice Exam 2

1Z0-071 Practice Exam 3

1Z0-071 Practice Exam 4

1Z0-071 Practice Exam 5

1Z0-071 Practice Exam 6

Ξεκλείδωμα Όλου του Περιεχομένου για 1Z0-071

Προεπισκόπηση (10 / 120)

Κάρτες Μελέτης

Διαθέσιμες Γλώσσες

Θέματα Εξέτασης

1Z0-071 Cheat Sheet

Oracle Database SQL (1Z0-071)

Exam Details

Domain Weights

Study Tips

Exam Day Checklist

SELECT Statement Components

WHERE Clause Operators

Column Aliases & Expressions

Substitution Variables & DEFINE

Single-Row Character Functions

Single-Row Numeric Functions

Date Functions

Conversion Functions

NULL-Handling Functions

JOIN Types

Subquery Types

Set Operators

Aggregate Functions

Data Manipulation Language (DML)

Multi-Table INSERT

Transaction Control

Data Definition Language (DDL)

Oracle Datatypes

Constraints

Views

Sequences, Indexes & Synonyms

Hierarchical Queries

CASE & DECODE Expressions