Oracle 1Z0-084 flashcards

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

DUAL table

A one-row, one-column table owned by SYS and available to all users. It is used to evaluate expressions or return a single row without selecting from a real table.

WHERE vs HAVING

WHERE filters individual rows before grouping and cannot contain group functions. HAVING filters groups after aggregation and can contain group functions.

NVL function

NVL(expr1, expr2) returns expr2 when expr1 is NULL, otherwise expr1. Both arguments should have compatible data types to avoid implicit conversion.

COALESCE function

COALESCE returns the first non-NULL argument from a list. It is standard SQL, accepts multiple arguments, and short-circuits evaluation.

NULLIF function

NULLIF(a, b) returns NULL when a equals b, otherwise a. It is commonly used to prevent division by zero: 1/NULLIF(denominator, 0).

TO_CHAR format model

A pattern that controls output: 9999.99 for numbers, $99,999.99 for currency, and YYYY-MM-DD HH24:MI:SS for dates. It must match the input when converting strings.

Implicit data conversion

Oracle automatically converts data types when comparing or assigning values. It depends on NLS settings and can prevent index use when a VARCHAR2 column is compared to a number.

COUNT(*) vs COUNT(col)

COUNT(*) counts all rows including those with NULLs. COUNT(col) counts only non-NULL values in that column. COUNT(DISTINCT col) counts distinct non-NULL values.

GROUP BY rule

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

ROLLUP operator

ROLLUP(a, b) produces grouped rows for (a, b), subtotals for (a), and a grand total. It is used for hierarchical subtotal reports.

CUBE operator

CUBE(a, b) produces all possible grouping combinations: (a, b), (a), (b), and the grand total. It is useful for cross-tabular summaries.

GROUPING SETS

GROUPING SETS explicitly lists the grouping combinations to produce, such as ((a, b), (a), ()). It gives precise control without generating every CUBE combination.

INNER JOIN

An inner join returns only rows that satisfy the join condition in both tables. Rows without a match in either table are excluded.

LEFT OUTER JOIN

A left outer join returns all rows from the left table plus matching rows from the right table. Unmatched right-side columns are returned as NULL.

Oracle outer join (+)

Legacy syntax where the (+) operator marks the side that may lack matching rows. It cannot be used on both sides of a condition or with ANSI join syntax.

CROSS JOIN

A cross join returns the Cartesian product of two tables with no join condition. The result row count equals the product of the two table row counts.

NATURAL JOIN

A natural join joins two tables on all columns with the same name and compatible data types. The duplicate join columns appear only once in the output.

Self-join

A join of a table to itself using two different aliases. It is used for hierarchical relationships such as employee to manager.

Single-row subquery

A subquery that returns exactly one row and uses single-row comparison operators such as =, <, or >. A multi-row result raises ORA-01427.

EXISTS operator

EXISTS returns TRUE when the subquery returns at least one row. It is used for semi-joins and can stop evaluating after the first matching row.

NOT IN with NULL

If the subquery result contains a NULL, NOT IN returns no rows because the comparison evaluates to UNKNOWN. Use NOT EXISTS or exclude NULLs explicitly.

UNION vs UNION ALL

UNION combines result sets and removes duplicate rows, which requires a sort. UNION ALL keeps all rows including duplicates and is faster.

MINUS operator

MINUS returns rows from the first query that do not appear in the second query. It removes duplicates and is not commutative: A MINUS B differs from B MINUS A.

MERGE statement

MERGE performs an upsert: WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. It combines insert and update logic in a single statement.

Implicit commit

DDL statements such as CREATE, ALTER, and DROP issue an implicit commit before and after execution. DML changes cannot be rolled back after a DDL statement.

TRUNCATE vs DELETE

TRUNCATE is DDL, cannot be rolled back, resets the high-water mark, and does not fire DELETE triggers. DELETE is DML, is transactional, and can be rolled back.

RETURNING INTO clause

RETURNING INTO returns affected column values into host variables or a record after INSERT, UPDATE, or DELETE. It avoids a follow-up SELECT.

FOREIGN KEY constraint

A foreign key references a PRIMARY KEY or UNIQUE column in another table. ON DELETE CASCADE deletes child rows; ON DELETE SET NULL sets them to NULL.

WITH CHECK OPTION

A view option that rejects INSERT or UPDATE statements that would make rows invisible through the view. WITH READ ONLY blocks all DML on the view.

Sequence NEXTVAL and CURRVAL

NEXTVAL advances the sequence and returns the next value. CURRVAL returns the last value for the session and is invalid before NEXTVAL is called.

USER_ vs ALL_ vs DBA_ views

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

WITH GRANT OPTION

A clause on GRANT that lets the grantee grant the same object privilege to other users. Revoking the original grant cascades to dependent grants.