Databases & SQL worksheet with answers

Which clause filters rows before grouping?

1. AGROUP BY
2. BHAVING
3. CORDER BY
4. DWHERE

A foreign key is:

1. AA key that is always encrypted
2. BA column that refers to the primary key of another table
3. CA key used by foreign users
4. DA column whose values must be unique across all rows of the table

What is a surrogate key?

1. AA key that is only used in views
2. BA key made from real-world data such as an email address
3. CA key that refers to another table
4. DAn artificial identifier with no business meaning, such as an auto-increment id

In relational algebra, the selection operator (σ) corresponds to which SQL clause?

1. AGROUP BY
2. BWHERE
3. CORDER BY
4. DSELECT list

What does a common table expression (WITH name AS (...)) provide?

1. AA permanent table that stays stored on disk until it is dropped
2. BAn index on a column
3. CA new database user
4. DA named temporary result that can be used within one statement

Third normal form (3NF) removes:

1. AAtomic values
2. BTransitive dependencies between non-key attributes
3. CRedundant indexes on columns that are not part of a key
4. DComposite keys

What is a known drawback of decomposing tables into BCNF compared with 3NF?

1. AThe decomposition always loses data when joined back
2. BSome functional dependencies may no longer be checkable within a single table
3. CBCNF tables cannot have primary keys
4. DBCNF allows more redundancy than 3NF

The query FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' unexpectedly drops customers with no orders. Why?

1. Astatus is a reserved word
2. BThe WHERE condition on o rejects the NULL-filled rows, turning it into an inner join
3. CThe join should use USING instead of ON
4. DLEFT JOIN never keeps unmatched rows