Subquery with EXISTS operator in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find customers with orders

Given two tables Customers and Orders, which query returns all customers who have placed at least one order?

SQL

SELECT CustomerID, CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

A Returns customers who have no orders.

B Returns all customers regardless of orders.

C Syntax error due to missing alias.

D Returns all customers who have at least one order.

💡 Hint

EXISTS checks if the subquery returns any rows for each customer.

The EXISTS operator returns true if the subquery finds any matching rows. Here, it checks if there is at least one order for each customer.

❓ query_result

intermediate

Identify products never ordered

Which query returns products that have never been ordered from the Products and OrderDetails tables?

SQL

SELECT ProductID, ProductName FROM Products WHERE NOT EXISTS (SELECT 1 FROM OrderDetails WHERE OrderDetails.ProductID = Products.ProductID);

A Syntax error due to missing NOT keyword.

B Returns products that have been ordered at least once.

C Returns products that have never been ordered.

D Returns all products regardless of orders.

💡 Hint

NOT EXISTS returns true when the subquery finds no matching rows.

The query uses NOT EXISTS to find products without any matching order details, meaning never ordered.

📝 Syntax

advanced

Detect syntax error in EXISTS subquery

Which option contains a syntax error in the use of EXISTS with a subquery?

SQL

SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

A SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

B SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

C SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT * FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

D SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT OrderID FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

💡 Hint

The subquery in EXISTS must select at least one column or a constant.

Option B is missing the column or constant after SELECT, causing a syntax error.

❓ optimization

advanced

Optimize query with EXISTS vs IN

Which query is generally more efficient to find customers with orders, assuming Orders has many rows?

A SELECT CustomerID FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

B SELECT CustomerID FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);

C SELECT CustomerID FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

D SELECT DISTINCT CustomerID FROM Orders;

💡 Hint

EXISTS stops searching after finding the first match, IN may scan all matches.

EXISTS is often more efficient because it stops checking once a match is found, while IN may process all matching rows.

🧠 Conceptual

expert

Understanding EXISTS with correlated subqueries

Consider the query:

SELECT DepartmentID FROM Departments d WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID AND e.Salary > 50000);

What does this query return?

A Departments that have at least one employee earning more than 50000.

B All departments regardless of employee salaries.

C Departments with no employees earning more than 50000.

D Employees earning more than 50000 in any department.

💡 Hint

EXISTS checks for each department if the subquery finds any employee with salary > 50000.

The query returns departments where the subquery finds at least one employee with salary above 50000.