SQL Querying and Data Analysis

Introduction

Database applications do more than store records. They retrieve relevant rows, combine related entities, calculate measures, identify patterns, and present evidence for operational and strategic decisions. Querying is therefore a central programming skill in full-stack development. A well-designed query can support a shopping-cart page, an administrator dashboard, a financial report, or a recommendation feature; a poorly designed query can return incorrect results, expose private data, or consume excessive server resources.

This chapter develops practical SQL querying skills using MySQL 8.0 syntax. It covers filtering, joins, subqueries, set operations, aggregation, grouping, common table expressions, window functions, and views. MongoDB is also considered where its document-oriented query model provides a useful comparison. The emphasis is not merely on syntax, but on reasoning: identifying the required result, understanding relationships, controlling duplication, handling missing values, and validating analytical results.

Learning Objectives

By the end of this chapter, students should be able to:

1. Construct reliable SELECT queries with filtering, sorting, aliases, and null handling.
2. Join related tables and distinguish inner, outer, self, and cross joins.
3. Use subqueries, set operations, aggregation, and grouping for application and reporting tasks.
4. Apply common table expressions and window functions to readable analytical queries.
5. Create and use views while considering security, maintainability, and performance.
6. Recognise equivalent patterns in MongoDB aggregation pipelines.
7. Analyse query results critically and apply professional data-governance practices.

1. The Relational Query Model

A relational query describes the desired result rather than a step-by-step procedure for obtaining it. A table consists of rows and columns; a row represents an entity or event, while a column represents an attribute. Primary keys identify rows, and foreign keys represent relationships between tables.

For the examples in this chapter, assume the following simplified schema:

customers(customer_id, customer_name, email, city, created_at)
orders(order_id, customer_id, order_date, status, total_amount)
order_items(order_id, product_id, quantity, unit_price)
products(product_id, product_name, category, price)
payments(payment_id, order_id, paid_at, amount, method)

An order belongs to one customer, and an order can contain many order items. A product can occur in many order items. These cardinalities are important: joining orders to order_items usually produces one result row per item, not one row per order.

A typical logical processing order is:

1. FROM and JOIN identify source rows.
2. WHERE filters individual rows.
3. GROUP BY forms groups.
4. HAVING filters groups.
5. Window calculations are evaluated over the resulting row set.
6. SELECT produces output expressions.
7. DISTINCT removes duplicate output rows when requested.
8. ORDER BY sorts the result.
9. LIMIT restricts the returned number of rows.

The written order of clauses differs from this logical order. Understanding the distinction explains why a select-list alias cannot normally be used in WHERE, and why aggregate conditions belong in HAVING rather than WHERE.

2. Selecting, Filtering, and Sorting

The basic query is:

SELECT product_id, product_name, price
FROM products
WHERE category = 'Books'
  AND price BETWEEN 10.00 AND 50.00
ORDER BY price DESC, product_name ASC;

BETWEEN is inclusive at both ends. Text comparisons depend on the column collation, which may be case-insensitive. For user-provided search terms, parameterised queries should be used by application code:

SELECT customer_id, customer_name
FROM customers
WHERE email = ?;

The placeholder is bound by the database driver. Concatenating user input into SQL creates a SQL-injection risk.

Useful predicates include IN, LIKE, comparison operators, and IS NULL:

SELECT order_id, customer_id, order_date
FROM orders
WHERE status IN ('PAID', 'SHIPPED')
  AND order_date >= '2025-01-01'
  AND order_date <  '2025-02-01';

Using a half-open date interval—greater than or equal to the start and less than the next boundary—is safer than applying a function to a timestamp column. It includes every time on the first day and can preserve index usability.

NULL means unknown or absent, not zero and not an empty string. Comparisons with NULL do not evaluate to true; they evaluate to unknown under SQL's three-valued logic. Therefore, use:

SELECT *
FROM customers
WHERE city IS NULL;

COALESCE(value, replacement) returns the first non-null expression:

SELECT customer_name,
       COALESCE(city, 'Not supplied') AS city_label
FROM customers;

DISTINCT removes duplicate result rows, but it does not repair an incorrect join. If duplicate rows arise because the join relationship is one-to-many, the correct solution is usually aggregation or a more precise query condition.

3. Joins and Relationship Reasoning

