MySQL Northwind database, Products table : Display discontinued Product list
MySQL Northwind database: Exercise-3 with Solution
3. Write a MySQL query to get discontinued Product list (Product ID and name).
Code:
-- This SQL query retrieves the ProductID and ProductName columns from the Products table, filtering records where the Discontinued column is set to 1 (indicating the product is discontinued), 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 = 1 -- Filters the rows to include only those where the Discontinued column is set to 1
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 1, indicating that the product is discontinued.
- 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 17 Alice Mutton 5 Chef Anton's Gumbo Mix 24 Guaran Fantstica 9 Mishi Kobe Niku 53 Perth Pasties 28 Rssle Sauerkraut 42 Singaporean Hokkien Fried Mee 29 Thringer Rostbratwurst
MySQL Online Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/mysql-exercises/northwind/products-table-exercises/mysql-exercise-northwind-database-product-table-3.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics