SQL Programming Resources

Practice 4 — Rank customers by total spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
RANK() OVER ( ORDER BY total_spending DESC ) AS spending_rank
FROM customer_sales;

Practice 5 — Create customer segments based on spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
CASE WHEN total_spending >= 100000 THEN 'VIP'
     WHEN total_spending >= 50000 THEN 'Premium'
     ELSE 'Standard' END AS segment
FROM customer_sales;

🧪 Mini SQL Challenge You have: orders(order_id, customer_id, amount, order_date) Find the top 5 customers by total spending, but only consider customers who have placed at least 3 orders. Solution:

WITH customer_metrics AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
),
qualified_customers AS (
    SELECT customer_id, order_count, total_spending
    FROM customer_metrics WHERE order_count >= 3
)

SELECT customer_id, order_count, total_spending
FROM qualified_customers ORDER BY total_spending DESC LIMIT 5;

Logic: Orders ↓ GROUP BY customer ↓ Calculate order count + spending ↓ Keep customers with ≥ 3 orders ↓ Sort by spending ↓ Return top 5 💡 Double Tap ❤️ For More ----- 1.87 ₽ · /balance_help

Recursive CTEs are an advanced topic, but understanding their purpose is important. 🧠 17. Why CTEs Are Valuable for Data Analysts Imagine a business request: «Identify customers who increased their spending, classify them by segment, compare them against the average, and calculate their rank.» Trying to write everything as one giant query can become difficult. CTEs allow you to think in stages: • Raw Orders ↓ • Customer Metrics ↓ • Customer Segmentation ↓ • Average Comparison ↓ • Ranking ↓ • Final Report Each step has a clear purpose. ⚠️ 18. Common CTE Mistakes Mistake 1 — Forgetting the CTE name Incorrect: WITH ( SELECT ... ) Correct: WITH customer_sales AS ( SELECT ... ) Mistake 2 — Forgetting the comma between CTEs Incorrect: Two CTEs without comma Correct: Separate CTEs with a comma Mistake 3 — Using a CTE without understanding its grain A CTE might produce: 1 row = 1 order while you think it produces: 1 row = 1 customer Always validate the grain. Mistake 4 — Creating too many unnecessary CTEs CTEs should make logic clearer. If every two-line transformation becomes its own CTE, the query can become harder to follow. Use them when they improve structure. 🔎 19. Debugging with CTEs One major advantage is easier debugging. Suppose your final query produces incorrect revenue. Instead of debugging one huge query, test each stage. First:

WITH customer_sales AS (
    ...
)
SELECT *
FROM customer_sales;

• Check the results. • Then add the next CTE. • This allows you to identify exactly where the numbers become incorrect. 🎤 SQL Interview Questions • Q1. What is a CTE? A Common Table Expression is a named temporary result set defined using the "WITH" clause and available to the query that follows it. • Q2. What is the syntax of a CTE? WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name; • Q3. Can you create multiple CTEs? Yes. WITH cte1 AS ( ... ), cte2 AS ( ... ) SELECT ... FROM cte2; • Q4. Can one CTE reference another CTE? Yes. A later CTE can generally reference an earlier CTE in the same "WITH" clause. • Q5. What is the difference between a CTE and a subquery? Both can represent intermediate query results, but CTEs often make multi-step logic easier to read and reuse within the same statement. • Q6. Does a CTE permanently store data? No. A standard CTE is associated with the SQL statement in which it is defined. • Q7. Does using a CTE always improve performance? No. CTEs primarily improve query organization and readability. Performance depends on the database engine and execution plan. • Q8. What is a recursive CTE? A CTE that references itself, typically used for hierarchical or recursive data. • Q9. Why are CTEs useful in analytics? They allow complex analytical logic to be divided into clear, manageable stages. • Q10. What should you check when using multiple CTEs? Check the grain, row count, joins, aggregations, and filters at each stage. 📝 Practice Questions Practice 1 — Calculate total spending per customer using a CTE.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)

SELECT * FROM customer_sales;

Practice 2 — Find customers spending more than ₹50,000.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)

SELECT * FROM customer_sales WHERE total_spending > 50000;

Practice 3 — Calculate order count and revenue per customer.

WITH customer_metrics AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_revenue
    FROM orders GROUP BY customer_id
)
SELECT * FROM customer_metrics;

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY customer_id
)

