Database and SQL

Subqueries are categorized into two main types based on the number of rows they return: single-row subqueries (which return exactly one row and use single-row operators like =, >, <) and multiple-row subqueries (which return multiple rows and require operators like IN, ANY, or ALL).

A single-row subquery is designed to return exactly one row from the inner query. This type of subquery can be used with single-row comparison operators like =, >, <, etc. Options B and C are incorrect because they describe multi-row or no-row scenarios, while D is false as single-row subqueries are a valid SQL construct.

The subquery uses GROUPBY (should be GROUP BY) on Rollnum, which returns one minimum mark value per unique Rollnum - potentially multiple rows. The outer query uses the single-row comparison operator = with stud_marks, expecting exactly one value. When the subquery returns multiple rows (one per Rollnum group), this creates a mismatch and triggers the 'single-row subquery returns more than one row' error.

ALL is a SQL operator that compares a value to EVERY value returned by a subquery. For example, 'salary > ALL (SELECT salary FROM employees)' means the salary must be greater than every salary in the subquery results. IN checks membership in a set, ANY checks if the condition is true for at least one value, and OF is not a comparison operator.

DML (Data Manipulation Language) statements include INSERT (add new rows), UPDATE (modify existing rows), and DELETE (remove existing rows). Since all three operations are DML statements, 'All of the above' is correct. DML commands specifically manipulate data stored in database tables.

The correct SQL syntax for inserting a row is 'INSERT INTO tablename VALUES (...)' or 'INSERT INTO tablename (columns) VALUES (...)'. Option C shows the basic form with VALUES clause. Options A and B use incorrect syntax ('Insert to' and 'Insert into table' instead of 'INSERT INTO'). D is incorrect because not all forms shown are valid.

IGNORE DUPLICATE ROWS is ignored when a table has unique indexes because the unique index constraint still enforces uniqueness at the database level. The option only applies to tables without unique indexes.

Teradata MLOAD supports the DROP DDL statement, allowing users to drop tables as part of the MLOAD script. However, MLOAD does not support CREATE, ALTER, or RENAME DDL statements - these must be executed separately outside of MLOAD. MLOAD is optimized for high-volume data loading with limited DDL support, focusing on DELETE and INSERT operations along with DROP for target table management.

In SQL join conditions, table names are NOT mandatory when column names are unambiguous (unique across joined tables). However, it's best practice to qualify column names to avoid ambiguity and improve code clarity.

An outer join returns records from one table even when there's no matching record in the other table. This is useful for finding unmatched records (like customers without orders) or maintaining all records from a primary table.

The (+) symbol is the Oracle-specific outer join operator, traditionally used before ANSI standard JOIN syntax was adopted. It's placed on the side of the join condition that may be missing (NULL).

A self-join joins a table to itself, treating it as if it were two separate tables. This is useful for comparing rows within the same table, such as finding employee-manager relationships in an employees table.

To join N tables together, you need N-1 join conditions. For 10 tables, you need 9 join statements. This follows the principle that each new table requires one additional join to link it to the existing structure.