0% completed
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
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 theproduct_id
from theInventory
table. - Apply Conditions: Use a
WHERE
clause to filter products where bothorganic
andbiodegradable
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 | +-------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible