Build a Coffee Shop Database with MySQL

Design a multi-table MySQL database and write SQL queries that answer real business questions.

Introduction

30 Second Summary

Every coffee shop tracks what's on the menu, who their regulars are, and which drinks sell the fastest. Behind every "top sellers" report or loyalty program is a database organizing all that data.

In this project, you will build a coffee shop database from scratch using MySQL. You will design tables, fill them with realistic data, and write the exact kind of SQL queries that employers test in interviews.

What You'll Build

Picture running a query in MySQL Workbench and instantly seeing your top customer by spending, your best-selling drink, and revenue broken down by category.

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

  • A 4-table relational database modelling a real coffee shop, where you can trace any order back to the customer who placed it and every item they bought.
  • A set of SQL queries that filter the menu by price, sort items from most to least expensive, and pull customer names alongside their order details using JOINs.
  • Aggregate reports answering real business questions: "Who are my top 5 customers?", "What's the best-selling item?", and "How much revenue did each category generate?"
  • Secret Mission: Build a customer loyalty report that classifies customers into Gold, Silver, and Bronze spending tiers.

Are there any prerequisites?

This project assumes you already have MySQL and MySQL Workbench installed on your machine. No other tools or accounts are needed.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Connect to MySQL and Create Your Database

Before you can build tables or run queries, you need a live connection between MySQL Workbench and your MySQL server. Think of it like plugging in a power cord before flipping any switches.

In this step, you will open MySQL Workbench, confirm the connection is live, and create a fresh database called coffee_shop to hold every table you build throughout this project.

In this step, get ready to:
  • Connect to your local MySQL server through MySQL Workbench.
  • Create the coffee_shop database.
  • Verify the database exists.
Open MySQL Workbench and connect to your server

MySQL Workbench gives you a visual SQL editor with a results grid, so you can see your query output in a table instead of plain text. Your first job is to open it and make sure it can talk to your MySQL server.

  • Press the Windows key to open your search bar.
  • Type MySQL Workbench and press Enter to open it.
  • On the home screen, you will see a connection tile under MySQL Connections. It is typically named something like Local instance followed by your username.
  • Click the connection tile to connect.

What if it asks for a password?

If a password dialog appears, enter the root password you set when you first installed MySQL. This is the password you chose during the MySQL installer setup, not your Windows password.

A SQL query tab opens automatically. This is your SQL editor where you will type and run all your queries.

  • Confirm the connection is live by running this SQL in the editor:
SELECT VERSION();
  • Click the Execute button (the lightning bolt icon in the toolbar) to run the statement.

A results grid appears at the bottom of the screen showing your MySQL server version number. If you see a version like 8.4.11 or similar, your connection is working.

Connection failed?

If you see a connection error, your MySQL server may not be running. Open the Windows search bar, type Services, and look for MySQL in the list. Right-click it and select Start. Then return to MySQL Workbench and click the connection tile again.

help me connect to my local MySQL server from MySQL Workbench on Windows

Create the coffee_shop database

Now that your connection is live, you need a dedicated database to hold all the tables for this project. In MySQL, a database is a container that groups related tables together.

  • Press Ctrl+A in the SQL editor to select all text.
  • Press Delete to clear the editor.
  • Create the database and switch to it by typing this SQL:
-- Create the coffee shop database
CREATE DATABASE coffee_shop;

-- Switch to it so all future tables land here
USE coffee_shop;
  • Click the Execute button (lightning bolt icon) to run both statements.

What does this code do?

  • CREATE DATABASE coffee_shop; tells MySQL to create a brand-new, empty database called coffee_shop.
  • USE coffee_shop; switches your session into that database. Every table you create from now on will land inside coffee_shop.
  • Check the output panel at the bottom of the screen. You should see both statements completed without errors.
  • Save the SQL to a file by pressing Ctrl+S. Name the file 01_create_database.sql and save it to your Desktop.

Seeing a "database exists" error?

If you ran the SQL more than once, MySQL will tell you the database already exists. Run DROP DATABASE coffee_shop; to delete it, then run both statements again.

help me fix a "database already exists" error in MySQL

✔️ Awesome, I've got everything!

Great. Double check you have saved your file as 01_create_database.sql.

ⓧ I'd like to double check the full code

Your 01_create_database.sql file should contain exactly this:

CREATE DATABASE coffee_shop;
USE coffee_shop;
Verify the database exists

Before moving on, confirm that MySQL actually created the database. One quick command will list every database on your server.

Before you run the next command, what do you think you will see? Will coffee_shop appear alongside MySQL's built-in databases?

  • Press Ctrl+A in the editor to select all text.
  • Press Delete to clear the editor.
  • List all databases on your server by running this SQL:
SHOW DATABASES;
  • Click the Execute button (lightning bolt icon) to run the statement.

The results grid shows a list of databases. You should see coffee_shop in the list alongside MySQL's built-in databases like information_schema and mysql. That confirms your new database is ready.

Don't see coffee_shop in the list?

