Analyze a Bookstore Database with SQL

Query a PostgreSQL bookstore database and analyze sales data with pandas.

Introduction

30 Second Summary

Every bookstore needs to know which books are flying off shelves and which are gathering dust. Real businesses use databases and data analysis tools to answer these questions every day.

In this project, you will build a sales analysis system for a fictional bookstore using PostgreSQL and Python's pandas library. You will create a database, fill it with sample data, then write queries and scripts to uncover sales trends.

What You'll Build

A data analysis pipeline that connects a PostgreSQL database to Python, letting you query bookstore sales data with SQL and analyze it with pandas DataFrames.

By the end of this project, you'll have:

A PostgreSQL database with a normalized schema (3 tables with foreign key relationships).
SQL queries that join tables, aggregate data, and filter results.
A Python script that pulls SQL results into pandas DataFrames for analysis.
Secret Mission: A low stock alert system that identifies books running low and exports the list to a CSV file.

Are there any prerequisites?

None. This project includes full setup instructions for every tool you need, including PostgreSQL, Python, and VS Code.

Install PostgreSQL

To analyze our bookstore data, we need somewhere to store it. PostgreSQL is an open-source relational database used by companies worldwide to manage production data.

In this step, you will install PostgreSQL 17 on your Windows machine along with pgAdmin 4, a graphical interface for managing your databases.

In this step, get ready to:

Install PostgreSQL 17 and pgAdmin 4.
Verify PostgreSQL is accessible from the command line.
Connect to your local PostgreSQL server in pgAdmin 4.

Check and install PostgreSQL 17

This project requires PostgreSQL version 17 specifically. Let's first check if you already have it installed.

Press the Windows key to open your search bar.
Type Command Prompt and press Enter to open it.
Check your PostgreSQL version by running this command:

psql --version

✔️ I see a version starting with 17

You should see output like psql (PostgreSQL) 17.10 or similar. PostgreSQL 17 is already installed and on your PATH.

Skip ahead to the "Connect to your server in pgAdmin 4" section below.

ⓧ I see an older version

If you see a version number lower than 17 (for example psql (PostgreSQL) 16.14), you need to install PostgreSQL 17. The new installer will upgrade your existing installation.

Open this link in your browser: https://www.postgresql.org/download/windows/
Click Download the installer to go to the EDB downloads page.
Select the installer for PostgreSQL version 17 (Windows x86-64).
Run the downloaded installer file.

The installer will walk you through several screens:

On the Select Components screen, make sure PostgreSQL Server, pgAdmin 4, and Command Line Tools are all checked.
On the Password screen, set a password for the PostgreSQL superuser. Write this down somewhere safe. You will need it later.
On the Port screen, keep the default value 5432.
Accept the defaults on all remaining screens and click Next until installation completes.
When the installer offers to launch Stack Builder at the end, uncheck it and click Finish. You do not need Stack Builder for this project.

Why remember that password?

The password you set is for the postgres superuser account. This is the master account that controls your entire PostgreSQL server. You will use it to connect from pgAdmin 4 and from your Python script later.

ⓧ Command not found

If you see an error saying the command is not recognized, PostgreSQL is not installed yet. Let's install it now.

Open this link in your browser: https://www.postgresql.org/download/windows/
Click Download the installer to go to the EDB downloads page.
Select the installer for PostgreSQL version 17 (Windows x86-64).
Run the downloaded installer file.

The installer will walk you through several screens:

On the Select Components screen, make sure PostgreSQL Server, pgAdmin 4, and Command Line Tools are all checked.
On the Password screen, set a password for the PostgreSQL superuser. Write this down somewhere safe. You will need it later.
On the Port screen, keep the default value 5432.
Accept the defaults on all remaining screens and click Next until installation completes.
When the installer offers to launch Stack Builder at the end, uncheck it and click Finish. You do not need Stack Builder for this project.

Why remember that password?

Verify PostgreSQL is on your PATH

The EDB installer does not always add PostgreSQL to your system PATH automatically. Let's verify it works.

Close your current Command Prompt window completely.
Open a new Command Prompt (press the Windows key, type Command Prompt, press Enter).
Run the version check again:

psql --version

You should see output like psql (PostgreSQL) 17.10 confirming PostgreSQL 17 is accessible.

Still seeing 'not recognized'?

If psql is still not recognized after installing, you need to manually add PostgreSQL to your system PATH. Follow these steps:

Press the Windows key and search for Edit the system environment variables. Click on it.
In the System Properties window, click Environment Variables.
Under System variables, find and select Path, then click Edit.
Click New and paste this path:

C:\Program Files\PostgreSQL\17\bin

Click OK on all open windows to save.
Close and reopen Command Prompt, then run psql --version again.

If you are still stuck, help me fix my PostgreSQL PATH on Windows.

