Teradata and SQL Database Quiz

In PL/SQL, the maximum size of a NVARCHAR2 variable is 32,767 bytes, whereas the maximum size of a NVARCHAR2 column in an Oracle database table is 4,000 bytes.

CHECK constraints only evaluate to TRUE or FALSE, but they are not evaluated when the value is NULL. NULL values bypass CHECK constraint validation entirely, so they never violate the constraint.

ROLLUP generates subtotals (super-aggregates) at each hierarchy level in a GROUP BY operation. COUNT, AVG, and SUM are standard aggregate functions that produce single values per group, not hierarchical results.

To answer this question, the user needs to have knowledge of database management systems and the concept of Foreign Keys.

When a Foreign Key references a Primary Key in another table, there is a possibility that the referenced record(s) may be deleted. In such cases, four actions can be taken: SET NULL, CASCADE, DELETE, or RESTRICT.

• ON DELETE SET NULL: This option sets the Foreign Key value to NULL when the referenced record(s) are deleted. This may lead to data integrity issues and is not ideal in most cases.

• ON DELETE CASCADE: This option deletes all the dependent rows in the child table when the referenced rows in the parent table are deleted. This is usually the preferred option when the child rows are no longer relevant without the parent rows.

• ON DELETE DELETE: This syntax does not exist, and hence, it is incorrect.

• ON DELETE RESTRICT: This option prevents the deletion of the referenced rows in the parent table when there are dependent rows in the child table. This is useful when the data in the child table is still relevant and associated with the data in the parent table.

Therefore, the correct answer is:

The Answer is: B. ON DELETE CASCADE

UNION ALL is faster than UNION because it doesn't eliminate duplicates. UNION performs a sort to identify and remove duplicate rows, which adds overhead. Use UNION ALL when you don't need deduplication.

WM_CONCAT is an Oracle-specific string aggregation function that concatenates values from multiple rows into a single column. SUBSTR extracts substrings, while CON_STR and CON_CAT are not valid SQL functions.

The TRANSLATE function in SQL replaces a sequence of individual characters in a string with another corresponding set of individual characters, mapping them one-to-one.

HAVING filters aggregate functions after grouping. WHERE filters rows before aggregation and cannot use aggregate functions. WHEN is used in CASE expressions, and ANY is a comparison operator.

In SQL, the REPLACE function replaces all occurrences of a specified substring (word or sequence of characters) with another substring, whereas TRANSLATE performs character-by-character substitution.

The IN operator evaluates the subquery once to get a list of values, whereas EXISTS evaluates the subquery for each row processed by the outer query to check for the existence of a match.

Teradata uses the Primary Index (PI) to distribute data across AMPs. The PI value is hashed to determine which AMP will store each row. The Primary Key is a logical concept for uniqueness, while the PI is the physical distribution mechanism.

The Primary Index is defined within the CREATE TABLE statement (not after). Its core purpose is to distribute rows to AMPs via the hashing algorithm. It is not used to find the Primary Key - PK is for uniqueness and relationships.

In Teradata, the Primary Index (PI) is defined during the table CREATE statement. It is used by the hashing algorithm to assign and distribute table rows across Access Module Processors (AMPs).

The COUNT(*) aggregate function counts the number of rows in a table. If a table has been created but contains no rows, the query returns 0 rather than an error or 'No data Found'.

In Oracle SQL, a single quote is escaped by doubling it. The expression '''||'TCS'||''' concatenates an escaped single quote, the string 'TCS', and another escaped single quote, resulting in 'TCS'.

The date format string 'yyyymmdd' is incompatible with the input '2009-may-25'. Month abbreviations like 'may' require format codes like 'mon' or 'month', not 'mm'. The mismatch causes Oracle to throw an error rather than attempt conversion.

To output literal single quotes around TCS, use two consecutive single quotes: '''TCS'''. In Oracle SQL, a single quote within a string is escaped by doubling it. The outer quotes define the string, inner pairs become literal quotes in output.

The Parsing Engine (PE) handles session management, SQL parsing, and optimization. It's the entry point for client connections. AMPs manage data storage, BYNET handles communication, and Clique is a high-availability grouping of nodes.

Teradata uses a Shared Nothing architecture - each node has its own memory and disk. No resources are shared between nodes. This eliminates contention and enables linear scalability. Shared Everything (like SMP) shares memory and disk among processors.