MySQL Northwind database, Products table : Display Product list of stock is less than the quantity on order

MySQL Northwind database: Exercise-10 with Solution

10. Write a MySQL query to get Product list (name, units on order , units in stock) of stock is less than the quantity on order.

Code:

-- This SQL query retrieves the ProductName, UnitsOnOrder, and UnitsInStock columns from the Products table, filtering records where the product is not discontinued and the UnitsInStock is less than UnitsOnOrder.

SELECT ProductName,  UnitsOnOrder , UnitsInStock -- Selects the ProductName, UnitsOnOrder, and UnitsInStock columns from the Products table
FROM Products -- Specifies the table from which to retrieve data, in this case, the Products table
WHERE (((Discontinued)=False) AND ((UnitsInStock)<UnitsOnOrder)); -- Filters the rows to include only those where the product is not discontinued and the UnitsInStock is less than UnitsOnOrder

Explanation:

• The SELECT statement retrieves the ProductName, UnitsOnOrder, and UnitsInStock columns from the Products table.
• The WHERE clause filters the rows to include only those where the product is not discontinued (Discontinued = False) and the UnitsInStock is less than UnitsOnOrder.

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			UnitsOnOrder		UnitsInStock
Chang				40				17
Aniseed Syrup			70				13
Queso Cabrales			30				22
Sir Rodney's Scones		40				3
Gorgonzola Telino		70				0
Mascarpone Fabioli		40				9
Gravad lax			50				11
Rogede sild			70				5
Chocolade			70				15
Maxilaku			60				10
Wimmers gute Semmelkndel	80				22
Louisiana Hot Spiced Okra	100				4
Scottish Longbreads		10				6
Longlife Tofu			20				4

MySQL Online Editor:

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