Databases & SQL worksheet with answers

How does UNION ALL differ from UNION?

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

What is a candidate key?

1. AA set of columns shared between two tables to link their rows
2. BA column that is waiting to become a foreign key
3. CA minimal set of columns that uniquely identifies each row
4. DAny column that contains NULL values

What does a UNIQUE constraint on a column guarantee?

1. AThe column automatically becomes the primary key of the table
2. BThe column cannot be changed after insert
3. CNo two rows have the same non-NULL value in that column
4. DThe column values are sorted

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 foreign key column course_id in the students table pointing to courses
3. CWith a list of course ids stored in one column
4. DWith a junction table holding foreign keys to both students and courses

What does EXCEPT (called MINUS in Oracle) return?

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

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

1. AConsistency
2. BIsolation
3. CDurability
4. DAtomicity

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

1. ANULL
2. B10
3. CAn error
4. D0

There is a composite index on (last_name, first_name). Which condition can use it most effectively?

1. AWHERE last_name = 'Jansen'
2. BWHERE first_name = 'Eva'
3. CWHERE UPPER(first_name) = 'EVA'
4. DWHERE first_name LIKE '%a'