Database and SQL

DISTINCT keyword removes duplicate rows from query results, returning only unique values. It applies to the entire SELECT list, not just a single column.

Subqueries are classified by how many rows they return: single-row subqueries return one row (use =, >, <), multiple-row subqueries return multiple rows (use IN, ANY, ALL). Option D correctly identifies both types.

Single-row subqueries return exactly one row (one column value). They use single-row comparison operators like =, >, <, >=, <=. If they return zero rows, the comparison fails.

The ALL operator compares a scalar value to every value produced by a subquery, requiring the condition to hold for each row. IN checks membership, ANY (or SOME) checks against at least one row, and OF is not an SQL operator, making ALL the right choice.

DML (Data Manipulation Language) statements are used to manipulate data in tables. INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows. All three operations are DML commands.

SQL stands for Structured Query Language. It's the standard language for relational database management systems used to query, manipulate, and define data.

The WHERE clause is used to filter rows based on specific conditions. DISTINCT removes duplicates, and projection refers to selecting specific columns (not limiting rows).

The subquery uses GROUP BY Rollnum, which returns multiple MIN(stud_marks) values (one per unique rollnum). When compared with a single value using = in the WHERE clause, this causes the error 'single-row subquery returns more than one row' because the main query expects exactly one value to compare against.

The SQL statement will fail because string literals in SQL must be enclosed in single quotes, not double quotes. The correct syntax would be: ename = 'Scott'. Double quotes are for identifiers, not string values.

SQL SELECT statements have three main capabilities: Projection (selecting specific columns), Selection (filtering rows with WHERE), and Join (combining data from multiple tables). All of these are fundamental SELECT operations.

To retrieve every column from a table, the standard SQL syntax uses the asterisk wildcard: SELECT * FROM EMP;. The phrase “Select all columns from EMP;” is not valid SQL, and SELECT DISTINCT applies to rows, not columns. Therefore only the asterisk option is correct.

Identity columns in Teradata are independent of the Primary Index - they can be defined on non-PI columns. They are also allowed to be part of the PI, contradicting option B. Teradata enforces a strict limit of one identity column per table to maintain data integrity and avoid ambiguity in auto-generation.

A Cover Query is optimized to retrieve all required columns directly from a Join Index without accessing the base table. Join Indexes pre-join and store frequently queried column combinations, allowing the optimizer to satisfy queries entirely from the index structure. This eliminates expensive joins and dramatically improves performance.

Join Indexes store pre-joined data from multiple tables, naturally resulting in repeating values as the join relationship creates duplicates. Teradata automatically maintains Join Indexes when underlying base table rows change, ensuring the pre-joined data stays synchronized without manual intervention.