Go back to the previous substep and re-run the CREATE DATABASE coffee_shop; statement. Make sure the output panel shows no errors before running SHOW DATABASES; again.

help me figure out why my coffee_shop database is not showing up

Your database is created and ready to go. Next up, you will build your first table and see actual data come back from a query.

Build Your First Table and See Data Come Back

Your coffee_shop database is created and selected. Now you need somewhere to actually store data inside it.

A database on its own is just an empty container. Tables are the structures inside it that hold rows and columns of data. In this step, you will create your first table, fill it with menu items, and immediately query it to see data come back.

In this step, get ready to:
  • Create the menu_items table with columns for each menu item's details.
  • Insert 8 sample menu items spanning drinks and food.
  • Write SELECT, WHERE, ORDER BY, and LIMIT queries to explore the data.
Create the menu_items table and add data

Every coffee shop needs a menu. Your menu_items table will store each item's name, category, and price.

  • Open a new SQL tab in MySQL Workbench by pressing Ctrl+T.
  • Create the menu_items table by typing this SQL into the editor:
-- Menu items table
CREATE TABLE menu_items (
    item_id INT NOT NULL AUTO_INCREMENT,
    item_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(5,2) NOT NULL,
    PRIMARY KEY (item_id)
);

What does this table structure mean?

  • item_id INT NOT NULL AUTO_INCREMENT creates a numeric ID column that automatically assigns the next number (1, 2, 3...) to each new row.
  • VARCHAR(100) stores text up to 100 characters. DECIMAL(5,2) stores a number with up to 5 digits total and 2 decimal places, which is perfect for prices like 4.75.
  • PRIMARY KEY (item_id) designates item_id as the primary key, meaning every row must have a unique ID. No two menu items can share the same item_id.
  • NOT NULL means every column must have a value. You cannot add a menu item without giving it a name, category, and price.
  • Click the Execute button (the lightning bolt icon in the toolbar) to run the query.
  • Confirm the table was created by running this command in the same tab:
DESCRIBE menu_items;

You should see a results grid listing four columns: item_id, item_name, category, and price, each showing its data type.

Seeing an error after running CREATE TABLE?

  • If you see No database selected, run USE coffee_shop; first. MySQL needs to know which database to create the table in.
  • If you see Table 'menu_items' already exists, the table was already created from a previous run. You can safely move on to the next step.

Help me debug my CREATE TABLE error.

  • Save this tab as 02_create_tables.sql by clicking File, then Save Script As.

The table exists, but it is empty. Time to fill it with data.

  • Open a new SQL tab by pressing Ctrl+T.
  • Insert 8 menu items into your table by typing this SQL:
-- Insert menu items
INSERT INTO menu_items (item_name, category, price) VALUES
    ('Espresso', 'Drinks', 3.50),
    ('Latte', 'Drinks', 4.75),
    ('Cappuccino', 'Drinks', 4.50),
    ('Cold Brew', 'Drinks', 5.00),
    ('Croissant', 'Food', 3.25),
    ('Blueberry Muffin', 'Food', 3.75),
    ('Avocado Toast', 'Food', 8.50),
    ('Bagel with Cream Cheese', 'Food', 4.25);

What does this INSERT statement do?

INSERT INTO menu_items (item_name, category, price) tells MySQL which table to add data to and which columns to fill.

Notice you do not include item_id in the column list. Because it is set to AUTO_INCREMENT, MySQL assigns the next available number automatically.

  • Click the Execute button to run the INSERT.

Before you check the results, what do you think you will see if you query the table right now? How many rows do you expect?

  • Verify your data by typing this query below the INSERT statement, highlighting just this line, and clicking Execute:
SELECT * FROM menu_items;

You should see 8 rows in the results grid, each showing an item_id (1 through 8), a name, a category (Drinks or Food), and a price. This is your first real data coming back from your database.

Seeing 0 rows or an error?

  • If you see 0 rows, the INSERT did not run. Make sure you highlighted the INSERT statement and clicked Execute before running the SELECT.
  • Check the Output panel at the bottom of MySQL Workbench for any red error messages. A missing comma between value rows is the most common typo.

Help me figure out why my INSERT is not working.

  • Save this tab as 03_insert_data.sql by clicking File, then Save Script As.
Filter and sort with WHERE and ORDER BY

Seeing all 8 rows is a great start. In practice, you rarely need every row at once. WHERE lets you filter rows that match a condition, and ORDER BY lets you control the sort order of the results.

  • Open a new SQL tab by pressing Ctrl+T.
  • Find all menu items under $5.00 by typing this query and clicking Execute:
-- Filter: items under $5.00
SELECT item_name, category, price
FROM menu_items
WHERE price < 5.00;

What does WHERE do?

WHERE price < 5.00 acts as a filter. MySQL scans every row in menu_items and only returns the rows where the price is less than 5.00. Everything else is excluded from the results.

You should see 5 items in the results. The Avocado Toast ($8.50), Cold Brew ($5.00), and Latte ($4.75) are excluded because their prices are not less than $5.00.

  • Now sort the entire menu by price from highest to lowest. Type this query below the previous one, highlight it, and click Execute:
