Databases & SQL worksheet with answers

Which clause filters rows before grouping?

1. AHAVING
2. BGROUP BY
3. CWHERE
4. DORDER BY

What is a candidate key?

1. AA minimal set of columns that uniquely identifies each row
2. BA column that is waiting to become a foreign key
3. CAny column that contains NULL values
4. DA set of columns shared between two tables to link their rows

What is a primary key?

1. AThe first column of every table
2. BA password for the database
3. CA column that may hold duplicate values but must never contain NULL
4. DA column or set of columns that uniquely identifies each row

A table has rows where col is NULL. Which rows does WHERE col = NULL return?

1. AAll rows of the table
2. BNo rows, because the comparison is unknown
3. COnly the rows where col is NULL, as with IS NULL
4. DAn error in every database

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

1. AWHERE
2. BSELECT list
3. CORDER BY
4. DGROUP BY

What does SELECT name, CASE WHEN grade >= 5.5 THEN 'pass' ELSE 'fail' END FROM results return?

1. AEach name with the label 'pass' or 'fail' depending on the grade
2. BOnly the students who passed
3. CThe number of students who passed and failed
4. DAn error because CASE is not allowed in SELECT

After customers LEFT JOIN orders, why use COUNT(o.id) rather than COUNT(*) to count orders per customer?

1. ACOUNT(*) counts the NULL-filled row as 1 for customers without orders
2. BCOUNT(o.id) counts only distinct customers
3. CCOUNT(*) ignores rows with NULL values
4. DCOUNT(*) is not allowed after a join

Why is NATURAL JOIN considered risky in production queries?

1. AIt is not supported by any database
2. BIt ignores primary and foreign keys and keeps only NULL rows
3. CIt joins on every column with the same name, so a new column can silently change the result
4. DIt always returns a Cartesian product