Connect to your server in pgAdmin 4

pgAdmin 4 is a graphical tool for managing PostgreSQL databases. It was installed alongside PostgreSQL by the EDB installer.

Press the Windows key to open your search bar.
Type pgAdmin 4 and press Enter to open it.

What is the pgAdmin master password?

When pgAdmin 4 opens for the first time, it asks you to set a master password. This is separate from your PostgreSQL superuser password. The master password protects your saved server connections inside pgAdmin itself.

Set any password you will remember. You can use the same password you chose during PostgreSQL installation if you prefer to keep things simple.

Set a master password when prompted.
In the left sidebar, expand Servers.
Click on PostgreSQL 17. If prompted for a password, enter the superuser password you set during installation.
Expand Databases in the left sidebar.

You should see the default postgres database listed under Databases. This confirms pgAdmin 4 is connected to your local PostgreSQL server.

Don't see PostgreSQL 17 under Servers?

If the server is not listed, try right-clicking Servers and selecting Register then Server. In the dialog, enter PostgreSQL 17 as the name. On the Connection tab, set the host to localhost, port to 5432, and username to postgres. Enter your superuser password and click Save.

If you are still stuck, help me connect pgAdmin 4 to my local PostgreSQL server.

Your PostgreSQL server is running and ready. Next up, you will set up Python and the libraries needed to query your database from code.

Set Up Python and Libraries

Your PostgreSQL database is installed and running. Now you need the tools to talk to it from Python.

In this step, you will set up Python, create an isolated project environment, and install the libraries that let Python communicate with PostgreSQL. pandas handles data analysis, and SQLAlchemy manages the database connection.

In this step, get ready to:

Confirm Python 3.9+ is installed on your machine.
Create a project folder and virtual environment.
Install pandas, SQLAlchemy, and psycopg2-binary.

Check your Python installation

This project requires Python 3.9 or higher. Let's confirm which version you have.

Open a terminal window.
Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.

Check your Python version by running this command:

macOS

python3 --version

Windows

python --version

✔️ I see version 3.9 or higher

You should see output like:

Python 3.11.4

As long as the number is 3.9 or above, you are good to go.

ⓧ I see a version lower than 3.9

If you see a version like 3.8 or 2.7, you need to install a newer Python.

Go to https://www.python.org/downloads/ in your browser.
Download the latest Python installer for your operating system.
Run the installer. On Windows, check the box that says Add python.exe to PATH before clicking Install Now.
Close and reopen your terminal, then run the version check command again to confirm it shows 3.9 or higher.

macOS users with an old system Python

macOS sometimes ships with an older Python that shadows newer installs. After installing the latest version, try running python3.11 --version or python3.12 --version to access the version you just installed.

ⓧ Command not found

Python is not installed yet. Let's install it now.

Go to https://www.python.org/downloads/ in your browser.
Download the latest Python installer for your operating system.
Run the installer. On Windows, check the box that says Add python.exe to PATH before clicking Install Now.
Close and reopen your terminal, then run the version check command again to confirm Python is recognized.

Still seeing 'command not found' after installing?

On Windows, the installer may not have added Python to your PATH. Search for Environment Variables in the Start menu, open it, and add C:\Users\YourName\AppData\Local\Programs\Python\Python311\ to your PATH variable. Then restart your terminal.

help me fix Python not being recognized in my terminal

Now that Python is confirmed, let's create a dedicated folder for your project.

Move to your Desktop by running this command:

macOS

cd ~/Desktop

Windows

cd %USERPROFILE%\Desktop

Create your project folder and move into it by running these commands:

mkdir bookstore-project
cd bookstore-project

A virtual environment keeps this project's packages separate from everything else on your computer. This avoids version conflicts between projects.

Create a virtual environment by running this command:

macOS

python3 -m venv bookstore-env

Windows

python -m venv bookstore-env

Activate the virtual environment by running this command:

macOS

source bookstore-env/bin/activate

Windows

bookstore-env\Scripts\activate

Confirm the virtual environment is active. You should see (bookstore-env) at the start of your terminal prompt.

What does (bookstore-env) mean?

The prefix in your prompt tells you that any packages you install with pip will go into this environment only. When you close the terminal or run deactivate, you leave the environment.

Don't see (bookstore-env) in your prompt?

Make sure you ran the activate command from inside the bookstore-project folder. On Windows, if you get a permissions error in PowerShell, try using Command Prompt instead.

help me activate my Python virtual environment

Install the required Python packages

You need three packages. pandas is the data analysis library. sqlalchemy manages database connections. psycopg2-binary is the driver that lets Python speak to PostgreSQL.

Install all three packages by running this command:

pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

Why pin exact version numbers?