-- Sort: most expensive items first
SELECT item_name, category, price
FROM menu_items
ORDER BY price DESC;

What does ORDER BY do?

ORDER BY price DESC sorts the results by the price column in descending order (highest first). Change DESC to ASC to flip it to lowest first.

You should see all 8 items with Avocado Toast ($8.50) at the top and Croissant ($3.25) at the bottom.

Results look the same as before?

  • Make sure you highlighted only the new query before clicking Execute. If nothing is highlighted, MySQL Workbench runs all queries in the tab and the results grid shows the output of the last one.

Help me run a specific query in MySQL Workbench.

Write targeted queries with LIMIT

You can combine WHERE with ORDER BY and add LIMIT to control how many rows come back. This is how you build targeted queries that answer specific questions.

  • Find all items in the Drinks category by typing this query below the previous ones, highlighting it, and clicking Execute:
-- Filter by category
SELECT item_name, price
FROM menu_items
WHERE category = 'Drinks';

You should see 4 rows: Espresso, Latte, Cappuccino, and Cold Brew. The food items are filtered out.

  • Now answer the question: "What are the 3 most expensive items on the menu?" Type this query, highlight it, and click Execute:
-- Top 3 most expensive items
SELECT item_name, price
FROM menu_items
ORDER BY price DESC
LIMIT 3;

What does LIMIT do?

LIMIT 3 tells MySQL to return only the first 3 rows from the sorted results. Combined with ORDER BY price DESC, it gives you the top 3 most expensive items.

You should see exactly 3 rows: Avocado Toast ($8.50), Cold Brew ($5.00), and Latte ($4.75).

  • Scroll to the top of this tab and add SELECT * FROM menu_items; as the first query (above your WHERE query) with the comment -- Select all menu items. This keeps your "see everything" query as part of the file.
  • Save this tab as 04_basic_queries.sql by clicking File, then Save Script As.

✔️ Awesome, I've got everything!

Make sure you have saved 04_basic_queries.sql. It should contain 5 queries: SELECT all, WHERE price, ORDER BY price, WHERE category, and LIMIT.

ⓧ I'd like to double check the full code

Here is the complete 04_basic_queries.sql file. Make sure your tab matches this exactly:

-- Select all menu items
SELECT * FROM menu_items;

-- Filter: items under $5.00
SELECT item_name, category, price
FROM menu_items
WHERE price < 5.00;

-- Sort: most expensive items first
SELECT item_name, category, price
FROM menu_items
ORDER BY price DESC;

-- Filter by category
SELECT item_name, price
FROM menu_items
WHERE category = 'Drinks';

-- Top 3 most expensive items
SELECT item_name, price
FROM menu_items
ORDER BY price DESC
LIMIT 3;

Query returning unexpected results?

  • Make sure string values in WHERE use single quotes: 'Drinks', not double quotes.
  • Check that DESC comes after the column name in ORDER BY price DESC. Leaving it off defaults to ascending order.

Help me fix my SELECT query.

You have a table full of data and a set of queries that filter, sort, and limit the results. A single table can answer simple questions like these. But a real coffee shop also tracks customers and their orders, and that means linking multiple tables together. Next up, you will build those tables and discover why connecting them matters.

Add Customers and Orders with Foreign Keys

In the last step, you built a menu_items table and queried it with SELECT, WHERE, and ORDER BY. A single table can answer simple questions about what's on the menu, but a real coffee shop needs to track more than that.

A business owner wants to know who bought what and when. That means linking multiple tables together using foreign keys. In this step, you will create three new tables and connect them to each other and to your existing menu.

In this step, get ready to:
  • Create a customers table and populate it with sample customer data.
  • Create orders and order_items tables with foreign keys linking them to customers and menu items.
  • Discover the limitation of viewing raw data when tables only show numeric IDs.
Create the customers table

Every order needs to belong to a customer. Before you can record any purchases, you need a place to store customer information.

  • In the SQL Editor tab in MySQL Workbench, clear any previous queries and paste the following SQL to create the customers table:
-- Customers table
CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    join_date DATE NOT NULL,
    PRIMARY KEY (customer_id)
);

What does this code do?

  • customer_id INT NOT NULL AUTO_INCREMENT creates a unique ID for each customer that increments automatically, just like item_id in your menu table.
  • VARCHAR(50) stores text up to 50 characters. This is plenty for names.
  • DATE stores a calendar date in YYYY-MM-DD format. You will use it to track when each customer joined.
  • Click the Execute button (the lightning bolt icon in the toolbar) to run the statement.
  • Confirm the table was created by running this query:
DESCRIBE customers;

You should see a results grid listing five columns: customer_id, first_name, last_name, email, and join_date.

Seeing an error?

  • Make sure coffee_shop is still selected as the active database. Run USE coffee_shop; first if you are not sure.
  • If you see a "table already exists" error, you may have run the statement twice. That is fine. Continue to the next step.

