Database and SQL

You can call a stored procedure from an anonymous PL/SQL block. Anonymous blocks can invoke procedures using the EXECUTE statement or direct procedure calls. This is a standard way to test or execute procedures without creating a named stored block. The procedure executes within the block's scope.

DDL (Data Definition Language) contains the SQL commands that define database structures like CREATE TABLE. Generating DDL allows you to duplicate table structure along with constraints and indexes. Copy/Alter/Export table operate on data, not structure.

Statement i is false because unique indexes can be created on any column or combination of columns that require uniqueness, not exclusively on primary keys. Statement ii is true because indexes allow the database engine to locate rows quickly when predicates are applied to indexed columns.

A cursor must always be opened before fetching rows. In standard embedded SQL, each execution of the FETCH statement retrieves exactly one row into host variables. Both statements are true, making the selected option correct.

By definition, a primary key uniquely identifies each row in a table and therefore cannot contain NULL values. NULL would violate the uniqueness constraint since NULL = NULL is not true in SQL. Option A is false because primary key values must be unique. Option B is incorrect because LONG is not a standard SQL data type (though CLOB/BLOB exist).

A view is a virtual table that can restrict access to specific columns. By creating a view that excludes the phone_number column and granting users access only to the view (not the base table), you effectively limit their read access. Referential constraints enforce relationships between tables, revoking access from a column doesn't work as columns are accessed through the table, and check constraints validate data values.

ON DELETE CASCADE automatically deletes all matching rows in the child table (T2) when the referenced row in the parent table (T1) is deleted. RESTRICT prevents deletion if child rows exist, SET NULL sets the foreign key to NULL, and CHILD and MATCH are not standard ON DELETE options.

The WITH HOLD option in DB2 (and other RDBMS) keeps a cursor open across COMMIT operations. Without WITH HOLD, the cursor would close automatically when a COMMIT is issued. It doesn't force closure, lock the cursor, or wait for all rows to be fetched.

SQL supports both EQUI-JOIN (joining using equality operator) and NON-EQUI-JOIN (joining using operators other than =, such as <, >, BETWEEN, etc.). An EQUI-JOIN is a join condition using =, while NON-EQUI-JOIN uses any other comparison operator.

When joining two tables without a WHERE clause specifying the join condition, you get a Cartesian product (cross join). Every row in T1 pairs with every row in T2, so 100 rows x 50 rows = 5000 rows. This is a common SQL mistake that produces much more data than intended.

The ALTER TABLE command is used to modify the structure of an existing table (like changing columns), not to create (CREATE TABLE) or delete (DROP TABLE) a table. Therefore, both A and C are false statements about ALTER TABLE, making this option correct.

Referential integrity maintains relationships between tables via foreign keys and primary keys. Unique constraints ensure column uniqueness within a single table but don't enforce relationships between tables. Cascading actions (like CASCADE delete) and foreign keys directly maintain referential integrity, while primary keys define the parent side of the relationship.

Cursors are SQL memory structures that point to a result set and allow the application to process multiple rows one at a time. Host variables, indicator variables, and the SQLCA (SQL Communication Area) are used for data exchange, null handling, and error checking, not row iteration.

The DATEADD function in SQL Server adds a specified interval to a date. To get the previous year's date, we need to subtract 1 year using DATEADD(year, -1, GETDATE()). Option A adds 1 year (future), while Option C correctly subtracts 1 year to get the previous year's date. DATEDIFF calculates the difference between dates, it doesn't add or subtract intervals.

CONVERT function in SQL Server can convert datetime to varchar with optional style codes. CONVERT(varchar(10), sys_date, 101) converts to mm/dd/yyyy format (style 101). CONVERT(varchar(11), sys_date) converts without style (default format). CAST(sys_date as varchar(11)) is equivalent to CONVERT without style. Option C is incomplete - it needs a style parameter or length specification to be syntactically correct for this use case.