1Z0-149 flashcards

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

Logical order of SELECT clauses

FROM is evaluated first, then WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY. Because SELECT comes late, column aliases defined there are usable only in ORDER BY.

NULL in arithmetic

Any arithmetic expression containing NULL evaluates to NULL, so 5 + NULL and NULL * 0 both return NULL. Use NVL or COALESCE to substitute a value before computing.

DESCRIBE command

A SQL*Plus command that displays a table's column names, data types, and nullability. It is not a SQL statement and cannot be embedded inside a SELECT or other DML statement.

BETWEEN operator

Tests whether a value lies within an inclusive range, equivalent to value >= low AND value <= high. Both boundary values are included in the result, and NULL input yields UNKNOWN.

NOT IN with NULL

If the list passed to NOT IN contains a NULL, every comparison becomes UNKNOWN and the query returns no rows. Use NOT EXISTS or filter NULLs out of the list instead.

LIKE wildcards

Percent matches any sequence of characters including none, and underscore matches exactly one character. The ESCAPE clause designates a character that makes the wildcards literal.

Default NULL ordering

ORDER BY places NULLs last for ascending order and first for descending order. The NULLS FIRST or NULLS LAST clause overrides this default explicitly.

SUBSTR with zero position

A position argument of zero is treated as one, so SUBSTR('ABCDE',0,2) returns 'AB'. A negative position counts backward from the end of the string.

Date arithmetic in Oracle

Adding a number to a date adds that many days, and subtracting two dates returns a number of days. Add hours by adding the fraction hours divided by 24.

MONTHS_BETWEEN function

Returns the number of months between two dates as a fractional number. If the day-of-month components differ, the result includes a fraction based on a 31-day month.

SYSDATE versus CURRENT_DATE

SYSDATE returns the database server's date and time in the database time zone. CURRENT_DATE returns the date and time in the session time zone.

NVL function

Converts NULL to a specified substitute value: NVL(commission,0) returns zero when commission is NULL. Both arguments should be type-compatible to avoid implicit conversion.

NULLIF versus COALESCE

NULLIF returns NULL when its two arguments are equal, otherwise the first argument. COALESCE scans its argument list and returns the first non-NULL value found.

DECODE and NULL equality

DECODE treats two NULL values as equal when matching, so DECODE(NULL,NULL,'match','nomatch') returns 'match'. Standard CASE comparisons treat NULL equals NULL as UNKNOWN.

TO_CHAR date format elements

Format models such as YYYY, MM, DD, HH24, MI, and SS control output. The FM modifier suppresses leading spaces and padding in the formatted result.

COUNT(*) versus COUNT(column)

COUNT(*) counts every row in the group, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL, so the two often differ.

AVG ignoring NULLs

The AVG group function skips NULL values and divides the sum by the count of non-NULL values, not by the total number of rows in the group.

GROUP BY rule

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

WHERE versus HAVING

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

Oracle outer join operator

The (+) symbol marks the side that may lack matching rows, so unmatched rows from the other table are preserved with NULLs. It cannot appear on both sides of a condition.

Cartesian product

A join with no join condition, or an explicit CROSS JOIN, returns every combination of rows from the two tables. A table with m rows joined to one with n rows yields m times n rows.

NATURAL JOIN behavior

Joins two tables on all columns having the same name and compatible data type. The shared join columns appear only once in the output result set.

Single-row subquery error

A subquery used with a single-row comparison operator such as = must return at most one row. Returning more than one row raises ORA-01427.

EXISTS operator

Returns TRUE when the correlated subquery produces at least one row. NOT EXISTS is NULL-safe, unlike NOT IN, because it only tests row existence.

Scalar subquery

A subquery placed in the SELECT list that must return exactly one row and one column. If it returns no rows, the result for that row is NULL.

UNION versus UNION ALL

UNION merges results and removes duplicate rows, requiring a sort. UNION ALL keeps all rows including duplicates and performs better when duplicates are acceptable.

MINUS set operator

Returns rows produced by the first query that do not appear in the second query result. It is not commutative, so A MINUS B and B MINUS A differ.

Set operator column rules

Queries combined by a set operator must have the same number of columns in the same order with compatible data types. Output column names come from the first query.

TRUNCATE versus DELETE

TRUNCATE removes all rows quickly as a DDL operation, cannot be rolled back, and resets storage. DELETE removes rows as DML and can be rolled back before commit.

SAVEPOINT and rollback

A savepoint marks a position in a transaction. ROLLBACK TO that savepoint undoes only the work performed after it, leaving earlier changes and the transaction active.

Sequence NEXTVAL and CURRVAL

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

Composite index leading column

A composite index is keyed on multiple columns in a defined order. The optimizer can usually use it only when the predicate references the leading column.

Private versus public synonym

A private synonym belongs to one schema and is visible only to that user. A public synonym is visible to every user who has privileges on the underlying object.

WITH CHECK OPTION

A view clause that rejects INSERT and UPDATE statements producing rows the view's WHERE clause would not return. It keeps data visible through the view consistent.

Private temporary table

A session-private table whose name must begin with ORA$PTT_. Its definition and rows disappear at commit or session end, depending on the ON COMMIT setting.

USER versus ALL versus DBA views

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

USER_OBJECTS view

Lists objects owned by the current user with name, type, status, and creation time. A STATUS of INVALID indicates a view or stored object needing recompilation.

USER_DEPENDENCIES view

Records references between objects, showing which object depends on which other object. It helps identify views invalidated when a base table is altered or dropped.