Database and SQL

In Oracle, readers never block writers and writers never block readers due to multiversion read consistency. When rows are locked by DML operations, other sessions can still query those rows using the before-image from undo segments. This prevents SELECT statements from waiting for row locks to be released.

Oracle allows reference to SYSDATE, NEXTVAL, and CURRVAL in constraint definitions. These pseudocolumns and sequence functions can be used in DEFAULT, CHECK constraints and other constraint definitions. For example, a DEFAULT SYSDATE constraint or a check constraint using CURRVAL is valid.

When you define a UNIQUE KEY or PRIMARY KEY constraint, Oracle automatically creates a unique index to enforce uniqueness. This index may be explicitly named or system-generated. Without a unique index, the database could not efficiently prevent duplicate values.

Yes, subqueries are valid in INSERT statements. You can use INSERT INTO table SELECT ... to populate a table from query results. Both single-row and multi-row inserts support subqueries as the value source.

VARCHAR2 has different maximum sizes in SQL versus PL/SQL. In SQL (Oracle 12c+), VARCHAR2 max is 4000 bytes by default (up to 32767 with MAX_STRING_SIZE=EXTENDED). In PL/SQL, VARCHAR2 max is 32767 bytes. Option B reflects this 4000/32767 split.

The LENGTH() function counts the total characters including trailing spaces. VARCHAR2 stores trailing spaces as part of the value when explicitly added. 'TCSLTD' (6 chars) plus 7 spaces = 13 characters total. The length is 13, not 6.

When a table is dropped, all associated indexes including non-unique ones are automatically dropped. Oracle cannot maintain an index on a non-existent table. The indexes are cleaned up as part of the DROP TABLE operation.

EXISTS is the only operator listed that requires a correlated subquery. The EXISTS predicate checks whether the subquery returns any rows, and it typically needs to correlate with the outer query to be meaningful. IN can use uncorrelated subqueries, BETWEEN and LIKE are not subquery operators.

MAXVALUE is a valid sequence parameter. Other options are invalid for sequences: IDENTIFIED BY (for users), USING TEMPORARY TABLESPACE (for users), ON DELETE CASCADE (for foreign keys), and while CURRVAL and NEXTVAL are sequence pseudocolumns, they are used in SQL statements not in sequence definition syntax.

Option B is incorrect because 'table 2' contains a space, which is invalid syntax for a table name without quotes. In standard SQL, table names with spaces must be enclosed in quotes or brackets. Options A, C, and D show valid ALTER TABLE syntax in various database systems for renaming columns, adding columns, and enabling constraints respectively.

Views containing GROUP BY or DISTINCT clauses are not updateable in SQL because the database cannot determine which rows to update uniquely. The question has significant issues: option B says 'yes' but should say 'no', option D has a typo ('yesy'), and the phrasing 'no on simple views with group by and distinct clause' is awkward. Despite quality issues, C is the intended correct answer as it correctly identifies the non-updateable case.

An Equi-join is based on equality between columns of two tables. A Natural Join is a special case of equi-join that automatically joins tables with matching column names. An Inner Join is the general category that includes equi-joins. Therefore, an equi-join can be both a Natural Join (when matching columns exist) and an Inner Join.

Bitmap indexes are ideal for columns with low cardinality (few distinct values) like Gender which has only two values (M/F). Unlike B-Tree indexes which store each row entry separately, bitmap indexes use a single bitmap per distinct value, making them very efficient for low-cardinality columns in data warehousing scenarios.