Databases & SQL worksheet with answers

What does COMMIT do in a transaction?

1. AStarts a new table
2. BMakes all changes of the transaction permanent
3. CUndoes all changes made since the transaction began
4. DLocks the whole database

A foreign key is:

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

Which statement deletes a whole table, including its structure?

1. ATRUNCATE ROWS
2. BDROP TABLE
3. CREMOVE TABLE
4. DDELETE FROM

A LEFT JOIN returns:

1. ARows that match in neither table
2. BOnly matching rows from both tables
3. CAll rows from the left table and matching rows from the right, with NULLs where there is no match
4. DAll rows from the right table only

What does EXCEPT (called MINUS in Oracle) return?

1. ARows of the first query that do not appear in the second
2. BRows of the second query that do not appear in the first one
3. CRows that appear in both queries
4. DAll rows from both queries without duplicates

What does INTERSECT return when used between two SELECT queries?

1. AThe Cartesian product of the results
2. BRows in the first result but not the second
3. CRows that appear in both results
4. DAll rows from both results

Two-phase locking (2PL) ensures:

1. AConflict-serializable schedules
2. BThat transactions never wait
3. CThat no locks are needed
4. DThat deadlocks can never happen

A table has a million rows and a Boolean column is_active that is TRUE for 95% of them. Why might the optimiser ignore an index on is_active for WHERE is_active = TRUE?

1. AThe optimiser never uses indexes in WHERE clauses
2. BMost rows match, so a full table scan is cheaper than many index lookups
3. CIndexes cannot be created on Boolean columns
4. DTRUE is treated as NULL by the index