INNER JOIN with table aliases in SQL - Mini Project: Build & Apply

Using INNER JOIN with Table Aliases in SQL

📖 Scenario: You are working with a small online store database. There are two tables: customers and orders. You want to find out which customers have placed orders and see their order details.

🎯 Goal: Build an SQL query that uses INNER JOIN with table aliases to combine customers and orders tables, showing customer names and their order IDs.

📋 What You'll Learn

Create two tables named customers and orders with specified columns.

Insert exact sample data into both tables.

Write an INNER JOIN query using table aliases c for customers and o for orders.

Select the customer name and order ID from the joined tables.

💡 Why This Matters

🌍 Real World

Joining tables with aliases is common in databases to combine related data, like customers and their orders, making queries easier to read and write.

💼 Career

Database developers and analysts use INNER JOIN with aliases daily to write clear and efficient queries for reports and applications.

1

Create the customers and orders tables with sample data

Write SQL statements to create a table called customers with columns customer_id (integer) and customer_name (text). Then create a table called orders with columns order_id (integer) and customer_id (integer). Insert these exact rows into customers: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into orders: (101, 1), (102, 2), (103, 1).

SQL

-- Create customers and orders tables and insert data
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with multiple rows for sample data.

2

Add table aliases for customers and orders

Write a SELECT statement that uses table aliases c for customers and o for orders. Just write the FROM clause with aliases: FROM customers AS c, orders AS o.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER
);

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

INSERT INTO orders (order_id, customer_id) VALUES
  (101, 1),
  (102, 2),
  (103, 1);

-- Write a SELECT statement with table aliases c and o
SELECT *
FROM customers AS c, orders AS o
-- Your code here

Need a hint?

Use AS to assign aliases c and o to the tables.

3

Write the INNER JOIN query using table aliases

Write an SQL query that selects c.customer_name and o.order_id from customers aliased as c joined with orders aliased as o using INNER JOIN. Join on c.customer_id = o.customer_id.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER
);

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

INSERT INTO orders (order_id, customer_id) VALUES
  (101, 1),
  (102, 2),
  (103, 1);

-- Write INNER JOIN query with aliases c and o
SELECT c.customer_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
-- Your code here

Need a hint?

Use INNER JOIN with ON to join tables using aliases.

4

Complete the query with ORDER BY clause

Add an ORDER BY clause to the query to sort the results by c.customer_name in ascending order.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER
);

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

INSERT INTO orders (order_id, customer_id) VALUES
  (101, 1),
  (102, 2),
  (103, 1);

SELECT c.customer_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY c.customer_name ASC
-- Your code here

Need a hint?

Use ORDER BY with the alias c.customer_name to sort results alphabetically.