Writing Subqueries Flashcards

A data analyst needs to find all products that have a list price higher than the average list price of all products. Which of the following queries correctly accomplishes this task?

Answer: SELECT ProductName FROM Products WHERE ListPrice > (SELECT AVG(ListPrice) FROM Products);

The correct query uses a scalar subquery in the WHERE clause. The subquery `(SELECT AVG(ListPrice) FROM Products)` is executed first, returning a single value (the average list price). The outer query then uses this single value to filter the products, comparing each product's `ListPrice` to the calculated average. Aggregate functions like `AVG()` cannot be used directly in a `WHERE` clause applied to individual rows.

Which of the following statements best describes a correlated subquery?

Answer: An inner query that depends on the outer query for its values and is re-evaluated for each row processed by the outer query.

A correlated subquery is dependent on the outer query. It cannot be executed independently because it references one or more columns from the outer query's tables. This dependency means the inner query is executed repeatedly, once for each row being processed by the outer query.

A developer is writing a query to find all customers who have placed at least one order. There are two tables: `Customers` (CustomerID, Name) and `Orders` (OrderID, CustomerID). For large tables, which query is generally the most efficient for this existence check?

Answer: SELECT Name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

The `EXISTS` operator is typically more efficient for checking the existence of related rows, especially with large datasets. It stops scanning the subquery as soon as it finds the first matching row, as it only needs to determine if the subquery returns any rows (TRUE/FALSE). In contrast, `IN` with a subquery often requires the database to materialize the entire result set of the subquery first before processing the outer query.

You need to write a query that calculates the average order total for each customer and then joins this result back to the `Customers` table to display the customer's name and their average order total. The `Orders` table contains `CustomerID` and `OrderTotal`. What is the correct way to structure this query?

Answer: SELECT c.CustomerName, Agg.AvgTotal FROM Customers c JOIN (SELECT CustomerID, AVG(OrderTotal) AS AvgTotal FROM Orders GROUP BY CustomerID) AS Agg ON c.CustomerID = Agg.CustomerID;

This scenario is a perfect use case for a subquery in the `FROM` clause, also known as a derived table. The subquery `(SELECT CustomerID, AVG(OrderTotal) AS AvgTotal FROM Orders GROUP BY CustomerID)` first calculates the average total for each customer. This result set is then treated like a temporary table (aliased as `Agg`) and joined with the `Customers` table to retrieve the customer names.

A query is written to find employees whose salary is greater than ALL salaries in the 'Intern' department. The subquery `(SELECT Salary FROM Employees WHERE Department = 'Intern')` returns the values (30000, 32000, 35000). Which of the following `WHERE` clauses will correctly identify an employee with a salary of 40000?

Answer: WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Intern')

The `ALL` operator is used with a comparison operator to compare a value to every value in a list returned by a subquery. The condition `> ALL` evaluates to TRUE only if the value is greater than every single value in the subquery's result set. A salary of 40000 is greater than 30000, 32000, and 35000, so it satisfies the condition.

Why will the following SQL query fail? `SELECT ProductName FROM Products WHERE ProductID = (SELECT ProductID FROM OrderDetails WHERE Quantity > 100);`

Answer: The subquery returns multiple rows, which cannot be compared using the `=` operator.

The `=` operator is a single-row comparison operator, meaning it expects to compare against a single value. If the subquery `(SELECT ProductID FROM OrderDetails WHERE Quantity > 100)` finds more than one order detail with a quantity over 100, it will return multiple rows. This causes an error because the database doesn't know which of the multiple values to compare `ProductID` against. To fix this, one would typically use the `IN` operator instead of `=`.