Databases & SQL worksheet with answers

Which constraint makes sure a column can never be left empty (NULL)?

1. ADEFAULT
2. BCHECK NULL
3. CUNIQUE
4. DNOT NULL

Which SQL keyword removes duplicate rows from a result?

1. AUNIQUE
2. BDIFFERENT
3. CDISTINCT
4. DSINGLE

In a LIKE pattern, what does the _ (underscore) wildcard stand for?

1. AAny letter but no digits
2. BZero or more characters
3. CExactly one character
4. DA space character

What does EXCEPT (called MINUS in Oracle) return?

1. ARows of the second query that do not appear in the first one
2. BRows that appear in both queries
3. CAll rows from both queries without duplicates
4. DRows of the first query that do not appear in the second

A foreign key is declared with ON DELETE CASCADE. What happens when a parent row is deleted?

1. AThe foreign key in the child rows is set to NULL automatically
2. BThe delete is always rejected
3. CThe parent row is copied to an archive table
4. DThe child rows that reference it are deleted automatically

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. ACombine the table with itself using UNION ALL and a WHERE filter
2. BUse GROUP BY manager_id only
3. CCreate a second copy of the table on disk
4. DJoin the employees table with itself using two aliases

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

Which window function assigns 1, 2, 3, ... to rows within each partition without ties?

1. ACOUNT()
2. BRANK()
3. CROW_NUMBER()
4. DDENSE_RANK()