Databases & SQL worksheet with answers

What does a CHECK constraint do?

1. ARejects rows whose values do not satisfy a condition
2. BChecks whether a table exists before a query is run on it
3. CVerifies the user's password
4. DCounts the rows in a table

What does a UNIQUE constraint on a column guarantee?

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

Which clause filters rows before grouping?

1. AWHERE
2. BORDER BY
3. CGROUP BY
4. DHAVING

An employees table has a column manager_id that refers to another employee. How do you list each employee next to their manager's name?

1. AUse GROUP BY manager_id only
2. BCombine the table with itself using UNION ALL and a WHERE filter
3. CCreate a second copy of the table on disk
4. DJoin the employees table with itself using two aliases

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. AA deletion anomaly
2. BAn update anomaly
3. CA phantom read
4. DAn insertion anomaly

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

Two transactions both read stock = 10, each subtracts 1 and writes the result, giving 9 instead of 8. Which anomaly is this?

1. ALost update
2. BPhantom read
3. CDeletion anomaly
4. DDirty read

Transaction T1 locks row A and waits for row B, while T2 locks row B and waits for row A. What does a typical DBMS do?

1. ALets both transactions wait until one of them commits normally
2. BDetects the deadlock and aborts one transaction as the victim
3. CMerges the two transactions into one
4. DCommits both transactions at once