Back to Worked Examples
Intermediate SQL

SQL Query Building

Problem Statement

Using the library database from the ERD example, write SQL queries to:

  1. List all books in alphabetical order by title
  2. Find all students in Year 10
  3. Show all loans that are overdue (not returned and past due date)
  4. Count how many books each student has borrowed
  5. Find which students borrowed a specific book

Students

StudentID, FirstName, LastName, YearLevel, Email

Books

BookID, Title, Author, ISBN, Genre

Loans

LoanID, StudentID, BookID, BorrowDate, DueDate, ReturnDate

Step-by-Step Solutions

1

List all books alphabetically by title

A simple SELECT with ORDER BY. Use ASC for A-Z order. 

SELECT Title, Author, Genre
FROM Books
ORDER BY Title ASC;

Breakdown: SELECT columns → FROM table → ORDER BY to sort

2

Find all students in Year 10

Add a WHERE clause to filter by year level. 

SELECT StudentID, FirstName, LastName, Email
FROM Students
WHERE YearLevel = 10;

Note: Use = for exact matches. Numbers don't need quotes.

3

Show all overdue loans

An overdue loan: ReturnDate is NULL (not returned) AND DueDate is in the past. 

SELECT LoanID, StudentID, BookID, BorrowDate, DueDate
FROM Loans
WHERE ReturnDate IS NULL
AND DueDate < CURRENT_DATE;

Key Points:

  • • Use IS NULL to check for empty values (not = NULL)
  • • CURRENT_DATE gives today's date
  • • AND requires both conditions to be true
4

Count books borrowed by each student

Use COUNT() with GROUP BY. Join to Students to get names. 

SELECT
    s.FirstName,
    s.LastName,
    COUNT(l.LoanID) AS BooksBorrowed
FROM Students s
LEFT JOIN Loans l ON s.StudentID = l.StudentID
GROUP BY s.StudentID, s.FirstName, s.LastName
ORDER BY BooksBorrowed DESC;

Step-by-step explanation:

  1. LEFT JOIN includes students with no loans (shows 0)
  2. s and l are table aliases for shorter code
  3. COUNT(l.LoanID) counts loans for each group
  4. GROUP BY creates groups for each student
  5. AS BooksBorrowed renames the count column
5

Find students who borrowed a specific book

Join all three tables to connect students to books via loans. 

SELECT
    s.FirstName,
    s.LastName,
    b.Title,
    l.BorrowDate
FROM Students s
INNER JOIN Loans l ON s.StudentID = l.StudentID
INNER JOIN Books b ON l.BookID = b.BookID
WHERE b.Title = 'The Hobbit'
ORDER BY l.BorrowDate DESC;

Multi-table JOIN pattern: 

Students ← (StudentID) ← Loans → (BookID) → Books

The Loans table acts as a bridge between Students and Books.

★

Bonus: Most popular books (borrowed 5+ times)

SELECT
    b.Title,
    b.Author,
    COUNT(l.LoanID) AS TimesBorrowed
FROM Books b
INNER JOIN Loans l ON b.BookID = l.BookID
GROUP BY b.BookID, b.Title, b.Author
HAVING COUNT(l.LoanID) >= 5
ORDER BY TimesBorrowed DESC;

HAVING vs WHERE: Use HAVING to filter groups after GROUP BY. WHERE filters individual rows before grouping.

SQL Query Building Template 

SELECT columns          -- What do you want to see?
FROM table             -- Which table(s)?
JOIN table ON ...      -- Need data from multiple tables?
WHERE conditions       -- Filter individual rows
GROUP BY columns       -- Summarize by groups?
HAVING conditions      -- Filter the groups?
ORDER BY column        -- Sort the results?
LIMIT number;          -- Restrict how many results?

Common Mistakes to Avoid

  • ✗ Using = NULL instead of IS NULL
  • ✗ Forgetting quotes around text values: WHERE Title = The Hobbit (wrong)
  • ✗ Using WHERE to filter groups instead of HAVING
  • ✗ SELECT columns not in GROUP BY (without aggregate functions)
  • ✗ Missing the ON clause in JOIN statements
  • ✗ Using INNER JOIN when you need LEFT JOIN (losing unmatched rows)