Skip to content
notes.vinhnq.com

Search

Database & ORMMid14 min read

SQL practice: joins, GROUP BY and normalization in 15 library-database queries

Review 12 cards
On this page

Backend practice series: 1. Requirements and data design · 2. APIs and overbooking · 3. Interview review · 4. Recursion and sorting · 5. SQL exercises · 6. SQL on the E-Learning database

This post is a quick review of the database part of the practice sessions, then my solutions to 15 SQL questions on a library database; in several places the original answers are wrong, and I point out where and why. After working through it you can write joins and GROUP BY/HAVING correctly and spot three common traps: the LEFT JOIN, COUNT, and conditions that can’t use an index. The full theory is in the Databases posts; the 9 questions on the E-Learning database (ratings, lesson progress, locked accounts) are in part 6.

1. Database review

Key idea: the relationships in the ERD decide what you join through, the join type decides which rows survive, and the logical evaluation order explains why WHERE and HAVING differ. More in Databases.

An ERD (Entity Relationship Diagram) describes data with entities (they become tables), attributes (columns, including the primary key) and relationships. Each relationship has a cardinality (how many rows on one side match one row on the other):

Cardinality Example Implementation
1–1 a user has one profile a unique foreign key (or a shared primary key)
1–N a customer has many orders a foreign key on the “many” side
N–M students and courses a junction table holding two foreign keys

Join types:

flowchart LR
  I[INNER JOIN<br/>only rows that match in both tables]
  L[LEFT JOIN<br/>every left row + matching right rows, NULL when no match]
  R[RIGHT JOIN<br/>the mirror of LEFT]
  F[FULL OUTER JOIN<br/>everything from both sides - MySQL lacks it, emulate with LEFT UNION RIGHT]
  C[CROSS JOIN<br/>Cartesian product]
  S[SELF JOIN<br/>a table joined to itself]

GROUP BY, HAVING and aggregate functions:

  • Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one value.
  • GROUP BY splits rows into groups so you aggregate per group.
  • WHERE filters rows before grouping; HAVING filters groups after aggregation.
  • COUNT(*) counts rows, COUNT(col) skips NULLs. That decides the result of a LEFT JOIN.

The logical order of a SELECT:

flowchart LR
  A[FROM / JOIN] --> B[WHERE] --> C[GROUP BY] --> D[HAVING] --> E[SELECT<br/>expressions, aliases, window functions] --> F[DISTINCT] --> G[ORDER BY] --> H[LIMIT / OFFSET]

Consequences:

  • You can’t use a SELECT alias in WHERE (it doesn’t exist yet), but you can in ORDER BY.
  • MySQL relaxes the rules and allows aliases in HAVING. PostgreSQL and standard SQL don’t.
  • This is the logical order; the optimiser may execute things differently as long as the result is the same.

Normalisation (splitting tables so every fact is stored in one place):

Form Requirement Wrong Right
1NF every cell holds one atomic value, no repeating groups a phones = "0901,0902" column a user_phone(user_id, phone) table
2NF 1NF, and every non-key column depends on the whole primary key (only relevant for composite keys) enrollment(student_id, course_id, course_title): course_title depends only on course_id move course_title to course
3NF 2NF, and no transitive dependencies (non-key columns depending on other non-key columns) employee(id, dept_id, dept_name): dept_name depends on dept_id split out a department table

Exercises

Exercise 1: SQL on a library database (15 questions)

The task uses a library database: books, publishers, members (library cards), employees and loan invoices. I renamed things to snake_case and added a return_date column: the questions ask about “not yet returned” and “returned on time”, which can’t be answered correctly without a return date.

publisher(pub_id PK, name, city)
book(book_id PK, title, pub_id FK, price, type)
member(card_no PK, name, address, phone)
employee(emp_id PK, name)
book_invoice(invoice_no PK, book_id FK, card_no FK, emp_id FK,
             date_out DATE, due_date DATE, return_date DATE NULL)  -- one invoice per book

Write each query yourself before opening the solution. The three traps most worth remembering sit under Q2, Q12 and Q13.

Q1. Books borrowed at least once, with their publisher.

Solution to Q1: EXISTS instead of JOIN + DISTINCT
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (SELECT 1 FROM book_invoice i WHERE i.book_id = b.book_id);

EXISTS stops at the first invoice and never produces duplicates. JOIN ... DISTINCT title would wrongly merge two different books that share a title.

Q2. Number of books each publisher has published (including publishers with none).

Solution to Q2: LEFT JOIN and COUNT(b.book_id)
SELECT p.pub_id, p.name, COUNT(b.book_id) AS total_books
FROM publisher p
LEFT JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name;

Q3. Books priced above the average, with their publisher.

Solution to Q3: a subquery for AVG
SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price > (SELECT AVG(price) FROM book);

Q4. Every member and how many books they’ve borrowed (including members who never borrowed).

Solution to Q4: LEFT JOIN and COUNT(i.invoice_no)
SELECT m.card_no, m.name, COUNT(i.invoice_no) AS total_borrowed
FROM member m
LEFT JOIN book_invoice i ON i.card_no = m.card_no
GROUP BY m.card_no, m.name;

