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

How does UNION ALL differ from UNION?

1. AUNION ALL keeps duplicate rows
2. BUNION ALL only works on one table
3. CUNION ALL removes duplicate rows
4. DUNION ALL sorts the result

Which aggregate function returns the average of a numeric column?

1. AMEAN
2. BSUM
3. CMEDIAN
4. DAVG

Why does SELECT price * 2 AS double_price FROM t WHERE double_price > 10 fail in most databases?

1. AArithmetic is not allowed in the SELECT list
2. BWHERE is evaluated before the SELECT list, so the alias does not exist yet
3. CWHERE can only compare text columns
4. DAliases may not contain an underscore

Students can take many courses and courses have many students. How is this modelled in tables?

1. ABy merging students and courses into one table
2. BWith a junction table holding foreign keys to both students and courses
3. CWith a foreign key column course_id in the students table pointing to courses
4. DWith a list of course ids stored in one column

What is a database trigger?

1. AA type of index that speeds up searches
2. BCode that runs automatically when a specified event such as INSERT, UPDATE or DELETE occurs on a table
3. CA query that a user runs by hand
4. DA scheduled backup of the database

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

1. A1, 2, 3
2. B1, 1, 3
3. C2, 2, 3
4. D1, 1, 2

What is the result of SELECT COUNT(col) FROM t if all values in col are NULL and the table has 10 rows?

1. AAn error
2. B0
3. C10
4. DNULL