SQL Functions and Joins

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.

When restarting an MLOAD script after a failure, the Log Table (Logtable) is essential because it contains the restart information and metadata needed to resume the job from where it left off. The log table tracks the progress of the MLOAD operation and stores critical state information. Error tables, UV tables, and work tables support the operation but are not the primary source of restart data.

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.

SQL allows ordering by column aliases defined in the SELECT list, because the alias is resolved after the SELECT clause but before ORDER BY is processed. Hence sorting by an alias is possible, making the claim that it isn’t possible false.

SQL functions are categorized into single-row functions (operate on one row at a time, like UPPER, SUBSTR) and multiple-row functions (operate on groups of rows, like SUM, COUNT, AVG). This classification determines how they're used in queries.

Single‑row functions, such as UPPER or SYSDATE, operate on each row individually and return exactly one value per row. They cannot produce multiple results from a single input row; that behavior belongs to set‑returning functions, making the statement false.

The ROUND function in SQL rounds a number to the specified decimal places. ROUND(1111.93412, 3) rounds to 3 decimal places, giving 1111.934. Option A (1112) would require 0 decimal places, option B (1111.93) would be 2 decimal places, and option C shows the original number unchanged.

TRUNC(99.57,1) cuts the number to one decimal place without rounding, yielding 99.5. ROUND(99.57,1) rounds to the nearest tenth, producing 99.6. The selected option matches these results, while the other choices misrepresent one or both functions.

The MOD function returns the remainder when one number is divided by another. For example, MOD(10, 3) returns 1 because 10 divided by 3 equals 3 with remainder 1. Option A describes division with quotient (DIV function), option B describes multiplication, and option D is incorrect as MOD is a standard SQL function.

NULL represents an unknown or missing value, which is fundamentally different from zero (a numeric value) or a blank space (a character value). Any arithmetic operation with NULL returns NULL, and NULL is not equal to anything, including itself. This is a core SQL concept.

An equi-join is a join based on equality between columns, typically joining on the same column name from different tables. Option B shows Table1.column1 = Table2.column1, which represents an equi-join condition where the same column from both tables is being compared for equality.