Still stuck? Help me debug my CREATE TABLE customers statement.

The table is ready, but it is empty. Time to add some customers.

  • Insert 5 sample customers by clearing the editor and running this statement:
-- Insert customers
INSERT INTO customers (first_name, last_name, email, join_date) VALUES
    ('Alice', 'Johnson', 'alice.johnson@email.com', '2025-01-15'),
    ('Bob', 'Smith', 'bob.smith@email.com', '2025-02-20'),
    ('Carol', 'Davis', 'carol.davis@email.com', '2025-03-10'),
    ('David', 'Wilson', 'david.wilson@email.com', '2025-04-05'),
    ('Emma', 'Brown', 'emma.brown@email.com', '2025-05-12');

What does this code do?

This inserts 5 customers in a single statement. Each set of parentheses is one row. Notice you do not provide a customer_id value. AUTO_INCREMENT handles that for you, assigning 1, 2, 3, 4, and 5 automatically.

  • Click Execute to run the insert.
  • Verify your data by running:
SELECT * FROM customers;

You should see 5 rows in the results grid, starting with Alice Johnson and ending with Emma Brown.

Seeing 0 rows?

  • Check the Output panel at the bottom of MySQL Workbench. If you see an error on the INSERT, it may not have run. Try clicking Execute again with just the INSERT statement selected.
  • Make sure the customers table exists first. Run SHOW TABLES; to confirm.

Need help? Help me troubleshoot my INSERT INTO customers statement in MySQL.

Create the orders and order items tables

You have a menu and a list of customers. Now you need to record what each customer buys. This requires two more tables: one for orders and one for the individual items in each order.

These tables will use foreign keys to link back to the customers and menu_items tables. A foreign key is a column that must match a primary key in another table, creating a guaranteed link between the two.

  • Create the orders table by running this SQL:
-- Orders table
CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What does this code do?

  • FOREIGN KEY (customer_id) REFERENCES customers(customer_id) tells MySQL that every customer_id in this table must match an existing customer_id in the customers table. If you try to insert an order for a customer that does not exist, MySQL will reject it.
  • DATETIME stores both the date and the time of each order. DEFAULT CURRENT_TIMESTAMP means MySQL uses the current date and time if you do not specify one.
  • Click Execute to create the table.
  • Verify the table structure by running:
DESCRIBE orders;

You should see three columns: order_id, customer_id, and order_date.

Seeing a foreign key error?

  • The customers table must exist before you create orders. Run SHOW TABLES; to check that customers is listed. If it is not, go back and create it first.
  • Double-check that the column name customer_id is spelled the same way in both the customers and orders table definitions.

Still stuck? Help me fix my FOREIGN KEY constraint error in MySQL.

One order can contain multiple items. A customer might order a Latte and two Croissants in the same visit. The order_items table tracks each line item separately, connecting an order to a menu item with a quantity.

  • Create the order_items table by running this SQL:
-- Order items table
CREATE TABLE order_items (
    order_item_id INT NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);

What does this code do?

This table has two foreign keys. order_id links back to the orders table, and item_id links back to menu_items. It acts as a bridge between the two.

quantity INT NOT NULL DEFAULT 1 tracks how many of each item were ordered. The default of 1 means if you forget to specify a quantity, MySQL assumes the customer ordered one.

  • Click Execute to create the table.
  • Verify that all four tables now exist by running:
SHOW TABLES;

You should see four tables in the results: customers, menu_items, order_items, and orders.

Only see some of the tables?

  • If a CREATE TABLE statement failed silently, run SHOW TABLES; to see which tables exist. Create any missing ones by re-running the relevant statement above.
  • Order matters. The customers table must be created before orders, and both orders and menu_items must exist before order_items.

Still stuck? Help me figure out why my tables are missing in MySQL.

The tables exist, but they are empty. Time to fill them with order data.

  • Insert 8 orders across your 5 customers by running this SQL:
-- Insert orders
INSERT INTO orders (customer_id, order_date) VALUES
    (1, '2025-06-01 08:30:00'),
    (1, '2025-06-03 09:15:00'),
    (2, '2025-06-01 10:00:00'),
    (3, '2025-06-02 07:45:00'),
    (3, '2025-06-04 08:00:00'),
    (3, '2025-06-05 09:30:00'),
    (4, '2025-06-03 11:00:00'),
    (1, '2025-06-05 08:45:00');

What does this code do?

Each row represents one visit to the coffee shop. Notice that customer_id 1 (Alice) appears three times, and customer_id 3 (Carol) appears three times too. Repeat customers are normal in a coffee shop.

The order_date values include both a date and a time (like 08:30:00) because the column uses the DATETIME data type.

  • Click Execute to insert the orders.
  • Verify by running:
SELECT * FROM orders;

You should see 8 rows. Each row shows an order_id, a customer_id number, and an order_date.

Now you need to fill in what each order actually contains.

  • Insert 15 order line items across those 8 orders by running this SQL:
