12.3: SQL for Data Manipulation

SQL for Data Manipulation

SQL (Structured Query Language) is the standard language for interacting with relational databases. It is declarative: you specify what data you want, not how to compute it. For data analytics, SQL is often the first tool to ingest, filter, and aggregate large datasets. Key SQL skills include:

• Joins: combining tables (INNER JOIN, LEFT JOIN, etc.) on key columns.
• Subqueries and CTEs: writing subselects or Common Table Expressions (WITH queries) to break down complex queries.
• Window functions: functions like ROW_NUMBER(), RANK(), LAG(), SUM() OVER (PARTITION BY ...) that compute running or grouped calculations without collapsing rows.
  

  Figure \(\PageIndex{1}\): SQL Query Building Blocks -- Joins, CTEs, and Window Functions. A three-panel conceptual illustration showing how INNER/LEFT JOINs combine tables, how CTEs create temporary named result sets, and how window functions compute across row partitions without collapsing them. (CC BY 4.0; Felix Amoruwa) Access to a detailed description of Figure \(\PageIndex{1}\)

For example, a join and filter might look like:

SELECT o.order_id, c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31';

This query retrieves each order’s ID, the customer name, and amount, for orders in the first quarter, joining the orders and customers tables.

A Common Table Expression (CTE) can clarify complex queries. For instance:

WITH top_regions AS (
  SELECT region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
  HAVING SUM(amount) > 100000
)
SELECT region, product, SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Here, the WITH clause (CTE) computes top_regions as those with total sales over 100,000. The main query then uses that temporary result to filter and group the orders. As the PostgreSQL manual explains, “WITH provides a way to write auxiliary statements for use in a larger query. These statements […] can be thought of as defining temporary tables that exist just for one query”. Using CTEs often improves readability and, in some systems, can optimize execution.

Window functions allow calculations across rows. For example, to rank orders by amount:

SELECT order_id, amount,
       RANK() OVER (ORDER BY amount DESC) AS rank_within_all
FROM orders;

This query adds a ranking column where the largest amount gets rank 1, the next largest rank 2, and so on. As PostgreSQL docs note, “Window functions provide the ability to perform calculations across sets of rows that are related to the current query row”. Other window functions include LAG, LEAD, ROW_NUMBER, SUM() OVER (...), etc., enabling moving averages, cumulative sums, and rankings without writing self-joins.