Oracle SQL flashcards

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

DISTINCT keyword

Removes duplicate rows from the result set. It applies to the entire SELECT list, so every selected column combination must be identical for a row to be eliminated.

Column alias with AS

AS is optional when naming an output column. An alias in double quotes keeps its case and may contain spaces, while an unquoted alias is uppercase.

NULL in arithmetic

Any arithmetic expression containing NULL evaluates to NULL. The concatenation operator || treats NULL as an empty string, so 'A'||NULL returns 'A'.

BETWEEN operator

Tests whether a value lies inside an inclusive range. col BETWEEN a AND b is equivalent to col >= a AND col <= b, and both endpoints are included.

LIKE wildcards

The percent sign matches any sequence of zero or more characters and the underscore matches exactly one character; the ESCAPE clause makes wildcard characters literal.

ORDER BY NULL placement

Ascending order places NULL values last by default, and descending order places them first. NULLS FIRST or NULLS LAST overrides this default behavior.

SUBSTR function

SUBSTR(char, position, length) extracts characters starting at the 1-based position. A negative position counts backward from the end, and length is optional.

NVL function

NVL(expr, replacement) returns the replacement when expr is NULL and returns expr otherwise. Both arguments must have compatible data types and the row count is unchanged.

ADD_MONTHS function

ADD_MONTHS(date, n) returns the date shifted by n months. If the resulting day does not exist in the target month, the last day of that month is returned.

TO_CHAR date conversion

TO_CHAR(date, 'fmt') converts a DATE value into a character string using format elements such as YYYY, MM, DD, HH24:MI:SS, MON, or DY.

DECODE function

DECODE(expr, search1, result1, ..., default) compares expr by equality and returns the matching result; it treats two NULLs as equal, unlike CASE.

NULLIF function

NULLIF(a, b) returns NULL when a equals b, otherwise it returns a. It requires at least two arguments and the first cannot be the literal NULL.

COUNT(*) versus COUNT(column)

COUNT(*) counts all rows in a group including rows containing NULLs, while COUNT(column) counts only the rows where that column is not NULL.

HAVING clause

Filters grouped rows after GROUP BY and aggregation have been applied. It may reference group functions, which the WHERE clause cannot, and runs before ORDER BY.

Group functions and NULLs

AVG, SUM, MIN, MAX, COUNT, STDDEV and VARIANCE ignore NULL values. Wrap the argument in NVL when NULLs must be treated as a real value.

GROUP BY requirement

Every column in the SELECT list that is not inside a group function must appear in the GROUP BY clause, otherwise Oracle raises error ORA-00979.

NATURAL JOIN

Joins two tables automatically on every column having the same name and compatible data types; those join columns appear only once in the output.

LEFT OUTER JOIN

Returns all rows from the left table plus matching rows from the right table; unmatched left rows appear once with NULLs in all right-table columns.

USING clause

Specifies join columns that must exist with identical names in both tables. The column appears once in the output and cannot be qualified with an alias.

Cartesian product

Results when a join lacks a valid join condition; every row of the first table pairs with every row of the second, producing m times n rows.

Single-row subquery operators

A subquery returning one row and one column may use =, >, <, >=, <= or <>. If it returns more than one row, Oracle raises error ORA-01427.

ANY and ALL operators

col > ANY (subquery) means the value exceeds the minimum returned value; col > ALL (subquery) means it exceeds the maximum returned value.

Correlated subquery

A subquery that references a column from the outer query. It is re-evaluated for each candidate outer row instead of once for the whole statement.

EXISTS operator

Tests whether a subquery returns at least one row and yields TRUE or FALSE. NOT EXISTS avoids the NULL problems that affect NOT IN subqueries.

UNION ALL operator

Combines the results of two queries and keeps every row including duplicates. It performs no duplicate elimination or sort, so it is generally faster than UNION.

MINUS operator

Returns only the distinct rows that appear in the first query and not in the second; NULL values are treated as equal during that comparison.

Set operator rules

Each SELECT must return the same number of columns with compatible data types; headings come from the first SELECT and one ORDER BY is allowed at the end.

UPDATE without WHERE

An UPDATE statement with no WHERE clause modifies every row in the table. The change stays uncommitted until COMMIT or ROLLBACK is issued.

TRUNCATE versus DELETE

TRUNCATE is DDL, removes all rows, cannot be rolled back, and fires no triggers; DELETE is DML, can be conditional, is rolled back, and fires triggers.

Transaction end events

A transaction ends with COMMIT, ROLLBACK, or the execution of DDL such as CREATE or ALTER, which implicitly commits any pending changes.

Sequence NEXTVAL and CURRVAL

NEXTVAL generates the next sequence value, while CURRVAL returns the last value generated in the current session and is invalid until NEXTVAL has been referenced.

Unique index creation

Oracle automatically creates a unique index when a PRIMARY KEY or UNIQUE constraint is defined; an index speeds retrieval but adds overhead to INSERT, UPDATE and DELETE.