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

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

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

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

Need a hint?

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