Databases & SQL worksheet with answers

What is a composite key?

1. AA key that allows duplicate values
2. BA key stored in a separate table
3. CA key that is generated by the database
4. DA key made up of two or more columns

What does a UNIQUE constraint on a column guarantee?

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

GRANT and REVOKE belong to which part of SQL?

1. AData Control Language (DCL)
2. BData Query Language (DQL)
3. CData Definition Language (DDL)
4. DData Manipulation Language (DML)

Third normal form (3NF) removes:

1. ATransitive dependencies between non-key attributes
2. BRedundant indexes on columns that are not part of a key
3. CAtomic values
4. DComposite keys

Which ACID property means concurrent transactions do not see each other's unfinished changes?

1. AAtomicity
2. BDurability
3. CIsolation
4. DAvailability

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

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

Table b contains the values 1, 2 and NULL in column y. What does SELECT * FROM a WHERE x NOT IN (SELECT y FROM b) return?

1. AOnly rows of a where x is NULL
2. BEvery row of a
3. CNo rows at all
4. DRows of a where x is not 1 or 2

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