The == syntax locks each package to a specific version. This ensures your project behaves the same way regardless of when you install it. Without pinning, a newer release could introduce breaking changes.

Wait for the installation to finish. You should see a Successfully installed message listing all three packages.

Seeing a permissions error or install failure?

Make sure your virtual environment is active. You should see (bookstore-env) in your prompt. If the environment is not active, packages try to install system-wide and may fail due to permissions.

help me fix pip install errors in my virtual environment

Create the analysis script in VS Code

With your packages installed, you need a Python file to write your analysis code in. You will use VS Code as your code editor.

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Visual Studio Code and press Enter to open it.
In VS Code, click File in the top menu bar.
Click Open Folder.
Navigate to your Desktop and select the bookstore-project folder, then click Open.

Create a new file by clicking File in the top menu bar, then New File.
Save the file immediately with Cmd+S (macOS) or Ctrl+S (Windows).
Name the file bookstore_analysis.py and save it inside the bookstore-project folder.

Add the following import lines to bookstore_analysis.py:

import pandas as pd
from sqlalchemy import create_engine, text

What do these imports do?

import pandas as pd loads the pandas library and gives it the shorthand pd. This is the standard convention in the data science community.

from sqlalchemy import create_engine, text pulls in two specific tools. create_engine sets up the database connection. text lets you write raw SQL queries safely.

Save bookstore_analysis.py with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm the file appears in the VS Code sidebar under the bookstore-project folder.

Don't see the file in the sidebar?

Make sure you opened the bookstore-project folder in VS Code (not your Desktop or a parent folder). The file must be saved inside that folder to appear in the Explorer panel.

help me find my file in VS Code

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before moving on.

ⓧ I'd like to double check the full code

Here is the complete content of bookstore_analysis.py at this point:

import pandas as pd
from sqlalchemy import create_engine, text

Your Python environment is locked and loaded. Next up, you will create the bookstore database and fill it with tables full of books, authors, and sales records.

Create the Database and Schema

Your Python environment is ready to talk to a database. Now you need the database itself.

In this step, you will create a PostgreSQL database called bookstore and define three related tables inside it: one for authors, one for books, and one for sales records. You will also populate the tables with sample data so you have something to analyze in later steps.

In this step, get ready to:

Create a new database in pgAdmin 4.
Define three tables with foreign key relationships.
Insert sample data spanning 5 authors, 12 books, and 30 sales records.

Create the bookstore database

Before you can create tables, you need a dedicated database to hold them. The default postgres database is for system use. You will create a new one called bookstore.

Switch back to pgAdmin 4 in your browser.
In the left-hand tree (the Object Explorer), expand your server node until you see the Databases node.
Right-click Databases and select Create then Database....
In the General tab, type bookstore into the Database field.
Leave all other settings at their defaults.
Click Save.

You should see bookstore appear under the Databases node in the tree.

Click on the bookstore database in the tree to select it.
Open the Query Tool by clicking Tools in the top menu bar, then selecting Query Tool.

A new Query Tool tab opens, connected to your bookstore database. This is where you will write and run SQL.

What is a Query Tool?

The Query Tool is pgAdmin's built-in SQL editor. You type SQL in the upper panel, click the play/execute button, and results appear in the lower panel. It connects directly to whichever database you selected in the tree.

Don't see the bookstore database?

Right-click the Databases node and click Refresh. If it still does not appear, make sure you clicked Save (not just closed the dialog).

Help me create the bookstore database in pgAdmin

Create the three tables

A relational database stores data in tables that reference each other through foreign keys. Your bookstore needs three tables: authors (who wrote the book), books (what is being sold), and sales (individual sale transactions).

In the Query Tool editor panel, paste the following SQL to create all three tables:

-- Create the bookstore database schema
-- Run this in pgAdmin 4 Query Tool connected to the 'bookstore' database

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    genre VARCHAR(50) NOT NULL,
    price NUMERIC(6,2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES books(id),
    quantity INTEGER NOT NULL,
    sale_date DATE NOT NULL
);

What does this SQL do?

Each CREATE TABLE statement defines a table structure. SERIAL PRIMARY KEY creates an auto-incrementing ID column. NOT NULL means the column cannot be left empty.

The REFERENCES keyword creates a foreign key constraint. For example, author_id INTEGER NOT NULL REFERENCES authors(id) means every book must point to a valid author. This enforces data integrity at the database level.

Click the execute/play button (the triangle icon) in the Query Tool toolbar to run the SQL.
Check the Messages panel at the bottom. You should see messages confirming that each table was created successfully.

Seeing a syntax error?

Make sure you pasted all three CREATE TABLE statements including the semicolons at the end of each one. If you see a "relation already exists" error, it means the tables were already created from a previous attempt. You can drop them first by running DROP TABLE IF EXISTS sales, books, authors; and then re-running the CREATE statements.

