INNER JOIN syntax in SQL - 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 information and one with their orders. You want to see which customers placed which orders.

🎯 Goal: Build a SQL query using INNER JOIN to combine the customers and orders tables on the customer_id field.

📋 What You'll Learn

Create a customers table with customer_id and customer_name columns.

Create an orders table with order_id, customer_id, and order_date columns.

Write a SQL query using INNER JOIN to combine these tables on customer_id.

Select customer_name and order_date from the joined tables.

💡 Why This Matters

🌍 Real World

Combining customer and order data is common in sales and business reporting to understand customer activity.

💼 Career

Knowing how to write INNER JOIN queries is essential for database analysts, developers, and anyone working with relational databases.

1

Create the customers table

Write SQL code to create a table called customers with columns customer_id as an integer primary key and customer_name as text. Insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

SQL

-- 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

Write SQL code to create a table called orders with columns order_id as an integer primary key, customer_id as integer, and order_date as text. Insert these exact rows: (101, 1, '2024-01-10'), (102, 2, '2024-01-11'), (103, 1, '2024-01-12').

SQL

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

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

Need a hint?

Remember to define order_id as the primary key and include customer_id to link to customers.

3

Write the INNER JOIN query

Write a SQL query that uses INNER JOIN to combine customers and orders on customer_id. Select customer_name and order_date from the joined tables.

SQL

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT
);

INSERT INTO orders (order_id, customer_id, order_date) VALUES
  (101, 1, '2024-01-10'),
  (102, 2, '2024-01-11'),
  (103, 1, '2024-01-12');

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

Need a hint?

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

4

Complete the query with ordering

Add an ORDER BY clause to the previous query to sort the results by order_date in ascending order.

SQL

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT
);

INSERT INTO orders (order_id, customer_id, order_date) VALUES
  (101, 1, '2024-01-10'),
  (102, 2, '2024-01-11'),
  (103, 1, '2024-01-12');

SELECT customers.customer_name, orders.order_date
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 orders.order_date ASC to sort the results by date.