Inner joins

An inner join returns only rows satisfying the join condition:

SELECT o.order_id, c.customer_name, o.order_date, o.total_amount
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

Rows without a matching customer are excluded. With enforced foreign keys this may be acceptable; in data-quality investigations it may hide orphaned records.

Outer joins

A left join retains every row from the left table and supplies NULL for missing right-side matches:

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

To find customers with no orders:

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A frequent error is placing a right-table filter in WHERE, which converts a left join into an effective inner join:

-- Retains customers even when they have no paid orders
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID';

A right join is logically equivalent to reversing table order and using a left join. MySQL does not provide a general full outer join, so a full-outer result can be approximated with a UNION of left- and right-preserving queries, subject to careful duplicate handling.

Many-to-many joins and duplication

To list products in each order:

SELECT o.order_id, p.product_name, oi.quantity, oi.unit_price
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id;

If order totals are calculated from items, the relevant expression is:

SUM(oi.quantity * oi.unit_price)

The multiplication is row-level; the SUM is group-level. Joining two independent one-to-many tables—for example, order items and payments—can multiply rows and inflate totals. Aggregate each child table first, then join the results, or use carefully designed correlated subqueries.

A self-join compares rows within one table. For example, if an employee table includes manager_id, a self-join can display each employee and manager. A cross join produces every possible pair and should be used only when that Cartesian product is intentional.

4. Aggregation and Grouping

Aggregate functions summarise multiple rows:

• COUNT(*) counts rows.
• COUNT(column) counts non-null values in that column.
• COUNT(DISTINCT column) counts distinct non-null values.
• SUM, AVG, MIN, and MAX operate on numeric or comparable values as appropriate.

Example:

SELECT status,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order_value
FROM orders
GROUP BY status;

Every selected expression must either be aggregated or be functionally determined by the grouping columns. MySQL's ONLY_FULL_GROUP_BY mode helps detect ambiguous queries.

WHERE filters rows before grouping; HAVING filters groups after aggregation:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS customer_spend
FROM orders
WHERE status IN ('PAID', 'SHIPPED')
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000;

A conditional aggregate is often more useful than separate queries:

SELECT
  COUNT(*) AS all_orders,
  SUM(status = 'PAID') AS paid_orders,
  SUM(status = 'CANCELLED') AS cancelled_orders
FROM orders;

In MySQL, boolean expressions evaluate as 1 or 0 in numeric context. A portable alternative is SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END).

For revenue by month, grouping by a date expression is convenient:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month,
       SUM(total_amount) AS revenue
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY order_month;

When dates and times have business-time-zone implications, conversion should be performed consistently before grouping. Otherwise, orders near midnight may appear in the wrong reporting period.

5. Subqueries and Set Operations

A scalar subquery returns one value:

SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

The subquery must return at most one row. A multi-row subquery is appropriate with IN, ANY, ALL, or EXISTS:

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'PAID'
);

EXISTS tests whether at least one related row exists; the selected value inside it is irrelevant. It often expresses semi-join logic clearly and avoids duplicate customer rows.

A correlated subquery refers to a row of the outer query. It can be readable, but may be evaluated repeatedly and should be tested on realistic data. A derived table is a subquery in FROM:

SELECT x.customer_id, x.customer_spend
FROM (
  SELECT customer_id, SUM(total_amount) AS customer_spend
  FROM orders
  WHERE status = 'PAID'
  GROUP BY customer_id
) AS x
WHERE x.customer_spend > 1000;

MySQL supports set operations such as UNION, UNION ALL, INTERSECT, and EXCEPT in current 8.0 releases, subject to version and compatibility requirements. The participating queries must have compatible column counts and types.

UNION removes duplicates; UNION ALL preserves them and is generally faster when duplicate removal is not required:

SELECT email FROM customers
UNION
SELECT email FROM suppliers;

Set operations are appropriate when two queries represent the same kind of entity or attribute. They should not replace a join when the goal is to place related columns side by side.

6. Common Table Expressions

A common table expression (CTE) names a temporary result for one statement. It improves readability and permits staged reasoning:

