Database and SQL

In Teradata, the SET keyword in CREATE TABLE/CREATE SET eliminates duplicate rows, enforcing uniqueness. MULTISET allows duplicate rows. The question claims the opposite, so the statement is false. SET = unique rows only, MULTISET = duplicates allowed.

Standard join types in database systems include Inner Join, Outer Join, and Self Join. Explicit refers to join syntax style rather than a specific logical join type.

The SQL LIKE operator is used for pattern matching in string columns. The percent sign (%) is a wildcard that matches zero or more characters. To find values starting with 'a', use 'a%' (matches 'a' followed by anything). Option A ('%a') finds values ending with 'a', option B uses equality instead of pattern matching, and option D has incorrect wildcard placement.

The TRUNCATE command is a Data Definition Language operation that quickly removes all rows from a table without generating individual row rollback information, making it much faster than DELETE.

A FULL OUTER JOIN returns all records when there is a match in either the left or the right table records, effectively combining all matched and unmatched rows from both tables.

Views CAN be created as joins across multiple tables - this is a common use case for simplifying complex queries. Views can also include ORDER BY in some databases (with limitations), and GROUP BY is standard. Option A is correct. Option B directly contradicts A. Options C and D are false - views can include ORDER BY (with TOP/LIMIT in some DBs) and definitely support GROUP BY.

When a base table is dropped, any dependent view becomes invalid because its underlying schema no longer exists. The view object isn't automatically dropped (it remains in metadata with an INVALID status). Attempts to query the invalid view will fail. The view must be recreated or the table restored to make it functional again.

The UPDATE statement is used to modify existing records in a table, making it the correct command to update a married employee's last name. In contrast, ALTER modifies the table's structure (schema), INSERT adds new records, and CREATE is used to instantiate new database objects like tables.

The DISTINCT keyword in SQL eliminates duplicate rows from query results, returning only unique values. Option A (DIFFERENT) is not valid SQL syntax. UNIQUE is a constraint, not a query keyword for filtering duplicates. ALL returns all rows including duplicates (default behavior).

COUNT(*) is the standard SQL aggregate function that returns the total number of rows in a table, including NULL values. Option A uses non-existent COLUMNS function. Option B has empty COUNT() syntax - needs an argument like * or column name. Option D repeats the COLUMNS error.

NVL (or COALESCE in standard SQL) substitutes a replacement value when NULL is encountered. NVL(expr, replacement) returns 'replacement' if 'expr' is NULL, otherwise returns 'expr'. COUNT counts rows, CASE is conditional logic, and MAX finds maximum values - none of these handle NULL substitution.

ORDER BY is the standard SQL clause used to sort query results. The 'ORDER' keyword alone is incomplete, 'SORT' and 'SORT BY' are not valid SQL syntax for sorting result sets.

SQLCODE -545 is the DB2-specific error code for invalid date, time, or timestamp values. This error occurs when the provided value doesn't conform to the expected format or represents an impossible date (like February 30).

When tables share the same distribution key and are joined on that key, matching rows are already on the same SPU due to hash distribution. This collocated join eliminates the need for data redistribution across SPUs, making the join highly efficient. It's a key performance optimization in MPP databases like Netezza that avoids expensive data movement.