1Z0-149 key terms

The comma-separated set of columns, expressions, or literals that determines which values appear in each row of a query's output. Aliases rename these columns in the result set.

A temporary name given to a column or expression in a query result, written after the expression either with or without the AS keyword. It lasts only for the duration of the statement.

A column alias or object name enclosed in double quotation marks, which preserves exact case, spaces, and special characters. Unquoted names are stored in uppercase and referenced case-insensitively.

A marker for an unknown or inapplicable value, distinct from zero and from the empty string. Any arithmetic involving NULL yields NULL, and comparisons with NULL are never true.

A one-row, one-column dummy table owned by SYS that lets you evaluate expressions or call functions without touching real data, for example SELECT SYSDATE FROM dual.

A calculation in a SQL statement using the operators plus, minus, asterisk, and slash. Multiplication and division bind tighter than addition and subtraction, and parentheses override that order.

The part of a statement that filters rows before they are returned or aggregated, keeping only rows for which the condition evaluates to true. Rows evaluating to false or unknown are discarded.

A symbol such as equals, not equals, greater than, or less than used in a condition to compare two values. It returns true, false, or unknown when NULL is involved.

A range test that is inclusive of both endpoints, equivalent to a pair of greater-than-or-equal and less-than-or-equal comparisons. It works with numbers, dates, and character strings.

A membership test that checks whether a value matches any item in a supplied list or the result of a subquery. It is equivalent to a chain of equality tests joined by OR.

A pattern-matching test for character data. The percent sign matches any sequence of characters, the underscore matches exactly one character, and ESCAPE lets you search for those symbols literally.

The only correct way to test for the absence of a value. Writing a column equal to NULL always yields unknown, so IS NULL or IS NOT NULL must be used instead.

The clause that sorts a result set, applied last in query processing. It supports ascending and descending direction, NULLS FIRST or NULLS LAST placement, and referencing select-list aliases or positions.

A column, alias, expression, or positional number named in ORDER BY that defines the sorting sequence. Multiple keys are applied left to right, each breaking ties in the previous one.

A function that operates on one row at a time and returns exactly one result per row, unlike group functions. Character, numeric, date, and conversion functions all belong to this category.

A function that substitutes a replacement value when its first argument is NULL, otherwise returning the first argument itself. Both arguments should share a compatible data type.

A three-argument function that returns the second argument when the first is not NULL and the third argument when the first is NULL. It differs from NVL by offering two distinct outcomes.

A function that returns the first non-NULL argument in its list, accepting two or more arguments. It is the standard alternative to nested NVL calls and requires compatible types.

A two-argument function that returns NULL if the arguments are equal and returns the first argument otherwise. It is commonly used to avoid division by zero or to blank out redundant values.

A function that changes a value from one data type to another, such as TO_CHAR, TO_DATE, or TO_NUMBER. Explicit conversion is preferred over implicit conversion for clarity and control.

Automatic type coercion performed by the database when mismatched types meet, for example comparing a character column with a number. It can silently disable indexes and cause unexpected errors.

A character pattern of element codes, such as YYYY-MM-DD or FM9990.99, passed to conversion functions to control how dates and numbers are displayed or interpreted. It must match the input layout.

A conversion function that turns a character string into a date value using a supplied format model. Without a format model it relies on the session's default date format.

A conversion function that formats a date or number as a character string using a format model. For dates it can also pad with blanks unless the FM modifier is used.

A conditional construct that evaluates conditions in order and returns the result of the first match, with an optional ELSE for no match. It is ANSI-standard SQL usable in both SELECT lists and WHERE clauses.

A legacy conditional function that compares an expression against search values and returns the corresponding result, treating two NULLs as equal. Its nonstandard NULL handling differs from CASE.

A function such as SUM, AVG, COUNT, MIN, or MAX that operates on many rows and returns one value per group. It ignores NULLs except when counting all rows with the asterisk form.

The clause that partitions rows into groups sharing the same values in the listed columns or expressions. Every nonaggregated column in the select list must appear here.

A filter applied to groups after aggregation, allowing conditions on group function results. It differs from WHERE, which filters individual rows before grouping occurs.

