Table aliases in MySQL - Mini Project: Build & Apply

Using Table Aliases in SQL Queries

📖 Scenario: You are managing a small bookstore database. The database has two tables: books and authors. You want to write queries that join these tables to find out which author wrote which book. To make your queries easier to read and write, you will use table aliases.

🎯 Goal: Build a SQL query using table aliases to join the books and authors tables and select the book title along with the author's name.

📋 What You'll Learn

Create a table called books with columns book_id, title, and author_id.

Create a table called authors with columns author_id and name.

Insert the exact data provided into both tables.

Write a SELECT query that uses table aliases b for books and a for authors.

Join the tables on b.author_id = a.author_id and select b.title and a.name.

💡 Why This Matters

🌍 Real World

Table aliases are used in real databases to simplify complex queries, especially when joining multiple tables.

💼 Career

Knowing how to use table aliases is essential for database developers, analysts, and anyone working with SQL to write clear and efficient queries.

1

Create the books table and insert data

Create a table called books with columns book_id (integer), title (varchar 100), and author_id (integer). Then insert these exact rows: (1, 'The Great Gatsby', 101), (2, '1984', 102), (3, 'To Kill a Mockingbird', 103).

MySQL

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

Hint

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

2

Create the authors table and insert data

Create a table called authors with columns author_id (integer) and name (varchar 100). Then insert these exact rows: (101, 'F. Scott Fitzgerald'), (102, 'George Orwell'), (103, 'Harper Lee').

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Great Gatsby', 101),
(2, '1984', 102),
(3, 'To Kill a Mockingbird', 103);

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

Hint

Use CREATE TABLE and INSERT INTO like in Step 1.

3

Write a SELECT query using table aliases

Write a SELECT query that uses the table alias b for books and a for authors. Join the tables on b.author_id = a.author_id. Select b.title and a.name.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Great Gatsby', 101),
(2, '1984', 102),
(3, 'To Kill a Mockingbird', 103);

CREATE TABLE authors (
  author_id INT,
  name VARCHAR(100)
);

INSERT INTO authors (author_id, name) VALUES
(101, 'F. Scott Fitzgerald'),
(102, 'George Orwell'),
(103, 'Harper Lee');

-- Write the SELECT query using table aliases b and a
-- Your code here

Hint

Use FROM books b and JOIN authors a ON b.author_id = a.author_id.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the query to sort the results by a.name in ascending order.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Great Gatsby', 101),
(2, '1984', 102),
(3, 'To Kill a Mockingbird', 103);

CREATE TABLE authors (
  author_id INT,
  name VARCHAR(100)
);

INSERT INTO authors (author_id, name) VALUES
(101, 'F. Scott Fitzgerald'),
(102, 'George Orwell'),
(103, 'Harper Lee');

SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id
-- Add ORDER BY clause to sort by author name
-- Your code here

Hint

Use ORDER BY a.name ASC at the end of the query.

Practice

1. What is the main purpose of using table aliases in a SQL query?

easy

A. To delete a table after the query runs

B. To give a short name to a table for easier reference

C. To create a new table from an existing one

D. To rename columns permanently in the database

Solution

1. Step 1: Understand what table aliases do

   Table aliases provide a temporary short name for a table within a query.

2. Step 2: Compare options with this purpose

   Only To give a short name to a table for easier reference correctly describes this purpose; others describe unrelated actions.

3. Final Answer:

   To give a short name to a table for easier reference -> Option B

4. Quick Check:

   Table aliases = short table names [OK]

Hint: Aliases shorten table names to simplify queries [OK]

Common Mistakes:

• Thinking aliases rename tables permanently
• Confusing aliases with creating or deleting tables
• Using aliases only for columns

2. Which of the following is the correct syntax to assign an alias c to a table named customers in a SELECT query?

easy

A. SELECT * FROM customers c c;

B. SELECT * FROM customers c AS;

C. SELECT * FROM AS customers c;

D. SELECT * FROM customers AS c;

Solution

1. Step 1: Recall correct alias syntax

   The correct syntax is: FROM table_name AS alias or FROM table_name alias.

