Databases & SQL: practice questions with explanations

Free Databases & SQL multiple-choice practice questions with hints and explanations, at three levels: key terms and facts, applying the concepts, cases and analysis.

Free practice problemEasy

Which constraint makes sure a column can never be left empty (NULL)?

Next problem →
Practise Databases & SQL for freePractise Databases & SQL now5 free problems every day. No card needed.

What you practise

  1. Easykey terms and facts
  2. Mediumapplying the concepts
  3. Hardcases and analysis

Learn Databases & SQL from scratch

1What a relational database is

A relational database stores data in tables. A table looks like a spreadsheet: each row is one record, each column is one property.

A shop might have a table customers with the columns id, name and city, and a table orders with the columns id, customer_id and total.

A primary key is a column, or a set of columns, that identifies each row uniquely. It can never be repeated and never be empty. Here that is the id of each table.

A foreign key is a column that refers to the primary key of another table. In orders, customer_id says which customer placed the order.

This is how tables are linked: one customer can have many orders, but each order belongs to one customer. That is a one-to-many relationship.

SQL (Structured Query Language) is the language you use to create tables, change data and ask questions about it.

Self-check What is a primary key?
  1. A column or set of columns that identifies each row uniquely
  2. A column that refers to a row in another table
  3. An index that makes searches faster
  4. The first column of every table

Answer: A column or set of columns that identifies each row uniquely. A primary key is unique and never NULL, so it picks out exactly one row. A column that refers to another table is a foreign key.

2Reading data with SELECT

Most questions are about the SELECT statement. It is built from clauses, always written in this order:

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …
  • SELECT lists the columns you want. DISTINCT removes duplicate rows.
  • FROM names the table, or the tables joined together.
  • WHERE keeps only the rows that meet a condition, for example WHERE total > 20.
  • GROUP BY puts rows with the same value into one group, so you can count or add per group.
  • HAVING keeps only the groups that meet a condition. It may use aggregates, which WHERE may not.
  • ORDER BY sorts the result, ascending by default; add DESC for highest first.

The aggregate functions summarise many rows into one value: COUNT, SUM, AVG, MIN and MAX.

The database does not run the clauses in the order you write them. It runs FROM first, then WHERE, GROUP BY, HAVING, SELECT and finally ORDER BY.

Other statements change data: INSERT adds rows, UPDATE changes rows and DELETE removes rows. CREATE, ALTER and DROP change the tables themselves.

Self-check You want only the customers with more than 5 orders. Which clause filters on COUNT(*) > 5?
  1. DISTINCT
  2. HAVING
  3. ORDER BY
  4. WHERE

Answer: HAVING. The count only exists after the rows are grouped, and HAVING filters groups. WHERE runs before grouping, so it cannot use an aggregate such as COUNT.

3Joins, normalisation and transactions

A join combines rows of two tables using a condition, usually foreign key = primary key:

FROM customers c JOIN orders o ON o.customer_id = c.id
  • INNER JOIN (or JOIN) keeps only rows that have a match in both tables.
  • LEFT JOIN keeps every row of the left table. Where there is no match, the columns of the right table are NULL.
  • NULL means a value is missing or unknown. Test it with IS NULL, never with = NULL.

Normalisation splits tables so that every fact is stored once. This avoids updating the same fact in several places.

  • 1NF: every cell holds one atomic value, with no repeating groups.
  • 2NF: 1NF, and every non-key column depends on the whole key, not on part of a composite key.
  • 3NF: 2NF, and no non-key column depends on another non-key column.

A transaction groups statements into one unit. COMMIT makes it permanent and ROLLBACK undoes it. Transactions follow ACID: atomicity (all or nothing), consistency, isolation and durability.

Self-check A customer has placed no orders. What happens to that customer in customers LEFT JOIN orders?
  1. The customer appears once for every order in the table
  2. The customer appears once, with NULL in the order columns
  3. The customer is left out of the result
  4. The query fails with an error

Answer: The customer appears once, with NULL in the order columns. A LEFT JOIN keeps every row of the left table and fills the missing right side with NULL. Leaving the customer out is what an INNER JOIN does.

4The method step by step

To write a query, or to work out what a given query returns:

  1. Find the tables that hold the data, and how they are linked by keys.
  2. Write FROM with the joins. Choose LEFT JOIN when rows without a match must stay.
  3. Add WHERE for conditions on single rows.
  4. If the question says “per” or “for each”, add GROUP BY and the aggregate.
  5. Add HAVING for conditions on the aggregate, then ORDER BY if the order matters.
  6. Check the result on a small example: list the rows after each clause, in the order the database runs them.

For a theory question, name the concept first (a key, a join type, a normal form, an ACID property), then pick the option that matches its definition.

5A worked example

Follow every step. Try to predict the next one before you open it.

Worked example

The table customers has the rows (1, Ana), (2, Ben) and (3, Cas). The table orders has the rows (id, customer_id) = (10, 1), (11, 1), (12, 2) and (13, 1). What does this query return? SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name

1

Run FROM and the join

Each customer is paired with the orders that carry its id. Ana matches orders 10, 11 and 13. Ben matches order 12.

