First Queries with SQL

Introduction

With the database built, we can ask questions. SQL is the language for this. If you took the SQL courses on this site, it will feel familiar. Here we use it from Python, with pandas to display the results as tables. The outputs below are from real runs on our practice data.

A helper to run a query and print a table:

import pandas as pd

def q(sql, *params):
    print(pd.read_sql_query(sql, con, params=params).to_string(index=False))

How many loans?

q("SELECT COUNT(*) AS loans FROM loans")

 loans
    30

Books per genre

q("""SELECT genre, COUNT(*) AS books
     FROM books
     GROUP BY genre
     ORDER BY books DESC, genre""")

  genre  books
  Crime      2
Fiction      2
   Food      2
   Home      2

GROUP BY puts rows into groups, and COUNT(*) counts each group. Every genre has two books, so the library is evenly balanced. We added genre to ORDER BY so tied counts have a fixed order.

Joining tables: the most borrowed books

Loans only store book ids. To show titles, join the tables:

q("""SELECT b.title, COUNT(*) AS loans
     FROM loans l
     JOIN books b ON b.id = l.book_id
     GROUP BY b.id
     ORDER BY loans DESC, b.title
     LIMIT 3""")

      title  loans
  Cold Case      6
Night Train      5
Paper Moons      4

JOIN ... ON matches each loan to its book. The two Crime books are the most borrowed. LIMIT 3 keeps the top three.

Loans by city

q("""SELECT m.city, COUNT(*) AS loans
     FROM loans l
     JOIN members m ON m.id = l.member_id
     GROUP BY m.city
     ORDER BY loans DESC""")

 city  loans
Leeds     18
 York     11
 Hull      1

Leeds has the most loans. But look closer: Leeds has three members, York two and Hull one. So more loans per city may partly reflect more members. A good analyst asks: "Is this a fair comparison?" Loans per member would be fairer.

Books still out

q("SELECT COUNT(*) AS still_out FROM loans WHERE returned_on IS NULL")

 still_out
         3

IS NULL finds empty values. You cannot test for empty with = NULL.

Read every result critically

After each query, ask:

1. Does the number make sense? 18 + 11 + 1 = 30, matching the total loans.
2. Is the comparison fair? Think about group sizes.
3. What is missing? Rows with missing values can drop out of calculations.
4. Could I check it another way?

With only 30 loans, any pattern here is just practice. Real conclusions need more data, as you will see in later courses.

Try it yourself

Write a query that counts loans per member name, and one that finds the books that have never been borrowed (hint: a LEFT JOIN and WHERE l.id IS NULL). Check that each result adds up.

Summary

• GROUP BY with COUNT answers "how many in each group?", and JOIN links tables through ids.
• Use IS NULL to find missing values, and order results with ORDER BY and LIMIT.
• Check each result: do the parts add up to the total, and is the comparison fair?