-- Insert order items
INSERT INTO order_items (order_id, item_id, quantity) VALUES
    (1, 2, 1),
    (1, 5, 2),
    (2, 1, 2),
    (2, 7, 1),
    (3, 4, 1),
    (3, 6, 1),
    (4, 2, 1),
    (4, 5, 1),
    (5, 3, 2),
    (5, 8, 1),
    (6, 1, 1),
    (6, 2, 1),
    (6, 6, 2),
    (7, 7, 1),
    (7, 4, 1);

What does this code do?

Each row is one line item on an order. For example, the first row (1, 2, 1) means order #1 includes item #2 (Latte) with a quantity of 1. The second row (1, 5, 2) means the same order also includes 2 of item #5 (Croissant).

The foreign keys guarantee that every order_id here exists in the orders table, and every item_id exists in menu_items. You cannot accidentally reference a product that does not exist.

  • Click Execute to insert the order items.
  • Verify by running:
SELECT * FROM order_items;

You should see 15 rows in the results grid.

Seeing a foreign key constraint error on INSERT?

  • The data must be inserted in the right order. Customers first, then orders, then order items. If you skipped inserting orders, the order_items insert will fail because the referenced order_id values do not exist yet.
  • If you see a duplicate entry error, you may have run the same INSERT twice. Run SELECT COUNT(*) FROM order_items; to check how many rows exist.

Need help? Help me fix my INSERT INTO order_items foreign key error in MySQL.

✔️ Awesome, I've got everything!

Great. You now have all four tables with data. Save your SQL files if you have not already.

ⓧ I'd like to double check the full code

Your 02_create_tables.sql file should now contain all four CREATE TABLE statements in this order:

  • menu_items (from Step 2).
  • customers (created in this step).
  • orders with a foreign key to customers (created in this step).
  • order_items with foreign keys to orders and menu_items (created in this step).

Your 03_insert_data.sql file should now contain all four INSERT blocks in this order:

  • 8 menu items (from Step 2).
  • 5 customers (inserted in this step).
  • 8 orders (inserted in this step).
  • 15 order items (inserted in this step).

Scroll up and confirm each SQL block you pasted matches the code above.

See the limits of viewing raw data

Your database now tracks customers, orders, and the items in each order. Before you move on, take a close look at what the raw data actually tells you.

  • Run this query to see all the orders:
SELECT * FROM orders;

Before you look at the results, think about this: what do you expect to see? Customer names, or something else?

The results show customer_id values like 1, 2, and 3. There are no names anywhere. You can see that customer #1 placed an order on June 1st, but you have no idea who customer #1 is without checking another table.

  • Now run this query to see the order line items:
SELECT * FROM order_items;

The same problem. You see order_id 1 and item_id 2, but you cannot tell that this means "Alice ordered a Latte" just by looking at these numbers.

Why are the results just numbers?

This is by design. Each table stores its own data and uses numeric IDs to refer to rows in other tables. The orders table knows which customer_id placed the order, but it does not duplicate the customer's name.

To turn those IDs into readable names, you need a way to combine rows from multiple tables at query time. That is exactly what JOINs do, and you will learn them in the next step.

You have a fully connected database with four tables, proper foreign keys, and realistic sample data. The only problem is that querying a single table gives you raw IDs instead of useful names. In the next step, you will write JOINs that combine these tables into readable, meaningful results.

Unlock Multi-Table Queries with JOINs

Your coffee_shop database now has four tables packed with data. But at the end of the last step, you hit a wall: running SELECT * FROM orders gave you rows full of numbers like customer_id 1 and item_id 3 with no way to tell who ordered what.

INNER JOIN and LEFT JOIN solve this by combining rows from multiple tables based on matching keys. In this step, you will turn those meaningless IDs into readable results showing real customer names, item names, and prices.

In this step, get ready to:
  • Write INNER JOIN queries to combine customer names with their orders and menu items.
  • Write a LEFT JOIN to reveal customers who have never placed an order.
  • Add a calculated column that shows the line total for each order item.
Write an INNER JOIN to combine customer names with orders

An INNER JOIN matches rows from two tables where a shared column has the same value. If the customer_id in orders matches a customer_id in customers, the JOIN combines those rows into one result.

  • In MySQL Workbench, open a new query tab by pressing Ctrl+T.
  • Write this INNER JOIN query in the new tab to see customer names next to their order dates:
-- INNER JOIN: customers with their orders
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

What does this query do?

  • FROM orders o starts with the orders table and gives it a short alias o so you can refer to its columns as o.order_id instead of orders.order_id.
  • INNER JOIN customers c ON o.customer_id = c.customer_id brings in the customers table and connects each order to its customer by matching the customer_id columns.
  • The SELECT picks columns from both tables: the customer's name from c and the order details from o.
  • Select the query and click the Execute button (the lightning bolt icon in the toolbar) to run it.

You should see 8 rows in the results grid, each showing a customer's first and last name next to their order ID and date. Alice Johnson appears three times because she placed three orders.

