Oracle 1Z0-071 flashcards

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

SELECT logical processing order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Only ORDER BY can reference SELECT aliases; WHERE filters rows before grouping, HAVING filters groups.

NULL arithmetic

Any arithmetic expression involving NULL evaluates to NULL. NULL is not equal to any value, including another NULL; use IS NULL or IS NOT NULL to test for nulls.

DISTINCT keyword

DISTINCT eliminates duplicate rows from the result set. It applies to the entire SELECT list, not to individual columns, and can be used with group functions as COUNT(DISTINCT column).

BETWEEN operator

BETWEEN x AND y is inclusive of both endpoints. It is equivalent to column >= x AND column <= y. It can be used with numbers, dates, and strings.

LIKE wildcards

In LIKE, % matches any sequence of zero or more characters and _ matches exactly one character. ESCAPE defines an escape character to treat % and _ literally.

ORDER BY NULLS FIRST

By default, NULLs sort last in ascending order and first in descending order. NULLS FIRST or NULLS LAST explicitly controls the position of nulls in the sorted result.

NVL function

NVL(expr, replacement) returns replacement if expr is NULL; otherwise it returns expr. It takes exactly two arguments and can be used with any data type.

COALESCE function

COALESCE(expr1, expr2, ...) returns the first non-null expression in the list. It takes two or more arguments and is ANSI-compliant; it is more flexible than NVL.

SUBSTR function

SUBSTR(string, start_position, length) returns a substring starting at start_position for length characters. If start_position is negative, it counts from the end of the string.

TO_CHAR date format

TO_CHAR(date, 'format') converts a date to a string using format models such as YYYY, MM, DD, HH24, MI, SS. The format must be enclosed in single quotes.

RR date format

The RR format interprets two-digit years: 00-49 as 2000-2049 and 50-99 as 1950-1999. YY uses the current century, which can produce different results.

DECODE function

DECODE(expr, search1, result1, ..., default) compares expr to each search value and returns the corresponding result. If no match, it returns default or NULL.

COUNT(*) vs COUNT(column)

COUNT(*) counts all rows in a group, including rows with NULLs. COUNT(column) counts only non-null values in that column. COUNT(DISTINCT column) counts distinct non-null values.

HAVING clause

HAVING filters groups after GROUP BY and can contain group functions. WHERE filters rows before grouping and cannot contain group functions.

ROLLUP operator

ROLLUP generates subtotals and a grand total for a hierarchy of columns. It is used with GROUP BY to produce multiple levels of aggregation in one query.

Inner join

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

Outer join (+) operator

In Oracle's traditional syntax, the (+) operator on the side of the table that may have missing rows creates an outer join. It is placed in the WHERE clause join condition.

Correlated subquery

A correlated subquery references columns from the outer query and is evaluated once for each row processed by the outer query. It is often used with EXISTS or NOT EXISTS.

UNION vs UNION ALL

UNION combines result sets and removes duplicate rows, sorting the output. UNION ALL combines all rows including duplicates and is faster because it does not eliminate duplicates.

MINUS operator

MINUS returns rows from the first query that are not present in the second query. It removes duplicates and requires both queries to have the same number of columns and compatible data types.

Set operator column rules

All SELECT statements in a set operation must have the same number of columns, and corresponding columns must have compatible data types. Column names come from the first SELECT.

COMMIT and ROLLBACK

COMMIT makes all changes in the current transaction permanent. ROLLBACK undoes all changes since the last COMMIT or SAVEPOINT. DDL statements issue an implicit COMMIT.

TRUNCATE TABLE

TRUNCATE removes all rows from a table quickly, releases storage, and is DDL (implicit commit). It cannot be rolled back and does not fire DELETE triggers.

MERGE statement

MERGE performs INSERT or UPDATE operations in a single statement based on a condition. It is used for upserts and requires source and target tables with a join condition.

PRIMARY KEY constraint

A PRIMARY KEY uniquely identifies each row in a table and implicitly creates a unique index. It cannot contain NULLs and each table can have only one primary key.

CREATE TABLE AS SELECT

CTAS creates a new table and populates it with the result of a SELECT statement. It copies column definitions and data but does not copy constraints unless explicitly specified.

WITH CHECK OPTION

When creating a view, WITH CHECK OPTION prevents INSERT or UPDATE operations that would make rows invisible through the view. It enforces the view's WHERE condition on DML.

Sequence NEXTVAL and CURRVAL

NEXTVAL returns the next value from a sequence and increments it. CURRVAL returns the current value for the session and must be preceded by NEXTVAL in the same session.

Public synonym

A public synonym is a database object alias available to all users. It is created with CREATE PUBLIC SYNONYM and requires the appropriate privilege.

WITH GRANT OPTION

When granting an object privilege, WITH GRANT OPTION allows the grantee to grant the same privilege to other users. Revoking the privilege cascades to those grants.

Role in Oracle

A role is a named group of privileges that can be granted to users or other roles. Roles simplify privilege management and can be enabled or disabled per session.

SYSDATE vs SYSTIMESTAMP

SYSDATE returns the current date and time from the database server as a DATE value. SYSTIMESTAMP returns the current timestamp with time zone and fractional seconds.

ADD_MONTHS function

ADD_MONTHS(date, n) returns the date n months after the given date. If the resulting month has fewer days, the last day of that month is returned.

REGEXP_LIKE

REGEXP_LIKE(source, pattern, match_parameter) returns TRUE if the source string matches the regular expression pattern. It is used in WHERE clauses for advanced pattern matching.

REGEXP_SUBSTR

REGEXP_SUBSTR(source, pattern, position, occurrence, match_parameter) returns the substring that matches the pattern. Position and occurrence default to 1.

ROWNUM pseudocolumn

ROWNUM assigns a sequential number to each row returned by a query, starting at 1. It is assigned before ORDER BY, so limiting sorted results requires a subquery.

CONNECT BY PRIOR

CONNECT BY PRIOR defines a hierarchical query that traverses parent-child relationships. START WITH specifies the root row, and LEVEL indicates the depth in the hierarchy.

FETCH FIRST clause

FETCH FIRST n ROWS ONLY limits the number of rows returned by a query. It is available in Oracle 12c and later and is applied after ORDER BY.