Databases & SQL worksheet with answers

Which SQL statement changes values in rows that already exist?

1. AUPDATE
2. BINSERT
3. CREPLACE COLUMN
4. DMODIFY

Which aggregate function returns the average of a numeric column?

1. AMEDIAN
2. BMEAN
3. CAVG
4. DSUM

What does COMMIT do in a transaction?

1. AStarts a new table
2. BUndoes all changes made since the transaction began
3. CLocks the whole database
4. DMakes all changes of the transaction permanent

What does EXCEPT (called MINUS in Oracle) return?

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

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. A5
2. B8
3. C3
4. D15

Second normal form (2NF) removes:

1. AAll NULL values
2. BPartial dependencies of non-key columns on part of a composite key
3. CTransitive dependencies between non-key columns via another non-key column
4. DForeign keys

A correlated subquery is one that:

1. AReferences columns of the outer query and is evaluated per outer row
2. BRuns only once before the outer query and caches its result for every row
3. CUses only aggregate functions
4. DJoins two tables with a foreign key

R(A, B, C, D) has the functional dependencies A → B and C → D. What is the candidate key?

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