Database Fundamentals and SQL Programming

In Teradata's hierarchical space management, when a child database is created with a spool allocation, it does not reduce the parent's spool space. The parent (Database A) retains its full 5GB spool allocation. The child's 3GB is a separate allocation, not a subtraction from the parent.

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.

Indexes are database structures created specifically to accelerate data retrieval operations. They provide fast lookup paths to rows without scanning entire tables. Option A is incorrect - indexes are commonly used. Option B is wrong - indexes add overhead to storage. Option C is false - indexes frequently dramatically improve query performance.

A self-join refers to a standard inner or outer join operation where a table is joined with itself. This is typically used to query hierarchical data stored within a single table by using aliases.

To solve this question, the user needs to know the syntax of SQL SELECT statements, how to select all rows from a table, how to specify column names, and how to use the WHERE clause to filter rows based on a certain condition.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT * FROM Contest WHERE ContestDate >= '05/25/2006': This option is correct. This statement selects all columns and rows from the Contest table where the ContestDate column has a value greater than or equal to May 25, 2006. The date is specified in the format MM/DD/YYYY, which is a valid format for SQL.

B. SELECT * FROM Contest GROUPBY ContestDate >= '05/25/2006': This option is incorrect because it contains a syntax error. The GROUP BY clause is used to group rows based on a certain criteria, not to filter them. Also, the condition is not valid in the GROUP BY clause.

C. SELECT * FROM Contest WHERE ContestDate < '05/25/2006': This option is incorrect because it selects all rows from the Contest table where the ContestDate column has a value less than May 25, 2006. The question asks for values greater than or equal to that date.

D. SELECT * FROM Contest HAVING ContestDate >= '05/25/2006': This option is incorrect because the HAVING clause is used to filter rows based on a condition that involves an aggregate function. It cannot be used to filter rows based on a simple comparison like the one specified in this question.

The Answer is: A

An INNER JOIN selects and returns only the rows from both tables that satisfy the join condition (matching values in the specified fields). Rows that do not have matching values in both tables are excluded.

The SQL ORDER BY clause defaults to sorting records in ascending order (ASC) if no direction keyword (ASC or DESC) is explicitly specified after the column name.

The correct TRUNCATE syntax in SQL is 'TRUNCATE TABLE table_name,' which removes all rows from a table while preserving its structure. TRUNCATE is a DDL operation that is faster than DELETE because it doesn't log individual row deletions. Options A, B, and D incorrectly mix TRUNCATE with other keywords or place TABLE in the wrong position.

ACID is the standard acronym for the key properties of database transactions: Atomicity (all-or-nothing execution), Consistency (maintaining valid database states), Isolation (independent transactions), and Durability (permanent persistence of committed data).

The NVL2(expr1, expr2, expr3) function returns expr2 if expr1 is not null, and expr3 if expr1 is null. Since the first argument is explicitly NULL, the function returns the third argument, which is also NULL.

In SQL and PL/SQL, any comparison involving NULL values (including NULL = NULL) returns NULL (or UNKNOWN), not TRUE or FALSE. This is because NULL represents an unknown value, and you cannot determine whether two unknown values are equal. NULL requires special handling with IS NULL or IS NOT NULL operators.

The standard implicit cursor attributes are %FOUND, %NOTFOUND, and %ROWCOUNT (along with %ISOPEN). In contrast, %rowtype is a data type declaration attribute rather than a cursor attribute, and %toomanyrows is a predefined exception.

To solve this question, the user needs to know what happens when a variable is declared in PL/SQL without an initial value.

The answer is:

B. NULL

If a variable is declared in PL/SQL without an initial value, its value will be NULL when it is first used in the executable section of the PL/SQL block. This means that the variable does not have a value assigned to it yet. If the variable is used in an expression or operation, it will result in a NULL value, which can cause unexpected behavior in your code.

Option A is incorrect because the variable will not have a value of 0 by default.

Option C is incorrect because declaring a variable without an initial value is legal and will not result in a compilation error.

Option D is incorrect because an exception will not be raised when a variable is declared without an initial value. It will simply have a value of NULL.

The Exception section of a PL/SQL block contains error trapping and handling logic. When an error occurs in the Executable section, control transfers to the Exception section where you can catch specific exceptions (like NO_DATA_FOUND) or use WHEN OTHERS for a catch-all handler. The Declarative section declares variables, and the Header section contains the block name and parameters.