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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics