SQL - Structured Query Language Writing Subqueries 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?

Accepted Answer

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

Answer

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

Answer

SELECT ProductName, AVG(ListPrice) FROM Products HAVING ListPrice > AVG(ListPrice);

Answer

SELECT P1.ProductName FROM Products P1 JOIN Products P2 ON P1.ListPrice > AVG(P2.ListPrice);

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

An inner query that is executed once before the outer query starts.

Answer

An inner query that can be run independently of the outer query.

Answer

An inner query that is used in the FROM clause to create a temporary table.

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

SELECT Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

Answer

SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.OrderID IS NOT NULL;

Answer

SELECT Name FROM Customers WHERE CustomerID = ANY (SELECT CustomerID FROM Orders);

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

SELECT c.CustomerName, (SELECT AVG(o.OrderTotal) FROM Orders o WHERE c.CustomerID = o.CustomerID) AS AvgTotal FROM Customers c;

Answer

SELECT c.CustomerName, AVG(o.OrderTotal) FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;

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

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

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

Answer

WHERE Salary IN (SELECT Salary FROM Employees WHERE Department = 'Intern')

Answer

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

Question 6

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

Accepted Answer

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

Answer

The subquery is not enclosed in parentheses.

Answer

The subquery cannot be used in a `WHERE` clause.

Answer

The subquery selects a column that does not exist in the `Products` table.

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?

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?