2. Step 2: Check each option

   SELECT * FROM customers AS c; uses 'AS' correctly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   SELECT * FROM customers AS c; -> Option D

4. Quick Check:

   Correct alias syntax = SELECT * FROM customers AS c; [OK]

Hint: Use 'AS' between table name and alias or just space [OK]

Common Mistakes:

• Placing 'AS' after alias name
• Using multiple aliases for one table
• Misordering keywords in FROM clause

3. Given the tables orders and customers, what will the following query return?

SELECT c.name, o.order_id FROM customers AS c JOIN orders AS o ON c.id = o.customer_id;

medium

A. A list of customer names with their order IDs

B. A list of order IDs only

C. A list of customer names only

D. An error due to incorrect alias usage

Solution

1. Step 1: Understand the query structure

   The query joins customers (aliased as c) and orders (aliased as o) on matching IDs.

2. Step 2: Analyze the SELECT clause

   It selects customer names (c.name) and order IDs (o.order_id), so both appear in results.

3. Final Answer:

   A list of customer names with their order IDs -> Option A

4. Quick Check:

   Join with aliases returns combined columns [OK]

Hint: Aliases let you select columns clearly from joined tables [OK]

Common Mistakes:

• Assuming aliases cause errors
• Expecting only one column in output
• Ignoring join condition effects

4. Identify the error in this query using table aliases:

SELECT c.name, o.order_id FROM customers c JOIN orders o ON c.id = orders.customer_id;

medium

A. Missing AS keyword for aliases

B. Aliases cannot be used in JOIN clauses

C. Using 'orders' instead of alias 'o' in ON clause

D. SELECT clause must use full table names

Solution

1. Step 1: Check alias usage in JOIN condition

   The query aliases 'orders' as 'o', but ON clause uses 'orders.customer_id' instead of 'o.customer_id'.

2. Step 2: Understand alias consistency

   Once aliased, the original table name should not be used in the query; use the alias instead.

3. Final Answer:

   Using 'orders' instead of alias 'o' in ON clause -> Option C

4. Quick Check:

   Use aliases consistently after defining them [OK]

Hint: Use alias names everywhere after aliasing tables [OK]

Common Mistakes:

• Mixing original table names with aliases
• Thinking AS keyword is mandatory
• Assuming aliases can't be used in JOIN

5. You want to join the employees table to itself to find pairs of employees who share the same manager. Which query correctly uses table aliases to achieve this?

hard

A. SELECT e1.name, e2.name FROM employees AS e1 JOIN employees AS e2 ON e1.manager_id = e2.manager_id WHERE e1.id != e2.id;

B. SELECT employees.name, employees.name FROM employees JOIN employees ON employees.manager_id = employees.manager_id;

C. SELECT e.name FROM employees e JOIN employees e ON e.manager_id = e.manager_id;

D. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.id;

Solution

1. Step 1: Understand self-join with aliases

   Joining a table to itself requires different aliases to distinguish rows.

2. Step 2: Check join condition and alias usage

   SELECT e1.name, e2.name FROM employees AS e1 JOIN employees AS e2 ON e1.manager_id = e2.manager_id WHERE e1.id != e2.id; correctly uses aliases e1 and e2 and joins on matching manager_id, excluding same employee pairs.

3. Step 3: Review other options for errors

   SELECT employees.name, employees.name FROM employees JOIN employees ON employees.manager_id = employees.manager_id; uses same table names without aliases, causing ambiguity. SELECT e.name FROM employees e JOIN employees e ON e.manager_id = e.manager_id; uses same alias twice, invalid syntax. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.id; joins on employee IDs, not managers.

4. Final Answer:

   SELECT e1.name, e2.name FROM employees AS e1 JOIN employees AS e2 ON e1.manager_id = e2.manager_id WHERE e1.id != e2.id; -> Option A

5. Quick Check:

   Self-join needs distinct aliases and correct join condition [OK]

Hint: Use different aliases for self-join tables [OK]

Common Mistakes:

• Using same alias for both tables
• Joining on wrong columns
• Not excluding same row pairs in self-join