Oracle 1Z0-071 key terms

The core SQL command that retrieves rows from one or more tables or views. Its clauses are evaluated in a fixed order, and only the projection list determines which columns appear in the output.

The comma-separated expressions between SELECT and FROM that define the columns of the result set. It may contain column names, literals, aliases, arithmetic, and function calls, including the asterisk shorthand.

A temporary name assigned to an output column with AS or by writing the name directly after the expression. It renames the result heading only and does not change the underlying table definition.

A one-row, one-column dummy table owned by SYS that lets you evaluate expressions and call functions without reading application data. Queries such as SELECT SYSDATE FROM dual are common in this exam.

A marker for missing or unknown data. It is not equal to zero or to a blank string, and any arithmetic involving it yields NULL unless a function explicitly handles it.

The rule that a comparison involving NULL evaluates to UNKNOWN rather than TRUE or FALSE. A WHERE clause keeps only rows whose condition is TRUE, so UNKNOWN rows are discarded.

The clause that filters rows before grouping and before the projection list is applied. It accepts predicates built from comparison, logical, BETWEEN, IN, LIKE, and NULL-test operators.

A pattern-matching predicate using percent as a wildcard for any number of characters and underscore for exactly one character. An escape character can make those wildcards literal.

The clause that sorts the final result set. It runs after SELECT and after any grouping, may use column aliases or positions, and defaults to ascending order with NULLs placed last.

An ORDER BY modifier that forces NULL values to appear before all non-NULL values regardless of ascending or descending direction. By default Oracle sorts NULLs as if larger than any value.

A function that returns exactly one result for each input row, unlike a group function. Character, numeric, date, and conversion functions are typical examples used in the projection list or WHERE clause.

A function that substitutes a replacement value when its first argument is NULL. Both arguments should share a compatible data type, otherwise Oracle performs an implicit conversion that may surprise you.

A function that returns the first non-NULL argument from a list, evaluating arguments left to right. It is the standard alternative to nested NVL calls and requires at least two arguments.

A function such as TO_CHAR, TO_DATE, or TO_NUMBER that changes a value from one data type to another. Explicit conversion with a format model is safer than relying on implicit conversion.

A character string of elements such as YYYY, MM, DD, or HH24 that tells a conversion function how to interpret or display a value. It must match the actual data or an error is raised.

A function that converts a character string into a DATE value using a format model. Without a format model it relies on the session's default date format, which is a frequent source of errors.

Automatic type conversion Oracle performs when operands or arguments have mismatched types. It depends on session settings, can disable index use, and should be avoided in favor of explicit conversion.

A conditional expression that returns a value from the first matching WHEN branch, with an optional ELSE default. Without ELSE, unmatched rows return NULL, and it can appear anywhere an expression is allowed.

An Oracle-specific conditional function comparing an expression to search values and returning a corresponding result. It treats two NULLs as equal, unlike CASE, which uses standard comparison rules.

An aggregate such as COUNT, SUM, AVG, MIN, or MAX that returns one result per group of rows. These functions ignore NULLs except COUNT(*), which counts rows regardless of content.

The clause that partitions rows into groups before aggregate functions are evaluated. Every non-aggregated expression in the projection list must appear in this clause or the query fails.

The clause that filters groups after aggregation, so it may reference group functions. A WHERE clause cannot do this because it evaluates before rows are grouped.

A group function returning the number of rows. COUNT(*) counts all rows including those with NULLs, while COUNT(column) and COUNT(DISTINCT column) skip NULL values.

A join returning only rows where the join condition matches in both tables. Rows without a match on either side are excluded from the result set.

A join that preserves unmatched rows from one table, filling the other side's columns with NULLs. LEFT, RIGHT, and FULL variants differ in which unmatched rows are retained.

The predicate that determines which rows from two row sources are combined. It belongs in the ON clause for ANSI syntax or in the WHERE clause for the older Oracle join syntax.