Q5. The most and least expensive books, with their publisher (all of them if several share the price).

Solution to Q5: RANK() in both directions
SELECT title, publisher, price
FROM (
  SELECT b.title, p.name AS publisher, b.price,
         RANK() OVER (ORDER BY b.price DESC) AS r_desc,
         RANK() OVER (ORDER BY b.price ASC)  AS r_asc
  FROM book b JOIN publisher p ON p.pub_id = b.pub_id
) t
WHERE r_desc = 1 OR r_asc = 1;

RANK() is a window function: it computes over a “window” of related rows without collapsing them the way GROUP BY does.

Q6. Total number of books published by all publishers.

Solution to Q6: one grand total

The original task phrases this almost exactly like Q2; I read it as one grand total:

SELECT COUNT(*) AS total_books FROM book WHERE pub_id IS NOT NULL;

Q7. Books with at least one loan returned on time, with their publisher.

Solution to Q7: compare return_date with due_date
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (
  SELECT 1 FROM book_invoice i
  WHERE i.book_id = b.book_id
    AND i.return_date IS NOT NULL
    AND i.return_date <= i.due_date
);

Correction: the original answer compares due_date >= date_out. That is practically always true (the due date is always after the loan date), so it says nothing about lateness. “On time” means comparing the actual return date with the due date.

Q8. Members who have borrowed books, with their count, in descending order.

Solution to Q8: INNER JOIN and ORDER BY
SELECT m.*, COUNT(*) AS total_borrowed
FROM member m
JOIN book_invoice i ON i.card_no = m.card_no
GROUP BY m.card_no
ORDER BY total_borrowed DESC;

Correction: the original uses a LEFT JOIN (which includes members who never borrowed, contrary to the task) and has no ORDER BY.

SELECT m.* with GROUP BY m.card_no is valid in MySQL because the columns are functionally dependent on the primary key (knowing the key gives exactly one value for each column).

Q9. Publishers with at least two books.

Solution to Q9: HAVING COUNT(*) >= 2
SELECT p.pub_id, p.name, COUNT(*) AS total_books
FROM publisher p
JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name
HAVING COUNT(*) >= 2;

Q10. Members who borrowed “crime story” books at least twice.

Solution to Q10: INNER JOIN, WHERE, then HAVING
SELECT m.card_no, m.name, COUNT(*) AS crime_borrowed
FROM member m
JOIN book_invoice i ON i.card_no = m.card_no
JOIN book b         ON b.book_id = i.book_id
WHERE b.type = 'crime story'
GROUP BY m.card_no, m.name
HAVING COUNT(*) >= 2;

The original uses a LEFT JOIN and then filters WHERE b.type = .... A condition on the right-hand table turns the LEFT JOIN into an INNER JOIN anyway, so write the INNER JOIN to make the intent clear.

HAVING alias >= 2 only works in MySQL; HAVING COUNT(*) >= 2 works everywhere.

Q11. Books currently on loan and not returned, their loan date and days overdue.

Solution to Q11: return_date IS NULL and GREATEST
SELECT b.title, i.date_out, i.due_date,
       GREATEST(DATEDIFF(CURRENT_DATE, i.due_date), 0) AS days_overdue
FROM book_invoice i
JOIN book b ON b.book_id = i.book_id
WHERE i.return_date IS NULL;

Correction: the original only filters due_date < NOW() and never checks that the book is unreturned, so books returned long ago still show up as “overdue”. GREATEST(..., 0) makes books that aren’t due yet show 0 instead of a negative number.

Q12. Unreturned books more than 7 days overdue, with their publisher.

Solution to Q12: compare due_date with a constant
SELECT b.title, p.name AS publisher, DATEDIFF(CURRENT_DATE, i.due_date) AS days_overdue
FROM book_invoice i
JOIN book b      ON b.book_id = i.book_id
JOIN publisher p ON p.pub_id = b.pub_id
WHERE i.return_date IS NULL
  AND i.due_date < CURRENT_DATE - INTERVAL 7 DAY;

Q13. How many invoices each employee created today (including employees with none).

Solution to Q13: the date condition goes in ON
SELECT e.emp_id, e.name, COUNT(i.invoice_no) AS invoices_today
FROM employee e
LEFT JOIN book_invoice i
       ON i.emp_id = e.emp_id
      AND i.date_out >= CURRENT_DATE
      AND i.date_out <  CURRENT_DATE + INTERVAL 1 DAY
GROUP BY e.emp_id, e.name;

Q14. Books from Hanoi publishers that have been borrowed, with their loan count.

Solution to Q14: join three tables, then GROUP BY
SELECT b.book_id, b.title, COUNT(*) AS times_borrowed
FROM book b
JOIN publisher p    ON p.pub_id = b.pub_id
JOIN book_invoice i ON i.book_id = b.book_id
WHERE p.city = 'Hanoi'
GROUP BY b.book_id, b.title;

