Databases & SQL worksheet with answers

A customer can place many orders, but each order belongs to exactly one customer. What type of relationship is this?

1. AMany-to-many
2. BOne-to-many
3. CZero-to-zero
4. DOne-to-one

Which rows does WHERE price BETWEEN 10 AND 20 select?

1. ARows with price equal to 10 or 20 only
2. BRows with price from 10 to 20, including 10 and 20
3. CRows with price above 10 and below 20, excluding 10 and 20
4. DRows with price outside the range 10 to 20

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

1. AZero or more characters
2. BA space character
3. CAny letter but no digits
4. DExactly one character

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

Which statement removes all rows of a table, keeps its structure and cannot take a WHERE clause?

1. ATRUNCATE TABLE
2. BALTER TABLE
3. CDROP TABLE
4. DDELETE FROM with a WHERE clause

A LEFT JOIN returns:

1. AOnly matching rows from both tables
2. BAll rows from the left table and matching rows from the right, with NULLs where there is no match
3. CAll rows from the right table only
4. DRows that match in neither table

What does a FULL OUTER JOIN return?

1. AOnly unmatched rows
2. BOnly matching rows
3. CThe Cartesian product, pairing each row of one table with every row of the other
4. DAll rows from both tables, matched where possible and NULL-filled otherwise

Which SQL feature is best suited to finding every employee below a manager in an organisation tree of unknown depth?

1. AA single self-join of the employees table on manager_id
2. BA recursive common table expression (WITH RECURSIVE)
3. CA GROUP BY on manager_id
4. DA UNIQUE constraint on manager_id