Oracle 1Z0-116 flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Instance versus database

An instance is the running memory structures (SGA) and background processes; the database is the set of physical files on disk that the instance mounts and opens.

Implicit commit events

DDL and DCL statements issue an implicit COMMIT before and after they execute; a normal exit from a client tool commits, while an abnormal termination rolls back the open transaction.

SELECT clause evaluation order

Clauses are written SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, but the server evaluates FROM and WHERE first, then GROUP BY and HAVING, then SELECT, and ORDER BY last.

DISTINCT keyword scope

DISTINCT removes duplicate rows from the whole select list, not only the first column, and is applied to the projected result before ORDER BY sorts it.

DUAL table purpose

DUAL is a one-row, one-column table owned by SYS that lets you evaluate expressions or call functions in a SELECT statement that needs no application table.

BETWEEN and LIKE operators

BETWEEN low AND high is inclusive at both ends; LIKE matches percent for any characters and underscore for one character, and the ESCAPE clause defines a literal wildcard prefix.

Default NULL ordering in ORDER BY

With ASC, NULLs sort last; with DESC, NULLs sort first. The NULLS FIRST and NULLS LAST keywords in the ORDER BY clause override either default explicitly.

NVL function semantics

NVL(expr1, expr2) returns expr2 when expr1 is NULL, otherwise expr1; the two arguments must have the same or implicitly convertible data types.

SUBSTR and INSTR positions

SUBSTR(string, start, length) uses 1-based positions, and a negative start counts from the end of the string; INSTR returns the position of a match, or zero when none is found.

MONTHS_BETWEEN and ADD_MONTHS

ADD_MONTHS(date, n) returns the date n months later; MONTHS_BETWEEN(date1, date2) returns the fractional number of months between two dates, which can be negative.

YY versus RR format mask

YY always returns the current century. RR maps 00 through 49 to the current century and 50 through 99 to the previous century, based on the current year.

FM fill mode modifier

The FM modifier in a format model suppresses leading blanks and trailing zeros that Oracle normally pads into formatted numbers and dates, for example TO_CHAR(hiredate,'FMMonth').

DECODE expression matching

DECODE(expr, search1, result1, ..., default) returns the result whose search value matches expr; two NULLs are treated as equal and a missing match returns NULL.

COUNT(*) versus COUNT(column)

COUNT(*) counts every row including rows with nulls; COUNT(column) counts only non-null values, and COUNT(DISTINCT column) counts distinct non-null values in that column.

Group functions ignore NULLs

SUM, AVG, MAX, MIN, STDDEV and VARIANCE skip NULL values before computing; AVG therefore divides the total by the count of non-null values, not the row count.

HAVING versus WHERE clause

WHERE filters individual rows before grouping and cannot contain group functions; HAVING filters groups after GROUP BY and may contain group functions such as SUM or COUNT.

Oracle outer join plus operator

The (+) operator marks the table that supplies NULL-extended rows, so it goes on the side lacking matching rows; it cannot appear on both sides of a condition.

USING clause column restriction

JOIN USING(column) joins two tables on a same-named column and returns that column once; no table alias may qualify the column anywhere in the statement.

Natural join behaviour

A NATURAL JOIN joins automatically on every column that has the same name in both tables; if the tables share no such column, the result is a Cartesian product.

Correlated subquery definition

A correlated subquery references a column from the outer query, so it is re-evaluated for each candidate outer row instead of once for the whole statement.

NOT IN with NULLs

If the subquery returns any NULL value, NOT IN evaluates to unknown for every row and returns no rows; NOT EXISTS or filtering nulls avoids this pitfall.

Scalar subquery row limit

A scalar subquery used in the SELECT list or a WHERE condition must return exactly one column and at most one row; returning more rows raises ORA-01427.

UNION versus UNION ALL

UNION sorts the combined result and removes duplicate rows; UNION ALL concatenates the rows without sorting and keeps all duplicates, so it usually runs faster.

Set operator column rules

Every query in a compound statement must return the same number of columns with compatible data types; headings come from the first query and ORDER BY appears once at the end.

UPDATE without WHERE clause

An UPDATE statement with no WHERE clause modifies every row in the table; the same omission in DELETE removes all rows but leaves the table structure and indexes intact.

SAVEPOINT and ROLLBACK TO

SAVEPOINT name marks a point inside the current transaction; ROLLBACK TO name undoes only the changes made after that point while keeping earlier uncommitted work in the transaction.

TRUNCATE versus DELETE

TRUNCATE is DDL, cannot be rolled back, takes no WHERE clause and resets the high-water mark; DELETE is DML, can be rolled back, fires triggers and supports WHERE.

CTAS constraints copied

CREATE TABLE AS SELECT copies the rows and the NOT NULL constraints only; primary key, unique, check and foreign key constraints must be added with ALTER TABLE afterwards.

CHAR versus VARCHAR2 storage

CHAR(n) blank-pads every stored value to exactly n characters; VARCHAR2(n) stores variable-length character strings up to 4000 bytes and does not pad values with blanks.

DROP TABLE with PURGE

DROP TABLE moves the table and its indexes to the recycle bin, where FLASHBACK TABLE can restore them; adding the PURGE keyword deletes the table permanently and frees space.

WITH CHECK OPTION view

WITH CHECK OPTION rejects INSERT and UPDATE statements through the view that would create rows the view's WHERE clause could not select, enforcing the view's row filter.

Sequence NEXTVAL and CURRVAL

NEXTVAL generates and returns the next sequence value; CURRVAL returns the last value generated in the current session and raises an error before NEXTVAL has been called.