A query operation combining rows from two or more tables based on a related condition. Inner joins keep matches only, while outer joins also preserve unmatched rows from one or both sides.

A join whose condition uses equality between columns, typically a foreign key and its matching primary key. Qualifying ambiguous column names with table aliases is usually required.

A join that returns matched rows plus unmatched rows from one side, filling the other side with NULLs. LEFT, RIGHT, and FULL variants differ in which unmatched rows are preserved.

A short name assigned to a table in the FROM clause to simplify references and to self-join the same table twice. Once assigned, the alias replaces the table name in column qualifiers.

A join in which a table is referenced twice under two different aliases so rows can be compared with other rows of the same table, such as matching employees to their managers.

A SELECT statement nested inside another statement, used to supply values or rows for comparison, filtering, or data manipulation. It may be single-row or multi-row depending on the operator used.

A subquery that references a column from the outer query, so it is re-evaluated for each candidate outer row. It can be slower than a join but expresses some conditions more naturally.

A test that returns true when a correlated subquery produces at least one row, stopping evaluation at the first match. It is often used instead of IN for existence checks.

An operator such as UNION, UNION ALL, INTERSECT, or MINUS that combines the result sets of two queries. Column counts and data types must correspond positionally between the queries.

A set operator that concatenates the rows of two queries without removing duplicates or sorting. It is faster than UNION because no duplicate elimination step is performed.

A set operator that returns rows from the first query that do not appear in the second, removing duplicates. It is the Oracle equivalent of the standard EXCEPT operator.

A data manipulation statement that adds new rows to a table, either one row with a VALUES clause or many rows from a subquery. Omitted columns receive their default or NULL.

A data manipulation statement that changes column values in existing rows selected by a WHERE clause. Omitting the WHERE clause modifies every row in the table.

A data manipulation statement that removes rows matching a WHERE condition while leaving the table structure intact. Unlike TRUNCATE, it can be rolled back and fires delete triggers.

A data definition statement that quickly removes all rows from a table and releases its space. It is not rollback-able in the usual sense, does not fire delete triggers, and cannot be filtered.

A value defined for a column that is used when an INSERT omits that column or explicitly specifies the DEFAULT keyword. It does not apply when NULL is inserted explicitly.

A database object that generates unique numeric values on request, typically for surrogate primary keys. It exists independently of tables and is not affected by commits or rollbacks.

The sequence reference that returns the next generated number and advances the sequence. Each reference produces a new value, so the same call should not be repeated in one statement.

The sequence reference that returns the value most recently obtained by NEXTVAL in the current session. It raises an error if NEXTVAL has not yet been called in that session.

An alias for another schema object that lets users reference it without qualifying the owner and object name. Private synonyms belong to one schema; public synonyms are visible to all users.

An optional structure associated with a table that speeds retrieval of rows by providing a fast access path on indexed columns. It consumes space and adds overhead to inserts, updates, and deletes.

An index that enforces distinctness of the indexed column values, automatically created for primary key and unique constraints. It rejects any insert or update that would create a duplicate key.

A stored query that behaves like a virtual table, showing selected rows and columns without storing data itself. Simple views on one table can often be updated through to the base table.

A view constraint that prevents inserts and updates through the view from producing rows the view's defining query would not return. It keeps data visible through the view consistent.

A view whose query result is physically stored and refreshed on demand or on a schedule. It trades storage and refresh cost for faster reads of expensive aggregations or joins.

A table whose rows are visible only to the session or transaction that inserted them and are purged automatically. Global temporary tables persist their definition while private ones vanish at session end.

The set of read-only tables and views maintained by the database that describes every object, user, privilege, and storage structure. Users query it through prefixed views rather than base tables.

A data dictionary view listing tables owned by the current user, including column count and storage statistics. It shows nothing for tables owned by other schemas that the user can access.

A data dictionary view describing every object accessible to the current user, whether owned by that user or granted by another schema. It includes object name, type, and status.

A column-like construct that returns a value but is not stored in a table, such as ROWID, ROWNUM, SYSDATE, or a sequence reference. It can be queried but not inserted into.