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 query to get Product list (name, units on order , units in stock) of stock is less than the quantity on order.

Code:

SELECT ProductName,  UnitsOnOrder , UnitsInStock
FROM Products
WHERE (((Discontinued)=False) AND ((UnitsInStock)<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.