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.