SQL and Database Fundamentals Quiz

ON UPDATE CASCADE is a referential integrity constraint that automatically updates dependent rows when a referenced primary key is updated. This ensures data consistency across related tables, preventing orphaned records and maintaining the integrity of relationships. Options A and C describe different database concepts (normalization and materialized views).

When a NULL value appears in a NOT IN subquery or list, the entire condition evaluates to UNKNOWN for all rows because comparing any value to NULL (except with IS NULL) returns UNKNOWN, which falsifies the AND condition. This causes the query to return zero rows, not any rows as the statement suggests.

Teradata provides multiple mechanisms to enable joins to occur on the same AMP: identical primary indexes naturally distribute matching rows to the same AMP; dynamic redistribution can rehash tables during query processing; and duplicating small tables across all AMPs eliminates data movement. All three are valid join optimization strategies.

The SHOW command in SQL displays the DDL (CREATE TABLE, etc.) statements used to create database objects. EXPLAIN shows the query execution plan rather than the object definition, while HAVING is used for filtering grouped results. HELP provides documentation and usage information.

SQL operators follow the standard precedence: parentheses override all other grouping, NOT is evaluated before AND, and AND is evaluated before OR. This order ensures that expressions like 'A OR B AND C' are evaluated as 'A OR (B AND C)' rather than '(A OR B) AND C'.

While GROUP BY is commonly used with aggregates and non-aggregates, it's not the only approach. Window functions can combine aggregates with non-aggregated data without GROUP BY. Additionally, some databases allow mixing aggregates and non-aggregates when non-aggregates are functionally dependent on GROUP BY columns.

A self-join requires treating a single table as if it were multiple tables using different aliases (e.g., employees e1 JOIN employees e2), which can be conceptualized as denormalization for the join operation. Unlike other join types that combine distinct tables, a self-join operates on one table by creating virtual copies through aliasing.

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.

SQL stands for Structured Query Language, which is the standard language for relational database management systems. It is used for querying, manipulating, and defining data in databases.

DDL (Data Definition Language) statements define database structures. ALTER is a DDL command used to modify table structure. SELECT, UPDATE, and DELETE are DML (Data Manipulation Language) commands that operate on data within existing structures.

SQL keywords are case-insensitive in most database systems. SELECT, Select, and select are all treated identically. However, string literals and identifier naming (depending on system) may be case-sensitive.

The statement asks which SQL statement deletes a TABLE (not data). DROP TABLE completely removes the table structure and data from the database. DELETE removes rows but keeps the table, TRUNCATE removes all rows but keeps the table structure. Only DROP removes the table itself.

AND requires both conditions to be true for a record to be returned (intersection). OR returns records if either condition is true (union). This is the standard behavior of these logical operators in SQL WHERE clauses.

The query uses LIKE '%la%' which matches any string containing 'la' anywhere (startswith, middle, or end). To match names starting with any character followed by 'la' (like 'Clara', 'Flavia'), the correct pattern would be '_la%' where underscore matches exactly one character. '%la%' matches 'Flamingo' (contains 'la'), not just patterns like '_la'.

The SELECT statement is the standard SQL command for retrieving data from a database. The syntax 'SELECT column_name FROM table_name' correctly specifies which column to retrieve from which table. EXTRACT is not a valid SQL keyword for column retrieval, and 'SELECT Employee.LastName' would only work if using qualified notation with a table alias, which is not shown in the options.

The asterisk () in SQL acts as a wildcard that represents all columns in a table. 'SELECT * FROM table_name' is the standard syntax to retrieve every column from the specified table. Options A and B use incorrect syntax ('.Employee' places the wildcard incorrectly, and '[all]' is not valid SQL syntax). Option D is missing both the column specification and FROM clause.