Databases & SQL worksheet with answers

Which statement adds a new column to an existing table?

1. AUPDATE TABLE
2. BCREATE COLUMN
3. CALTER TABLE
4. DINSERT COLUMN

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

1. ALIKE
2. BSIMILAR
3. CCONTAINS
4. DMATCH

Which keyword sorts the result of a query?

1. AORDER BY
2. BSORT
3. CGROUP BY
4. DARRANGE

What does INTERSECT return when used between two SELECT queries?

1. ARows that appear in both results
2. BThe Cartesian product of the results
3. CRows in the first result but not the second
4. DAll rows from both results

A view in SQL is:

1. AA copy of a table on disk
2. BAn index type
3. CA stored query that behaves like a virtual table
4. DA screen in the database client for browsing tables

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 junction table holding foreign keys to both students and courses
3. CBy merging students and courses into one table
4. DWith a list of course ids stored in one column

A table has a million rows and a Boolean column is_active that is TRUE for 95% of them. Why might the optimiser ignore an index on is_active for WHERE is_active = TRUE?

1. AThe optimiser never uses indexes in WHERE clauses
2. BTRUE is treated as NULL by the index
3. CIndexes cannot be created on Boolean columns
4. DMost rows match, so a full table scan is cheaper than many index lookups

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

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