Databases & SQL worksheet with answers

What does COMMIT do in a transaction?

1. AUndoes all changes made since the transaction began
2. BStarts a new table
3. CMakes all changes of the transaction permanent
4. DLocks the whole database

Which aggregate function counts rows?

1. ASUM
2. BAVG
3. CCOUNT
4. DMAX

In PostgreSQL and MySQL, which clause returns only the first 10 rows of a result?

1. AMAX 10
2. BFIRST 10 ONLY
3. CTOP ROWS 10
4. DLIMIT 10

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

1. A'unknown'
2. BAn empty string
3. CNULL
4. DAn error

Which ACID property guarantees that committed changes survive a system crash?

1. AConsistency
2. BAtomicity
3. CDurability
4. DIsolation

A LEFT JOIN returns:

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

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

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

What does SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) FROM emp return?

1. AAn error because name is not in a GROUP BY
2. BOnly employees who earn more than their department average
3. CEvery employee row together with the average salary of their department
4. DOne row per department with its average salary