Databases & SQL worksheet with answers

What does a CROSS JOIN return?

1. AThe rows of the first table only
2. BOnly the rows that match on a shared key column
3. COnly rows that do not match
4. DEvery combination of rows from the two tables

A foreign key is:

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

Statements such as CREATE, ALTER and DROP belong to which part of SQL?

1. AData Manipulation Language (DML)
2. BTransaction Control Language (TCL)
3. CData Control Language (DCL)
4. DData Definition Language (DDL)

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

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

A column holds the values 10, 20 and NULL. What does AVG(col) return?

1. A15
2. B10
3. C30
4. DNULL

In relational algebra, the projection operator (π) corresponds to:

1. ACombining two tables on a key
2. BChoosing which columns appear in the result
3. CSorting the result
4. DChoosing which rows satisfy a given condition

Table employee(emp_id, dept_id, dept_name) has the key emp_id, and dept_id → dept_name. Which statement is correct?

1. AIt is not in 1NF
2. BIt is not in 2NF
3. CIt is in 2NF but not in 3NF
4. DIt is in BCNF and therefore in 3NF

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