Databases & SQL worksheet with answers

What is a composite key?

1. AA key made up of two or more columns
2. BA key that allows duplicate values
3. CA key stored in a separate table
4. DA key that is generated by the database

What does ROLLBACK do in a transaction?

1. AUndoes the changes made since the transaction began
2. BDeletes the table
3. CRepeats the last statement
4. DSaves all changes of the transaction permanently to disk

Which keyword sorts the result of a query?

1. AARRANGE
2. BGROUP BY
3. CORDER BY
4. DSORT

Third normal form (3NF) removes:

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

Which query returns the highest salary in each department?

1. ASELECT MAX(dept), salary FROM emp GROUP BY salary
2. BSELECT dept, MAX(salary) FROM emp ORDER BY dept
3. CSELECT dept, salary FROM emp WHERE salary = MAX(salary)
4. DSELECT dept, MAX(salary) FROM emp GROUP BY dept

In relational algebra, the projection operator (π) corresponds to:

1. AChoosing which rows satisfy a given condition
2. BCombining two tables on a key
3. CSorting the result
4. DChoosing which columns appear in the result

Given the functional dependencies A → B, B → C and C → D on R(A, B, C, D), what is the closure A⁺?

1. A{A, B, C}
2. B{A}
3. C{A, B, C, D}
4. D{A, B}

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