Databases & SQL worksheet with answers

Which clause filters rows before grouping?

1. AGROUP BY
2. BWHERE
3. CHAVING
4. DORDER BY

What does a UNIQUE constraint on a column guarantee?

1. AThe column cannot be changed after insert
2. BThe column values are sorted
3. CNo two rows have the same non-NULL value in that column
4. DThe column automatically becomes the primary key of the table

What is a primary key?

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

What does a common table expression (WITH name AS (...)) provide?

1. AA permanent table that stays stored on disk until it is dropped
2. BAn index on a column
3. CA new database user
4. DA named temporary result that can be used within one statement

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

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

A view in SQL is:

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

What is a phantom read?

1. AReading uncommitted changes
2. BA query that returns no rows
3. CA repeated query returns new rows inserted by another committed transaction
4. DReading data that was never written

Table employee(emp_id, dept_id, dept_name) has the key emp_id, and dept_id → dept_name. Which statement is correct?

1. AIt is in BCNF and therefore in 3NF
2. BIt is not in 1NF
3. CIt is in 2NF but not in 3NF
4. DIt is not in 2NF