The standard way to write joins using JOIN, ON, and USING keywords instead of comma-separated tables. It separates join conditions from filter conditions and supports outer joins explicitly.

A SELECT statement nested inside another statement, used to supply values, rows, or existence tests. It may be single-row or multi-row depending on the comparison operator applied to it.

A subquery that references a column from the outer query, so it is re-evaluated for each candidate row. It can express logic that is awkward with joins but may be slower.

A predicate that is true when a correlated subquery returns at least one row. It short-circuits on the first match, so the subquery's select list content does not matter.

A keyword such as UNION, UNION ALL, INTERSECT, or MINUS that combines the result sets of two queries. Both queries must have the same number of columns with compatible data types.

A set operator that concatenates two result sets without removing duplicate rows. Because it skips the sort and duplicate elimination that UNION performs, it is usually faster.

A set operator returning rows from the first query that do not appear in the second. Duplicates within the first result are removed, and column order and types must match.

A DML command that adds rows to a table, either one row with a VALUES list or many rows from a subquery. Unlisted columns receive their default value or NULL.

A DML command that changes column values in existing rows. Omitting a WHERE clause modifies every row in the table, which is a common and costly mistake.

A DML command that removes rows from a table while preserving the table structure. Unlike TRUNCATE it can be rolled back and it fires row-level triggers.

The COMMIT, ROLLBACK, and SAVEPOINT statements that end or partially undo a transaction. COMMIT makes changes permanent and visible to other sessions; ROLLBACK discards them.

A data definition command such as CREATE, ALTER, DROP, or TRUNCATE that changes schema objects. In Oracle each DDL statement performs an implicit commit before and after it runs.

A DDL command that quickly removes all rows from a table and releases its space. It cannot be rolled back, does not fire delete triggers, and is far faster than a full DELETE.

A column attribute supplying the value used when an INSERT omits that column. If no default is defined, the column receives NULL, and defaults may be added or changed with ALTER TABLE.

A stored query that behaves like a table when queried. A simple view on one table can often accept DML, while views with joins, aggregates, or DISTINCT are generally read-only.

A view clause preventing INSERT or UPDATE through the view from producing rows the view's WHERE clause would not return. It protects the view's own filtering rule.

A schema object that generates unique numeric values on request using NEXTVAL and CURRVAL. It is independent of tables and transactions, so gaps can appear after rollbacks or shared cache use.

An alias for another schema object such as a table, view, or sequence. A public synonym is visible to all users, while a private one belongs to its creating schema.

An optional schema object that speeds access to table rows by key value. It is maintained automatically on DML, and a unique index enforces uniqueness on its columns.

A rule declared on a table such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK. Constraints can be enabled, disabled, and validated without dropping them.

A privilege statement giving a user or role the right to perform an action, such as SELECT on a table or CREATE SESSION. Object privileges may include the WITH GRANT OPTION clause.

A named collection of privileges that can be granted to users or other roles. Granting a role is simpler than granting many privileges individually, and roles can be enabled or disabled per session.

A right to perform a class of actions in the database, such as CREATE TABLE or CREATE SESSION. It differs from an object privilege, which applies to one specific named object.

A datetime type storing century, year, month, day, hour, minute, and second but no fractional seconds. Arithmetic on dates works in whole days, and subtracting two dates gives a number.

An extension of DATE that also stores fractional seconds, with optional time zone variants. It is preferred when precise timing or cross-time-zone recording matters.

A function such as REGEXP_LIKE, REGEXP_SUBSTR, or REGEXP_REPLACE that matches text against a pattern. These extend ordinary LIKE matching with character classes, quantifiers, and groups.

A subquery placed in the FROM clause and treated as a named row source. It can be aliased and joined like a table, letting you filter or aggregate before the outer query runs.

A pseudocolumn assigning sequential numbers to rows as they are returned by a query. Because it is applied before ORDER BY, wrapping the query in an inline view is the usual way to get top-N results.