Q15. Books from Hanoi publishers priced below the average.

Solution to Q15: the task is ambiguous about “the average”
SELECT b.title, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE p.city = 'Hanoi'
  AND b.price < (SELECT AVG(price) FROM book);

The task is ambiguous: the average of all books, or of Hanoi books? I took it literally (all books). If it means Hanoi books, the subquery must also filter on city = 'Hanoi'. At work, ask about ambiguities like this instead of guessing.

Remember

  • Can recite the logical evaluation order of a SELECT and explain why an alias can’t be used in WHERE.
  • Can explain why a right-table condition must go in ON when unmatched rows have to be kept.
  • Know when to use COUNT(*), COUNT(col) and COUNT(DISTINCT col).
  • Can spot a non-sargable condition and rewrite it as a range.
  • Know when to use EXISTS instead of JOIN ... DISTINCT, and RANK instead of two subqueries.

Interview questions

Junior

WHERE vs HAVING?

WHERE filters individual rows before GROUP BY; HAVING filters groups after aggregation.

WHERE can’t use aggregate functions, while HAVING can use COUNT(*) >= 2. If a filter can go in WHERE, put it there: fewer rows to group.

How do INNER JOIN and LEFT JOIN differ? Give an example that needs a LEFT JOIN.

INNER JOIN keeps only the row pairs that match in both tables; LEFT JOIN keeps every row of the left table, filling the right-hand columns with NULL when there’s no match.

You need a LEFT JOIN when the result must include entities that “have nothing”: every publisher with its book count, including publishers with no books (count with COUNT(b.book_id) to get 0).

Mid

Why does putting a right-table condition in WHERE break a LEFT JOIN?

Because WHERE runs after the join and drops the unmatched rows, so the LEFT JOIN becomes an INNER JOIN.

A LEFT JOIN keeps unmatched left rows by filling the right side with NULLs. A condition such as i.date_out = today evaluates to unknown for NULL, so the row is dropped. To keep unmatched rows (so they count as 0), put the condition in the ON clause.

How do COUNT(*), COUNT(col) and COUNT(DISTINCT col) differ?

COUNT(*) counts rows, COUNT(col) only counts rows where col isn’t NULL, and COUNT(DISTINCT col) counts distinct values.

  • COUNT(*) counts even rows full of NULLs. After a LEFT JOIN, COUNT(right_column) gives 0 for an unmatched row while COUNT(*) gives 1.
  • COUNT(DISTINCT col) is how you counter fan-out (parent rows multiplied by joining down to a child table), for example counting chapters after joining down to lessons.
  • Performance: InnoDB keeps no stored row count, so COUNT(*) on a big table still scans an index.
How do ROW_NUMBER, RANK and DENSE_RANK differ?

All three are window functions that number rows by the ORDER BY inside OVER; they differ in how they treat ties.

For scores 10, 9, 9, 8:

  • ROW_NUMBER gives 1, 2, 3, 4: always distinct. Which of two equal rows comes first is undefined without a tie-breaker.
  • RANK gives 1, 2, 2, 4: ties, then a gap.
  • DENSE_RANK gives 1, 2, 2, 3: ties, no gap.

“Top 5 including ties” uses DENSE_RANK or RANK depending on the reading. “Exactly one latest row per group” uses ROW_NUMBER() OVER (PARTITION BY ...).

Senior

When would you deliberately denormalise data?

When an important read has to join many tables or re-aggregate constantly, or when data must be frozen at a point in time.

  • Heavy reads: course progress, comment counts, order totals.
  • Frozen data: prices in an order, the shipping address.
  • The conditions: you know the source of truth, you have a sync mechanism (same transaction, events, a reconciliation job), and the extra write cost or lag is acceptable.
A report query using DATE(created_at) = CURDATE() keeps getting slower. Why, and how do you fix it?

Wrapping the column in a function makes the predicate non-sargable, so MySQL scans everything; fix it by rewriting the condition as a range on the bare column.

  • Why: MySQL can’t use the B-tree index on created_at, so it gets slower as the table grows.
  • Fix: created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY, confirmed with EXPLAIN.
  • Alternatives: a functional index (MySQL 8.0.13+) or an indexed generated column. Rewriting the condition is still the simplest.

Useful phrases for interviews

  • “Filters on the right-hand table go in the ON clause, otherwise the LEFT JOIN silently becomes an INNER JOIN.”
  • “I’d rewrite that predicate as a date range so it stays sargable and can use the index.”
  • “Joining down to the child table fans out the rows, so I count distinct ids or aggregate in a CTE first.”
  • “The requirement is ambiguous here, so I’d confirm which average they mean before writing the query.”
  • “WHERE filters rows before grouping, HAVING filters groups after aggregation.”
  • “I count the right-hand column, not star, so groups without matches come out as zero.”
  • “For top-N with ties I use DENSE_RANK instead of LIMIT, otherwise the cut-off is arbitrary.”
  • “I normalise by default and denormalise on purpose, once I know who keeps the copy in sync.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.