Database and SQL

A surrogate key is an artificial identifier used as a primary key when no natural primary key exists or when the natural key is too complex. It's typically a system-generated unique number for each row, serving as a substitute for natural keys.

Oracle supports ALTER TABLE ... DROP COLUMN to remove columns even when the table contains data. The column and all its data are permanently removed.

Oracle databases have a hard limit of 1000 columns per table or view. This is defined in Oracle's architecture and documentation.

In Oracle, when sorting in ascending order (ASC), NULL values are treated as greater than all non-NULL values and appear at the end of the result set.

The correct SQL*Plus command is 'SET TIMING ON' before executing the query. This displays elapsed time for each SQL statement executed.

In Oracle, NULL is a special value indicating absence of data and is commonly represented as CHAR(0) in type system discussions. NULL is not Long, Int(0), or Binary.

Oracle does not allow DML operations (INSERT, UPDATE, DELETE) inside a function that is called from a SELECT statement. This restriction maintains database integrity and prevents side effects during query execution.

The anonymous PL/SQL block shown is invalid because it lacks an executable section. Even though it has the DECLARE and BEGIN/END structure, there are no statements between BEGIN and END. Oracle requires at least one executable statement (often NULL;) in the execution block. The empty execution section makes this a syntax error.

In an outer join operation, columns from the non-preserved side (the table that may not have matching rows) return NULL for unmatched rows. Matched columns from both sides return their actual values, not NULL. Option A is incorrect because matched columns don't return NULL - only the unmatched columns from the non-preserved table do.

The Oracle data dictionary view USER_SOURCE stores the source code for stored PL/SQL objects including Functions, Package bodies, Procedures, and Triggers. Cursors are not stored objects - they are declared within PL/SQL blocks and are not stored in USER_SOURCE. The question tests understanding of which code objects have their source persisted in the data dictionary.

A Prepared Statement in JDBC is a precompiled SQL statement. Precompilation improves execution performance for repeated queries and provides strong protection against SQL injection.

In DL/I, INSERT operations can be performed in different modes. LOAD mode is used for bulk initial data loading, while UPDATE mode allows inserting records with update capabilities. Both LOAD MODE and UPDATE MODE are valid modes for INSERT operations in DL/I.

In Oracle SQL, dropping a parent table referenced by foreign keys requires the CASCADE CONSTRAINTS clause to automatically drop the referencing foreign key constraints on child tables.