Databases & SQL worksheet with answers

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

1. AMAX 10
2. BLIMIT 10
3. CFIRST 10 ONLY
4. DTOP ROWS 10

What does COMMIT do in a transaction?

1. ALocks the whole database
2. BUndoes all changes made since the transaction began
3. CStarts a new table
4. DMakes all changes of the transaction permanent

A foreign key is:

1. AA key that is always encrypted
2. BA column whose values must be unique across all rows of the table
3. CA key used by foreign users
4. DA column that refers to the primary key of another table

What does SELECT name, CASE WHEN grade >= 5.5 THEN 'pass' ELSE 'fail' END FROM results return?

1. AThe number of students who passed and failed
2. BEach name with the label 'pass' or 'fail' depending on the grade
3. CAn error because CASE is not allowed in SELECT
4. DOnly the students who passed

What does SELECT SUM(amount) FROM orders return when the WHERE clause matches no rows?

1. ANULL
2. BAn empty string
3. C0
4. DAn error

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

1. AConsistency
2. BDurability
3. CAtomicity
4. DIsolation

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

1. AROW_NUMBER()
2. BDENSE_RANK()
3. CCOUNT()
4. DRANK()

Scores are 90, 90 and 80. Which values does RANK() OVER (ORDER BY score DESC) give?

1. A1, 1, 2
2. B2, 2, 3
3. C1, 2, 3
4. D1, 1, 3