Column aliases in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query with column aliases?

Consider a table employees with columns id, first_name, and last_name. What will be the output column names of this query?

SELECT first_name AS fname, last_name AS lname FROM employees;

MySQL

SELECT first_name AS fname, last_name AS lname FROM employees;

A [{"first_name": "John", "lname": "Doe"}, {"first_name": "Jane", "lname": "Smith"}]

B [{"first_name": "John", "last_name": "Doe"}, {"first_name": "Jane", "last_name": "Smith"}]

C [{"fname": "John", "last_name": "Doe"}, {"fname": "Jane", "last_name": "Smith"}]

D [{"fname": "John", "lname": "Doe"}, {"fname": "Jane", "lname": "Smith"}]

💡 Hint

Column aliases rename the output columns in the result set.

Using AS keyword renames the output columns to the alias names specified. So first_name becomes fname and last_name becomes lname in the output.

📝 Syntax

intermediate

Which query uses correct syntax for column aliasing?

Choose the query that correctly aliases the column salary as monthly_salary in MySQL.

A SELECT salary AS monthly_salary FROM employees;

B SELECT salary monthly_salary FROM employees;

C SELECT salary : monthly_salary FROM employees;

D SELECT salary -> monthly_salary FROM employees;

💡 Hint

MySQL uses AS keyword or just a space for aliasing, but some symbols are invalid.

The correct syntax uses AS keyword or a space. Option A is valid in MySQL but less clear. Option A is the standard and recommended syntax. Options C and D use invalid symbols.

❓ query_result

advanced

What is the output of this query with expression alias?

Given a table orders with columns price and quantity, what is the output of this query?

SELECT price * quantity AS total_cost FROM orders;

MySQL

SELECT price * quantity AS total_cost FROM orders;

A [{"total_cost": 200}, {"total_cost": 450}]

B [{"price * quantity": 200}, {"price * quantity": 450}]

C [{"total_cost": "price * quantity"}, {"total_cost": "price * quantity"}]

D [{"price": 200}, {"price": 450}]

💡 Hint

The alias renames the expression column in the output.

The expression price * quantity is calculated for each row and the result is shown under the column named total_cost.

🔧 Debug

advanced

Why does this query cause an error?

This query tries to alias a column but causes an error:

SELECT first_name AS 'fname' FROM employees;

Why?

A AS keyword cannot be used with column aliases.

B Alias names cannot be shorter than original column names.

C Using single quotes around alias name causes a syntax error in MySQL.

D Column names cannot be aliased in SELECT statements.

💡 Hint

MySQL treats single quotes as string literals, not identifiers.

In MySQL, alias names should not be enclosed in single quotes because single quotes denote strings. Use backticks or no quotes for aliases.

🧠 Conceptual

expert

Which statement about column aliases is TRUE?

Choose the correct statement about column aliases in SQL.

A Column aliases must match the original column names exactly.

B Column aliases can be used to rename columns only in the output, not in WHERE clause.

C Column aliases can be used in WHERE clause directly after aliasing.

D Column aliases permanently rename columns in the database table.

💡 Hint

Think about when aliases exist during query execution.

Column aliases exist only in the output of the SELECT statement and cannot be used in WHERE clause because WHERE is evaluated before SELECT.

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