Database and SQL

The TRUNCATE command is a DDL operation that quickly deletes all rows from a table without logging individual row deletions in the rollback segment, unlike DELETE which writes to rollback.

NUMBER(10,2) means total 10 digits with 2 after decimal, so max 8 digits before decimal (10-2=8). Option B has 9 digits before the decimal (871039453), which exceeds the allowed precision and causes an error. Option C has 3 decimal places but only 8 integer digits, so it would round to 97234512.12 without error. Oracle rounds values that exceed scale but fits within precision, only errors when integer part exceeds allowed digits.

Subqueries in SQL cannot use ORDER BY clause because subqueries return a set of rows used by the outer query, and the outer query determines the final ordering. However, subqueries can use WHERE for row filtering and HAVING for group filtering. ORDER BY is only allowed in the outermost query or in subqueries that use TOP/LIMIT/FETCH FIRST clauses (not in this question's context).

The LENGTH function in Oracle returns the character count of its argument after converting it to a string. When passed -1, Oracle implicitly converts it to the string '-1', which contains 2 characters: the minus sign and the digit 1. The LENGTH function counts all characters including signs and decimal points. This is not an error - Oracle allows implicit type conversion from number to varchar2.

COUNT(column_name) ignores NULL values and only counts non-NULL entries. With 14 total records and 1 NULL in MGR column, COUNT(MGR) returns 13, which is <14. COUNT(*) would return 14 because it counts all rows regardless of NULLs. The question tests understanding that aggregate functions like COUNT, SUM, AVG skip NULL values when operating on a specific column.

A composite primary key involves multiple columns and must be declared using table-level constraint syntax (outside the column definitions). Single-column constraints like NOT NULL, UNIQUE, and CHECK can be declared inline with the column. For example: CREATE TABLE foo (a INT, b INT, PRIMARY KEY (a,b)) - the PRIMARY KEY clause at table level creates a composite key. Inline column syntax cannot specify multiple columns for a single constraint.

In Oracle, deferred constraints are validated only at transaction commit time, not after each DML operation. This allows intermediate constraint violations during transaction processing. Option A is incorrect because constraints ARE checked - just deferred. Option C describes behavior for initially deferred vs. immediate constraints. Option D is incorrect because there's no time-interval-based checking.

In Oracle SQL, hints are passed to the optimizer using specially formatted comments starting with a plus sign, such as /*+ HINT */. Thus, the statement is true.

Oracle DATE type stores all components: century, year, month, day, hour, minute, and second. All six options are correct as DATE includes time information down to seconds.

Standard SQL subqueries cannot contain an ORDER BY clause because a subquery defines an unordered set of rows. Clauses like WHERE and HAVING are perfectly valid inside subqueries.

In Oracle SQL, the LENGTH function returns NULL when given a negative number input. When you execute 'SELECT length(-1) FROM dual', Oracle implicitly converts -1 to a string, but LENGTH(-1) evaluates to NULL, not the count of digits. This is specific Oracle behavior for the LENGTH function with negative numeric inputs.

The COUNT(column) function ignores NULL values. Since one of the 14 records has a NULL value in the mgr column, COUNT(mgr) will return 13, which is less than 14.

In SQL, a composite primary key (primary key spanning multiple columns) must be declared using table-level constraint syntax. Column-level constraints apply to single columns only. NOT NULL (B), UNIQUE (C), and CHECK (D) can all be declared at column level. Only composite primary keys require the table CONSTRAINT syntax like CONSTRAINT pk_name PRIMARY KEY (col1, col2).

Deferred constraints in Oracle are checked only when the transaction is committed, not immediately after each DML statement. This allows temporary violations during a transaction as long as constraints are satisfied by commit time. Option A is false - constraints ARE checked. Option C describes a validated constraint, not deferred. Option D is false - there is no time interval-based checking.