4 Advanced SQL Explained

Key Concepts

• Subqueries
• Common Table Expressions (CTEs)
• Window Functions
• Transactions

Subqueries

Subqueries, also known as nested queries, are queries embedded within another SQL query. They can be used in the SELECT, FROM, WHERE, or HAVING clauses. Subqueries allow for more complex data retrieval and manipulation by breaking down the problem into smaller, manageable parts.

Example: To find all customers who have placed orders with a total amount greater than the average order amount, you can use a subquery in the WHERE clause:

        SELECT CustomerID, OrderID, TotalAmount
        FROM Orders
        WHERE TotalAmount > (SELECT AVG(TotalAmount) FROM Orders);
    

Analogies: Think of subqueries as a way to ask a question within a question, similar to how you might ask for a specific book within a library by first finding the section it belongs to.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs provide a way to write clearer and more maintainable SQL code by breaking down complex queries into simpler, more readable parts.

Example: To find the total sales for each product category, you can use a CTE to first calculate the total sales for each product and then aggregate by category:

        WITH ProductSales AS (
            SELECT ProductID, SUM(Quantity * Price) AS TotalSales
            FROM OrderDetails
            GROUP BY ProductID
        )
        SELECT CategoryID, SUM(TotalSales) AS CategorySales
        FROM ProductSales
        JOIN Products ON ProductSales.ProductID = Products.ProductID
        GROUP BY CategoryID;
    

Analogies: CTEs are like creating a temporary table in memory to store intermediate results, making it easier to perform further calculations or queries on those results.

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row. Instead, the rows retain their separate identities.

Example: To calculate the running total of sales for each month, you can use the SUM() window function:

        SELECT OrderDate, TotalAmount,
               SUM(TotalAmount) OVER (ORDER BY OrderDate) AS RunningTotal
        FROM Orders;
    

Analogies: Window functions are like having a running tally in a spreadsheet, where each row shows the cumulative total up to that point.

Transactions

Transactions are a sequence of one or more SQL statements that are executed as a single unit of work. Transactions ensure that all operations within the transaction are completed successfully, or none are, maintaining data integrity. The key properties of transactions are Atomicity, Consistency, Isolation, and Durability (ACID).

Example: To transfer money from one account to another, you can use a transaction to ensure that both the withdrawal and deposit are completed successfully:

        BEGIN TRANSACTION;
        UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
        UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
        COMMIT TRANSACTION;
    

Analogies: Transactions are like a chain of operations that must all be completed for the chain to hold. If one link breaks, the entire chain fails, ensuring that the system remains in a consistent state.