WITH customer_spend AS (
  SELECT customer_id,
         SUM(total_amount) AS spend
  FROM orders
  WHERE status IN ('PAID', 'SHIPPED')
  GROUP BY customer_id
)
SELECT c.customer_name, cs.spend
FROM customer_spend AS cs
JOIN customers AS c ON c.customer_id = cs.customer_id
WHERE cs.spend >= 1000
ORDER BY cs.spend DESC;

A CTE is not automatically a permanent table, and its materialisation behaviour is implementation-dependent. It should be used for clarity, not assumed to guarantee performance improvement.

Recursive CTEs are useful for hierarchical data such as categories or organisational structures. A recursive CTE has an anchor query and a recursive query, connected by UNION ALL. It must have a termination condition, and production systems should protect against cycles and excessive depth.

7. Window Functions

A window function calculates across related rows without collapsing them into one row per group. Its general structure is:

function(...) OVER (
  PARTITION BY grouping_columns
  ORDER BY ordering_columns
  frame_specification
)

PARTITION BY defines independent groups; ORDER BY defines sequence within each group. Unlike GROUP BY, a window function preserves the detail rows.

Ranking example:

SELECT customer_id, order_id, order_date, total_amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY order_date DESC, order_id DESC
       ) AS order_number,
       RANK() OVER (
         PARTITION BY customer_id
         ORDER BY total_amount DESC
       ) AS value_rank
FROM orders;

ROW_NUMBER assigns unique sequential numbers. RANK gives equal values the same rank and leaves gaps after ties. DENSE_RANK also gives equal values the same rank but does not leave gaps.

To return each customer's latest order:

WITH ranked_orders AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

Running totals use an ordered frame:

SELECT order_date, order_id, total_amount,
       SUM(total_amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM orders
WHERE status <> 'CANCELLED';

The ROWS frame is explicit and handles ties predictably when the ordering is unique. LAG and LEAD compare a row with a previous or subsequent row:

SELECT order_date, total_amount,
       total_amount - LAG(total_amount) OVER (ORDER BY order_date, order_id)
         AS change_from_previous
FROM orders;

Window functions cannot normally be filtered directly in the same query block where they are defined; use a CTE or derived table.

8. Views and Reusable Data Interfaces

A view is a stored query presented as a virtual table:

CREATE VIEW paid_customer_orders AS
SELECT o.order_id, o.customer_id, c.customer_name,
       o.order_date, o.total_amount
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status IN ('PAID', 'SHIPPED');

Applications and reports can query the view:

SELECT *
FROM paid_customer_orders
WHERE order_date >= '2025-01-01';

Views can provide a stable interface, hide implementation details, and restrict exposure of sensitive columns. They are not automatically a security boundary: permissions must be configured correctly, and underlying tables may remain accessible. Complex views can also make performance diagnosis difficult. A materialized view—supported directly or indirectly depending on the database platform—stores results and requires refresh management; it is useful for expensive, relatively stable reports.

9. MongoDB Comparison

MongoDB stores BSON documents and commonly performs analysis through an aggregation pipeline. Instead of joining normalised tables, related data may be embedded or referenced. Equivalent stages include:

• $match: filtering
• $project: selecting or computing fields
• $lookup: joining collections
• $group: aggregation
• $sort: ordering
• $setWindowFields: selected window-style operations in supported versions

For example, a MongoDB pipeline that calculates paid-order revenue by customer might be:

db.orders.aggregate([
  { $match: { status: { $in: ["PAID", "SHIPPED"] } } },
  { $group: {
      _id: "$customer_id",
      spend: { $sum: "$total_amount" },
      order_count: { $sum: 1 }
  }},
  { $match: { spend: { $gte: 1000 } } },
  { $sort: { spend: -1 } }
]);

The conceptual discipline is the same: filter early where appropriate, understand cardinality, define measures precisely, and validate results. MongoDB's document design can reduce joins for data that is accessed together, but duplicated data creates update-consistency responsibilities. SQL is often preferable for highly relational data, complex ad hoc joins, and strong transactional constraints; MongoDB may be suitable for flexible document structures and high-volume access patterns. The choice should follow workload and integrity requirements, not fashion.

10. Applied Case: An Online Retail Dashboard

Background

This illustrative case concerns an online retailer whose dashboard must show monthly revenue, the top customers, and each customer's latest order. Orders may be cancelled, and an order can contain several items.

Problem

A first report joined orders, order items, and payments directly, then summed total_amount. Because an order with four items and two payments became eight joined rows, reported revenue was inflated. The report also used WHERE payment.status = 'SETTLED' after a left join, unintentionally excluding customers without payments.

Analysis

The team defined revenue as the sum of one order-level amount for orders whose status is PAID or SHIPPED. It decided that monthly reporting uses the business time zone and that customer rankings use total qualifying spend. Child-table detail was not needed for this dashboard, so direct joins were unnecessary.

Approach and solution

A CTE first aggregates qualifying orders by customer and month. A second CTE ranks customers within each month:

WITH monthly_customer_sales AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
         customer_id,
         SUM(total_amount) AS revenue,
         COUNT(*) AS order_count
  FROM orders
  WHERE status IN ('PAID', 'SHIPPED')
  GROUP BY DATE_FORMAT(order_date, '%Y-%m'), customer_id
), ranked_sales AS (
  SELECT mcs.*,
         DENSE_RANK() OVER (
           PARTITION BY sales_month
           ORDER BY revenue DESC
         ) AS customer_rank
  FROM monthly_customer_sales AS mcs
)
SELECT rs.sales_month, rs.customer_rank,
       c.customer_name, rs.revenue, rs.order_count
