Databases & SQL worksheet with answers

In a LIKE pattern, what does the _ (underscore) wildcard stand for?

1. AA space character
2. BExactly one character
3. CAny letter but no digits
4. DZero or more characters

Which statement deletes a whole table, including its structure?

1. ADELETE FROM
2. BDROP TABLE
3. CREMOVE TABLE
4. DTRUNCATE ROWS

What is a composite key?

1. AA key stored in a separate table
2. BA key that is generated by the database
3. CA key made up of two or more columns
4. DA key that allows duplicate values

What does the functional dependency A → B mean?

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

Which query returns the number of orders per customer?

1. ASELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id
2. BSELECT DISTINCT COUNT(*) FROM orders
3. CSELECT customer_id, COUNT(*) FROM orders ORDER BY customer_id
4. DSELECT customer_id FROM orders WHERE COUNT(*) > 0

What does EXCEPT (called MINUS in Oracle) return?

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

What does EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) show for a query?

1. AThe list of users who ran the query
2. BThe meaning of each SQL keyword in the query
3. CThe execution plan chosen by the optimiser, such as scans and join methods
4. DA rewrite of the query in relational algebra notation only

What does the following return: SELECT name FROM students WHERE grade > (SELECT AVG(grade) FROM students)?

1. AAn error because subqueries are not allowed in WHERE
2. BStudents with the highest grade only
3. CStudents with a grade above the average grade
4. DThe average grade of all students