Database and SQL

Teradata requires exactly one Primary Index per table. This PI (which can be single-column or composite) determines how rows are distributed across AMPs. A table cannot have multiple PIs or zero PIs - the system uses this single PI for all data distribution and retrieval operations.

A Chasm Trap occurs when two many-to-one joins converge on a single table, causing the query to return more rows than expected (a Cartesian multiplication effect). This happens because aggregations get lost or duplicated at the convergence point. A Fan Trap involves one-to-many joins diverging, while a Loop is a circular join path.

A Shortcut Join creates a direct alternative path between two tables, bypassing intermediate tables in the join path. This improves query performance by reducing the number of joins the database engine must execute. The other options are standard SQL join types that don't specifically provide optimized alternative routes.

Views cannot be specified in trigger statements because triggers require base table events to fire properly. A view is a derived object that doesn't directly support the event-based nature of triggers. Triggers must be associated with actual tables that generate the events (INSERT, UPDATE, DELETE).

PL/SQL supports function overloading, which allows multiple functions to share the same name within the same block or package scope. The functions must have different parameter signatures (different number, order, or data types of parameters) so the compiler can distinguish which version to invoke based on the arguments provided.

A database trigger definition can contain only a single SQL or PL/SQL statement. While this single statement can be a compound statement (enclosed within a BEGIN and END block), the trigger structure itself syntactically limits the definition to exactly one top-level statement.

Database management systems like Oracle support nesting of subqueries in the WHERE clause up to a maximum depth of 255 levels. Exceeding this limit results in a database compilation or execution error, making 255 the correct limit, while other options are incorrect.

In database management systems like Oracle, there are twelve basic trigger types defined by the combination of timing (before, after, instead of) and action (insert, update, delete) at statement and row levels. Thus, the maximum number of triggers applicable to a single table is twelve.

When using aggregate functions like SUM, COUNT, AVG in SQL's SELECT clause, non-aggregated columns must be included in a GROUP BY clause. This groups rows before aggregation. WHERE filters rows before grouping, ORDER BY sorts results after aggregation. The 'Aggregate Function' option is not a clause.

MSUM is an OLAP (Online Analytical Processing) function used in data warehousing for calculating moving sums across dimensions. While SUM and AVG are standard SQL aggregate functions, they are not specifically OLAP functions. OLAP functions operate across data cubes and dimensions for analytical processing, not simple row aggregation.

In Teradata SQL, a Multiset table allows duplicate rows to be stored. Volatile tables are temporary session-specific tables. Global Temporary tables are also temporary. Permanent tables (default) typically enforce uniqueness constraints. The Multiset table type specifically permits duplicate rows, which is useful for certain data scenarios like logs or staging data.

In Teradata, if a Primary Key is defined but no Primary Index is specified during table creation, the system automatically uses the Primary Key as the Primary Index. The Primary Index determines data distribution across AMPs (Access Module Processors). Options A, B, and D are incorrect - it doesn't default to secondary index, first unique column, or first non-unique column.

Volatile tables are temporary tables that exist only for the duration of a session. Since they are automatically dropped at session end and used for transient data, Teradata does not collect statistics on them. Permanent tables, multiset tables, and set tables all support statistics collection.