Databases & SQL worksheet with answers

Which operator is used for simple text pattern matching in a WHERE clause?

1. ACONTAINS
2. BMATCH
3. CSIMILAR
4. DLIKE

What does UNION do with the results of two SELECT queries?

1. AJoins them side by side on a key
2. BKeeps only rows that appear in both
3. CKeeps only rows that appear in the first query but not the second
4. DCombines them into one result and removes duplicate rows

What is a primary key?

1. AA password for the database
2. BThe first column of every table
3. CA column or set of columns that uniquely identifies each row
4. DA column that may hold duplicate values but must never contain NULL

Third normal form (3NF) removes:

1. ARedundant indexes on columns that are not part of a key
2. BAtomic values
3. CComposite keys
4. DTransitive dependencies between non-key attributes

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

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

What does the functional dependency A → B mean?

1. AA and B are both primary keys
2. BEach value of A is associated with exactly one value of B
3. CA is a foreign key referencing B
4. DEach value of B is associated with exactly one value of A

For an equality join between two large tables with no useful indexes, which join algorithm does an optimiser often choose?

1. ABitmap join on the primary key
2. BCross join
3. CHash join
4. DNested loop join with index lookups

Table b contains the values 1, 2 and NULL in column y. What does SELECT * FROM a WHERE x NOT IN (SELECT y FROM b) return?

1. AOnly rows of a where x is NULL
2. BEvery row of a
3. CRows of a where x is not 1 or 2
4. DNo rows at all