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

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

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

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

Hint

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

Practice

1. What does an INNER JOIN with table aliases do in SQL?

easy

A. Deletes rows from both tables using aliases.

B. Updates rows in one table based on another without aliases.

C. Creates a new table without any conditions.

D. Combines rows from two tables where the join condition matches, using short names for tables.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows where matching keys exist in both tables.

2. Step 2: Role of table aliases

   Aliases are short names to simplify table references in queries.

3. Final Answer:

   Combines rows from two tables where the join condition matches, using short names for tables. -> Option D

4. Quick Check:

   INNER JOIN + aliases = matched rows with short table names [OK]

Hint: INNER JOIN matches rows; aliases shorten table names [OK]

Common Mistakes:

• Confusing INNER JOIN with DELETE or UPDATE
• Thinking aliases create new tables
• Ignoring the join condition in INNER JOIN

2. Which of the following is the correct syntax for an INNER JOIN with table aliases?

easy

A. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id;

B. SELECT a.name, b.salary FROM employees AS a JOIN salaries b WHERE a.id = b.emp_id;

C. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b USING a.id = b.emp_id;

D. SELECT a.name, b.salary FROM employees a JOIN salaries b ON a.id == b.emp_id;

Solution

1. Step 1: Check INNER JOIN syntax

   Correct syntax uses INNER JOIN with ON clause for join condition.

2. Step 2: Validate alias usage and condition

   Aliases 'a' and 'b' are used correctly; ON clause uses single '=' for comparison.

3. Final Answer:

   SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id; -> Option A

4. Quick Check:

   INNER JOIN + ON + aliases = correct syntax [OK]

Hint: Use ON with single = for join condition [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals '==' in SQL
• Incorrect USING clause syntax

3. Given tables students (id, name) and grades (student_id, grade), what is the output of this query?

SELECT s.name, g.grade FROM students s INNER JOIN grades g ON s.id = g.student_id;

medium

A. Syntax error due to missing alias

B. [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}]

C. [{"student_id": 1, "grade": "A"}]

D. [{"name": "Alice"}, {"grade": "A"}]

Solution

1. Step 1: Understand the join condition

   The query joins students and grades where students.id matches grades.student_id.

2. Step 2: Predict output rows

   Only students with matching grades appear, showing their name and grade as pairs.

3. Final Answer:

   [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}] -> Option B

4. Quick Check:

   INNER JOIN returns matched student names with grades [OK]

Hint: INNER JOIN shows matched rows with selected columns [OK]

Common Mistakes:

• Expecting unmatched rows to appear
• Confusing alias names in SELECT
• Assuming syntax error without cause

4. Identify the error in this SQL query using INNER JOIN with aliases:

SELECT e.name, d.department FROM employees e INNER JOIN departments d ON e.dept_id = d.idd;

medium

A. Alias 'e' is not defined.

B. Missing AS keyword for aliases.

C. Column 'idd' does not exist in departments table.

D. INNER JOIN should be LEFT JOIN.

Solution

1. Step 1: Check alias definitions

   Aliases 'e' and 'd' are correctly defined for employees and departments.

2. Step 2: Verify join condition columns

   Column 'idd' in departments does not exist; likely a typo for 'id'.

3. Final Answer:

   Column 'idd' does not exist in departments table. -> Option C

4. Quick Check:

   Incorrect column name in ON clause causes error [OK]

Hint: Check column names in ON clause carefully [OK]

Common Mistakes:

• Assuming missing AS keyword causes error
• Confusing alias definition errors
• Changing join type unnecessarily

5. You have two tables: orders (order_id, customer_id, amount) and customers (cust_id, name). Which query correctly uses INNER JOIN with aliases to list customer names and their total order amounts, grouping by customer name?

hard

A. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.name;

C. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY o.name;

Solution

1. Step 1: Match join keys correctly

   Join customers.cust_id with orders.customer_id to link orders to customers.

2. Step 2: Use aliases and group by customer name

   Use aliases 'c' and 'o' and group results by c.name to sum amounts per customer.

3. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name; -> Option A

4. Quick Check:

   Correct join keys and grouping produce total per customer [OK]

Hint: Join on matching keys, group by customer name [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Grouping by wrong column
• Mixing up alias names