Cas has no orders. Because it is a LEFT JOIN, Cas stays in the result, with NULL for o.id.

2

List the joined rows

  • Ana, 10
  • Ana, 11
  • Ana, 13
  • Ben, 12
  • Cas, NULL

That is 5 rows.

3

Group by name

GROUP BY c.name makes three groups: Ana with 3 rows, Ben with 1 row and Cas with 1 row.

4

Count per group

COUNT(o.id) counts only the values that are not NULL. Ana gets 3 and Ben gets 1.

Cas has one row, but its o.id is NULL, so Cas gets 0.

5

Check the answer

Ana, Ben and Cas together have 3 + 1 + 0 = 4 orders, the same as the number of rows in orders. With COUNT(*) instead, Cas would wrongly get 1, because COUNT(*) counts the row itself.

Answer

Ana 3, Ben 1, Cas 0.

6Common mistakes

  • Using an aggregate in WHERE. Conditions on COUNT, SUM or AVG belong in HAVING.
  • Writing = NULL. Any comparison with NULL is unknown; use IS NULL or IS NOT NULL.
  • Using an INNER JOIN when rows without a match must stay. Use a LEFT JOIN.
  • Forgetting the join condition. Without ON, every row is paired with every row (a cross join).
  • Selecting a column that is neither in GROUP BY nor inside an aggregate.
  • Mixing up DELETE (removes rows) and DROP TABLE (removes the whole table).
  • Running UPDATE or DELETE without WHERE. It changes every row in the table.
Self-check A table has 10 rows, and the column col is NULL in every row. What does SELECT COUNT(col) return?
  1. 0
  2. 10
  3. 1
  4. NULL

Answer: 0. COUNT(col) counts only values that are not NULL, so it returns 0. COUNT(*) counts rows and would return 10.

7How exam questions look

  • Easy: key terms and facts. What SELECT, WHERE, ORDER BY or DELETE do, primary and foreign keys, what NULL means.
  • Medium: applying the concepts. The right query for a task, WHERE versus HAVING, inner versus outer joins, the normal forms, ACID.
  • Hard: cases and analysis. Subqueries, isolation levels and their anomalies, BCNF, window functions, indexes and query performance, SQL injection.

When a query is given, trace it on a few rows in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Most traps show up within three rows.

Learn it step by step in the appLearn it step by step in the app

Example questions

Example 1Easy

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. CCombines them into one result and removes duplicate rows
  4. DKeeps only rows that appear in the first query but not the second
Show hints
  1. Both queries must have the same number of compatible columns.
  2. Two wrong answers are now crossed out. One of the remaining two is right.
Show the answer and explanation

Why this is the answer

UNION stacks the rows of both queries and removes duplicates; UNION ALL keeps the duplicates.

Answer

C · Combines them into one result and removes duplicate rows
Example 2Medium

What does SELECT SUM(amount) FROM orders return when the WHERE clause matches no rows?

  1. AAn error
  2. B0
  3. CNULL
  4. DAn empty string
Show hints
  1. SUM differs from COUNT here.
  2. Two wrong answers are now crossed out. One of the remaining two is right.
Show the answer and explanation

Why this is the answer

SUM, AVG, MIN and MAX return NULL on an empty set, while COUNT returns 0; COALESCE(SUM(amount), 0) gives 0 instead.

Answer

C · NULL
Example 3Hard

What does SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) FROM emp return?

  1. AOnly employees who earn more than their department average
  2. BEvery employee row together with the average salary of their department
  3. CAn error because name is not in a GROUP BY
  4. DOne row per department with its average salary
Show hints
  1. Window functions do not collapse rows.
  2. Two wrong answers are now crossed out. One of the remaining two is right.
Show the answer and explanation

Why this is the answer

Unlike GROUP BY, a window function keeps every input row and adds a value computed over its partition, here the department.

Answer

B · Every employee row together with the average salary of their department

Questions and answers

What is Databases & SQL?

A relational database stores data in tables. A table looks like a spreadsheet: each row is one record, each column is one property.

A shop might have a table customers with the columns id, name and city, and a table orders with the columns id, customer_id and total.

How do I learn Databases & SQL step by step?
  1. What a relational database is
  2. Reading data with SELECT
  3. Joins, normalisation and transactions
  4. The method step by step
  5. A worked example
  6. Common mistakes
  7. How exam questions look
What do the Databases & SQL exercises cover?
  • Easy: key terms and facts
  • Medium: applying the concepts
  • Hard: cases and analysis

Every question has four options, and only one is right. Stuck? Open a small hint first, and the full explanation only when you need it.

How practising works

  • Every question has four options, and only one is right.
  • Stuck? Open a small hint first, and the full explanation only when you need it.
  • Your progress, mistakes and saved problems are kept, so you can review before the exam.

Courses with this topic

Computer Science Fundamentals

More topics in Computer science

Number systemsIPv4 subnettingComputer networksAlgorithms & complexityData structures

Printable worksheet

Databases & SQL worksheet with answers
Practise Databases & SQL for freePractise Databases & SQL now5 free problems every day. No card needed.