Databases & SQL worksheet with answers

What is a composite key?

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

Statements such as CREATE, ALTER and DROP belong to which part of SQL?

1. AData Control Language (DCL)
2. BData Manipulation Language (DML)
3. CData Definition Language (DDL)
4. DTransaction Control Language (TCL)

Which keyword sorts the result of a query?

1. ASORT
2. BORDER BY
3. CARRANGE
4. DGROUP BY

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

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

What does SELECT COALESCE(phone, 'unknown') FROM customers return for a customer whose phone is NULL?

1. ANULL
2. B'unknown'
3. CAn empty string
4. DAn error

What does EXCEPT (called MINUS in Oracle) return?

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

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

Why is SELECT dept, name, COUNT(*) FROM emp GROUP BY dept invalid in standard SQL?

1. AGROUP BY must list all columns of the table
2. Bdept must appear inside an aggregate function
3. CCOUNT(*) cannot be used together with GROUP BY
4. Dname is neither grouped nor aggregated, so it has no single value per group