FROM ranked_sales AS rs
JOIN customers AS c ON c.customer_id = rs.customer_id
WHERE rs.customer_rank <= 5
ORDER BY rs.sales_month, rs.customer_rank, c.customer_name;

A separate latest-order query uses ROW_NUMBER, avoiding the unreliable practice of selecting MAX(order_date) and then joining without resolving ties.

Outcome

The revised report produced one row per customer per month, avoided item-payment multiplication, made tie handling explicit, and was easier to test. Indexes on orders(status, order_date, customer_id) and the foreign-key column orders.customer_id were considered, but execution plans were inspected before adoption.

Learning points

1. Define the grain of every intermediate result: here, one customer-month.
2. Avoid joining independent one-to-many relationships before aggregation.
3. Separate business definitions from SQL syntax.
4. Use deterministic tie-breakers when selecting a single row.
5. Validate totals against an independently calculated control report.

11. Performance, Quality, and Governance

Correctness comes before optimisation, but professional query development requires both. Use EXPLAIN or EXPLAIN ANALYZE to inspect access paths, estimated or actual row counts, joins, and sorting. Index columns used selectively in joins, filters, and ordering, while remembering that indexes increase storage and write cost. Avoid SELECT * in application interfaces, return only needed columns, and paginate large result sets with a stable ordering.

A query should be tested with empty tables, null values, duplicate timestamps, ties, orphaned records, large groups, and boundary dates. Compare aggregate reports with known totals. Check whether a left join remains left-preserving after filters are added.

Use least-privilege database accounts. Sensitive personal data should not be exposed through unrestricted views or logs. Parameterise all external values. Apply retention, access control, auditing, and applicable privacy requirements. Analytical extracts should be minimised and, where appropriate, anonymised or aggregated. AI coding assistants can suggest query patterns, but generated SQL must be reviewed for injection risks, incorrect joins, privacy leakage, dialect incompatibility, and misleading aggregation.

Contemporary systems increasingly combine operational databases with warehouses, columnar engines, and federated query tools. These developments do not remove the need for relational reasoning. They make data contracts, lineage, semantic definitions, and reproducible transformations more important.

Chapter Summary

SQL querying transforms stored data into application results and analytical evidence. Filtering controls row selection, while joins combine related entities according to cardinality. Outer joins preserve unmatched rows, but misplaced filters can change their meaning. Aggregation and grouping produce summaries; WHERE filters rows before grouping and HAVING filters groups afterward. Subqueries and set operations express existence tests, comparisons, and combinations of compatible results. CTEs make multi-stage logic readable, and window functions calculate ranks, running totals, and comparisons without collapsing detail rows. Views provide reusable interfaces but require appropriate permissions and performance review. MongoDB pipelines express many corresponding operations through stages, although document modelling changes how relationships are represented. Reliable querying also requires parameterisation, null awareness, testing, execution-plan analysis, privacy protection, and explicit definitions of business measures.

Key Terms

Relational database: A database that represents data in related tables.

Primary key: A column or column set that uniquely identifies a row.

