SQL - Structured Query Language Writing Subqueries Questions and Answers 1

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?

Accepted Answer

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

Answer

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?

Accepted 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.

Answer

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?

Accepted Answer

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

Answer

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?

Accepted 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;

Answer

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?

Accepted Answer

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

Answer

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.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Writing Subqueries Questions and Answers 1