Databases & SQL worksheet with answers

A foreign key is:

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

What does GROUP BY do in a query?

1. AJoins two tables on their common columns
2. BSorts the rows of the result alphabetically
3. CRemoves rows with NULL values
4. DPuts rows with the same values into groups so aggregates are computed per group

Which aggregate function returns the average of a numeric column?

1. AMEAN
2. BSUM
3. CAVG
4. DMEDIAN

A table stores the customer's address on every order row. Changing the address in one row but not in the others is an example of:

1. AAn update anomaly
2. BAn insertion anomaly
3. CA phantom read
4. DA deletion anomaly

Third normal form (3NF) removes:

1. ARedundant indexes on columns that are not part of a key
2. BComposite keys
3. CAtomic values
4. DTransitive dependencies between non-key attributes

What is a database trigger?

1. AA scheduled backup of the database
2. BA query that a user runs by hand
3. CA type of index that speeds up searches
4. DCode that runs automatically when a specified event such as INSERT, UPDATE or DELETE occurs on a table

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 read
2. BNon-repeatable read within one transaction
3. CDirty write
4. DWrite skew

For an equality join between two large tables with no useful indexes, which join algorithm does an optimiser often choose?

1. ABitmap join on the primary key
2. BCross join
3. CNested loop join with index lookups
4. DHash join