Databases & SQL worksheet with answers

A foreign key is:

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

What is a surrogate key?

1. AA key that is only used in views
2. BA key made from real-world data such as an email address
3. CAn artificial identifier with no business meaning, such as an auto-increment id
4. DA key that refers to another table

Which keyword sorts the result of a query?

1. AORDER BY
2. BARRANGE
3. CSORT
4. DGROUP BY

Which query returns the number of different cities in the customers table?

1. ASELECT SUM(DISTINCT city) FROM customers
2. BSELECT DISTINCT COUNT(city) FROM customers
3. CSELECT COUNT(city) FROM customers GROUP BY city
4. DSELECT COUNT(DISTINCT city) FROM customers

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

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

What does the "A" in ACID stand for?

1. AAccuracy
2. BAggregation
3. CAtomicity
4. DAvailability

Why is NATURAL JOIN considered risky in production queries?

1. AIt is not supported by any database
2. BIt always returns a Cartesian product
3. CIt ignores primary and foreign keys and keeps only NULL rows
4. DIt joins on every column with the same name, so a new column can silently change the result

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

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