w3resource

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 Expression: MySQL Northwind: Display discontinued Product list.

Relational Algebra Tree:

Relational Algebra Tree: MySQL Northwind: Display discontinued Product list.

Structure of Products table:

northwind database 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?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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