Database and SQL

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.

Oracle does support using comments to pass optimizer hints. These are specially formatted comments that tell the optimizer how to execute a query - for example, /*+ INDEX(table index_name) */ hints which index to use. This is a documented Oracle feature.

Oracle DATE type stores century, year, month, day, hour, minute, and second. All six components listed (A through F) are stored. Oracle DATE has no time zone information, but stores all the temporal components shown.

In Oracle, VARCHAR max length is 2000 bytes while VARCHAR2 max is 4000 bytes. VARCHAR is included only for backward compatibility and should not be used for new code. Both types work in SQL and PL/SQL, and both store variable-length character data.

Deferred constraints in Oracle defer checking until the transaction is committed. This allows temporary violations during intermediate transaction steps, which are resolved before the changes are permanently saved.

The CASCADE CONSTRAINTS clause in a DROP TABLE statement automatically drops any referential integrity constraints (foreign keys in child tables) that refer to primary or unique keys in the table being dropped.

Selecting a literal constant like 8 from a table evaluates that constant for every row matching the query. Since the employee table contains 20 records, the number 8 is returned 20 times.

In Oracle, a UNIQUE constraint allows multiple NULL values. Oracle treats NULL as incomparable (NULL != NULL), so each row with NULL in the unique column is considered distinct. This is standard SQL behavior.

The REUSE STORAGE clause in TRUNCATE specifies that the space freed by deleting rows remains allocated to the table (kept in its tablespace). The default DROP STORAGE would return that space to the tablespace for other tables to use. TRUNCATE cannot be rolled back.

In Oracle SQL, rowid (the unique physical address of a row) and rownum (the sequential order number of a retrieved row) are valid built-in pseudocolumns. row_num, sl_no, and sid are not standard pseudocolumns.