Why understanding relationships matters in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Identify the correct output of a JOIN query

Given two tables, Customers and Orders, which SQL query correctly returns all customers and their orders, including customers with no orders?

SQL

SELECT Customers.CustomerID, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

A Returns all orders with their customers; excludes customers without orders

B Returns only customers who have orders; excludes customers without orders

C Returns all customers with their orders; customers without orders show NULL for OrderID

D Returns all customers and orders, but duplicates customers without orders

💡 Hint

Think about what a LEFT JOIN does compared to INNER JOIN.

A LEFT JOIN returns all rows from the left table (Customers) and matching rows from the right table (Orders). If there is no match, the right side columns are NULL.

🧠 Conceptual

intermediate

Why foreign keys are important

Which statement best explains why foreign keys are important in relational databases?

A They enforce data integrity by ensuring relationships between tables are valid

B They automatically delete all data in related tables when one row is deleted

C They allow storing duplicate data to improve performance

D They speed up queries by indexing all columns automatically

💡 Hint

Think about what happens if you try to link data that doesn't exist.

Foreign keys ensure that the value in one table matches a valid value in another, preventing invalid or orphaned data.

📝 Syntax

advanced

Identify the syntax error in a JOIN query

Which option contains a syntax error in the SQL JOIN statement?

SQL

SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

A SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d USING (DepartmentID);

B SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

C ;DItnemtrapeD.d = DItnemtrapeD.e NO d stnemtrapeD NIOJ RENNI e seeyolpmE MORF emaNtnemtrapeD.d ,emaN.e TCELES

D SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d WHERE e.DepartmentID = d.DepartmentID;

💡 Hint

Check the JOIN syntax and how conditions are specified.

Option D uses WHERE instead of ON for the JOIN condition, which is invalid syntax for JOIN clauses.

❓ optimization

advanced

Optimizing queries with relationships

You have two large tables, Orders and Customers. Which approach optimizes the query to find customers with orders in the last month?

A Use INNER JOIN with a WHERE clause filtering Orders.OrderDate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

B Use subquery in WHERE EXISTS to check for orders in last month

C Use CROSS JOIN and filter Orders.OrderDate in WHERE clause

D Use LEFT JOIN without filtering Orders, then filter in WHERE Orders.OrderDate >= ...

💡 Hint

Consider which method avoids unnecessary row combinations and improves performance.

Using WHERE EXISTS with a subquery efficiently checks for related rows without joining all data, improving performance on large tables.

🔧 Debug

expert

Debugging incorrect relationship results

Given these tables and query, why does the query return fewer rows than expected?

SELECT c.CustomerID, o.OrderID FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate > '2024-01-01';

A The WHERE clause filters out rows where Orders are NULL, turning LEFT JOIN effectively into INNER JOIN

B The JOIN condition is incorrect and excludes matching rows

C The query syntax is invalid and causes an error

D The Orders table has no rows with OrderDate after 2024-01-01

💡 Hint

Think about how WHERE affects rows with NULL values from LEFT JOIN.

Filtering on a column from the right table in WHERE removes rows where that column is NULL, negating the LEFT JOIN effect.

Practice

1. Why is it important to understand relationships between tables in a database?

easy

A. Because relationships prevent any data from being deleted.

B. Because relationships make the database run faster automatically.

C. Because relationships allow us to connect and combine data from different tables.

D. Because relationships store data in a single table only.

Solution

1. Step 1: Understand the role of relationships

   Relationships link tables so data can be combined meaningfully.

2. Step 2: Recognize the benefit of linking data

   Linking data helps answer questions that need info from multiple tables.

3. Final Answer:

   Because relationships allow us to connect and combine data from different tables. -> Option C

4. Quick Check:

   Relationships connect tables = C [OK]

Hint: Relationships connect tables to combine data easily [OK]

Common Mistakes:

• Thinking relationships speed up database automatically
• Believing relationships prevent data deletion
• Assuming all data is stored in one table

2. Which SQL keyword is used to combine rows from two tables based on a related column?

easy

A. JOIN

B. SELECT

C. WHERE

D. GROUP BY

Solution

1. Step 1: Identify the keyword for combining tables

   JOIN is used to link rows from two tables using a common column.

2. Step 2: Differentiate from other keywords

   SELECT retrieves data, WHERE filters rows, GROUP BY groups rows; only JOIN combines tables.

3. Final Answer:

   JOIN -> Option A

4. Quick Check:

   JOIN combines tables = B [OK]

Hint: JOIN links tables on common columns [OK]

Common Mistakes:

• Using SELECT to combine tables
• Confusing WHERE with JOIN
• Thinking GROUP BY combines tables

3. Given two tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name, dept_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.dept_id;

medium

A. A list of department names only.

B. A list of employee names with their department names.

C. A list of employee names only.

D. An error because JOIN syntax is wrong.

Solution

1. Step 1: Understand the JOIN condition

   The query joins Employees and Departments where dept_id matches.

2. Step 2: Identify selected columns

   It selects employee names and their matching department names.

3. Final Answer:

   A list of employee names with their department names. -> Option B

4. Quick Check:

   JOIN on dept_id returns employee and department names = A [OK]

Hint: JOIN returns combined rows matching keys [OK]

Common Mistakes:

• Expecting only one table's columns
• Thinking JOIN causes syntax error
• Ignoring the ON condition

4. What is wrong with this SQL query?

SELECT name, dept_name FROM Employees JOIN Departments WHERE Employees.dept_id = Departments.dept_id;

medium

A. WHERE cannot be used with JOIN.

B. SELECT cannot have multiple columns.

C. Table names are incorrect.

D. Missing ON keyword for JOIN condition.

Solution

1. Step 1: Check JOIN syntax

   JOIN requires ON keyword to specify join condition, not WHERE.

2. Step 2: Understand WHERE usage

   WHERE filters rows after join; join condition must be in ON clause.

3. Final Answer:

   Missing ON keyword for JOIN condition. -> Option D

4. Quick Check:

   JOIN needs ON for condition = D [OK]

Hint: JOIN condition must use ON, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Thinking SELECT can't have multiple columns
• Assuming table names are wrong

5. You have three tables:
Orders(order_id, customer_id, product_id)
Customers(customer_id, customer_name)
Products(product_id, product_name)
How would you write a query to list each order with the customer name and product name?

hard

A. SELECT order_id, customer_name, product_name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id JOIN Products ON Orders.product_id = Products.product_id;

B. SELECT order_id, customer_name, product_name FROM Orders, Customers, Products WHERE Orders.customer_id = Customers.customer_id;

C. SELECT order_id, customer_name, product_name FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

D. SELECT order_id, customer_name, product_name FROM Customers JOIN Products ON Customers.customer_id = Products.product_id;

Solution

1. Step 1: Identify needed joins

   Orders must join Customers on customer_id and Products on product_id to get names.

2. Step 2: Write correct JOIN syntax

   Use JOIN with ON for both tables to link properly.

3. Step 3: Check other options

   B misses the product_id join condition; C misses Products join; D joins unrelated keys.

4. Final Answer:

   SELECT order_id, customer_name, product_name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id JOIN Products ON Orders.product_id = Products.product_id; -> Option A

5. Quick Check:

   Correct JOINs on keys = A [OK]

Hint: Join all related tables on keys using ON [OK]

Common Mistakes:

• Missing one join to include all data
• Joining on wrong columns
• Using WHERE instead of ON for joins