Column aliases in MySQL - Mini Project: Build & Apply

Using Column Aliases in SQL Queries

📖 Scenario: You work at a small bookstore. The store keeps a database of books with columns for the book's title, author, and price. You want to create a report that shows the book title and price, but with clearer column names for the report.

🎯 Goal: Create a SQL query that selects the title and price columns from the books table, but uses column aliases to rename title as Book_Title and price as Book_Price.

📋 What You'll Learn

Create a table called books with columns title (VARCHAR), author (VARCHAR), and price (DECIMAL).

Insert three specific book records into the books table.

Write a SELECT query that uses column aliases to rename title as Book_Title and price as Book_Price.

Use the exact alias names Book_Title and Book_Price in the query.

💡 Why This Matters

🌍 Real World

Column aliases help make SQL query results easier to read and understand, especially when generating reports or exporting data.

💼 Career

Using column aliases is a common skill for database analysts, developers, and anyone working with SQL to present data clearly.

1

Create the books table and insert data

Create a table called books with columns title as VARCHAR(100), author as VARCHAR(100), and price as DECIMAL(5,2). Then insert these three rows exactly: ('The Great Gatsby', 'F. Scott Fitzgerald', 10.99), ('1984', 'George Orwell', 8.99), and ('To Kill a Mockingbird', 'Harper Lee', 7.99).

MySQL

-- Create the books table and insert the three rows here
-- Your code here

Hint

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

2

Write a basic SELECT query

Write a SELECT query that selects the title and price columns from the books table without any aliases.

MySQL

CREATE TABLE books (
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (title, author, price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
('1984', 'George Orwell', 8.99),
('To Kill a Mockingbird', 'Harper Lee', 7.99);

-- Write a SELECT query to get title and price from books
-- Your code here

Hint

Use SELECT title, price FROM books; to get the columns.

3

Add column aliases to the SELECT query

Modify the SELECT query to use column aliases. Rename title as Book_Title and price as Book_Price using the AS keyword.

MySQL

CREATE TABLE books (
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (title, author, price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
('1984', 'George Orwell', 8.99),
('To Kill a Mockingbird', 'Harper Lee', 7.99);

-- Modify the SELECT query to use column aliases
-- Your code here

Hint

Use AS to rename columns like title AS Book_Title.

4

Complete the query with aliases and order by price

Add an ORDER BY clause to the query to sort the results by Book_Price in ascending order. Use the alias Book_Price in the ORDER BY clause.

MySQL

CREATE TABLE books (
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (title, author, price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
('1984', 'George Orwell', 8.99),
('To Kill a Mockingbird', 'Harper Lee', 7.99);

-- Add ORDER BY clause using the alias Book_Price
-- Your code here

Hint

Use ORDER BY Book_Price ASC to sort by the alias.

Practice

1. What is the purpose of using a column alias in a MySQL query?

easy

A. To create a new column in the table

B. To permanently change the column name in the database table

C. To rename a column temporarily in the query result for better readability

D. To delete a column from the query result

Solution

1. Step 1: Understand what a column alias does

   A column alias temporarily renames a column in the output of a query without changing the actual table.

2. Step 2: Differentiate permanent and temporary changes

   Permanent changes affect the table structure, but aliases only affect the query result display.

3. Final Answer:

   To rename a column temporarily in the query result for better readability -> Option C

4. Quick Check:

   Column alias = temporary rename [OK]

Hint: Aliases rename columns only in query output, not in tables [OK]

Common Mistakes:

• Thinking alias changes the actual table column name
• Confusing alias with creating new columns
• Assuming alias deletes columns

2. Which of the following is the correct syntax to assign a column alias in MySQL?

easy

A. SELECT column_name, alias_name FROM table_name;

B. SELECT column_name AS alias_name FROM table_name;

C. SELECT alias_name AS column_name FROM table_name;

D. SELECT column_name TO alias_name FROM table_name;

Solution

1. Step 1: Recall the syntax for column aliasing

   The correct syntax uses the AS keyword: SELECT column_name AS alias_name FROM table_name;

2. Step 2: Check each option

   SELECT column_name, alias_name FROM table_name; uses comma, selects two separate columns instead of aliasing. SELECT column_name TO alias_name FROM table_name; uses invalid keyword TO. SELECT alias_name AS column_name FROM table_name; reverses alias and column. SELECT column_name AS alias_name FROM table_name; is correct syntax.

3. Final Answer:

   SELECT column_name AS alias_name FROM table_name; -> Option B

4. Quick Check:

   Use AS for aliasing columns [OK]

Hint: Use AS keyword between column and alias for clarity [OK]

Common Mistakes:

• Reversing alias and column names
• Using invalid keywords like TO
• Using comma instead of space or AS for aliases

3. Given the table employees with columns first_name and last_name, what will be the output column name of this query?
SELECT first_name AS fname, last_name lname FROM employees;

medium

A. fname and lname

B. first_name and last_name

C. first_name and lname

D. fname and last_name

Solution

1. Step 1: Identify aliases in the query

   The query uses AS fname for first_name and a space alias lname for last_name.

2. Step 2: Understand alias effect on output

   Both columns will appear with their aliases: fname and lname.

3. Final Answer:

   fname and lname -> Option A

4. Quick Check:

   Aliases rename columns in output [OK]

Hint: Aliases rename columns exactly as specified after AS or space [OK]

Common Mistakes:

• Assuming original column names show instead of aliases
• Confusing AS keyword usage
• Thinking only AS works, not space alias

4. Identify the error in this query:
SELECT name AS, age FROM users;

medium

A. Missing alias name after AS keyword

B. Incorrect table name

C. AS keyword should be lowercase

D. No error, query is correct

Solution

1. Step 1: Analyze the alias syntax

   The AS keyword must be followed by an alias name. Here, it is missing after AS.

2. Step 2: Check other parts of the query

   Table name and case of AS keyword are valid. The error is only the missing alias name.

3. Final Answer:

   Missing alias name after AS keyword -> Option A

4. Quick Check:

   AS must be followed by alias name [OK]

Hint: Always put alias name right after AS keyword [OK]

Common Mistakes:

• Leaving AS without alias name
• Thinking AS is case-sensitive
• Assuming missing alias is allowed

5. You want to display the total sales from a sales table with column amount. Which query correctly shows the total as Total_Sales and also calculates average sales as Avg_Sales?

hard

A. SELECT SUM(amount) AS Total_Sales, AVG(amount) AS Avg_Sales FROM sales;

B. SELECT SUM(amount) Total_Sales, AVG(amount) Avg_Sales FROM sales;

C. SELECT SUM(amount) AS Total_Sales, AVG(amount) Avg_Sales FROM sales;

D. All of the above

Solution

1. Step 1: Understand aliasing with and without AS

   MySQL allows column aliases with or without the AS keyword.

2. Step 2: Check each option

   SELECT SUM(amount) AS Total_Sales, AVG(amount) AS Avg_Sales FROM sales; uses AS for both aliases, correct syntax. SELECT SUM(amount) Total_Sales, AVG(amount) Avg_Sales FROM sales; uses space without AS, also valid. SELECT SUM(amount) AS Total_Sales, AVG(amount) Avg_Sales FROM sales; mixes both, still valid.

3. Step 3: Confirm all options produce correct output

   All queries will show columns named Total_Sales and Avg_Sales correctly.

4. Final Answer:

   All of the above -> Option D

5. Quick Check:

   Aliases can use AS or space [OK]

Hint: AS keyword is optional for column aliases in MySQL [OK]

Common Mistakes:

• Thinking AS is mandatory for aliases
• Mixing alias and column order
• Using invalid keywords for aliasing