Oracle Database: Constraints, Data Types, and SQL Functions

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.

An INTERVAL literal in SQL represents a period of time, such as '3 days' or '2 hours'. This is the standard interpretation across database systems.

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).

In SQL, an interval literal represents a specific span or period of time, such as years, months, days, hours, minutes, or seconds, distinguishing it from simple numerical values.

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 NUMBER(p,s), p=precision (total significant digits), s=scale (digits after decimal). For NUMBER(3,4), precision=3 allows 3 significant digits total, with 4 decimal places. Valid: 0.01 (1 significant digit). Invalid: 123.21 (5 sig digits), 0.001 (4 decimal places), 123.01 (5 sig digits).

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.