Oracle 1Z0-182 flashcards

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

Primary key

A constraint that uniquely identifies each row and implicitly forbids NULLs. Only one primary key is allowed per table, and it automatically creates a unique index.

Foreign key

A constraint whose values must match a primary or unique key in another table (or the same table). It enforces referential integrity and permits NULLs unless also NOT NULL.

NULL semantics

NULL represents unknown or missing data. Comparisons with NULL yield UNKNOWN, so filters must use IS NULL or IS NOT NULL, and NVL or COALESCE supplies defaults.

Logical query order

FROM and joins first, then WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY. This explains why SELECT aliases work in ORDER BY but not in WHERE.

ROWNUM behavior

ROWNUM numbers rows as they are produced, before ORDER BY. To limit a sorted result, wrap the ordered query in an inline view and filter ROWNUM outside.

FETCH FIRST clause

Oracle 12c row-limiting syntax: OFFSET n ROWS FETCH NEXT m ROWS ONLY. It applies after ORDER BY, unlike ROWNUM, and is the preferred top-N method.

LIKE and ESCAPE

LIKE matches with % (any string) and _ (one character). The ESCAPE clause names a character that turns the next wildcard into a literal.

ORDER BY NULL placement

By default NULLs sort last in ascending order and first in descending order. NULLS FIRST or NULLS LAST explicitly overrides this behavior per sort column.

NVL and COALESCE

NVL(a,b) returns b when a is NULL. COALESCE returns the first non-null argument and accepts more than two arguments, making it more flexible.

COUNT(*) versus COUNT(col)

COUNT(*) counts all rows including those with NULLs. COUNT(col) counts only rows where col is not NULL, so the two can return different totals.

GROUP BY rule

Every column in the SELECT list that is not inside a group function must appear in the GROUP BY clause, or the query fails with an error.

HAVING versus WHERE

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

DECODE versus CASE

DECODE performs equality comparisons only and treats NULLs as equal. CASE is ANSI-standard, supports any condition, and is preferred for readability.

Outer join (+) operator

The legacy Oracle operator is placed on the side that may lack matching rows. It cannot be combined with ANSI JOIN syntax, and both forms should not be mixed.

Self join

A join of a table to itself using two different aliases, commonly used to relate rows within one table, such as employees to their managers.

Correlated subquery

A subquery that references a column from the outer query and is evaluated once per outer row. EXISTS is often used and stops at the first match.

Scalar subquery

A subquery placed in the SELECT list or a comparison that must return exactly one row and one column; more than one row causes a runtime error.

MERGE statement

A single DML statement that inserts rows not found in the target and updates rows that match, based on an ON condition. It cannot update ON-clause columns.

DELETE versus TRUNCATE

DELETE is DML, can have a WHERE clause, fires triggers, and can be rolled back. TRUNCATE is DDL, removes all rows, and commits implicitly.

Implicit commit

DDL statements such as CREATE, ALTER, DROP, and TRUNCATE commit the current transaction before and after execution, so pending DML becomes permanent.

ON DELETE CASCADE

A foreign key option that automatically deletes child rows when the referenced parent row is deleted. The default behavior instead raises an error.

CHECK constraint limits

CHECK constraints evaluate a condition per row and cannot reference other rows, other tables, SYSDATE, or ROWNUM. They may use IN, BETWEEN, and IS NOT NULL.

WITH CHECK OPTION

A view clause that rejects INSERT or UPDATE statements producing rows the view's WHERE clause would not return, keeping data inside the view's defined set.

Simple versus complex view

A simple view uses one table with no group functions or DISTINCT and supports DML. A complex view uses joins or grouping and is generally read-only.

Sequence NEXTVAL and CURRVAL

NEXTVAL returns the next sequence value and increments it. CURRVAL returns the last value generated in the current session and errors before any NEXTVAL call.

Sequence gaps

Sequence values are generated outside transaction control. A rollback, cache loss, or shared use by many sessions leaves permanent gaps in the numbering.

Public synonym

A synonym created with CREATE PUBLIC SYNONYM that is visible to every user. A private synonym with the same name takes precedence for its owner.

Function-based index

An index built on an expression such as UPPER(name). Queries must use the identical expression for the optimizer to use the index.

CREATE USER syntax

Defines a database account with a password and optional default tablespace, quota, temporary tablespace, and profile. CREATE SESSION is needed to log in.

Data dictionary views

USER_ views show objects owned by the current user, ALL_ views show accessible objects, and DBA_ views show all objects and require DBA privileges.

Object versus system privilege

System privileges allow actions on the database, such as CREATE TABLE. Object privileges allow actions on a named object, such as SELECT on a table or EXECUTE on a procedure.

WITH GRANT OPTION

A clause on GRANT that lets the grantee grant the same object privilege to other users or roles. Revoking it can cascade to dependent grants.