Introduction:

Sub-queries and nested queries are powerful tools in SQL, enabling the use of results from one query as input for another. They essentially allow for multi-step logic to be applied in a single SQL command.

Concept 1: Introduction to Sub-queries

Concepts: A sub-query, also known as a subselect or inner query, is a query embedded within another SQL query. It can retrieve data that will be used in the main query as a condition to further narrow down the result set.

Sample Question: How do you find products that are priced above the average price of products in the 'Smartphone' category?

Example:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category = 'Smartphone');

Sample Output:

+----------------+-------+
| product_name   | price |
+----------------+-------+
| iPhone 13 Pro  | 999   |
| Samsung S22    | 850   |
| ...            | ...   |
+----------------+-------+

Concept 2: Sub-queries in the FROM Clause

Concepts: Sometimes, you might want to treat the result of a sub-query as a table itself to keep the relevant columns only while doing data manipulation

Sample Question: What are the average prices of each product category in our store?

Example:

SELECT category, AVG(price) as average_price
FROM (SELECT category, price FROM products) AS subquery
GROUP BY category;

Sample Output:

+------------+--------------+
| category   | average_price|
+------------+--------------+
| Smartphone | 650          |
| Laptop     | 1200         |
| ...        | ...          |
+------------+--------------+