SELECT
    SUM(revenue) AS total_revenue,
    COUNT(*) AS active_customers,
    SUM(revenue) / NULLIF(COUNT(*), 0) AS revenue_per_customer
FROM customer_revenue;

The CTE first creates: 1 row = 1 customer Then the final query calculates KPIs from that customer-level dataset. 🧱 14. CTE for Multi-Step Analytics Let's build a slightly more realistic analysis. • Step 1 — Calculate customer sales

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

• Step 2 — Create customer segments

, segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

• Step 3 — Analyze segments

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

The complete query:

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

This is a good example of structured analytical SQL. 🪟 15. CTE + Window Functions Preview CTEs become especially powerful when combined with window functions. For example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,
    RANK() OVER (
        ORDER BY total_spending DESC
    ) AS spending_rank
FROM customer_sales;

• The CTE creates the customer-level metric. • The window function ranks the customers. • This pattern is extremely common in analytics. 🔄 16. Recursive CTEs There is another advanced type of CTE: Recursive CTE It allows a query to repeatedly reference itself. Common use cases include: • organizational hierarchies • employee-manager structures • category trees • folder structures • graph-like relationships • generating sequences Example structure:

WITH RECURSIVE employee_tree AS (
    SELECT
        employee_id,
        employee_name,
        manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id
    FROM employees e
    JOIN employee_tree t
        ON e.manager_id = t.employee_id
)

SELECT *
FROM employee_tree;

The CTE changes the grain to: 1 row = 1 customer That makes the final filtering straightforward. 📈 6. CTE + JOIN CTEs become even more useful when combined with JOINs.

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    c.customer_name,
    cs.total_spending
FROM customers c
JOIN customer_sales cs
    ON c.customer_id = cs.customer_id;

• The CTE handles the aggregation. • The main query handles the customer information. 💰 7. CTE + COALESCE Want to include customers who haven't placed orders?

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(cs.total_spending, 0) AS total_spending
FROM customers c
LEFT JOIN customer_sales cs
    ON c.customer_id = cs.customer_id;

Now customers without orders appear with: total_spending = 0 🧮 8. CTE + CASE We can also create business segments.

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,

    CASE
        WHEN total_spending >= 100000 THEN 'VIP'
        WHEN total_spending >= 50000 THEN 'Premium'
        ELSE 'Standard'
    END AS customer_segment

FROM customer_sales;

• The CTE creates the metric. • "CASE" converts the metric into business categories. 🔍 9. CTE vs Subquery Both can solve similar problems. • Subquery

SELECT *
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;

• CTE

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT *
FROM customer_sales
WHERE total_spending > 50000;

The CTE often makes multi-step logic easier to read. 🧠 10. CTE vs Temporary Table A CTE is not the same as a permanent table. • CTE

WITH sales AS (...)
SELECT ...

Generally exists only for the duration of that SQL statement. • Temporary table

CREATE TEMP TABLE sales AS
SELECT ...;

A temporary table can generally be referenced by multiple statements during its session, depending on the database. Simple distinction: • CTE → temporary named query result • Temporary table → temporary database object ⚡ 11. CTE Does Not Automatically Mean Faster A common misconception is: CTEs make queries faster. Not necessarily. CTEs primarily improve: • readability • organization • maintainability • debugging • step-by-step logic Performance depends on the database engine and how it optimizes the query. Some databases may inline a CTE, while others may materialize it in certain situations. So: Use CTEs for clear logic, not simply because you expect better performance. 🧪 12. CTE for Data Quality Analysis Suppose we want to identify customers with missing contact information. First create a cleaned customer dataset:

WITH cleaned_customers AS (
    SELECT
        customer_id,
        TRIM(customer_name) AS customer_name,
        LOWER(TRIM(email)) AS email
    FROM customers
)

SELECT *
FROM cleaned_customers
WHERE email IS NULL;

This creates a clean intermediate layer before analysis. 📊 13. CTE for KPI Calculation Suppose we want: «Revenue per customer.»

🚀 SQL Roadmap 2026 — Part 13 🧱 SQL CTEs — Writing Complex Queries in Simple Steps As SQL queries become more advanced, they can become difficult to read. You may have: • JOINs • Subqueries • GROUP BY • CASE • Aggregations • Multiple calculations all inside one query. This is where CTEs become extremely useful. CTE stands for: «Common Table Expression» A CTE lets you create a temporary named result set and then use it in your main query. Think of it as: • Step 1 → Prepare the data • Step 2 → Transform the data • Step 3 → Analyze the data • Step 4 → Return the result 🧠 1. Basic CTE Syntax A CTE starts with:

