MySQL Northwind database, Products table: Display most expense and least expensive Product

MySQL Northwind database: Exercise-4 with Solution

4. Write a MySQL query to get most expense and least expensive Product list (name and unit price).

Code:

-- This SQL query retrieves the ProductName and UnitPrice columns from the Products table and orders the results by UnitPrice in descending order.

SELECT ProductName, UnitPrice -- Selects the ProductName and UnitPrice columns from the Products table
FROM Products -- Specifies the table from which to retrieve data, in this case, the Products table
ORDER BY UnitPrice DESC; -- Orders the result set by the UnitPrice column in descending order
 

Explanation:

• The SELECT statement retrieves the ProductName and UnitPrice columns from the Products table.
• The ORDER BY clause orders the result set by the UnitPrice column in descending order.

Relational Algebra Expression:

Relational Algebra Tree:

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
Cte de Blaye				263.5000
Thringer Rostbratwurst			123.7900
Mishi Kobe Niku				97.0000
Sir Rodney's Marmalade			81.0000
Carnarvon Tigers			62.5000
Raclette Courdavault			55.0000
Manjimup Dried Apples			53.0000
Tarte au sucre				49.3000
Ipoh Coffee				46.0000
Rssle Sauerkraut			45.6000
Vegie-spread				43.9000
Schoggi Schokolade			43.9000
Northwoods Cranberry Sauce		40.0000
Alice Mutton				39.0000
Queso Manchego La Pastora		38.0000
Gnocchi di nonna Alice			38.0000
Gudbrandsdalsost			36.0000
Mozzarella di Giovanni			34.8000
Camembert Pierrot			34.0000
Wimmers gute Semmelkndel		33.2500
Perth Pasties				32.8000
Mascarpone Fabioli			32.0000
Gumbr Gummibrchen			31.2300
Ikura					31.0000
Uncle Bob's Organic Dried Pears		30.0000
Sirop d'rable				28.5000
Gravad lax				26.0000
Nord-Ost Matjeshering			25.8900
Grandma's Boysenberry Spread		25.0000
Pt chinois				24.0000
Tofu					23.2500
Chef Anton's Cajun Seasoning		22.0000
Flotemysost				21.5000
Chef Anton's Gumbo Mix			21.3500
Louisiana Fiery Hot Pepper Sauce	21.0500
Queso Cabrales				21.0000
Gustaf's Knckebrd			21.0000
Maxilaku				20.0000
Ravioli Angelo				19.5000
Gula Malacca				19.4500
Chang					19.0000
Inlagd Sill				19.0000
Boston Crab Meat			18.4000
Lakkalikri				18.0000
Steeleye Stout				18.0000
Chartreuse verte			18.0000
Chai					18.0000
Pavlova					17.4500
Louisiana Hot Spiced Okra		17.0000
Valkoinen suklaa			16.2500
Genen Shouyu				15.5000
Outback Lager				15.0000
Rd Kaviar				15.0000
NuNuCa Nu-Nougat-Creme			14.0000
Laughing Lumberjack Lager		14.0000
Sasquatch Ale				14.0000
Singaporean Hokkien Fried Mee		14.0000
Escargots de Bourgogne			13.2500
Original Frankfurter grne Soe		13.0000
Chocolade				12.7500
Scottish Longbreads			12.5000
Gorgonzola Telino			12.5000
Spegesild				12.0000
Sir Rodney's Scones			10.0000
Aniseed Syrup				10.0000
Longlife Tofu				10.0000
Jack's New England Clam Chowder		9.6500
Rogede sild				9.5000
Zaanse koeken				9.5000
Teatime Chocolate Biscuits		9.2000
Tunnbrd					9.0000
Rhnbru Klosterbier			7.7500
Tourtire				7.4500
Filo Mix				7.0000
Konbu					6.0000
Guaran Fantstica			4.5000
Geitost					2.5000

MySQL Online Editor:

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