Help me fix my CREATE TABLE SQL error

Insert sample data

Empty tables are not useful for analysis. You will now populate the database with fictional bookstore data: 5 authors from different countries, 12 books across 4 genres, and 30 sales transactions spanning January through March 2025.

Clear the Query Tool editor (select all text and delete it).
Paste the following INSERT statements to populate all three tables:

-- Insert sample authors
INSERT INTO authors (name, country) VALUES
    ('Elena Marchetti', 'Italy'),
    ('James Okafor', 'Nigeria'),
    ('Yuki Tanaka', 'Japan'),
    ('Sarah Mitchell', 'United States'),
    ('Carlos Rivera', 'Mexico');

-- Insert sample books
INSERT INTO books (title, author_id, genre, price, stock) VALUES
    ('The Last Garden', 1, 'Fiction', 14.99, 8),
    ('Shadows of Rome', 1, 'Historical', 18.50, 3),
    ('Code of the Savanna', 2, 'Thriller', 12.99, 15),
    ('Lagos Nights', 2, 'Fiction', 11.50, 2),
    ('Digital Horizons', 2, 'Science Fiction', 16.00, 0),
    ('Whispers of Kyoto', 3, 'Fiction', 13.99, 7),
    ('The Quantum Garden', 3, 'Science Fiction', 19.99, 4),
    ('Beneath the Stars', 4, 'Fiction', 10.99, 12),
    ('The Silent Witness', 4, 'Thriller', 14.50, 1),
    ('American Echoes', 4, 'Historical', 17.99, 6),
    ('Midnight in Oaxaca', 5, 'Thriller', 13.50, 9),
    ('The Painted Desert', 5, 'Historical', 15.99, 3);

-- Insert sample sales (spanning January to March 2025)
INSERT INTO sales (book_id, quantity, sale_date) VALUES
    (1, 3, '2025-01-05'),
    (1, 2, '2025-01-18'),
    (2, 1, '2025-01-10'),
    (3, 4, '2025-01-12'),
    (3, 2, '2025-01-25'),
    (4, 3, '2025-01-20'),
    (5, 2, '2025-01-28'),
    (6, 3, '2025-02-02'),
    (6, 1, '2025-02-14'),
    (7, 2, '2025-02-05'),
    (8, 5, '2025-02-08'),
    (8, 3, '2025-02-20'),
    (9, 2, '2025-02-12'),
    (10, 1, '2025-02-18'),
    (11, 4, '2025-02-22'),
    (11, 2, '2025-02-28'),
    (12, 3, '2025-03-01'),
    (1, 4, '2025-03-05'),
    (2, 2, '2025-03-08'),
    (3, 3, '2025-03-10'),
    (4, 1, '2025-03-12'),
    (5, 3, '2025-03-15'),
    (6, 2, '2025-03-18'),
    (7, 1, '2025-03-20'),
    (8, 2, '2025-03-22'),
    (9, 3, '2025-03-25'),
    (10, 2, '2025-03-26'),
    (11, 1, '2025-03-28'),
    (12, 2, '2025-03-30'),
    (1, 1, '2025-03-31');

What does this data represent?

The authors table holds 5 authors from 5 different countries. The books table holds 12 titles across 4 genres (Fiction, Historical, Thriller, Science Fiction). Each book links to an author via author_id.

The sales table has 30 transactions spread across January, February, and March 2025. Each sale links to a book via book_id. This gives you enough data to find meaningful trends across months, genres, and authors.

Click the execute/play button to run all three INSERT statements.
Check the Messages panel. You should see confirmations like "INSERT 0 5", "INSERT 0 12", and "INSERT 0 30" indicating rows were inserted.

Verify the data loaded correctly by clearing the editor and running this quick query:

SELECT COUNT(*) FROM authors;
SELECT COUNT(*) FROM books;
SELECT COUNT(*) FROM sales;

You should see results of 5, 12, and 30 in the Data Output panel for each query.

Getting a foreign key violation?

Foreign key errors mean you are trying to insert a book that references an author ID that does not exist, or a sale referencing a book ID that does not exist. Make sure you ran the INSERT statements in order: authors first, then books, then sales.

If your tables have partial data from a failed attempt, run DELETE FROM sales; DELETE FROM books; DELETE FROM authors; to clear everything out, then re-run all three INSERT statements together.

Help me fix my INSERT data errors

✔️ Awesome, I've got everything!

Great. Make sure you see 5 authors, 12 books, and 30 sales when you run the COUNT queries above.

ⓧ I'd like to double check the full code

Here is the full SQL you should have run in the pgAdmin Query Tool against your bookstore database:

