31. Green Product Identification (easy)

Problem Statement

Table: Inventory
Each row in this table represents a unique product, detailing its ID, whether it’s organic, and whether its packaging is biodegradable.


+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| organic       | enum    |
| biodegradable | enum    |
+---------------+---------+
product_id is the primary key for this table.
organic is an ENUM of type ('Y', 'N'), where 'Y' indicates the product is organic and 'N' indicates it is not.
biodegradable is an ENUM of type ('Y', 'N'), where 'Y' indicates the packaging is biodegradable and 'N' indicates it is not.

Develop a solution to identify the IDs of products that are both organic and have biodegradable packaging.

Example

Input:


Inventory table:
+-------------+---------+--------------+
| product_id  | organic | biodegradable|
+-------------+---------+--------------+
| 10          | Y       | N            |
| 11          | Y       | Y            |
| 12          | N       | Y            |
| 13          | Y       | Y            |
| 14          | N       | N            |
+-------------+---------+--------------+

Output:


+-------------+
| product_id  |
+-------------+
| 11          |
| 13          |
+-------------+

Only products with product_id 11 and 13 fulfill both these conditions, making them environmentally friendly choices in the inventory.

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

To solve this problem, we need to find organic products in the Inventory table with biodegradable packaging. We’ll use SQL queries to filter and extract the required information.

Select Product IDs: Start with a SELECT statement to query the product_id from the Inventory table.
Apply Conditions: Use a WHERE clause to filter products where both organic and biodegradable fields are ‘Y’.

SQL Query

Here’s the final SQL query:


SELECT product_id
FROM Inventory
WHERE organic = 'Y' AND biodegradable = 'Y'

Step by Step Approach

Step 1: Select Product IDs

We begin by selecting the product_id from the Inventory table, which will be the basis for our output.


SELECT product_id
FROM Inventory

Output After Step 1:


+-------------+
| product_id  |
+-------------+
| 10          |
| 11          | 
| 12          |
| 13          |
| 14          |
+-------------+

Step 2: Apply Conditions

Next, we refine our selection by applying conditions only to include products that are organic (organic = 'Y') and have biodegradable packaging (biodegradable = 'Y').


SELECT product_id
FROM Inventory
WHERE organic = 'Y' AND biodegradable = 'Y'

Final Output:


+-------------+
| product_id  |
+-------------+
| 11          | 
| 13          |
+-------------+