Databases & SQL worksheet with answers

What does ROLLBACK do in a transaction?

1. ASaves all changes of the transaction permanently to disk
2. BDeletes the table
3. CUndoes the changes made since the transaction began
4. DRepeats the last statement

What is a primary key?

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

An INNER JOIN returns:

1. AOnly rows with matching values in both tables
2. BRows that do not match
3. CAll rows from the left table, with NULLs where there is no match
4. DAll rows from both tables

A view in SQL is:

1. AA screen in the database client for browsing tables
2. BAn index type
3. CA copy of a table on disk
4. DA stored query that behaves like a virtual table

Which ACID property guarantees that committed changes survive a system crash?

1. AIsolation
2. BConsistency
3. CAtomicity
4. DDurability

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. AUse GROUP BY manager_id only
2. BCombine the table with itself using UNION ALL and a WHERE filter
3. CCreate a second copy of the table on disk
4. DJoin the employees table with itself using two aliases

Boyce–Codd normal form (BCNF) requires that for every non-trivial functional dependency X → Y:

1. AX is a superkey
2. BY is a primary key
3. CY is atomic
4. DX contains NULL

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

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