Seeing an error about an unknown column?

  • Make sure you included the table aliases (o and c) in the FROM and INNER JOIN clauses.
  • Check that ON o.customer_id = c.customer_id uses the correct column name from each table.

Still stuck? Help me debug my INNER JOIN query. I'm trying to join the orders and customers tables on customer_id but I'm getting an error.

Extend to a 3-table JOIN for full order details

The 2-table JOIN shows who placed each order, but not what they ordered. To see item names, quantities, and prices, you need to bring in two more tables: order_items and menu_items.

  • Add a blank line below your first query, then write this 3-table JOIN:
-- 3-table JOIN: full order details
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    m.item_name,
    oi.quantity,
    m.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN menu_items m ON oi.item_id = m.item_id;

How does a 3-table JOIN work?

Each INNER JOIN adds another table to the result by matching a shared column. Think of it as a chain: orders links to customers through customer_id, then to order_items through order_id, and finally to menu_items through item_id.

Each link uses the foreign key you created in Step 3. The foreign keys are not just constraints. They are the roadmap that tells your JOIN how to connect the tables.

  • Select only the 3-table JOIN query (highlight it so the first query does not run again).
  • Click the Execute button to run it.

You should see 15 rows in the results grid. Each row now shows a customer's name, the date of their order, the item they ordered, the quantity, and the price. For example, Alice Johnson ordered a Latte for $4.75 and two Croissants for $3.25 each.

Run only the query you need

When you have multiple queries in one tab, highlight the one you want to run before clicking Execute. If nothing is highlighted, MySQL Workbench runs all the queries in the tab.

Getting 0 rows or an error?

  • Make sure each INNER JOIN line has the correct ON clause matching the right columns (e.g., oi.item_id = m.item_id, not oi.order_id = m.item_id).
  • Confirm you used the aliases consistently: o for orders, c for customers, oi for order_items, and m for menu_items.

Need more help? Help me fix my multi-table JOIN query.

Compare INNER JOIN and LEFT JOIN results

Your INNER JOIN shows 4 customers in the results. But the customers table has 5 people. One customer is missing because they never placed an order, and INNER JOIN only returns rows that have a match in both tables.

A LEFT JOIN keeps every row from the left table, even when there is no matching row in the right table. It fills in NULL for the missing columns.

  • Add a blank line below your 3-table JOIN, then write this LEFT JOIN query:
-- LEFT JOIN: all customers, including those with no orders
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

What changed from the INNER JOIN?

  • FROM customers c puts customers on the left side. Every customer will appear in the results, no matter what.
  • LEFT JOIN orders o attaches order data where it exists. For customers with no orders, the order columns show NULL.
  • Highlight the LEFT JOIN query and click Execute.

You should see 9 rows this time. Compare this to the 8 rows from your first INNER JOIN. The extra row is Emma Brown, who has NULL in the order_id and order_date columns because she has never placed an order.

When should I use each JOIN type?

Use INNER JOIN when you only care about rows that have data in both tables ("show me all orders with their customer names"). Use LEFT JOIN when you need to see every row from one table regardless of matches ("show me all customers, even those who never ordered").

LEFT JOIN is especially useful for finding gaps in your data. If a business owner asks "which customers have gone inactive?", a LEFT JOIN with a WHERE o.order_id IS NULL filter gives you the answer instantly.

Not seeing Emma Brown with NULL values?

  • Make sure you used LEFT JOIN instead of INNER JOIN. An INNER JOIN would exclude Emma because she has no orders.
  • Check that customers is on the left (in the FROM clause), not orders. The left table is the one that keeps all its rows.

Need help? Help me understand why my LEFT JOIN isn't showing NULL values.

Add a calculated column to your 3-table JOIN

Your 3-table JOIN shows the quantity and price for each item, but the total cost per line is not calculated for you. You can create a new column on the fly by multiplying two existing columns and giving the result a name with AS.

  • Scroll up to your 3-table JOIN query and find this line:
    m.price
FROM orders o
  • Add a comma after m.price and insert the calculated column on the next line, so it looks like this:
    m.price,
    (oi.quantity * m.price) AS line_total
FROM orders o
  • Also update the comment at the top of that query to match its new purpose:
-- 3-table JOIN: full order details with line total

What is AS?

AS creates an alias, which is a custom name for a calculated or renamed column. Without AS line_total, the column header in your results would show the raw expression (oi.quantity * m.price) instead of a clean name.

Before you run this, what do you think the line_total will show for a row where someone ordered 2 Croissants at $3.25 each?

  • Highlight the updated 3-table JOIN query and click Execute.

You should see 15 rows with a new line_total column on the right. The Croissant row shows 6.50 (2 x $3.25). You can now see customer names, item names, quantities, prices, and totals all in one result.

Seeing an error after adding line_total?

  • Check that you added a comma after m.price on the line above. A missing comma between columns causes a syntax error.
  • Make sure the parentheses around (oi.quantity * m.price) are balanced. One missing parenthesis will break the query.

Still stuck? Help me add a calculated column to my JOIN query.

  • Save this file by pressing Ctrl+S.
  • Name the file 05_join_queries.sql and save it in the same location as your other SQL files.

