Databases & SQL worksheet with answers

Which aggregate function counts rows?

1. ACOUNT
2. BAVG
3. CMAX
4. DSUM

What is a candidate key?

1. AAny column that contains NULL values
2. BA column that is waiting to become a foreign key
3. CA set of columns shared between two tables to link their rows
4. DA minimal set of columns that uniquely identifies each row

What is a primary key?

1. AA column or set of columns that uniquely identifies each row
2. BThe first column of every table
3. CA column that may hold duplicate values but must never contain NULL
4. DA password for the database

A view in SQL is:

1. AA copy of a table on disk
2. BAn index type
3. CA screen in the database client for browsing tables
4. DA stored query that behaves like a virtual table

What does INTERSECT return when used between two SELECT queries?

1. ARows that appear in both results
2. BThe Cartesian product of the results
3. CAll rows from both results
4. DRows in the first result but not the second

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

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

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, B}
3. C{A}
4. D{A, B, C, D}

A reporting database copies customer names into the orders table to avoid joins. What is the main cost of this denormalisation?

1. AThe database cannot use indexes any more
2. BPrimary keys can no longer be used
3. CQueries on the orders table become slower
4. DRedundant copies must be kept consistent whenever customer data changes