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.

Practice

1. What does an INNER JOIN with table aliases do in SQL?

easy

A. Deletes rows from both tables using aliases.

B. Updates rows in one table based on another without aliases.

C. Creates a new table without any conditions.

D. Combines rows from two tables where the join condition matches, using short names for tables.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows where matching keys exist in both tables.

2. Step 2: Role of table aliases

   Aliases are short names to simplify table references in queries.

3. Final Answer:

   Combines rows from two tables where the join condition matches, using short names for tables. -> Option D

4. Quick Check:

   INNER JOIN + aliases = matched rows with short table names [OK]

Hint: INNER JOIN matches rows; aliases shorten table names [OK]

Common Mistakes:

• Confusing INNER JOIN with DELETE or UPDATE
• Thinking aliases create new tables
• Ignoring the join condition in INNER JOIN

2. Which of the following is the correct syntax for an INNER JOIN with table aliases?

easy

A. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id;

B. SELECT a.name, b.salary FROM employees AS a JOIN salaries b WHERE a.id = b.emp_id;

C. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b USING a.id = b.emp_id;

D. SELECT a.name, b.salary FROM employees a JOIN salaries b ON a.id == b.emp_id;

Solution

1. Step 1: Check INNER JOIN syntax

   Correct syntax uses INNER JOIN with ON clause for join condition.

2. Step 2: Validate alias usage and condition

   Aliases 'a' and 'b' are used correctly; ON clause uses single '=' for comparison.

3. Final Answer:

   SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id; -> Option A

4. Quick Check:

   INNER JOIN + ON + aliases = correct syntax [OK]

Hint: Use ON with single = for join condition [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals '==' in SQL
• Incorrect USING clause syntax

3. Given tables students (id, name) and grades (student_id, grade), what is the output of this query?

SELECT s.name, g.grade FROM students s INNER JOIN grades g ON s.id = g.student_id;

medium

A. Syntax error due to missing alias

B. [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}]

C. [{"student_id": 1, "grade": "A"}]

D. [{"name": "Alice"}, {"grade": "A"}]

Solution

1. Step 1: Understand the join condition

   The query joins students and grades where students.id matches grades.student_id.

2. Step 2: Predict output rows

   Only students with matching grades appear, showing their name and grade as pairs.

3. Final Answer:

   [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}] -> Option B

4. Quick Check:

   INNER JOIN returns matched student names with grades [OK]

Hint: INNER JOIN shows matched rows with selected columns [OK]

Common Mistakes:

• Expecting unmatched rows to appear
• Confusing alias names in SELECT
• Assuming syntax error without cause

4. Identify the error in this SQL query using INNER JOIN with aliases:

SELECT e.name, d.department FROM employees e INNER JOIN departments d ON e.dept_id = d.idd;

medium

A. Alias 'e' is not defined.

B. Missing AS keyword for aliases.

C. Column 'idd' does not exist in departments table.

D. INNER JOIN should be LEFT JOIN.

Solution

1. Step 1: Check alias definitions

   Aliases 'e' and 'd' are correctly defined for employees and departments.

2. Step 2: Verify join condition columns

   Column 'idd' in departments does not exist; likely a typo for 'id'.

3. Final Answer:

   Column 'idd' does not exist in departments table. -> Option C

4. Quick Check:

   Incorrect column name in ON clause causes error [OK]

Hint: Check column names in ON clause carefully [OK]

Common Mistakes:

• Assuming missing AS keyword causes error
• Confusing alias definition errors
• Changing join type unnecessarily

5. You have two tables: orders (order_id, customer_id, amount) and customers (cust_id, name). Which query correctly uses INNER JOIN with aliases to list customer names and their total order amounts, grouping by customer name?

hard

A. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.name;

C. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY o.name;

Solution

1. Step 1: Match join keys correctly

   Join customers.cust_id with orders.customer_id to link orders to customers.

2. Step 2: Use aliases and group by customer name

   Use aliases 'c' and 'o' and group results by c.name to sum amounts per customer.

3. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name; -> Option A

4. Quick Check:

   Correct join keys and grouping produce total per customer [OK]

Hint: Join on matching keys, group by customer name [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Grouping by wrong column
• Mixing up alias names