✔️ Awesome, I've got everything!

Make sure you have saved 05_join_queries.sql with all three queries.

ⓧ I'd like to double check the full code

Your 05_join_queries.sql file should contain these three queries in order. Scroll up through your tab and confirm each one is present.

  • The 2-table INNER JOIN between orders and customers, selecting first_name, last_name, order_id, and order_date.
  • The 3-table INNER JOIN across orders, customers, order_items, and menu_items, including the (oi.quantity * m.price) AS line_total calculated column.
  • The LEFT JOIN between customers and orders, selecting first_name, last_name, order_id, and order_date.

Those numeric IDs are no longer a mystery. You can now pull customer names, item names, quantities, and prices into a single result with one query. Next up, you will use aggregate functions to answer real business questions like "Who are my top customers?" and "What is my best-selling item?"

Answer Business Questions with Aggregations

Your JOINs from the last step turned meaningless numeric IDs into readable results. You can now see exactly which customer ordered which item, at what price, and for what total.

But a coffee shop owner doesn't want to scroll through every individual order line. They want summaries: "Who are my top customers?", "What's my best-selling item?", "How much revenue did each category bring in?" Aggregate functions like COUNT, SUM, and AVG combined with GROUP BY turn raw rows into those answers.

In this step, get ready to:
  • Calculate total orders and spending per customer using aggregate functions with GROUP BY.
  • Find the best-selling items and average order value.
  • Build a revenue-by-category report using HAVING to filter grouped results.
Find your top customers by total spending

The question you want to answer is: "How many orders has each customer placed, and how much have they spent in total?" This requires collapsing many rows into one row per customer.

GROUP BY groups all rows that share the same value into a single summary row. COUNT and SUM then calculate totals within each group.

  • Open a new query tab in MySQL Workbench (you can use the same menu path you used for previous files).
  • Write the customer spending query by pasting this code into the new tab:
-- Total orders and spending per customer
SELECT
    c.first_name,
    c.last_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity * m.price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN menu_items m ON oi.item_id = m.item_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

What does this query do?

  • The four-table INNER JOIN chain connects each customer to their orders, order items, and menu prices. This is the same pattern you used in the previous step.
  • COUNT(DISTINCT o.order_id) counts the number of unique orders per customer. The DISTINCT keyword prevents double-counting when an order has multiple items.
  • SUM(oi.quantity * m.price) multiplies each line item's quantity by its price, then adds them all up for the customer's total spending.
  • GROUP BY c.customer_id, c.first_name, c.last_name collapses all of a customer's rows into one summary row. Including customer_id alongside the names ensures two customers with the same name won't get merged.
  • Click the Execute button (lightning bolt icon) in the toolbar to run the query.

You should see a results grid with one row per customer, showing their name, total number of orders, and total amount spent. The customer who spent the most appears at the top.

Seeing an error or unexpected results?

  • Make sure coffee_shop is still your active database. Run USE coffee_shop; at the top of your query tab if you're unsure.
  • If you see 0 for total_spent, check that the INNER JOIN to menu_items uses oi.item_id = m.item_id, not o.item_id.
  • Still stuck? Help me debug my GROUP BY customer spending query.
Rank best-selling items and calculate average order value

Next up: two more business questions. Which menu items sell the most units, and what does the average order cost?

  • Add a blank line below your first query, then paste the best-selling items query:
-- Top 3 best-selling items
SELECT
    m.item_name,
    SUM(oi.quantity) AS total_quantity
FROM order_items oi
INNER JOIN menu_items m ON oi.item_id = m.item_id
GROUP BY m.item_id, m.item_name
ORDER BY total_quantity DESC
LIMIT 3;

What does this query do?

  • SUM(oi.quantity) adds up the total quantity sold for each item across all orders.
  • This query only needs two tables (order_items and menu_items) because you don't need customer or order details to find the best sellers.
  • LIMIT 3 caps the results at the top 3 items after sorting by total_quantity DESC.
  • Highlight just the best-selling items query and click Execute to run it.

You should see three rows in the results grid, showing the top-selling menu items ranked by total quantity sold.

  • Now add a blank line below the best-selling query and paste the average order value query:
-- Average order value
SELECT
    SUM(oi.quantity * m.price) / COUNT(DISTINCT o.order_id) AS avg_order_value
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN menu_items m ON oi.item_id = m.item_id;

What does this query do?

This query calculates total revenue across all orders, then divides by the number of distinct orders. The result is a single number: the average dollar amount per order.

There's no GROUP BY here because you want one overall average, not a per-customer or per-item breakdown.

  • Highlight the average order value query and click Execute.

You should see a single row with one column, avg_order_value, showing the average dollar amount spent per order.

Getting NULL or 0 for the average?

  • Double-check that you're joining orders to order_items using o.order_id = oi.order_id, and order_items to menu_items using oi.item_id = m.item_id.
  • If you see NULL, make sure your order_items table has data. Run SELECT COUNT(*) FROM order_items; to check.
  • Still stuck? Help me debug my average order value query.
