MySQL Northwind database, Products table: Display Product list where current products cost less than $20

MySQL Northwind database: Exercise-5 with Solution

5. Write a MySQL query to get Product list (id, name, unit price) where current products cost less than $20.

Code:

-- This SQL query retrieves the ProductID, ProductName, and UnitPrice columns from the Products table, filtering records where the UnitPrice is less than 20 and the Discontinued column is set to False, and orders the results by UnitPrice in descending order.

SELECT ProductID, ProductName, UnitPrice -- Selects the ProductID, ProductName, and UnitPrice columns from the Products table
FROM Products -- Specifies the table from which to retrieve data, in this case, the Products table
WHERE (((UnitPrice)<20) AND ((Discontinued)=False)) -- Filters the rows to include only those where the UnitPrice is less than 20 and the Discontinued column is set to False
ORDER BY UnitPrice DESC; -- Orders the result set by the UnitPrice column in descending order

Explanation:

• The SELECT statement retrieves the ProductID, ProductName, and UnitPrice columns from the Products table.
• The WHERE clause filters the rows to include only those where the UnitPrice is less than 20 and the Discontinued column is set to False.
• 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:

ProductID		ProductName			UnitPrice
57			Ravioli Angelo			19.5000
44			Gula Malacca			19.4500
2			Chang				19.0000
36			Inlagd Sill			19.0000
40			Boston Crab Meat		18.4000
76			Lakkalikri			18.0000
1			Chai				18.0000
39			Chartreuse verte		18.0000
35			Steeleye Stout			18.0000
16			Pavlova				17.4500
66			Louisiana Hot Spiced Okra	17.0000
50			Valkoinen suklaa		16.2500
15			Genen Shouyu			15.5000
73			Rd Kaviar			15.0000
70			Outback Lager			15.0000
67			Laughing Lumberjack Lager	14.0000
25			NuNuCa Nu-Nougat-Creme		14.0000
34			Sasquatch Ale			14.0000
58			Escargots de Bourgogne		13.2500
77			Original Frankfurter grne Soe	13.0000
48			Chocolade			12.7500
31			Gorgonzola Telino		12.5000
68			Scottish Longbreads		12.5000
46			Spegesild			12.0000
3			Aniseed Syrup			10.0000
21			Sir Rodney's Scones		10.0000
74			Longlife Tofu			10.0000
41			Jack's New England Clam Chowder	9.6500
45			Rogede sild			9.5000
47			Zaanse koeken			9.5000
19			Teatime Chocolate Biscuits	9.2000
23			Tunnbrd				9.0000
75			Rhnbru Klosterbier		7.7500
54			Tourtire			7.4500
52			Filo Mix			7.0000
13			Konbu				6.0000
33			Geitost				2.5000

MySQL Online Editor:

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