Inner join in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Inner Join Result Rows Count

Given two tables Employees and Departments, where Employees has columns EmpID, Name, DeptID and Departments has columns DeptID, DeptName, what is the number of rows returned by the following inner join query?

SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

A The total number of departments regardless of employees

B The total number of employees regardless of department

C The number of employees who have a matching department in Departments

D The sum of employees and departments counts

💡 Hint

Think about what inner join does: it keeps only rows where the join condition matches in both tables.

An inner join returns only rows where the join condition is true. Here, it returns employees who have a matching department in Departments table.

🧠 Conceptual

intermediate

Understanding Inner Join Behavior

Which statement best describes what an inner join does between two tables?

A Returns only rows where there is a match in both tables based on the join condition

B Returns all rows from the first table and matching rows from the second table, filling with NULLs if no match

C Returns all rows from both tables, combining them regardless of matches

D Returns all rows from the second table and matching rows from the first table

💡 Hint

Remember inner join keeps only matching rows from both tables.

Inner join returns only rows where the join condition matches in both tables, excluding rows without matches.

📝 Syntax

advanced

Identify the Correct Inner Join Syntax

Which of the following SQL queries correctly performs an inner join between tables Orders and Customers on the column CustomerID?

A SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

B SELECT * FROM Orders INNER JOIN Customers USING (CustomerID);

C SELECT * FROM Orders INNER JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID;

D SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID == Customers.CustomerID;

💡 Hint

Check the correct syntax for ON clause and join condition operator.

Option A uses correct INNER JOIN syntax with ON and single equals sign for comparison. Option A uses WHERE instead of ON, which is invalid here. Option A uses USING without parentheses which is invalid syntax. Option A uses double equals which is not valid SQL syntax.

❓ optimization

advanced

Optimizing Inner Join Performance

You have two large tables, Sales and Products, joined on ProductID. Which of the following will most improve the performance of the inner join query?

A Use a LEFT JOIN instead of INNER JOIN

B Create an index on Sales.ProductID and Products.ProductID columns

C Add a WHERE clause filtering on a non-indexed column after the join

D Use SELECT * to retrieve all columns from both tables

💡 Hint

Indexes help speed up join operations on large tables.

Creating indexes on the join columns allows the database to quickly find matching rows, improving join performance. Selecting all columns or filtering on non-indexed columns does not help join speed. LEFT JOIN returns more rows and is not an optimization here.

🔧 Debug

expert

Debugging Unexpected Inner Join Result

You run this query:

SELECT e.Name, d.DeptName FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID;

But some employees you expect to see are missing. What is the most likely reason?

A The query should use LEFT JOIN instead of INNER JOIN

B There is a syntax error in the query causing rows to be skipped

C The Departments table is empty

D Some employees have DeptID values that do not exist in Departments table

💡 Hint

Inner join excludes rows without matching keys in both tables.

If employees have DeptID values not present in Departments, inner join excludes them. LEFT JOIN would include them with NULLs. The query syntax is correct. If Departments were empty, no rows would return, but the question implies some rows return.