Databases & SQL worksheet with answers

How does UNION ALL differ from UNION?

1. AUNION ALL only works on one table
2. BUNION ALL removes duplicate rows
3. CUNION ALL keeps duplicate rows
4. DUNION ALL sorts the result

Which SQL statement changes values in rows that already exist?

1. AMODIFY
2. BINSERT
3. CREPLACE COLUMN
4. DUPDATE

What does ROLLBACK do in a transaction?

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

Which query returns the highest salary in each department?

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

Why can a table have only one clustered index?

1. AThe rows can be stored physically in only one order
2. BClustered indexes use too much memory
3. CClustered indexes can only be on the primary key
4. DThe SQL standard forbids more than one index on a table

Third normal form (3NF) removes:

1. ATransitive dependencies between non-key attributes
2. BAtomic values
3. CRedundant indexes on columns that are not part of a key
4. DComposite keys

Two doctors on call each check that the other is still on call, then both sign off at the same time. Which anomaly can snapshot isolation still allow here?

1. ADirty write
2. BNon-repeatable read within one transaction
3. CWrite skew
4. DDirty read

An app runs UPDATE item SET qty = 4, version = 8 WHERE id = 1 AND version = 7, and reports a conflict if no row was updated. What technique is this?

1. ADatabase sharding
2. BOptimistic concurrency control
3. CWrite-ahead logging
4. DStrict two-phase locking with row locks