-- Create the bookstore database schema
-- Run this in pgAdmin 4 Query Tool connected to the 'bookstore' database

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    genre VARCHAR(50) NOT NULL,
    price NUMERIC(6,2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES books(id),
    quantity INTEGER NOT NULL,
    sale_date DATE NOT NULL
);

-- Insert sample authors
INSERT INTO authors (name, country) VALUES
    ('Elena Marchetti', 'Italy'),
    ('James Okafor', 'Nigeria'),
    ('Yuki Tanaka', 'Japan'),
    ('Sarah Mitchell', 'United States'),
    ('Carlos Rivera', 'Mexico');

-- Insert sample books
INSERT INTO books (title, author_id, genre, price, stock) VALUES
    ('The Last Garden', 1, 'Fiction', 14.99, 8),
    ('Shadows of Rome', 1, 'Historical', 18.50, 3),
    ('Code of the Savanna', 2, 'Thriller', 12.99, 15),
    ('Lagos Nights', 2, 'Fiction', 11.50, 2),
    ('Digital Horizons', 2, 'Science Fiction', 16.00, 0),
    ('Whispers of Kyoto', 3, 'Fiction', 13.99, 7),
    ('The Quantum Garden', 3, 'Science Fiction', 19.99, 4),
    ('Beneath the Stars', 4, 'Fiction', 10.99, 12),
    ('The Silent Witness', 4, 'Thriller', 14.50, 1),
    ('American Echoes', 4, 'Historical', 17.99, 6),
    ('Midnight in Oaxaca', 5, 'Thriller', 13.50, 9),
    ('The Painted Desert', 5, 'Historical', 15.99, 3);

-- Insert sample sales (spanning January to March 2025)
INSERT INTO sales (book_id, quantity, sale_date) VALUES
    (1, 3, '2025-01-05'),
    (1, 2, '2025-01-18'),
    (2, 1, '2025-01-10'),
    (3, 4, '2025-01-12'),
    (3, 2, '2025-01-25'),
    (4, 3, '2025-01-20'),
    (5, 2, '2025-01-28'),
    (6, 3, '2025-02-02'),
    (6, 1, '2025-02-14'),
    (7, 2, '2025-02-05'),
    (8, 5, '2025-02-08'),
    (8, 3, '2025-02-20'),
    (9, 2, '2025-02-12'),
    (10, 1, '2025-02-18'),
    (11, 4, '2025-02-22'),
    (11, 2, '2025-02-28'),
    (12, 3, '2025-03-01'),
    (1, 4, '2025-03-05'),
    (2, 2, '2025-03-08'),
    (3, 3, '2025-03-10'),
    (4, 1, '2025-03-12'),
    (5, 3, '2025-03-15'),
    (6, 2, '2025-03-18'),
    (7, 1, '2025-03-20'),
    (8, 2, '2025-03-22'),
    (9, 3, '2025-03-25'),
    (10, 2, '2025-03-26'),
    (11, 1, '2025-03-28'),
    (12, 2, '2025-03-30'),
    (1, 1, '2025-03-31');

Your database is fully loaded with sample data. Next up, you will connect Python to this database and start pulling data into pandas DataFrames for analysis.

Connect Python to PostgreSQL

Your PostgreSQL database is populated with authors, books, and sales records. Now it's time to bring Python into the picture.

In this step, you'll create a bridge between your database and Python using SQLAlchemy. Once connected, you'll pull query results directly into pandas DataFrames for analysis.

In this step, get ready to:

Create a SQLAlchemy engine and test your database connection.
Load your books table into a pandas DataFrame.
Write a JOIN query that combines books with their authors.

Create the database engine and test the connection

A SQLAlchemy engine is the starting point for any database connection in Python. It holds the connection details (host, port, database name, credentials) and manages how Python talks to PostgreSQL.

Switch back to VS Code with bookstore_analysis.py open.
Below the two import lines, add the following code to create the engine and test the connection:

# Connect to PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/bookstore")

# Test the connection
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print("Connection successful!" if result.fetchone() else "Connection failed!")

What does this code do?

create_engine() builds a connection manager using a URL that tells Python which driver to use (psycopg2), which user and password to authenticate with, and which database to target.

The with engine.connect() block opens a connection, runs SELECT 1 (the simplest possible query), and prints whether it succeeded. The text() wrapper is required by SQLAlchemy 2.0 for raw SQL strings.

What about the password in the connection string?

Replace your-password-here with the superuser password you set during PostgreSQL installation. This is the same password you used to log into pgAdmin 4.

Save bookstore_analysis.py.
Run the script in your terminal by running:

python bookstore_analysis.py

You should see Connection successful! printed in the terminal.

Seeing a connection error?

Double-check that you replaced your-password-here with your actual PostgreSQL password.
Make sure the database name is bookstore (all lowercase). This must match the database you created in pgAdmin.
Confirm that your PostgreSQL server is running. Open pgAdmin 4 and check that you can still see your tables.
Make sure your virtual environment is active. You should see (bookstore-env) at the start of your terminal prompt.