WITH cte_name AS (
    SELECT
        ...
    FROM ...
)
SELECT *
FROM cte_name;

Example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT *
FROM customer_sales;

The CTE customer_sales acts like a temporary result set for the duration of the query. 📊 2. Why Use CTEs? Without a CTE, a complex query can become difficult to understand. For example:

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > 50000;

With a CTE:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending
FROM customer_totals
WHERE total_spending > 50000;

The second version is often much easier to read. 🧩 3. CTEs Break Complex Problems into Steps Suppose the business question is: «Find customers whose total spending is above the average customer spending.» Instead of writing everything as one large nested query, break it into logical steps. • Step 1 — Calculate spending per customer

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

• Step 2 — Calculate average spending

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

average_spending AS (
    SELECT
        AVG(total_spending) AS avg_spending
    FROM customer_totals
)

• Step 3 — Compare customers with the average

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

average_spending AS (
    SELECT
        AVG(total_spending) AS avg_spending
    FROM customer_totals
)

SELECT
    c.customer_id,
    c.total_spending
FROM customer_totals c
CROSS JOIN average_spending a
WHERE c.total_spending > a.avg_spending;

Now the logic is much easier to follow. 🔗 4. Multiple CTEs A single query can contain multiple CTEs. Structure:

WITH first_cte AS (
    ...
),

second_cte AS (
    ...
),

third_cte AS (
    ...
)

SELECT ...
FROM third_cte;

Later CTEs can reference earlier CTEs. 🏢 5. Real-World Example Suppose an e-commerce company wants: «Customers with spending above ₹50,000 and at least 5 orders.» First calculate customer-level metrics:

WITH customer_metrics AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    order_count,
    total_spending
FROM customer_metrics
WHERE order_count >= 5
  AND total_spending > 50000;

Here are some essential data science concepts from A to Z: A - Algorithm: A set of rules or instructions used to solve a problem or perform a task in data science. B - Big Data: Large and complex datasets that cannot be easily processed using traditional data processing applications. C - Clustering: A technique used to group similar data points together based on certain characteristics. D - Data Cleaning: The process of identifying and correcting errors or inconsistencies in a dataset. E - Exploratory Data Analysis (EDA): The process of analyzing and visualizing data to understand its underlying patterns and relationships. F - Feature Engineering: The process of creating new features or variables from existing data to improve model performance. G - Gradient Descent: An optimization algorithm used to minimize the error of a model by adjusting its parameters. H - Hypothesis Testing: A statistical technique used to test the validity of a hypothesis or claim based on sample data. I - Imputation: The process of filling in missing values in a dataset using statistical methods. J - Joint Probability: The probability of two or more events occurring together. K - K-Means Clustering: A popular clustering algorithm that partitions data into K clusters based on similarity. L - Linear Regression: A statistical method used to model the relationship between a dependent variable and one or more independent variables. M - Machine Learning: A subset of artificial intelligence that uses algorithms to learn patterns and make predictions from data. N - Normal Distribution: A symmetrical bell-shaped distribution that is commonly used in statistical analysis. O - Outlier Detection: The process of identifying and removing data points that are significantly different from the rest of the dataset. P - Precision and Recall: Evaluation metrics used to assess the performance of classification models. Q - Quantitative Analysis: The process of analyzing numerical data to draw conclusions and make decisions. R - Random Forest: An ensemble learning algorithm that builds multiple decision trees to improve prediction accuracy. S - Support Vector Machine (SVM): A supervised learning algorithm used for classification and regression tasks. T - Time Series Analysis: A statistical technique used to analyze and forecast time-dependent data. U - Unsupervised Learning: A type of machine learning where the model learns patterns and relationships in data without labeled outputs. V - Validation Set: A subset of data used to evaluate the performance of a model during training. W - Web Scraping: The process of extracting data from websites for analysis and visualization. X - XGBoost: An optimized gradient boosting algorithm that is widely used in machine learning competitions. Y - Yield Curve Analysis: The study of the relationship between interest rates and the maturity of fixed-income securities. Z - Z-Score: A standardized score that represents the number of standard deviations a data point is from the mean. Credits: https://t.me/free4unow_backup Like if you need similar content 😄👍

🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥 Explore these FREE certification courses in today’s most in-demand technology fields: 📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF 💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo 💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q ☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn 🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv 🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH ⚡ Start learning today and prepare yourself for better career opportunities in 2026!

⚠️ 23. Common Subquery Mistakes Mistake 1 — Returning multiple rows with = Use IN instead of = when multiple rows are expected. Mistake 2 — Forgetting NULL behavior with NOT IN Mistake 3 — Ignoring duplicates Mistake 4 — Making the query unnecessarily complicated 🎤 SQL Interview Questions Q1. What is a subquery? A query nested inside another query. Q2. What is a scalar subquery? A subquery that returns a single value. Q3. When should you use IN? When the subquery returns a set of values. Q4. What does EXISTS do? It checks whether at least one matching row exists. Q5. What is a correlated subquery? A subquery that references a column from the outer query. Q6. What is a derived table? A subquery in the FROM clause treated as a temporary result set. Q7. Can a subquery be used in SELECT? Yes. Q8. What is the difference between IN and EXISTS? IN compares against a set, while EXISTS checks for existence. Q9. Why is NOT IN dangerous with NULL? Three-valued logic can cause unexpected results. Q10. Can every subquery be replaced with a JOIN? Many can, but the best approach depends on the logic. 📝 Practice Questions Practice 1 — Employees earning more than average:

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Practice 2 — Customers with at least one order:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Practice 3 — Customers with more than 5 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

Practice 4 — Products higher than average price:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Practice 5 — Customers who never placed an order:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🧪 Mini SQL Challenge Find customers whose total spending is greater than the average customer spending.

SELECT customer_id, total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > (
    SELECT AVG(total_spending)
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_spending
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

💡 Double Tap ❤️ For More ----- 0.118646 ₽ · /balance_help

Means: Return customers for whom no matching order exists. 🧠 12. EXISTS vs IN IN → Compare against a set of returned values EXISTS → Check whether a matching row exists 🔄 13. Correlated Subquery Depends on the current row of the outer query.

SELECT
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

«Is this employee's salary higher than the average salary of their own department?» 🏢 14. Above-Department-Average Salary This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average. 📦 15. Subquery in FROM It can appear in the FROM clause.

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales;

This is often called a derived table. 📈 16. Finding High-Value Customers

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;

🧩 17. Subquery in SELECT

SELECT
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers c;

⚠️ 18. But Be Careful with Correlated Subqueries Instead of a correlated subquery in SELECT, you could use:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

The better choice depends on the database optimizer, table size, indexes, and other factors. 🧱 19. Nested Subqueries

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id IN (
        SELECT category_id
        FROM categories
        WHERE category_name = 'Electronics'
    )
);

Deeply nested queries can become difficult to read. Use CTEs instead. 🧠 20. Subqueries vs JOINs Subquery:

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

JOIN:

SELECT DISTINCT
    c.*
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Choose based on readability, business logic, and performance. 🏆 21. Subqueries for Business Analysis Find products above average:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Find customers with at least one order:

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Find customers with more than 10 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 10
);

🧮 22. Subquery for KPI Comparison

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

🚀 SQL Roadmap 2026 — Part 12 🧩 SQL Subqueries — Using One Query Inside Another So far, we've learned how to retrieve, filter, group, transform, and combine data. But sometimes a business question requires one query to use the result of another query. For example: «Find employees whose salary is higher than the average salary.» First, we need to calculate:

SELECT AVG(salary)
FROM employees;

Then compare every employee against that result. A subquery allows us to do both inside one SQL statement. 🧠 1. What Is a Subquery? A subquery is a SQL query written inside another SQL query.

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query:

SELECT AVG(salary) FROM employees

calculates the average salary. The outer query then uses that result. Think of it as: Outer Query → Needs an answer → Subquery calculates the answer → Outer Query uses it 🔹 2. Basic Subquery Structure

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT ...
);

The inner query is enclosed in ( ... ). 📊 3. Subquery Returning One Value A scalar subquery returns a single value.

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query returns something like 65000. The outer query then finds employees earning more than 65000. 💰 4. Employees Earning Above Average Classic interview question.

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Logic: Calculate average salary → Compare every employee → Keep salary > average 🏆 5. Products More Expensive Than Average

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

🔢 6. Subquery with COUNT() Customers who have placed more than 5 orders:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

🔗 7. Subquery with IN Useful when a subquery returns multiple values.

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Returns customers who have at least one order. ⚠️ 8. Single Value vs Multiple Values One value → Use =, >, <, >=, <=

WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

Multiple values → Use IN

WHERE customer_id IN (
    SELECT customer_id
    FROM orders
)

🚫 9. NOT IN

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

⚠️ Important NULL Warning: NOT IN can produce unexpected results if the subquery contains NULL. For anti-matching, NOT EXISTS is often safer. 🔎 10. EXISTS

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Means: Return the customer if at least one matching order exists. 🚫 11. NOT EXISTS

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥 Explore these FREE certification courses in today’s most in-demand technology fields: 📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF 💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo 💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q ☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn 🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv 🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH ⚡ Start learning today and prepare yourself for better career opportunities in 2026!

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝘁𝗼 𝗚𝗲𝘁 𝗮 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯 𝗶𝗻 𝟮𝟬𝟮𝟲 📊 Build job-ready skills through live online classes, practical assignments and real-world projects. 💼 End-to-End Placement Support 🤝 500+ Partner Companies 🎓 2000+ Students Placed 🏆 Highest Salary: ₹41 LPA 📞 Get FREE career counselling and check your eligibility! 🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 https://pdlink.in/45vk5ph ⚡Prepare for roles such as Data Analyst, Business Analyst, BI Analyst and Reporting Analyst.

🚀 𝗧𝗼𝗽 𝟯 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 🔥 💫 Artificial Intelligence (AI) 📊 Data Analytics 🔐 Cybersecurity 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/4y2XyN1 🎯 Perfect for Students • Freshers • Beginners • Tech Enthusiasts 💡 Learn for FREE → Build Skills → Upgrade Your Career

𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰: Join for more: https://t.me/sqlanalyst 1. Danny’s Diner: Restaurant analytics to understand the customer orders pattern. Link: https://8weeksqlchallenge.com/case-study-1/ 2. Pizza Runner Pizza shop analytics to optimize the efficiency of the operation Link: https://8weeksqlchallenge.com/case-study-2/ 3. Foodie Fie Subscription-based food content platform Link: https://lnkd.in/gzB39qAT 4. Data Bank: That’s money Analytics based on customer activities with the digital bank Link: https://lnkd.in/gH8pKPyv 5. Data Mart: Fresh is Best Analytics on Online supermarket Link: https://lnkd.in/gC5bkcDf 6. Clique Bait: Attention capturing Analytics on the seafood industry Link: https://lnkd.in/ggP4JiYG 7. Balanced Tree: Clothing Company Analytics on the sales performance of clothing store Link: https://8weeksqlchallenge.com/case-study-7 8. Fresh segments: Extract maximum value Analytics on online advertising Link: https://8weeksqlchallenge.com/case-study-8

𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰: Join for more: https://t.me/sqlanalyst 1. Danny’s Diner: Restaurant analytics to understand the customer orders pattern. Link: https://8weeksqlchallenge.com/case-study-1/ 2. Pizza Runner Pizza shop analytics to optimize the efficiency of the operation Link: https://8weeksqlchallenge.com/case-study-2/ 3. Foodie Fie Subscription-based food content platform Link: https://lnkd.in/gzB39qAT 4. Data Bank: That’s money Analytics based on customer activities with the digital bank Link: https://lnkd.in/gH8pKPyv 5. Data Mart: Fresh is Best Analytics on Online supermarket Link: https://lnkd.in/gC5bkcDf 6. Clique Bait: Attention capturing Analytics on the seafood industry Link: https://lnkd.in/ggP4JiYG 7. Balanced Tree: Clothing Company Analytics on the sales performance of clothing store Link: https://8weeksqlchallenge.com/case-study-7 8. Fresh segments: Extract maximum value Analytics on online advertising Link: https://8weeksqlchallenge.com/case-study-8

𝗧𝗼𝗽 𝟱 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗮𝗿𝗲𝗲𝗿 📊 Want to start a career in Data Science without spending money? Here are 5 beginner-friendly learning resources covering essential skills such as Python, SQL, Machine Learning and hands-on projects. 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/4ilAmok 🎯 Perfect for Students • Freshers • Beginners • Aspiring Data Scientists 💡 Learn → Practice → Build Projects → Create Your Portfolio

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍 💫Accelerate your career in Data Science 💫Discover the skills, tools and career roadmap needed to enter this high-demand field. 🔥 Beginner-friendly online session—no prior experience required! 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/46adC3l (Only few slots left ) 📅 Date: September 11, 2026 ⏰ Time: 7:00 PM