Build a revenue report with HAVING

Your final query is the kind of report a business owner would actually run: total revenue broken down by menu category, showing only categories that earned enough to matter.

What is HAVING?

You already know WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

For example, WHERE price > 5 removes individual items before summing. HAVING total_revenue >= 20.00 removes entire categories after their revenue has been calculated.

  • Add a blank line below your average order value query, then paste the revenue-by-category query:
-- Revenue by category with HAVING filter
SELECT
    m.category,
    SUM(oi.quantity * m.price) AS total_revenue
FROM order_items oi
INNER JOIN menu_items m ON oi.item_id = m.item_id
GROUP BY m.category
HAVING total_revenue >= 20.00
ORDER BY total_revenue DESC;

What does this query do?

  • GROUP BY m.category collapses all items in the same category (Drinks, Food) into a single summary row.
  • SUM(oi.quantity * m.price) AS total_revenue calculates the total revenue for each category.
  • HAVING total_revenue >= 20.00 keeps only categories that earned at least $20. Categories below that threshold are excluded from the results.

Before you run this, take a guess: do you think both categories (Drinks and Food) will appear, or will one get filtered out by the HAVING clause?

  • Highlight the revenue-by-category query and click Execute.

You should see a summary with each category name alongside its total revenue, ordered from highest to lowest. Only categories with at least $20 in revenue appear.

  • Save this file as 06_aggregation_queries.sql.

HAVING clause not filtering anything?

  • Make sure HAVING comes after GROUP BY, not before it. The clause order in SQL is: FROM, WHERE, GROUP BY, HAVING, ORDER BY.
  • Check that you're comparing total_revenue >= 20.00 with the alias name, not the full expression. MySQL allows using the column alias in HAVING.
  • Still stuck? Help me debug my revenue-by-category query with HAVING.

✔️ Awesome, I've got everything!

Great. Double check you've saved 06_aggregation_queries.sql.

ⓧ I'd like to double check the full code

Your 06_aggregation_queries.sql file has four queries. Scroll up and confirm each one is present, in this order:

  • Total orders and spending per customer (the four-table JOIN with GROUP BY, COUNT(DISTINCT), and SUM).
  • Top 3 best-selling items (two-table JOIN with SUM(quantity), LIMIT 3).
  • Average order value (SUM divided by COUNT(DISTINCT), single result row).
  • Revenue by category with HAVING filter (GROUP BY category, HAVING total_revenue >= 20.00).

Secret mission

Build a Customer Loyalty Report

Your aggregate queries already calculate total spending per customer. But a real coffee shop owner would want to classify customers into loyalty tiers based on how much they spend. Can you write a single query that combines JOINs, aggregations, and conditional logic to build a full loyalty report?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local MySQL installation, so there are no ongoing costs. Decide whether to keep your database for more practice, or delete it entirely.

Resources you used:

  • The coffee_shop database on your local MySQL server, containing the menu_items, customers, orders, and order_items tables with sample data.

Keep everything running

No action needed. Choose this if you want to keep practicing SQL queries on your coffee shop data. The database sits on your local MySQL server with no ongoing costs.

  • Your coffee_shop database and all four tables stay on your local MySQL server.
  • Reopen MySQL Workbench anytime to connect and run new queries against your data.

Pause - I'll come back to this later

Close MySQL Workbench to free up memory. Your database stays on the local server and will be there whenever you reconnect.

  • Close any open SQL query tabs in MySQL Workbench.
  • Close MySQL Workbench.
  • When you want to practice again, reopen MySQL Workbench and connect to your local server.
  • Select the coffee_shop database to continue where you left off.

Delete - I don't want to use this again

Remove the coffee_shop database from your MySQL server to clear all tables and data.

  • In the SQL Editor in MySQL Workbench, run this command to drop the entire database:
DROP DATABASE coffee_shop;
  • Confirm the database is removed by running:
SHOW DATABASES;

You should no longer see coffee_shop in the list.

  • Close MySQL Workbench.

Nice Work!

Nice Work!

You just built a fully functional coffee shop database from scratch using MySQL. From designing schemas and inserting data to writing multi-table queries that answer real business questions, you now have hands-on experience with the exact SQL skills employers test in interviews.

You've learned how to:

  • Built a 4-table relational database with primary keys, foreign keys, and proper data types, then populated it with realistic sample data across menu_items, customers, orders, and order_items.
  • Combined data from multiple tables using INNER JOIN and LEFT JOIN to turn meaningless numeric IDs into readable results showing customer names, item names, quantities, and calculated line totals.
  • Answered real business questions using aggregate functions (COUNT, SUM, AVG) with GROUP BY and HAVING to find top customers, best-selling items, and revenue by category.
  • Secret Mission: Built a customer loyalty report using CASE WHEN to classify customers into Gold, Silver, and Bronze spending tiers, combining JOINs, aggregations, and conditional logic in a single query.

Ready to quiz yourself?