MySQL Northwind database, Products table : Display Product name and quantity/unit

MySQL Northwind database: Exercise-1 with Solution

1. Write a MySQL query to get Product name and quantity/unit.

Code:

-- This SQL query retrieves the names of products and their corresponding quantity per unit from the Products table.

SELECT ProductName, QuantityPerUnit
FROM Products;

Explanation:

• The SELECT statement is used to query data from the Products table.
• It retrieves the ProductName column, which contains the names of products, and the QuantityPerUnit column, which contains information about the quantity per unit for each product.

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				QuantityPerUnit
Chai					10 boxes x 20 bags
Chang					24 - 12 oz bottles
Aniseed Syrup				12 - 550 ml bottles
Chef Anton's Cajun Seasoning		48 - 6 oz jars
Chef Anton's Gumbo Mix			36 boxes
Grandma's Boysenberry Spread		12 - 8 oz jars
Uncle Bob's Organic Dried Pears		12 - 1 lb pkgs.
Northwoods Cranberry Sauce		12 - 12 oz jars
Mishi Kobe Niku				18 - 500 g pkgs.
Ikura					12 - 200 ml jars
Queso Cabrales				1 kg pkg.
Queso Manchego La Pastora		10 - 500 g pkgs.
Konbu					2 kg box
Tofu					40 - 100 g pkgs.
Genen Shouyu				24 - 250 ml bottles
Pavlova					32 - 500 g boxes
Alice Mutton				20 - 1 kg tins
Carnarvon Tigers			16 kg pkg.
Teatime Chocolate Biscuits		10 boxes x 12 pieces
Sir Rodney's Marmalade			30 gift boxes
Sir Rodney's Scones			24 pkgs. x 4 pieces
Gustaf's Knckebrd			24 - 500 g pkgs.
Tunnbrd					12 - 250 g pkgs.
Guaran Fantstica			12 - 355 ml cans
NuNuCa Nu-Nougat-Creme			20 - 450 g glasses
Gumbr Gummibrchen			100 - 250 g bags
Schoggi Schokolade			100 - 100 g pieces
Rssle Sauerkraut			25 - 825 g cans
Thringer Rostbratwurst			50 bags x 30 sausgs.
Nord-Ost Matjeshering			10 - 200 g glasses
Gorgonzola Telino			12 - 100 g pkgs
Mascarpone Fabioli			24 - 200 g pkgs.
Geitost						500 g
Sasquatch Ale				24 - 12 oz bottles
Steeleye Stout				24 - 12 oz bottles
Inlagd Sill					24 - 250 g jars
Gravad lax					12 - 500 g pkgs.
Cte de Blaye				12 - 75 cl bottles
Chartreuse verte			750 cc per bottle
Boston Crab Meat			24 - 4 oz tins
Jack's New England Clam Chowder		12 - 12 oz cans
Singaporean Hokkien Fried Mee		32 - 1 kg pkgs.
Ipoh Coffee				16 - 500 g tins
Gula Malacca				20 - 2 kg bags
Rogede sild				1k pkg.
Spegesild				4 - 450 g glasses
Zaanse koeken				10 - 4 oz boxes
Chocolade				10 pkgs.
Maxilaku				24 - 50 g pkgs.
Valkoinen suklaa			12 - 100 g bars
Manjimup Dried Apples			50 - 300 g pkgs.
Filo Mix				16 - 2 kg boxes
Perth Pasties				48 pieces
Tourtire				16 pies
Pt chinois				24 boxes x 2 pies
Gnocchi di nonna Alice			24 - 250 g pkgs.
Ravioli Angelo				24 - 250 g pkgs.
Escargots de Bourgogne			24 pieces
Raclette Courdavault			5 kg pkg.
Camembert Pierrot			15 - 300 g rounds
Sirop d'rable				24 - 500 ml bottles
Tarte au sucre				48 pies
Vegie-spread				15 - 625 g jars
Wimmers gute Semmelkndel		20 bags x 4 pieces
Louisiana Fiery Hot Pepper Sauce	32 - 8 oz bottles
Louisiana Hot Spiced Okra		24 - 8 oz jars
Laughing Lumberjack Lager		24 - 12 oz bottles
Scottish Longbreads			10 boxes x 8 pieces
Gudbrandsdalsost			10 kg pkg.
Outback Lager				24 - 355 ml bottles
Flotemysost					10 - 500 g pkgs.
Mozzarella di Giovanni			24 - 200 g pkgs.
Rd Kaviar					24 - 150 g jars
Longlife Tofu				5 kg pkg.
Rhnbru Klosterbier			24 - 0.5 l bottles
Lakkalikri				500 ml
Original Frankfurter grne Soe		12 boxes

MySQL Online Editor:

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