Table aliases in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of query using table aliases

Given two tables employees and departments, what is the output of this query?

SELECT e.name, d.name FROM employees AS e JOIN departments AS d ON e.dept_id = d.id;

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT);
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20);
CREATE TABLE departments (id INT, name VARCHAR(20));
INSERT INTO departments VALUES (10, 'HR'), (20, 'Sales');

SELECT e.name, d.name FROM employees AS e JOIN departments AS d ON e.dept_id = d.id;

A [{"name": "Alice"}, {"name": "Bob"}]

B [{"name": "Alice", "name": "Sales"}, {"name": "Bob", "name": "HR"}]

C [{"employee_name": "Alice", "department_name": "HR"}, {"employee_name": "Bob", "department_name": "Sales"}]

D SyntaxError

💡 Hint

Look at how the aliases e and d refer to employees and departments tables.

The query joins employees (aliased as e) with departments (aliased as d) on matching dept_id and id. So Alice is in HR and Bob is in Sales.

📝 Syntax

intermediate

Identify the syntax error with table alias

Which option contains a syntax error when using table aliases in MySQL?

MySQL

SELECT e.name FROM employees e JOIN departments d ON e.dept_id = d.id;

A SELECT e.name FROM employees AS e departments AS d ON e.dept_id = d.id;

B SELECT e.name FROM employees AS e JOIN departments AS d ON e.dept_id = d.id;

C SELECT e.name FROM employees e JOIN departments d ON e.dept_id = d.id;

D SELECT e.name FROM employees e, departments d WHERE e.dept_id = d.id;

💡 Hint

Check if the JOIN keyword is missing or misplaced.

Option A misses the JOIN keyword between the two table aliases, causing a syntax error.

❓ optimization

advanced

Optimizing query with table aliases

Which option uses table aliases to make the query more readable and efficient?

MySQL

SELECT employees.name, departments.name FROM employees JOIN departments ON employees.dept_id = departments.id;

A SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id;

B SELECT employees.name, departments.name FROM employees JOIN departments ON employees.dept_id = departments.id;

C SELECT e.name, d.name FROM employees JOIN departments ON employees.dept_id = departments.id;

D SELECT e.name, d.name FROM employees e, departments d WHERE employees.dept_id = departments.id;

💡 Hint

Using aliases shortens table names and clarifies columns.

Option A uses aliases e and d consistently, making the query shorter and clearer.

🔧 Debug

advanced

Debug the ambiguous column error

What error will this query raise?

SELECT name FROM employees e JOIN departments d ON e.dept_id = d.id;

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT);
CREATE TABLE departments (id INT, name VARCHAR(20));

SELECT name FROM employees e JOIN departments d ON e.dept_id = d.id;

A Syntax error

B Ambiguous column name 'name' error

C No error, returns employee names

D No error, returns department names

💡 Hint

Both tables have a column named 'name'.

The query does not specify which 'name' column to select, causing ambiguity error.

🧠 Conceptual

expert

Purpose of table aliases in complex queries

Why are table aliases especially useful in queries involving the same table multiple times?

A They prevent SQL injection attacks.

B They improve database performance by caching table data.

C They automatically create indexes on the tables.

D They allow referencing the same table multiple times with different names to compare rows.

💡 Hint

Think about self-joins and comparing rows within the same table.

Aliases let you treat the same table as if it were two different tables, useful for self-joins.