SQL Interview Preparation/

...

/

Answer: Subquery and Calculations

Answer: Subquery and Calculations

Find a detailed explanation of using subqueries.

We'll cover the following...

Solution

The solution is given below:

Press + to interact
MySQL
/* The query to find the items and categories with more than average sales */
SELECT p.ProductName, p.Category
FROM   Products AS p 
WHERE  p.UnitsSold >
       (SELECT AVG(p2.UnitsSold) FROM Products AS p2)

Explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects the ProductName and Category columns from the Products table. The Products table has been given an alias p for easy referencing.

  • Line 3: The WHERE clause applies the condition that sales of UnitsSold is greater than the result of the subquery.

  • Line 4: The subquery returns the calculated average of all units sold of the products.

Recall of relevant concepts

We have covered the following concepts in this question: ...

Ask