1Z0-071: Oracle Database SQLDemo

Dropping FINANCE removes the user account and the grant FINANCE made to FIN_CLERK, so FIN_CLERK loses the SELECT privilege on SCOTT.EMP. Oracle also cascades an object-privilege revocation to grants that the revoked user propagated using WITH GRANT OPTION; therefore, revoking FINANCE's SELECT also revokes FIN_CLERK's dependent SELECT grant. Dropping User Accounts and Revokes of Privileges and Roles from a User

CACHE 10 preallocates sequence values. After NEXTVAL returns 1, values 2 through 10 remain cached. Specifying NOCACHE when altering the sequence removes the cached allocation, so those unused values are skipped and the next generated value is 11.

A subquery used with the single-row comparison operator = must return no more than one row. The outer query can return any number of rows that satisfy the salary comparison.

NVL2 uses its first expression only to determine whether it is null; it returns either its second expression when the first is not null or its third expression when the first is null. COALESCE uses short-circuit evaluation and stops once it finds the first non-null expression.

MOD(quantity, 10) = 0 identifies values with no remainder after division by 10. TRUNC(quantity, -1) truncates the units digit; a value equals that result only when its units digit is zero. Oracle documents that MOD returns the remainder of division and that a negative second argument to numeric TRUNC truncates digits to the left of the decimal point.

INTERSECT returns the distinct rows that appear in both component query results. Corresponding select-list expressions must have matching counts and compatible datatype groups, but their column names need not match because set-operation columns are matched by position.

Arithmetic and other operators are evaluated before conditions. Within conditional logic, NOT has higher precedence than both AND and OR.

Oracle supports subtraction between compatible INTERVAL DAY TO SECOND values and addition of a day-to-second interval to a timestamp. It does not support arithmetic that subtracts a DATE from an interval or multiplies a DATE by an interval, and INTERVAL DAY and INTERVAL MONTH belong to incompatible interval families.

Oracle allows INSERT and UPDATE object privileges to be granted for specified table columns. A selective INSERT grant permits values only for the designated columns, and a selective UPDATE grant permits changes only to those columns.

A table alias is available only in the query block in which it is defined. Alias a is defined inside the subquery and cannot be used by the outer query’s select list; therefore a.avg_sal is invalid. A correlated subquery may reference e.department_id from its parent query block.

A savepoint marks a point within the current transaction that can later be targeted with ROLLBACK TO SAVEPOINT. A rollback to a savepoint that was not established for the transaction results in an error. Savepoints do not make uncommitted changes visible to other sessions, and COMMIT always applies to the entire transaction rather than only the work between savepoints. Oracle Database SQL Language Reference — SAVEPOINT

With GROUP BY dept_id, each selected item must be a grouping expression, an aggregate function, a constant, or an expression derived from those. MAX(last_name) and SUM(salary) are aggregate functions, while dept_id is the grouping column. Oracle documents this rule for select lists that contain a GROUP BY clause.

COMMIT permanently saves the changes made before it. ROLLBACK TO SAVEPOINT b reverses only work performed after savepoint b: it restores the deleted pen row and restores fountain pen's code from 30 to 3. The deletion of pencil occurred before savepoint b, so it remains deleted. The final committed rows therefore include pen with code 10 and fountain pen with code 3, but no pencil row. Oracle documents that a rollback to a savepoint reverses only changes made after that savepoint without ending the transaction.

WHERE filters rows before aggregate calculations are performed, so an aggregate function such as AVG, SUM, or COUNT cannot be used directly there. A subquery can calculate the aggregate result and make it available to the outer WHERE condition. Aggregate functions are designed for use in HAVING, while single-row functions can be used without requiring a subquery.

For large tables, TRUNCATE is generally faster than DELETE because it empties the table without individually processing every row. MySQL documents that TRUNCATE drops and re-creates the table, making it much faster than deleting rows one by one, particularly for large tables.

Oracle alternative-quoted string literals use q followed by a delimiter. The ! delimiter and the matched [ and ] delimiters allow the apostrophe in We're to appear as part of the returned text without escaping. Ordinary Oracle string literals require an embedded apostrophe to be represented by two consecutive single quotation marks; a backslash is not the ordinary string-literal escape mechanism.

System privileges include SELECT ANY TABLE for database-wide table access, EXECUTE ANY PROCEDURE for executing procedures across schemas, and CREATE SESSION for connecting to a database instance. UTL_FILE file access is controlled through READ and WRITE privileges on directory objects, and WITH GRANT OPTION is used when delegating object privileges. Oracle documents SELECT ANY TABLE as a system privilege and identifies CREATE ANY DIRECTORY as distinct from the object privileges granted on directories.

Oracle number format models round values to the precision represented by the model. Because $9,999 has no decimal element, 1023.95 is rounded and displayed as $1,024. A value of 10235.95 rounds to 10,236, which requires more integer digits than the four digit positions in the model; Oracle replaces an unrepresentable value with pound signs. The output width includes the sign position and format characters, producing #######.

ch.channel_type is retained at every aggregation level. ROLLUP(t.month, co.country_code) produces aggregates by channel type, month, and country; then the month subtotal within each channel type; then the channel-type total. This matches the rows in which CO is null for monthly subtotals and both MONTH and CO are null for each channel total. Oracle documents that ROLLUP produces superaggregate groupings in addition to regular groupings.

EMP.DEPTNO may be NULL because its foreign key does not make the column NOT NULL. A negative COMMISSION value is valid if it remains less than SALARY; the displayed constraints impose no nonnegative requirement on commission. EMP.MANAGER references SYS_C0012484, the primary-key constraint on EMP.EMPNO, so it is a foreign key to that column. Oracle check constraints are satisfied when their condition is true (or unknown because of NULL), and primary keys cannot contain NULL values.

Oracle permits DELETE with or without the optional FROM keyword. Deleting either the qualifying parent rows or all parent rows succeeds because ON DELETE CASCADE automatically deletes the related child rows in ORDER_ITEMS.

CUST_INCOME_LEVEL IS NOT NULL retains only customers with an income-level value, and CUST_CREDIT_LIMIT IS NOT NULL excludes rows for which CUST_CREDIT_LIMIT * 0.05 would be null. Oracle nulls must be tested with IS NULL or IS NOT NULL; comparisons such as != NULL and <> NULL evaluate to UNKNOWN rather than identifying non-null values. Oracle Database SQL Language Reference: Nulls

MOD(25,3) returns 1. A negative precision is valid for both numeric ROUND and TRUNC and applies to digits left of the decimal point. Consequently, both ROUND(1,-1) and TRUNC(1,-1) return 0.

ALTER TABLE can set a table to READ ONLY, preventing DML that affects it; rename a table with RENAME TO; and enable or disable integrity constraints through its enable/disable clause. Oracle does not allow dropping every column from a table using ALTER TABLE, and row-level locks are acquired through DML locking mechanisms rather than this DDL statement.