INNER JOIN with table aliases in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INNER JOIN query with aliases?

Consider two tables: Employees and Departments.

Employees has columns: emp_id, name, dept_id.

Departments has columns: dept_id, dept_name.

What will be the result of this query?

SQL

SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id;

A [{"name": "Alice", "dept_name": "IT"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Charlie", "dept_name": "Finance"}]

B [{"name": "Alice", "dept_name": "HR"}, {"name": "Bob", "dept_name": "IT"}, {"name": "Charlie", "dept_name": "IT"}]

C [{"name": "Alice", "dept_name": "HR"}, {"name": "Bob", "dept_name": "Finance"}]

D []

💡 Hint

Remember INNER JOIN returns rows where the join condition matches.

The query joins employees with their matching department by dept_id. Only employees with a matching department appear.

📝 Syntax

intermediate

Which option has correct INNER JOIN syntax using table aliases?

Choose the query that correctly uses INNER JOIN with table aliases a and b for tables TableA and TableB.

A SELECT a.col1, b.col2 FROM TableA a INNER JOIN TableB b ON a.id = b.id;

B SELECT a.col1, b.col2 FROM TableA AS a INNER JOIN TableB AS b WHERE a.id = b.id;

C SELECT a.col1, b.col2 FROM TableA a JOIN TableB b ON a.id == b.id;

D SELECT a.col1, b.col2 FROM TableA a INNER JOIN TableB b ON a.id = b.id WHERE;

💡 Hint

Check the JOIN syntax and ON clause format.

Option A uses correct INNER JOIN syntax with aliases and ON condition. Option A uses WHERE instead of ON, which is incorrect here. Option A uses '==' which is invalid in SQL. Option A has an incomplete WHERE clause.

❓ optimization

advanced

Which INNER JOIN query with aliases is most efficient for large tables?

Given large tables Orders (alias o) and Customers (alias c), which query is best optimized?

A SELECT o.order_id, c.customer_name FROM Orders o INNER JOIN Customers c ON o.customer_id = c.customer_id WHERE c.status = 'active';

B SELECT o.order_id, c.customer_name FROM Orders o INNER JOIN Customers c ON o.customer_id = c.customer_id;

C SELECT o.order_id, c.customer_name FROM Orders o, Customers c WHERE o.customer_id = c.customer_id AND c.status = 'active';

D SELECT o.order_id, c.customer_name FROM Orders o LEFT JOIN Customers c ON o.customer_id = c.customer_id WHERE c.status = 'active';

💡 Hint

Filtering early in the JOIN can improve performance.

Option A filters customers by status during the JOIN, reducing rows processed. Option A joins all customers, including inactive. Option A uses implicit join syntax which is less clear and may be less optimized. Option A uses LEFT JOIN but filters on customer status, effectively acting like INNER JOIN but less efficient.

🔧 Debug

advanced

Why does this INNER JOIN query with aliases fail?

Given tables Products (alias p) and Categories (alias c), why does this query cause an error?

SQL

SELECT p.product_name, c.category_name FROM Products p INNER JOIN Categories c ON p.category_id = category_id;

A Because table aliases cannot be used in JOIN conditions.

B Because INNER JOIN requires USING keyword instead of ON.

C Because SELECT clause must include table aliases for all columns.

D Because 'category_id' in ON clause is ambiguous without alias.

💡 Hint

Check the ON clause for proper column references.

The ON clause uses 'category_id' without specifying which table it belongs to. This causes ambiguity since both tables may have a column named 'category_id'. Aliases must prefix columns in JOIN conditions.

🧠 Conceptual

expert

What is the effect of using different aliases in INNER JOIN?

Consider the query:

SELECT a.col1, b.col2 FROM Table1 a INNER JOIN Table2 b ON a.id = b.id;

What is the main purpose of using aliases a and b here?

A They change the data types of the columns in the join.

B They create temporary tables that store the join result.

C They shorten table names to make the query easier to write and read.

D They automatically index the joined columns for faster queries.

💡 Hint

Think about why we use short names in writing queries.

Aliases are used to give tables shorter names to simplify query writing and improve readability, especially with long table names or multiple joins.