INNER JOIN in MySQL - Mini Project: Build & Apply

Using INNER JOIN to Combine Customer and Order Data

📖 Scenario: You work at a small online store. You have two tables: one with customer details and another with their orders. You want to see which customers placed orders and details about those orders.

🎯 Goal: Create a query using INNER JOIN to combine the customers and orders tables, showing only customers who have placed orders along with their order details.

📋 What You'll Learn

Create a customers table with columns customer_id (integer), name (text), and city (text).

Create an orders table with columns order_id (integer), customer_id (integer), and product (text).

Insert the exact data provided into both tables.

Write an INNER JOIN query to combine the tables on customer_id.

Select customers.name, customers.city, and orders.product in the result.

💡 Why This Matters

🌍 Real World

Combining customer and order data is common in sales and e-commerce to analyze who bought what.

💼 Career

Understanding INNER JOIN is essential for database querying roles like data analyst, backend developer, and database administrator.

1

Create the customers table and insert data

Create a table called customers with columns customer_id (integer), name (text), and city (text). Then insert these exact rows: (1, 'Alice', 'New York'), (2, 'Bob', 'Los Angeles'), (3, 'Charlie', 'Chicago').

MySQL

-- Create the customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the orders table and insert data

Create a table called orders with columns order_id (integer), customer_id (integer), and product (text). Then insert these exact rows: (101, 1, 'Book'), (102, 2, 'Pen'), (103, 1, 'Notebook').

MySQL

CREATE TABLE customers (
  customer_id INT,
  name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago');

-- Create the orders table and insert data
-- Your code here

Need a hint?

Define the orders table with the right columns and insert the given rows.

3

Write the INNER JOIN query to combine customers and orders

Write a SELECT query that uses INNER JOIN to combine customers and orders on customer_id. Select customers.name, customers.city, and orders.product.

MySQL

CREATE TABLE customers (
  customer_id INT,
  name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago');

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  product VARCHAR(50)
);

INSERT INTO orders (order_id, customer_id, product) VALUES
(101, 1, 'Book'),
(102, 2, 'Pen'),
(103, 1, 'Notebook');

-- Write the INNER JOIN query below
-- Your code here

Need a hint?

Use INNER JOIN with ON customers.customer_id = orders.customer_id to combine the tables.

4

Complete the query with an ORDER BY clause

Add an ORDER BY customers.name clause at the end of the query to sort the results by customer name alphabetically.

MySQL

CREATE TABLE customers (
  customer_id INT,
  name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago');

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  product VARCHAR(50)
);

INSERT INTO orders (order_id, customer_id, product) VALUES
(101, 1, 'Book'),
(102, 2, 'Pen'),
(103, 1, 'Notebook');

SELECT customers.name, customers.city, orders.product
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY customers.name to sort the results alphabetically by customer name.