Database and SQL

The IN operator lets you test if a value matches any value in a list. For example, WHERE city IN ('Mumbai', 'Delhi', 'Chennai') returns rows where city equals any of those three values. BETWEEN checks for a range, not a list of discrete values.

UNION combines results from multiple SELECT statements into one result set. SQL requires all SELECTs in a UNION to have identical column count, compatible data types in each position, and the same column order. These structural rules ensure rows can be stacked vertically.

Scalar functions operate on single values and return single values. MID() extracts a substring from a text value, and NOW() returns the current timestamp - both are scalar. MAX() and SUM() are aggregate functions that operate on multiple rows and return summary values.

In SQL, aggregate functions like COUNT, SUM, AVG cannot be used directly in the WHERE clause because WHERE filters rows before aggregation. Instead, use HAVING after GROUP BY to filter based on aggregate results. This statement accurately describes that limitation.

Indexes are ignored if the leading column is missing from search arguments, or if mismatched data types force implicit conversions. The optimizer may also bypass an index for a table scan if selectivity is low. Index names do not affect optimizer decisions.

Microsoft recommends a clustered index for almost every SQL Server table because it physically orders the data, greatly improving retrieval speeds for range queries and joins. Without a clustered index, the table exists as an unordered heap, which can lead to severe data fragmentation and slower lookups.

VARCHAR(50) is more efficient than CHAR(50) for state names because VARCHAR only uses storage space equal to the actual length of the data, while CHAR always allocates the full 50 bytes regardless of content length. For variable-length data like state names, VARCHAR reduces storage and improves cache efficiency. CHAR(10) is insufficient (some state names exceed 10 characters). NVARCHAR adds Unicode overhead unnecessarily for plain text. CHAR(100) wastes even more space.

Numeric columns generally make good index candidates because they are typically compact, fixed-width, and allow efficient comparisons. Numeric indexes enable fast range scans and sorting operations. However, this is not universally true - the selectivity of the column and query patterns matter more. Low-cardinality numeric columns like boolean flags may not benefit from indexing. But as a general principle, numeric columns are well-suited for indexes.

In a parent-child one-to-many relationship where the child column must always have a value, the child column (Table_B.Column_1) is a foreign key referencing the parent, must NOT allow NULLs, and is NOT unique (multiple child rows can reference same parent). Option D states 'Table_B.Column_1 should be unique' which is FALSE for a one-to-many relationship. Options A, B, and C all correctly describe the relationship: foreign key constraint, non-nullable child column, and proper indexing strategy.

Because the query frequently needs the most recent transaction per account, a clustered index on (Account_ID, Trans_Date) stores rows in that order, enabling fast retrieval of the latest date for a given account without additional sorting. A single‑column index or a non‑clustered index would be less efficient.

UNION ALL combines results from multiple tables without duplicate checking. Since the tables have mutually exclusive records, no duplicates exist, so the overhead of duplicate elimination in UNION is unnecessary. UNION is slower because it must sort and compare rows to remove duplicates. Separate SQL statements with front-end merging adds application complexity. A temporary table introduces unnecessary I/O and complexity. UNION ALL is the most efficient approach for mutually exclusive data.

Query hints should only be used as a last resort when the optimizer fails to choose an efficient plan. Hints like NOLOCK can return uncommitted data (dirty reads). UPDATE locks can cause unintended blocking. Once hints are added, they persist even if data distribution changes and the optimizer would have chosen a better plan. Best practice is to trust the query optimizer unless there's a proven performance issue. The statement is false.

In Oracle and most relational databases, DDL statements like CREATE TABLE commit automatically and implicitly. Because of this implicit commit, a subsequent ROLLBACK command has no effect on the table's existence. Therefore, the DESC command will successfully describe the table structure.

GROUP BY is used with aggregate functions like COUNT, SUM, AVG, MAX, MIN to perform calculations on groups of rows. The columns in GROUP BY determine how rows are grouped, and aggregates compute summary values for each group. Non-aggregate columns in the SELECT must appear in the GROUP BY clause itself.

The UPDATE statement syntax requires specifying the table name directly after UPDATE, not using the TABLE keyword. Option B correctly shows 'UPDATE TABLE_NAME SET column_name=value WHERE condition'. Option A incorrectly includes 'TABLE' as a keyword before the table name.