Why can't Python connect to my PostgreSQL database?

Load the books table into a DataFrame

With the connection confirmed, you can now pull data from PostgreSQL directly into a pandas DataFrame. The pd.read_sql() function takes a SQL query and a connection, then returns the results as a table you can manipulate in Python.

In bookstore_analysis.py, add the following code below the connection test block:

# Load the books table into a DataFrame
df_books = pd.read_sql("SELECT * FROM books", con=engine)
print("\n=== All Books ===")
print(df_books)

What does this code do?

pd.read_sql() sends your SQL query to PostgreSQL, receives the result rows, and wraps them in a DataFrame. You pass the SQLAlchemy engine as the con argument so pandas knows where to send the query.

SELECT * FROM books fetches every column and every row from the books table. The result is a full Python table you can filter, sort, and summarize.

Save bookstore_analysis.py and run the script again:

python bookstore_analysis.py

You should see Connection successful! followed by a table of all 12 books with columns for id, title, author_id, genre, price, and stock.

Not seeing the books table?

Make sure you ran the INSERT statements in pgAdmin in the previous step. Go back to pgAdmin, open the Query Tool for the bookstore database, and run SELECT * FROM books; to confirm data exists.
Check that you're connecting to the bookstore database in your connection string, not the default postgres database.

Help me debug why pd.read_sql is returning empty results.

Write a JOIN query to combine books and authors

Right now, the books table only shows an author_id number. A SQL JOIN lets you combine two related tables so you can see the author's name alongside each book title.

In bookstore_analysis.py, add the following code below the print(df_books) line:

# JOIN: Books with their authors
query_books_authors = """
    SELECT books.title, authors.name AS author, books.genre, books.price
    FROM books
    JOIN authors ON books.author_id = authors.id
"""
df_books_authors = pd.read_sql(query_books_authors, con=engine)
print("\n=== Books with Authors ===")
print(df_books_authors)

What does this query do?

JOIN authors ON books.author_id = authors.id tells PostgreSQL to match each book with the author whose id equals the book's author_id. This is why you created foreign keys in your schema.

AS author renames the authors.name column to author in the result. This makes the DataFrame column name cleaner.

Save bookstore_analysis.py and run the script again:

python bookstore_analysis.py

You should now see three outputs: the connection message, the full books table, and a new table showing each book's title, author name, genre, and price.

Seeing a syntax error in the query?

Check that the triple-quoted string starts with """ and ends with """. Missing one quote character breaks the string.
Make sure the table names are lowercase: books and authors. PostgreSQL folds unquoted identifiers to lowercase.

Help me fix my SQL JOIN query in Python.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here's the complete bookstore_analysis.py file so far:

import pandas as pd
from sqlalchemy import create_engine, text

# Connect to PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/bookstore")

# Test the connection
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print("Connection successful!" if result.fetchone() else "Connection failed!")

# Load the books table into a DataFrame
df_books = pd.read_sql("SELECT * FROM books", con=engine)
print("\n=== All Books ===")
print(df_books)

# JOIN: Books with their authors
query_books_authors = """
    SELECT books.title, authors.name AS author, books.genre, books.price
    FROM books
    JOIN authors ON books.author_id = authors.id
"""
df_books_authors = pd.read_sql(query_books_authors, con=engine)
print("\n=== Books with Authors ===")
print(df_books_authors)

Your Python-to-PostgreSQL pipeline is working end-to-end. Next up, you'll write more powerful queries that aggregate sales data and reveal which authors and genres are driving the most revenue.

Analyze Sales Data with SQL + pandas

In the previous step, you connected Python to PostgreSQL and confirmed that query results flow into pandas DataFrames. The pipeline is live.

Now it's time to answer the questions a bookstore owner actually cares about. Which authors generate the most revenue? Are monthly sales trending up or down? Which genres perform best? You'll write SQL queries that answer each question and pipe the results into pandas for clean reporting.

In this step, get ready to:

Find the top-selling authors by total revenue.
Track monthly revenue trends across January to March.
Break down sales performance by genre with summary statistics.

Find top-selling authors by revenue

The first business question is simple: which authors make the store the most money? To answer this, you need data from all three tables joined together.

Why join all three tables?

The sales table knows quantities and dates. The books table knows prices and genres. The authors table knows names. Revenue is price * quantity, so you need to join all three to calculate it and group it by author.

In bookstore_analysis.py, add the following code at the bottom of your file, below the Books with Authors section:

