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
Pytania
6
Testy praktyczne
63%
Wynik zdający
53
Wyświetlenia
0
Łącznie podejść
0%
Śr. wynik
0%
Zdawalność
0
Dyskusje
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 minuty
63%
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 minuty
63%
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 minuty
63%
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 minuty
63%
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 minuty
63%
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 minuty
63%
Odblokuj całą zawartość za 1Z0-071
6 Test(y) praktyczne + Fiszki — Dostęp na 3 miesiące
€39.99
€26.99
Oszczędź 30%
lub w ramach subskrypcji Miesięcznej / Pakietu treści
Podgląd (10 / 120)
What defines a relational database management system?
An RDBMS stores data in tables (relations) with rows and columns. It enforces integrity rules and supports SQL for data manipulation.
What is the purpose of a primary key?
A primary key uniquely identifies each row in a table. It must be unique and NOT NULL, ensuring no duplicate records exist.
What does a foreign key enforce?
A foreign key references a primary key in another table. It enforces referential integrity, ensuring child rows always point to valid parent rows.
What is a composite key?
A composite key uses two or more columns together to uniquely identify a row. No single column alone is sufficient for uniqueness.
What is a candidate key?
A candidate key is any column or set of columns that could serve as the primary key. Each candidate key is unique and NOT NULL.
How does a surrogate key differ from a natural key?
A surrogate key is system-generated with no business meaning. A natural key uses real-world data like email or SSN.
What does First Normal Form (1NF) require?
1NF requires each column to hold atomic (indivisible) values. No repeating groups or arrays are allowed within a single column.
What does Second Normal Form (2NF) require?
2NF requires 1NF plus every non-key column must depend on the entire primary key, not just part of a composite key.
What does Third Normal Form (3NF) require?
3NF requires 2NF plus no non-key column depends on another non-key column. All columns depend only on the primary key.
What is Boyce-Codd Normal Form (BCNF)?
BCNF is a stricter version of 3NF. Every determinant must be a candidate key, eliminating all remaining functional dependency anomalies.
Fiszki
fiszek obejmujących kluczowe pojęcia 120 1Z0-071
lub w ramach subskrypcji Miesięcznej / Pakietu treści
110 więcej fiszek dostępnych po odblokowaniu
Dostępne języki
English
Japanese
Korean
Portuguese
Spanish
Tematy egzaminu
1
13 Q
Relational Database Concepts
Understand relational database concepts, explain the relationship between a database and SQL, describe database design principles, and explain the theoretical and physical aspects of a relational database.
2
13 Q
DML and DDL
Use INSERT, UPDATE, DELETE, and MERGE statements. Create and modify tables, constraints, views, sequences, and indexes. Manage schema objects.
3
12 Q
Single-Row and Group Functions
Use single-row functions to customize output. Use conversion functions and conditional expressions. Use group functions and GROUP BY, HAVING clauses.
4
13 Q
Joins and Subqueries
Display data from multiple tables using joins (INNER, OUTER, CROSS, NATURAL). Write single-row and multiple-row subqueries. Use correlated subqueries.
5
12 Q
Set Operators and Data Management
Use set operators (UNION, UNION ALL, INTERSECT, MINUS). Control transactions with COMMIT, ROLLBACK, and SAVEPOINT. Manage data consistency.
1Z0-071 Cheat Sheet
Skrócona ściągawka - 6 sekcje
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.