MySQL Northwind database, Products table : Display Product of cost between $15 and $25

MySQL Northwind database: Exercise-6 with Solution

6. Write a MySQL query to get Product list (name, unit price) where products cost between $15 and $25.

Code:

-- This SQL query retrieves the ProductName and UnitPrice columns from the Products table, filtering records where the UnitPrice falls between 15 and 25 (inclusive) and the product is not discontinued, 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
WHERE (((UnitPrice)>=15 And (UnitPrice)<=25) -- Filters the rows to include only those where the UnitPrice falls between 15 and 25 (inclusive)
AND ((Products.Discontinued)=False)) -- Filters out discontinued products
ORDER BY Products.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 WHERE clause filters the rows to include only those where the UnitPrice falls between 15 and 25 (inclusive) and the product is not discontinued.
• 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
Grandma's Boysenberry Spread			25.0000
Pt chinois					24.0000
Tofu						23.2500
Chef Anton's Cajun Seasoning			22.0000
Flotemysost					21.5000
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
Inlagd Sill					19.0000
Chang						19.0000
Boston Crab Meat				18.4000
Chai						18.0000
Lakkalikri					18.0000
Steeleye Stout					18.0000
Chartreuse verte				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

MySQL Online Editor:

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