Database and SQL

Permanent objects like permanent tables and stored procedures require permanent space allocation. Without permanent space, a user cannot create or store these durable database objects. Views and macros don't require permanent space in the same way.

Multiset tables allow duplicate rows by definition. This is in contrast to set tables, which automatically eliminate duplicates. Volatile tables and global temporary tables can be defined as either set or multiset - it's the Multiset table type that specifically permits duplicates.

When you define a PRIMARY KEY but no explicit PRIMARY INDEX, Teradata automatically uses the PRIMARY KEY columns as the PRIMARY INDEX. This is a default behavior - the primary index ensures unique row distribution. It won't default to the first unique column or any other heuristic.

Permanent objects like permanent tables and stored procedures require permanent space allocation. Without permanent space, a user cannot create or store these durable database objects. Views and macros don't require permanent space allocation in the same way.

When using aggregate functions like COUNT, SUM, or AVG in the SELECT clause, non-aggregated columns must be included in a GROUP BY clause. This groups rows by those columns before aggregation. WHERE filters rows before grouping, while ORDER BY sorts results after aggregation.

MSUM (Moving Sum) is a Teradata-specific OLAP/window function used to calculate moving aggregations. Standard aggregates like SUM and AVG are basic SQL aggregation functions rather than dedicated moving or cumulative OLAP functions, making MSUM the correct choice.

Volatile tables are temporary tables that exist only for the duration of a session or transaction. Since they don't persist data and their contents can be deleted at any time, the database cannot reliably collect or maintain statistics on them. Permanent tables, set tables, and multiset tables all persist data and can have statistics collected.

When special characters are not defined in transaction RSKC in SAP BW, these characters can only be loaded into text fields (not attributes or other field types), and BW won't be able to generate SIDs for these characters because all fields where SIDs are generated cannot be loaded. This restriction prevents SID generation for undefined special characters in key fields.

A change run does NOT update the 'E' table of aggregates during master data alignment. The change run updates the 'M' (active) table, not the 'E' (historic/archive) table. The 'E' table contains historical data and is not affected by change run alignments.

In SAP BW, special characters in names or descriptions must be explicitly permitted using transaction RSKC (Special Characters Permitted). This transaction defines which special characters are allowed in the system. Option B correctly identifies this transaction code.

A self-join occurs when a table is joined to itself. This is typically done to compare rows within the same table, such as finding employees who report to each other (manager-subordinate relationships) or identifying duplicate records. The table appears in the FROM clause twice, usually with different aliases to distinguish the instances.

SQL's ORDER BY clause uses ASC (ascending) as the default sort direction when no explicit ASC or DESC keyword is specified. This means values are arranged from lowest to highest (A-Z for text, 0-9 for numbers, earliest to latest for dates). To sort in descending order, you must explicitly specify DESC.

The correct TRUNCATE syntax is 'TRUNCATE TABLE table_name.' This command removes all rows from a table quickly by deallocating data pages rather than deleting rows individually. Options A, B, and D all use incorrect syntax - TRUNCATE doesn't use asterisks, and 'TABLE' keyword placement matters. TRUNCATE is a DDL operation, not DML like DELETE.

Option A correctly uses SELECT with WHERE clause and >= operator to filter rows where ContestDate is on or after May 25, 2006. Option B incorrectly uses GROUPBY (should be GROUP BY and is for aggregation, not filtering). Option C uses < instead of >=. Option D uses HAVING, which filters after GROUP BY aggregation, not for simple row filtering.

INNER JOIN returns only rows where both tables have matching values in the joined field(s). Non-matching rows from either table are excluded from results. Option A is incorrect because INNER JOIN doesn't return 'all matching rows' - it returns rows with matches in BOTH tables. Option B describes LEFT JOIN. Option C describes FULL OUTER JOIN. Option D correctly defines INNER JOIN.