MySQL Northwind database, Products table : Display Product of above average price

MySQL Northwind database: Exercise-7 with Solution

7. Write a MySQL query to get Product list (id, name, unit price) of above average price.

Code:

-- This SQL query retrieves distinct ProductName and UnitPrice combinations from the Products table, filtering records where the UnitPrice is greater than the average UnitPrice of all products, and orders the results by UnitPrice.

SELECT DISTINCT ProductName, UnitPrice -- Selects distinct ProductName and UnitPrice combinations from the Products table
FROM Products -- Specifies the table from which to retrieve data, in this case, the Products table
WHERE UnitPrice> (SELECT avg(UnitPrice) FROM Products) -- Filters the rows to include only those where the UnitPrice is greater than the average UnitPrice of all products
ORDER BY UnitPrice; -- Orders the result set by the UnitPrice column

Explanation:

• The SELECT statement retrieves distinct ProductName and UnitPrice combinations from the Products table.
• The WHERE clause filters the rows to include only those where the UnitPrice is greater than the average UnitPrice of all products.
• The subquery(SELECT avg(UnitPrice) FROM Products) calculates the average UnitPrice of all products.
• The ORDER BY clause orders the result set by the UnitPrice column.

Structure of Products table:

Sample records of Products Table:

+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+
| ProductID | ProductName                     | SupplierID | CategoryID | QuantityPerUnit     | UnitPrice | UnitsInStock | UnitsOnOrder | ReorderLevel | Discontinued |
+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+
|         1 | Chai                            |          1 |          1 | 10 boxes x 20 bags  |   18.0000 |           39 |            0 |           10 |              |
|         2 | Chang                           |          1 |          1 | 24 - 12 oz bottles  |   19.0000 |           17 |           40 |           25 |              |
|         3 | Aniseed Syrup                   |          1 |          2 | 12 - 550 ml bottles |   10.0000 |           13 |           70 |           25 |              |
|         4 | Chef Anton's Cajun Seasoning    |          2 |          2 | 48 - 6 oz jars      |   22.0000 |           53 |            0 |            0 |              |
|         5 | Chef Anton's Gumbo Mix          |          2 |          2 | 36 boxes            |   21.3500 |            0 |            0 |            0 | ☺            |
|         6 | Grandma's Boysenberry Spread    |          3 |          2 | 12 - 8 oz jars      |   25.0000 |          120 |            0 |           25 |              |
|         7 | Uncle Bob's Organic Dried Pears |          3 |          7 | 12 - 1 lb pkgs.     |   30.0000 |           15 |            0 |           10 |              |
|         8 | Northwoods Cranberry Sauce      |          3 |          2 | 12 - 12 oz jars     |   40.0000 |            6 |            0 |            0 |              |
|         9 | Mishi Kobe Niku                 |          4 |          6 | 18 - 500 g pkgs.    |   97.0000 |           29 |            0 |            0 | ☺            |
|        10 | Ikura                           |          4 |          8 | 12 - 200 ml jars    |   31.0000 |           31 |            0 |            0 |              |
|        11 | Queso Cabrales                  |          5 |          4 | 1 kg pkg.           |   21.0000 |           22 |           30 |           30 |              |
|        12 | Queso Manchego La Pastora       |          5 |          4 | 10 - 500 g pkgs.    |   38.0000 |           86 |            0 |            0 |              |
|        13 | Konbu                           |          6 |          8 | 2 kg box            |    6.0000 |           24 |            0 |            5 |              |
|        14 | Tofu                            |          6 |          7 | 40 - 100 g pkgs.    |   23.2500 |           35 |            0 |            0 |              |
|        15 | Genen Shouyu                    |          6 |          2 | 24 - 250 ml bottles |   15.5000 |           39 |            0 |            5 |              |
....
+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+

Sample Output:

ProductName					UnitPrice
Uncle Bob's Organic Dried Pears			30.0000
Ikura						31.0000
Gumbr Gummibrchen				31.2300
Mascarpone Fabioli				32.0000
Perth Pasties					32.8000
Wimmers gute Semmelkndel			33.2500
Camembert Pierrot				34.0000
Mozzarella di Giovanni				34.8000
Gudbrandsdalsost				36.0000
Gnocchi di nonna Alice				38.0000
Queso Manchego La Pastora			38.0000
Alice Mutton					39.0000
Northwoods Cranberry Sauce			40.0000
Schoggi Schokolade				43.9000
Vegie-spread					43.9000
Rssle Sauerkraut				45.6000
Ipoh Coffee					46.0000
Tarte au sucre					49.3000
Manjimup Dried Apples				53.0000
Raclette Courdavault				55.0000
Carnarvon Tigers				62.5000
Sir Rodney's Marmalade				81.0000
Mishi Kobe Niku					97.0000
Thringer Rostbratwurst				123.7900
Cte de Blaye					263.5000

MySQL Online Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.