Foreign key: A column or column set referencing a key in another table.

Predicate: A condition that evaluates whether a row qualifies.

NULL: A marker for missing or unknown information.

Inner join: A join returning only matching rows.

Left join: A join retaining every row from its left input.

Cardinality: The number of related rows or the size of a result set.

Aggregate function: A function summarising multiple rows.

GROUP BY: A clause that forms groups for aggregation.

HAVING: A clause that filters grouped results.

Subquery: A query nested inside another query.

EXISTS: A predicate testing whether a subquery returns at least one row.

Set operation: An operation such as UNION, INTERSECT, or EXCEPT combining compatible query results.

CTE: A named temporary result defined with WITH.

Window function: A function calculating across related rows while preserving row detail.

View: A stored query exposed as a virtual table.

Query plan: The database engine's strategy for executing a query.

SQL injection: An attack caused by treating untrusted input as SQL code.

Aggregation pipeline: MongoDB's staged document-processing model.

Review Questions

Short Answer Questions

1. Why does COUNT(column) differ from COUNT(*) when the column contains NULL values?
2. What is the difference between WHERE and HAVING?
3. Why can joining order items and payments inflate an order-level total?
4. State one difference between ROW_NUMBER() and RANK().
5. Why should application code use parameterised queries?

Long Answer Questions

1. Explain inner joins, left joins, self-joins, and cross joins, including a suitable use for each.
2. Describe the logical processing order of a SQL query and explain why it matters.
3. Explain how CTEs and window functions can be combined to select the latest record per customer.
4. Compare SQL aggregation with MongoDB aggregation pipelines.
5. Discuss the benefits and limitations of database views in application architecture.

Analytical / Application Questions

1. An order has five items and three payment records. Explain why a direct three-table join may produce fifteen rows and design a query strategy that avoids inflated totals.
2. Design a query that returns the top three customers by monthly qualifying revenue, including ties. Justify your choice of window function.
3. A report using a left join returns no customers without orders after a status condition is added. Diagnose the error and show where the condition should be placed.

Multiple Choice Questions with Answers

Question 1

Which clause filters groups after aggregation?

A. WHERE

B. HAVING

C. ORDER BY

D. FROM

Correct Answer: B. HAVING

Question 2

What does a LEFT JOIN preserve?

A. Only matching rows from both tables

B. Every row from the right table

C. Every row from the left table

D. Only rows containing no NULL values

Correct Answer: C. Every row from the left table

Question 3

Which expression correctly tests for a missing value in SQL?

A. city = NULL

B. city == NULL

C. city IS NULL

D. city EQUALS NULL

Correct Answer: C. city IS NULL

Question 4

What is the main effect of UNION ALL compared with UNION?

A. It sorts all results automatically

B. It preserves duplicate rows

C. It requires a join condition

D. It returns only rows common to both queries

Correct Answer: B. It preserves duplicate rows

Question 5

Which window function assigns a unique sequential number even when ordering values tie?

A. RANK()

B. DENSE_RANK()

C. ROW_NUMBER()

D. COUNT()

Correct Answer: C. ROW_NUMBER()

Question 6

Which technique is most appropriate for testing whether a customer has at least one paid order without creating duplicate customer rows?

A. EXISTS

B. CROSS JOIN

C. UNION ALL

D. DISTINCT on every column

Correct Answer: A. EXISTS

Question 7

In the retail case, why could joining order items and payments inflate revenue?

A. SQL cannot sum decimal values

B. The join can create one row for every item-payment combination

C. LEFT JOIN always duplicates primary keys

D. HAVING automatically repeats aggregate values

Correct Answer: B. The join can create one row for every item-payment combination

Question 8

Which MongoDB aggregation stage is primarily used to filter documents?

A. $match

B. $group

C. $sort

D. $lookup

Correct Answer: A. $match

Question 9

What does a CTE primarily provide?

A. A permanent replacement for a table

B. A named result usable within one statement

C. Automatic indexing of every query

D. Encryption of selected columns

Correct Answer: B. A named result usable within one statement

Question 10

Which practice most directly reduces SQL-injection risk?

A. Using SELECT *

B. Concatenating user input into SQL

C. Using parameterised queries

D. Removing all indexes

Correct Answer: C. Using parameterised queries