Databases & SQL worksheet with answers

What does GROUP BY do in a query?

1. ASorts the rows of the result alphabetically
2. BRemoves rows with NULL values
3. CJoins two tables on their common columns
4. DPuts rows with the same values into groups so aggregates are computed per group

A foreign key is:

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

What is a candidate key?

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

An employees table has a column manager_id that refers to another employee. How do you list each employee next to their manager's name?

1. ACreate a second copy of the table on disk
2. BUse GROUP BY manager_id only
3. CJoin the employees table with itself using two aliases
4. DCombine the table with itself using UNION ALL and a WHERE filter

A column holds the values 10, 20 and NULL. What does AVG(col) return?

1. ANULL
2. B30
3. C10
4. D15

The orders table has 5 rows, each referring to an existing customer. The customers table has 3 rows. How many rows does an INNER JOIN on customer_id return?

1. A3
2. B15
3. C5
4. D8

What is a phantom read?

1. AReading data that was never written
2. BReading uncommitted changes
3. CA query that returns no rows
4. DA repeated query returns new rows inserted by another committed transaction

What does the write-ahead logging (WAL) rule require?

1. AEvery query must be logged before it is parsed
2. BA change's log record must reach stable storage before the changed data page does
3. CData pages must be written before the log
4. DThe log is written only after a crash