Queries Within Queries
SELECT
, FROM
, or WHERE
clauses.
Imagine you want to answer questions like:
product_id | product_name | category | price | stock |
---|---|---|---|---|
1 | Wireless Mouse | Electronics | 1200 | 25 |
2 | Yoga Mat | Fitness | 800 | 50 |
3 | Bluetooth Speaker | Electronics | 2500 | 15 |
4 | Water Bottle | Fitness | 300 | 100 |
5 | Smartwatch | Electronics | 5000 | 10 |
order_id | product_id | order_date | quantity |
---|---|---|---|
101 | 1 | 2025-01-01 | 2 |
102 | 2 | 2025-01-02 | 5 |
103 | 3 | 2025-01-02 | 1 |
104 | 5 | 2025-01-03 | 2 |
105 | 4 | 2025-01-03 | 10 |
WHERE
Clause(SELECT AVG(price) FROM products)
calculates the average price of all products.product_name | price |
---|---|
Bluetooth Speaker | 2500 |
Smartwatch | 5000 |
FROM
Clausecategory
and price
from the products
table.GROUP BY
.category | avg_price |
---|---|
Electronics | 2900 |
Fitness | 550 |
SELECT
Clauseproduct_name | price | avg_price |
---|---|---|
Wireless Mouse | 1200 | 1400 |
Yoga Mat | 800 | 1400 |
Bluetooth Speaker | 2500 | 1400 |
Water Bottle | 300 | 1400 |
Smartwatch | 5000 | 1400 |
IN
Clauseproduct_id
values from the orders
table.IN
.product_name |
---|
Wireless Mouse |
Yoga Mat |
Bluetooth Speaker |
Smartwatch |
Water Bottle |
EXISTS
product_id
exists in the orders
table.NOT EXISTS
ensures only unmatched products are returned.quantity
in the orders
table exceeds its stock
.WHERE
, creating temporary tables with FROM
, or enhancing your output with SELECT
, subqueries are indispensable.
In the next article, we’ll tackle how to insert, update or delete data from the table. Keep practicing!