# ANALYSIS 1: Top-selling authors by revenue
query_top_authors = """
    SELECT authors.name,
           SUM(books.price * sales.quantity) AS total_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    JOIN authors ON books.author_id = authors.id
    GROUP BY authors.name
    ORDER BY total_revenue DESC
"""
df_top_authors = pd.read_sql(query_top_authors, con=engine)
print("\n=== Top Authors by Revenue ===")
print(df_top_authors.to_string(index=False))

What does this code do?

JOIN books ON sales.book_id = books.id connects each sale to the book that was sold.
JOIN authors ON books.author_id = authors.id connects each book to its author.
SUM(books.price * sales.quantity) multiplies the book price by how many copies were sold, then totals it up.
GROUP BY authors.name collapses all rows for the same author into a single summary row.
to_string(index=False) prints the DataFrame without the default row numbers, giving a cleaner report.

Save bookstore_analysis.py.
Run the script in your terminal by running:

python bookstore_analysis.py

You should see a new section titled === Top Authors by Revenue === with all five authors listed in descending order by revenue.

Seeing a syntax or column error?

Check that the triple-quoted string starts and ends with """. A missing quote will cause a syntax error in Python.
Make sure you typed sales.book_id and books.author_id. Column names are case-sensitive in your query aliases.
Confirm your virtual environment is still active (you should see (bookstore-env) in your terminal prompt).

Help me debug my top authors revenue query.

Track monthly revenue trends

Next, you'll answer whether sales are growing month over month. PostgreSQL has a built-in function called DATE_TRUNC that groups dates by month, quarter, or year.

Add the following code at the bottom of bookstore_analysis.py:

# ANALYSIS 2: Monthly revenue trends
query_monthly = """
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(books.price * sales.quantity) AS monthly_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    GROUP BY DATE_TRUNC('month', sale_date)
    ORDER BY month
"""
df_monthly = pd.read_sql(query_monthly, con=engine, parse_dates=["month"])
df_monthly["month"] = df_monthly["month"].dt.strftime("%B %Y")
print("\n=== Monthly Revenue Trends ===")
print(df_monthly.to_string(index=False))

What does this code do?

DATE_TRUNC('month', sale_date) chops each date down to its month. A sale on January 18 becomes January 1, so all January sales group together.
parse_dates=["month"] tells pandas to treat the month column as a datetime object instead of plain text.
.dt.strftime("%B %Y") formats the datetime into readable text like "January 2025" instead of a raw timestamp.

Save bookstore_analysis.py and run the script again:

python bookstore_analysis.py

You should see a === Monthly Revenue Trends === section showing three rows: January 2025, February 2025, and March 2025, each with a revenue total.

Seeing raw timestamps instead of month names?

Make sure you included parse_dates=["month"] inside the pd.read_sql() call. Without it, pandas treats the column as a string and the .dt accessor won't work.
Check that the column alias in your SQL is AS month. The parse_dates list must match the column name exactly.

Help me fix my monthly revenue formatting.

Break down sales by genre

Finally, you'll see which genres drive the most sales. This time, you'll also use pandas' built-in .describe() method to generate summary statistics automatically.

Add the following code at the bottom of bookstore_analysis.py:

# ANALYSIS 3: Sales breakdown by genre
query_genre = """
    SELECT books.genre,
           SUM(sales.quantity) AS total_units_sold,
           SUM(books.price * sales.quantity) AS total_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    GROUP BY books.genre
    ORDER BY total_revenue DESC
"""
df_genre = pd.read_sql(query_genre, con=engine)
print("\n=== Sales by Genre ===")
print(df_genre.to_string(index=False))
print("\n=== Genre Statistics ===")
print(df_genre.describe())

What does this code do?

SUM(sales.quantity) AS total_units_sold counts the total number of books sold per genre.
SUM(books.price * sales.quantity) AS total_revenue calculates the dollar amount earned per genre.
.describe() is a pandas method that automatically calculates count, mean, min, max, and standard deviation for all numeric columns in the DataFrame.

Save bookstore_analysis.py and run the script one final time:

python bookstore_analysis.py

You should see === Sales by Genre === with four genres listed by revenue, followed by === Genre Statistics === showing the mean, standard deviation, min, and max for units sold and revenue.

Only seeing one genre or missing data?

Confirm your GROUP BY uses books.genre. If you accidentally group by something else, the results collapse incorrectly.
Make sure all 30 sales rows were inserted in Step 3. In pgAdmin, run SELECT COUNT(*) FROM sales to verify you have 30 rows.

Help me troubleshoot my genre breakdown query.

✔️ Awesome, I've got everything!

Great. Double check you've saved bookstore_analysis.py and that the script runs end-to-end without errors.

ⓧ I'd like to double check the full code

Here is the complete bookstore_analysis.py file. Compare it against yours to catch any differences.

import pandas as pd
from sqlalchemy import create_engine, text

# Connect to PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/bookstore")

# Test the connection
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print("Connection successful!" if result.fetchone() else "Connection failed!")

# Load the books table into a DataFrame
df_books = pd.read_sql("SELECT * FROM books", con=engine)
print("\n=== All Books ===")
print(df_books)

# JOIN: Books with their authors
query_books_authors = """
    SELECT books.title, authors.name AS author, books.genre, books.price
    FROM books
    JOIN authors ON books.author_id = authors.id
