MySQL Northwind database, Products table: Display current Product list

MySQL Northwind database: Exercise-2 with Solution

2. Write a MySQL query to get current Product list (Product ID and name).

Code:

S-- This SQL query retrieves the ProductID and ProductName columns from the Products table, filtering records where the Discontinued column is set to "False", and orders the results by ProductName.

SELECT ProductID, ProductName -- Selects the ProductID and ProductName columns from the Products table
FROM Products -- Specifies the table from which to retrieve data, in this case, the Products table
WHERE Discontinued = "False" -- Filters the rows to include only those where the Discontinued column is set to "False"
ORDER BY ProductName; -- Orders the result set by the ProductName column in ascending order

Explanation:

• The SELECT statement retrieves the ProductID and ProductName columns from the Products table.
• The WHERE clause filters the rows to include only those where the Discontinued column is set to "False".
• The ORDER BY clause orders the result set by the ProductName column in ascending 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
3			Aniseed Syrup
40			Boston Crab Meat
60			Camembert Pierrot
18			Carnarvon Tigers
1			Chai
2			Chang
39			Chartreuse verte
4			Chef Anton's Cajun Seasoning
48			Chocolade
38			Cte de Blaye
58			Escargots de Bourgogne
52			Filo Mix
71			Flotemysost
33			Geitost
15			Genen Shouyu
56			Gnocchi di nonna Alice
31			Gorgonzola Telino
6			Grandma's Boysenberry Spread
37			Gravad lax
69			Gudbrandsdalsost
44			Gula Malacca
26			Gumbr Gummibrchen
22			Gustaf's Knckebrd
10			Ikura
36			Inlagd Sill
43			Ipoh Coffee
41			Jack's New England Clam Chowder
13			Konbu
76			Lakkalikri
67			Laughing Lumberjack Lager
74			Longlife Tofu
65			Louisiana Fiery Hot Pepper Sauce
66			Louisiana Hot Spiced Okra
51			Manjimup Dried Apples
32			Mascarpone Fabioli
49			Maxilaku
72			Mozzarella di Giovanni
30			Nord-Ost Matjeshering
8			Northwoods Cranberry Sauce
25			NuNuCa Nu-Nougat-Creme
77			Original Frankfurter grne Soe
70			Outback Lager
16			Pavlova
55			Pt chinois
11			Queso Cabrales
12			Queso Manchego La Pastora
59			Raclette Courdavault
57			Ravioli Angelo
73			Rd Kaviar
75			Rhnbru Klosterbier
45			Rogede sild
34			Sasquatch Ale
27			Schoggi Schokolade
68			Scottish Longbreads
20			Sir Rodney's Marmalade
21			Sir Rodney's Scones
61			Sirop d'rable
46			Spegesild
35			Steeleye Stout
62			Tarte au sucre
19			Teatime Chocolate Biscuits
14			Tofu
54			Tourtire
23			Tunnbrd
7			Uncle Bob's Organic Dried Pears
50			Valkoinen suklaa
63			Vegie-spread
64			Wimmers gute Semmelkndel
47			Zaanse koeken

MySQL Online Editor:

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