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

MySQL Northwind database: Exercise-7 with Solution

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

Code:

SELECT DISTINCT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT avg(UnitPrice) FROM Products)
ORDER BY UnitPrice;

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.