Oracle 1Z0-116 key terms

A collection of data organized into tables of rows and columns, where each table models one entity and relationships between tables are represented by shared key values rather than physical pointers.

A named logical container that holds one user's database objects, such as tables, views, indexes and sequences. In Oracle, a schema is created automatically with a user account and carries the same name as that user.

The memory structures and background processes that run an Oracle database. An instance mounts and opens the database files; the database itself is the set of physical files stored on disk.

The SQL statement that retrieves rows from tables, views or other sources. SELECT and FROM are required; WHERE, GROUP BY, HAVING and ORDER BY are optional clauses that filter, group, aggregate and sort the result.

A temporary name assigned to a column or expression in the SELECT list for display. It lasts only for the query and may be written with or without AS; enclose it in double quotes to keep case or spaces.

A marker for an unknown or inapplicable value. It is neither zero nor a blank space; arithmetic with NULL yields NULL, and it must be tested with IS NULL or IS NOT NULL rather than with equality.

A clause that restricts which rows a statement processes. It follows FROM and takes a condition that evaluates to true, false or unknown; only rows where the condition is true are returned or changed.

A range comparison that tests whether a value falls between two inclusive bounds. It works with numbers, dates and character strings, and NOT BETWEEN returns values outside those bounds.

A pattern-matching operator for character values in which percent (%) matches any sequence of characters, including none, and underscore (_) matches exactly one character. ESCAPE lets you match literal wildcard characters.

The evaluation order NOT, then AND, then OR. Because AND binds more tightly than OR, conditions that mix both operators usually need parentheses to express the intended logic correctly.

The clause that sorts a query's result. It accepts column names, aliases, expressions or column positions and is evaluated last, after SELECT, so it can use aliases defined in the SELECT list.

A function that acts on one row at a time and returns exactly one value for each row processed. Character, numeric, date and conversion functions are single-row functions and can be nested in SELECT, WHERE and ORDER BY.

A function that manipulates text, such as UPPER, LOWER, INITCAP, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM and REPLACE. Each returns a character value and may be nested inside another function.

A function that operates on DATE or TIMESTAMP values, such as SYSDATE, CURRENT_DATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY and ROUND. Depending on the function it returns a date or a number.

A function that changes a value from one data type to another, most often TO_CHAR, TO_DATE and TO_NUMBER. Explicit conversion is preferred because it is predictable and can be used with format models.

A function that returns a substitute value when its first argument is NULL and otherwise returns the first argument. Both arguments should have compatible data types to avoid implicit conversion and unexpected results.

A conditional expression that returns one of several results based on comparisons. Simple CASE tests equality against listed values, while searched CASE evaluates arbitrary conditions; either form is valid wherever an expression is allowed.

An Oracle-specific conditional function that compares an expression with a list of search values and returns the matching result or an optional default. It treats two NULLs as equal, unlike a simple CASE expression.

A function that operates on a set of rows and returns one value per group, such as SUM, AVG, MIN, MAX and COUNT. All group functions ignore NULLs except COUNT(*), which counts every row.

A clause that divides query rows into groups so group functions can be applied to each one. Every SELECT-list item that is not inside a group function must appear in the GROUP BY clause.

A clause that filters groups after GROUP BY has formed them. It may contain group functions, which WHERE may not, and it runs before ORDER BY; use WHERE to filter rows and HAVING to filter groups.

The default join that returns only rows whose join condition matches in both tables. Unmatched rows from either side are discarded, so the result can contain fewer rows than either source table.

A join that keeps unmatched rows from one or both tables, filling the missing columns with NULL. LEFT and RIGHT outer joins preserve one side; FULL OUTER JOIN preserves unmatched rows from both tables.

A join with no join condition that pairs every row of one table with every row of another. The result is a Cartesian product whose row count equals the product of the two tables' row counts.

A SELECT statement nested inside another SQL statement. A single-row subquery returns at most one row and uses comparison operators; a multiple-row subquery returns several rows and needs IN, ANY or ALL.

A subquery that references a column from the outer query, so it is evaluated once for each outer row instead of once for the whole statement. It is commonly used with EXISTS and NOT EXISTS.

A predicate that is true when its subquery returns at least one row. Evaluation stops at the first match, and NOT EXISTS commonly finds rows in one table that have no counterpart in another.

An operator that combines the result sets of two or more queries: UNION, UNION ALL, INTERSECT or MINUS. The queries must return the same number of columns with compatible data types, and column names come from the first query.

A DML statement that adds rows to a table. It can name target columns and supply matching values, insert rows produced by a subquery, or use the DEFAULT keyword to take a column's declared default.

A DML statement that changes existing values in rows that satisfy the WHERE clause. Omitting WHERE updates every row, so a condition is usually essential; changes remain undoable until the transaction is committed.

A DML statement that removes rows satisfying the WHERE clause. Unlike TRUNCATE, it can be rolled back, fires triggers and generates undo; without a WHERE clause it removes every row in the table.

Statements that manage logical units of work: COMMIT makes changes permanent, ROLLBACK undoes uncommitted changes, and SAVEPOINT marks a point to roll back to. DDL statements commit implicitly before and after they execute.

A DDL statement that creates a table by naming its columns, giving each a data type, and optionally adding constraints and storage options. It commits the current transaction and makes the table available to users with privileges.

A rule on a table that limits the values a column or combination of columns may hold, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK. Constraints can be named and enabled or disabled later.

A constraint that uniquely identifies every row in a table. It combines NOT NULL and UNIQUE, only one may exist per table, and foreign keys in other tables can reference it.

A constraint that requires values in a child table to match values of a primary or unique key in a parent table. It enforces referential integrity and can specify ON DELETE CASCADE or ON DELETE SET NULL.

A DDL statement that changes an existing table's definition, such as adding, modifying or dropping a column, or adding and dropping constraints. Some changes are restricted when the table holds data or has dependent objects.

A stored query that presents data from one or more tables as if it were a table. Views can hide columns and rows from users, and a simple view on one table can often be updated through to its base table.

A schema object that generates unique numbers on demand through NEXTVAL and CURRVAL, usually to populate primary keys. It is independent of tables, so its values are not rolled back and can be shared across sessions.

A schema object that provides a fast access path to table rows, usually built on one or more columns to speed up queries and enforce uniqueness. Oracle creates indexes automatically for primary key and unique constraints.