Databases & SQL worksheet with answers

An INNER JOIN returns:

1. AAll rows from the left table, with NULLs where there is no match
2. BRows that do not match
3. CAll rows from both tables
4. DOnly rows with matching values in both tables

Which statement deletes a whole table, including its structure?

1. ATRUNCATE ROWS
2. BDROP TABLE
3. CREMOVE TABLE
4. DDELETE FROM

Which aggregate function counts rows?

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

A LEFT JOIN returns:

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

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

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

Second normal form (2NF) removes:

1. APartial dependencies of non-key columns on part of a composite key
2. BForeign keys
3. CAll NULL values
4. DTransitive dependencies between non-key columns via another non-key column

With multiversion concurrency control (MVCC), how can readers avoid blocking writers?

1. AWriters are not allowed while any reader is active
2. BReaders see a consistent snapshot of committed row versions while writers create new versions
3. CReaders read uncommitted data directly
4. DReaders always wait until all writers have committed

R(A, B, C, D) has the functional dependencies A → B and C → D. What is the candidate key?

1. A{A}
2. B{C}
3. C{A, C}
4. D{B, D}