SQL - Structured Query Language Writing Subqueries Questions and Answers 1 — Questions and Answers
Question 1: 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?
- SELECT ProductName FROM Products WHERE ListPrice > AVG(ListPrice);
- SELECT ProductName, AVG(ListPrice) FROM Products HAVING ListPrice > AVG(ListPrice);
- SELECT ProductName FROM Products WHERE ListPrice > (SELECT AVG(ListPrice) FROM Products); (Correct answer)
- SELECT P1.ProductName FROM Products P1 JOIN Products P2 ON P1.ListPrice > AVG(P2.ListPrice);
Correct 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.
Question 2: Which of the following statements best describes a correlated subquery?
- An inner query that is executed once before the outer query starts.
- An inner query that can be run independently of the outer query.
- An inner query that is used in the FROM clause to create a temporary table.
- An inner query that depends on the outer query for its values and is re-evaluated for each row processed by the outer query. (Correct answer)
Correct 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.
Question 3: 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?
- SELECT Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);
- SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.OrderID IS NOT NULL;
- SELECT Name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID); (Correct answer)
- SELECT Name FROM Customers WHERE CustomerID = ANY (SELECT CustomerID FROM Orders);
Correct 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.
Question 4: 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?
- SELECT c.CustomerName, (SELECT AVG(o.OrderTotal) FROM Orders o WHERE c.CustomerID = o.CustomerID) AS AvgTotal FROM Customers c;
- SELECT c.CustomerName, AVG(o.OrderTotal) FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;
- 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; (Correct answer)
- SELECT c.CustomerName, AVG(o.OrderTotal) FROM Customers c, Orders o WHERE c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING AVG(o.OrderTotal);
Correct 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.
Question 5: 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?
- WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'Intern')
- WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Intern') (Correct answer)
- WHERE Salary IN (SELECT Salary FROM Employees WHERE Department = 'Intern')
- WHERE Salary = ALL (SELECT Salary FROM Employees WHERE Department = 'Intern')
Correct 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.
Question 6: Why will the following SQL query fail? `SELECT ProductName FROM Products WHERE ProductID = (SELECT ProductID FROM OrderDetails WHERE Quantity > 100);`
- The subquery is not enclosed in parentheses.
- The subquery returns multiple rows, which cannot be compared using the `=` operator. (Correct answer)
- The subquery cannot be used in a `WHERE` clause.
- The subquery selects a column that does not exist in the `Products` table.
Correct 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 `=`.
SQL - Structured Query Language Writing Subqueries Questions and Answers 1
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?