INNER JOIN with table aliases in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the joined tables.

SQL

SELECT * FROM employees [1] departments ON employees.department_id = departments.id;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BLEFT JOIN

CRIGHT JOIN

DFULL JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN instead of INNER JOIN returns unmatched rows from the left table.

Using FULL JOIN returns all rows from both tables, not just matches.

The INNER JOIN keyword selects records that have matching values in both tables.

2fill in blank

medium

Complete the code to assign aliases to tables and select employee names and department names.

SQL

SELECT employees.name, d.name FROM employees [1] departments d ON employees.department_id = d.id;

Drag options to blanks, or click blank then click option'

ALEFT JOIN

BRIGHT JOIN

CJOIN

DINNER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN changes the result to include unmatched left rows.

Using RIGHT JOIN changes the result to include unmatched right rows.

The JOIN keyword is shorthand for INNER JOIN and works the same here.

3fill in blank

hard

Fix the error in the code by completing the alias for the employees table.

SQL

SELECT e.name, d.name FROM employees [1] departments d ON e.department_id = d.id;

Drag options to blanks, or click blank then click option'

AAS e INNER JOIN

BINNER JOIN

CINNER JOIN AS e

De INNER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Placing the alias after INNER JOIN causes syntax errors.

Omitting AS keyword can cause confusion but is allowed in some SQL dialects.

Use AS e to assign the alias e to employees before joining.

4fill in blank

hard

Fill both blanks to correctly alias tables and join them on matching department IDs.

SQL

SELECT [1].name, [2].name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.id;

Drag options to blanks, or click blank then click option'

Ae

Bd

Cemployees

Ddepartments

Attempts:

3 left

💡 Hint

Common Mistakes

Using full table names instead of aliases when aliases are defined.

Mixing aliases and full table names inconsistently.

Use the aliases e and d to refer to the tables in the SELECT clause.

5fill in blank

hard

Fill all three blanks to select employee name, department name, and filter by department location.

SQL

SELECT [1].name, [2].name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.id WHERE [3].location = 'New York';

Drag options to blanks, or click blank then click option'

Ae

Bd

Cdepartments

Demployees

Attempts:

3 left

💡 Hint

Common Mistakes

Using full table names instead of aliases in WHERE clause.

Filtering on the wrong table alias.

Use alias e for employees and d for departments. The location filter applies to the departments table alias d.

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