Stephen Williams

SQL - (Advanced Level)

---

If you are looking for beginner or intermediate level SQL, → SQL (Beginner Level)     SQL (Intermediate Level)

---

Advanced SQL users move beyond standard data retrieval and analysis into the realms of database architecture, performance optimization, and sophisticated data manipulation. With SQL we are capable of designing efficient systems, optimizing large-scale operations, and extracting deep analytical insights from complex data structures.

1. Window Functions (Analytical Functions)

Window functions are powerful analytical tools that perform calculations across a specific set of table rows ("window") related to the current row, without reducing the number of rows returned (unlike a GROUP BY). They are essential for rankings, running totals, moving averages, and comparing a row's value to an aggregate value.

Example: Calculating a 30-day moving average of daily sales.

 SELECT
  SaleDate,
  DailySales,
  AVG(DailySales) OVER (
   ORDER BY SaleDate
   -- Define the window: current row and the previous 29 rows
   ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS MovingAverage30Day
  FROM
  DailySalesData;

2. Recursive CTEs for Hierarchical Data

Recursive Common Table Expressions (CTEs) are a specialized, advanced use of the WITH clause. They are used specifically to traverse hierarchical data structures where rows are linked to other rows within the same table (e.g., employee/manager relationships, bill of materials, organizational charts).
Example: Finding all employees who report up to a specific CEO (Employee ID 1)

 WITH EmployeeHierarchy AS (
    -- Anchor member: The base case (the CEO)
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        1 AS HierarchyLevel
    FROM
        Employees
    WHERE
        EmployeeID = 1
    
    UNION ALL
    
    -- Recursive member: Joins employees to the results of this CTE
    SELECT
        E.EmployeeID,
        E.Name,
        E.ManagerID,
        EH.HierarchyLevel + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeHierarchy EH ON E.ManagerID = EH.EmployeeID
 )
 -- Select all results generated by the recursive process
 SELECT * FROM EmployeeHierarchy ORDER BY HierarchyLevel, Name;
        

3. Working with JSON Data in the Database

Modern advanced SQL databases (like PostgreSQL, SQL Server, MySQL, SQLite) support storing and querying JSON data natively. An advanced user can treat JSON fields not just as unstructured text, but as queryable structures, allowing for schema flexibility and efficient retrieval of embedded data.

Example: Querying a JSON column to extract specific data
Imagine a Products table with a Details column storing JSON objects like
{
  "Weight": "1kg",
  "Color": "Red",
  "Warranty": "1 year"
}

-- PostgreSQL / MySQL syntax for extracting JSON data
SELECT
  ProductName,
  Details->>'Color' AS Color,
  Details->>'Warranty' AS Warranty
FROM
  Products
WHERE
  -- Use the extracted JSON value in a WHERE clause
  Details->>'Color' = 'Red';
    

Example: Generating JSON Output from SQL Results
Advanced SQL can also format standard relational results into a single JSON object or array for easy consumption by APIs or front-end applications.

-- SQL Server Example (FOR JSON PATH or FOR JSON AUTO)
 SELECT
    OrderID,
    OrderDate,
    TotalAmount
 FROM
    Orders
 WHERE
    CustomerID = 42
 FOR JSON AUTO;

 --- For PostgreSQL ROW_TO_JSON(record)function converts an entire SQL row (a composite value) into a JSON object. Each column in the row becomes a key-value pair in the JSON object.
 SELECT
  row_to_json(sales)
 FROM sales
 where 
  city_name = 'Paris';
    

4. Query Optimization and Execution Plans (Expert Level)

At the advanced level, performance tuning is systematic. The user understands exactly why a query is slow by deeply interpreting the database engine's execution plan, using indexes strategically to minimize disk I/O and CPU usage.

5. Transactions and Concurrency Control

Advanced users manage database integrity in high-traffic, multi-user environments. They use transactions to ensure operations are atomic (all or nothing) and understand the different transaction isolation levels to prevent common data issues like dirty reads.

6. Database Design and Normalization/Denormalization

An advanced SQL user understands data modeling principles (1NF, 2NF, 3NF) and knows when to strictly adhere to normalization (for data integrity) and when to strategically denormalize (to improve performance for read-heavy analytical reporting). They are involved in the fundamental design of tables and relationships.

---