Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Subquery and Calculations

Explore how to write and understand SQL queries using subqueries and aggregate functions like AVG. This lesson helps you master filtering data based on calculated conditions and alternative subquery techniques to solve interview problems efficiently.

We'll cover the following...

Solution

The solution is given below:

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