Database and SQL

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.

Oracle supports two valid syntaxes for renaming tables: (1) RENAME old_name TO new_name, and (2) ALTER TABLE old_name RENAME TO new_name. The 'AS' keyword is invalid in both syntaxes. ALTER without TABLE is also invalid.

When you create a Primary Key constraint, the database automatically creates a unique index to enforce the uniqueness requirement. Similarly, a Unique constraint also creates a unique index to ensure no duplicate values. Check constraints only validate data integrity rules, Not Null enforces presence of values, and Foreign Key creates relationships but not unique indexes.

PreparedStatement is the JDBC Statement type designed for executing parameterized queries with placeholders (?). It precompiles SQL statements, allowing efficient reuse with different parameter values. Statement (basic) doesn't support parameters, and CallableStatement is for stored procedures. ParameterizedStatement is not a valid JDBC interface.

Oracle LONG columns are used to store variable-length character strings containing up to 2 gigabytes of text. They share many characteristics with VARCHAR2 columns, although they have many more restrictions on usage.

Oracle supports several datetime datatypes, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. All four options represent valid built-in datetime types in Oracle SQL.

Database sequences generate unique numeric values often used for primary keys and unique keys to provide automatically incrementing identifiers. Sequences are not typically used for sales figures (which are data values, not identifiers) or foreign keys (which reference existing primary keys). The generated values ensure uniqueness across rows.