"""
df_books_authors = pd.read_sql(query_books_authors, con=engine)
print("\n=== Books with Authors ===")
print(df_books_authors)

# ANALYSIS 1: Top-selling authors by revenue
query_top_authors = """
    SELECT authors.name,
           SUM(books.price * sales.quantity) AS total_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    JOIN authors ON books.author_id = authors.id
    GROUP BY authors.name
    ORDER BY total_revenue DESC
"""
df_top_authors = pd.read_sql(query_top_authors, con=engine)
print("\n=== Top Authors by Revenue ===")
print(df_top_authors.to_string(index=False))

# ANALYSIS 2: Monthly revenue trends
query_monthly = """
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(books.price * sales.quantity) AS monthly_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    GROUP BY DATE_TRUNC('month', sale_date)
    ORDER BY month
"""
df_monthly = pd.read_sql(query_monthly, con=engine, parse_dates=["month"])
df_monthly["month"] = df_monthly["month"].dt.strftime("%B %Y")
print("\n=== Monthly Revenue Trends ===")
print(df_monthly.to_string(index=False))

# ANALYSIS 3: Sales breakdown by genre
query_genre = """
    SELECT books.genre,
           SUM(sales.quantity) AS total_units_sold,
           SUM(books.price * sales.quantity) AS total_revenue
    FROM sales
    JOIN books ON sales.book_id = books.id
    GROUP BY books.genre
    ORDER BY total_revenue DESC
"""
df_genre = pd.read_sql(query_genre, con=engine)
print("\n=== Sales by Genre ===")
print(df_genre.to_string(index=False))
print("\n=== Genre Statistics ===")
print(df_genre.describe())

Your bookstore analysis is complete. You've gone from raw rows in a database to meaningful business reports in just a few queries. Next up is a bonus challenge: building a low stock alert system that helps the bookstore owner know when to reorder inventory.

Secret mission

Build a Low Stock Alert System

Your revenue analysis tells the bookstore owner which authors and genres are performing best. But what about preventing lost sales? Build a low stock alert system that flags books running low on inventory and exports a reorder list to CSV.

Clean Up Your Resources

This project runs entirely on your local machine. PostgreSQL runs as a background Windows service that uses a small amount of memory, but there are no cloud costs or recurring charges.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

PostgreSQL 17 server (running as a Windows service).
The bookstore database (with authors, books, and sales tables).
Project folder on your Desktop (containing bookstore_analysis.py, bookstore_schema.sql, low_stock_alert.csv, and the bookstore-env virtual environment).

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the project with more queries and analysis.

PostgreSQL continues running as a background service. It uses minimal memory when idle.
Your bookstore database and project files remain on disk, ready to use.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your database files and project folder stay on disk so you can restart later.

Press the Windows key, type Services, and press Enter to open the Windows Services app.
Scroll down to find the service named postgresql-x64-17.
Right-click it and select Stop.
To restart later, right-click the same service and select Start.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

Drop the bookstore database

Open pgAdmin 4.
In the left sidebar, expand Servers, then expand Databases.
Right-click bookstore and select Delete/Drop.
Click Yes to confirm.

Delete the project folder

Open a Command Prompt and delete the project folder by running this command:

cd %USERPROFILE%\Desktop
rmdir /s /q bookstore-project

What if my folder has a different name?

Replace bookstore-project with whatever you named your project folder on the Desktop. The virtual environment (bookstore-env) lives inside this folder, so deleting the folder removes it too.

Uninstall PostgreSQL (optional)

Open Settings on your Windows machine.
Select Apps, then Installed apps.
Search for PostgreSQL in the list.
Click the three-dot menu next to PostgreSQL 17 and select Uninstall.
Follow the prompts to remove the application. This also removes pgAdmin 4 (which was bundled with the installer).

Nice Work!

You just connected a real PostgreSQL database to Python and performed sales analysis that a bookstore owner could actually use.

You learned how to:

Built a normalized PostgreSQL database with 3 related tables and foreign key relationships.
Wrote SQL queries using JOIN, GROUP BY, aggregate functions, and DATE_TRUNC to analyze sales data.
Connected Python to PostgreSQL using SQLAlchemy and pandas, generating reports on top authors, monthly revenue trends, and genre performance.
Built a low stock alert system that assigns reorder priorities and exports to CSV.

Ready to quiz yourself?