SQL Queries and Programmable Database Objects

Introduction

A database becomes useful when applications can retrieve, combine, analyse, and modify its data reliably. SQL (Structured Query Language) provides the principal means of communicating with relational databases. It supports both declarative queries—statements describing the required result—and programmable database objects, such as stored procedures, functions, and triggers, which place reusable logic close to the data.

This chapter uses a small sales database as its running example. Assume the following relations:

• customers(customer_id, customer_name, city)
• 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)
• employees(employee_id, employee_name, department_id, salary)
• departments(department_id, department_name)

Examples use broadly familiar SQL. Exact syntax for procedures, functions, and triggers differs among PostgreSQL, MySQL, SQL Server, and Oracle; production developers must consult the selected DBMS documentation.

Learning Objectives

After completing this chapter, you should be able to:

1. Construct filtered queries using WHERE, comparison operators, pattern matching, and null-aware logic.
2. Combine relations with inner, outer, self, and cross joins.
3. Use scalar, correlated, and table subqueries.
4. Apply UNION, INTERSECT, and EXCEPT where supported.
5. Calculate aggregates and group results with GROUP BY and HAVING.
6. Build readable queries with common table expressions and analyse ordered data with window functions.
7. Explain the purposes and risks of stored procedures, functions, and triggers.
8. Apply security, integrity, performance, and maintainability principles to database programming.

1. The Structure of a Query

A basic query has the form:

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column_name;

SQL is declarative: the programmer states what rows are wanted, while the query optimizer chooses an execution plan. A useful conceptual processing order is:

1. FROM and JOIN identify source rows.
2. WHERE removes rows that do not meet the row-level condition.
3. GROUP BY forms groups.
4. HAVING removes groups.
5. SELECT computes the output columns.
6. DISTINCT removes duplicate output rows.
7. ORDER BY sorts the final result.
8. LIMIT or an equivalent clause restricts the returned rows.

This is a conceptual order, not necessarily the physical order used by the database engine.

A query should normally name columns explicitly rather than using SELECT *. Explicit columns make interfaces stable, reduce unnecessary data transfer, and clarify the intended result.

2. Filtering and Three-Valued Logic

Filtering uses operators such as =, <>, >, <, >=, and <=:

SELECT order_id, total_amount
FROM orders
WHERE status = 'PAID'
  AND total_amount >= 100.00;

The BETWEEN operator is inclusive at both ends:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31';

For a timestamp column, an exclusive upper bound is often safer because it includes every time on the final day:

WHERE order_date >= TIMESTAMP '2025-01-01 00:00:00'
  AND order_date <  TIMESTAMP '2025-02-01 00:00:00'

IN tests membership, while LIKE performs pattern matching. In common SQL notation, % represents any sequence of characters and _ represents one character:

SELECT customer_name
FROM customers
WHERE city IN ('Leeds', 'Bristol');

SELECT product_name
FROM products
WHERE product_name LIKE 'Wireless%';

NULL and Unknown Results

NULL represents missing, unknown, or inapplicable information; it is not equal to zero or an empty string. Comparisons involving NULL produce UNKNOWN, not TRUE or FALSE. Therefore, use IS NULL and IS NOT NULL:

SELECT order_id
FROM orders
WHERE status IS NULL;

A WHERE clause retains only rows for which the condition is TRUE. Rows producing FALSE or UNKNOWN are excluded. This three-valued logic is important when using NOT IN; if its list contains NULL, results may be unexpectedly unknown. NOT EXISTS is often safer for anti-matching queries.

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

SELECT customer_name, COALESCE(city, 'Unknown') AS reported_city
FROM customers;

3. Joins: Combining Relations

A join combines rows according to a relationship. A qualified column reference such as c.customer_id identifies the table alias and avoids ambiguity.

Inner Join

An inner join returns only matching rows:

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

The join condition should normally compare a foreign key with its referenced key. Missing or invalid foreign-key values can cause expected rows not to appear.

Outer Joins

A left join retains every row from the left table and fills unmatched right-side columns with NULL:

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

This query can identify customers without 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 right join is the reverse arrangement, and a full outer join retains unmatched rows from both sides where supported. A cross join creates every pair of rows and should be used deliberately, for example to generate combinations of products and regions.

A common mistake is placing a right-table filter in WHERE after a left join:

-- This behaves like an inner join for PAID orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'

To preserve customers with no paid order, place the condition in the join:

FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'

Self-Join

A self-join relates rows in the same table. If employees has a manager_id column:

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id;

4. Subqueries and Set Operations

A scalar subquery returns one value and can be used in an expression:

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

The inner query returns the average price; the outer query compares each product with it. A scalar subquery must return at most one row in systems enforcing scalar semantics.

A multi-row subquery commonly works with IN:

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 1000
);

EXISTS tests whether at least one related row exists. It is particularly useful for correlated subqueries:

SELECT 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'
);

The subquery refers to the current outer customer, so it is correlated. Conceptually, the database evaluates whether a qualifying order exists for each customer, although the optimizer may transform the operation.

Set Operations

Set operations combine compatible result sets. Corresponding columns must have compatible data types, and the queries must return the same number of columns.

SELECT customer_id FROM orders WHERE status = 'PAID'
UNION
SELECT customer_id FROM orders WHERE total_amount > 1000;

UNION removes duplicates; UNION ALL retains them and is usually faster when duplicates are meaningful or impossible. INTERSECT returns rows present in both results, and EXCEPT returns rows in the first result but not the second, subject to DBMS support and dialect rules.

5. Aggregation, Grouping, and HAVING

Aggregate functions summarise multiple rows:

• COUNT(*) counts rows.
• COUNT(column) counts non-null values.
• SUM(column) adds non-null values.
• AVG(column) calculates the mean of non-null values.
• MIN and MAX return extreme values.

For values x₁, x₂, ..., x_n, the arithmetic mean is:

Here x̄ is the mean, x_i is an observed value, and n is the number of included non-null observations. The formula applies when the values are measured on a scale for which averaging is meaningful.

Example:

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

Every selected expression must either be aggregated or be included in the GROUP BY clause, according to standard SQL rules. WHERE filters individual rows before grouping; HAVING filters completed groups:

SELECT customer_id, SUM(total_amount) AS value
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
HAVING SUM(total_amount) >= 5000;

A frequent error is using WHERE SUM(total_amount) >= 5000; aggregate conditions belong in HAVING.

6. Common Table Expressions

A common table expression (CTE) gives a name to a query available to the statement that follows:

WITH paid_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'PAID'
), customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_paid
    FROM paid_orders
    GROUP BY customer_id
)
SELECT c.customer_name, t.total_paid
FROM customer_totals AS t
JOIN customers AS c ON c.customer_id = t.customer_id;

CTEs improve readability, support multi-stage transformations, and can prevent repeated definitions of complex logic. They do not automatically guarantee materialization or improved performance. The optimizer may inline or otherwise transform them.

Recursive CTEs can represent hierarchical data, such as an employee reporting tree, but require a non-recursive anchor member and a recursive member. A termination condition is essential; otherwise recursion may continue until an engine limit or error occurs.

7. Window Functions

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

function_name(...) OVER (
    PARTITION BY grouping_columns
    ORDER BY ordering_columns
)

For example, rank employees within departments:

SELECT employee_name, department_id, salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

RANK gives equal values the same rank and leaves gaps after ties. DENSE_RANK gives equal values the same rank without gaps, while ROW_NUMBER assigns a distinct sequential number. If ties must be resolved predictably, add a unique tie-breaker to ORDER BY.

A running total can be calculated as follows:

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_total
FROM orders;

The ROWS frame explicitly defines which ordered rows participate. Window functions are evaluated after the row source has been formed, but before the final output ordering. They cannot generally be placed directly in WHERE; use a CTE or derived table first.

8. Stored Procedures and Functions

A stored procedure is a named database program invoked to perform an operation. It may accept input parameters, execute multiple statements, manage transactions depending on the DBMS, and return status or result sets. A function generally returns a value and can often be used in expressions or queries.

Illustrative PostgreSQL-style function:

CREATE OR REPLACE FUNCTION customer_order_total(p_customer_id integer)
RETURNS numeric
LANGUAGE SQL
AS $$
    SELECT COALESCE(SUM(total_amount), 0)
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'PAID';
$$;

It could be called with:

SELECT customer_name,
       customer_order_total(customer_id) AS paid_total
FROM customers;

A procedure might encapsulate a business operation such as creating an order, inserting its items, checking stock, and recording an audit event. Encapsulation can ensure that all clients follow the same rules. However, excessive procedural logic can make a system difficult to test, migrate, or understand, particularly when business rules are split between application code, procedures, and triggers.

Security practices include parameterized inputs, least-privilege execution rights, careful handling of dynamic SQL, and avoiding string concatenation for user-supplied values. Dynamic SQL should use the DBMS's identifier-quoting and parameter-binding mechanisms.

9. Triggers

A trigger is database logic automatically executed in response to an event such as INSERT, UPDATE, or DELETE. It may run before or after the event, and at row or statement level depending on the DBMS.

A trigger is appropriate for concerns that must hold regardless of which application performs the change, such as audit timestamps or recording deleted records. A conceptual audit trigger might copy the old order into order_history whenever an order is deleted.

Triggers require restraint. Hidden side effects can surprise developers, cause recursive behavior, increase transaction duration, and complicate debugging. A trigger should be small, deterministic, documented, and protected against unintended repeated firing. Constraints are preferable to triggers for simple integrity rules: use NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY where they express the requirement directly.

Applied Case: The Northwind Outfitters Sales Report

Background

Northwind Outfitters wants a monthly report showing each customer's paid spending, number of paid orders, and rank within the customer's city. Customers with no paid order must still appear so that the sales team can identify inactive accounts.

Problem Analysis

The report requires filtering orders by status and date, aggregating by customer, preserving customers without matches, and ranking customers within cities. A direct query combining all tasks would be difficult to read and easy to alter incorrectly.

Approach and Solution

A CTE first aggregates qualifying orders. A left join then preserves every customer. Finally, a window function ranks the result:

WITH monthly_sales AS (
    SELECT customer_id,
           COUNT(*) AS paid_orders,
           SUM(total_amount) AS paid_value
    FROM orders
    WHERE status = 'PAID'
      AND order_date >= DATE '2025-01-01'
      AND order_date <  DATE '2025-02-01'
    GROUP BY customer_id
), customer_report AS (
    SELECT c.customer_id,
           c.customer_name,
           c.city,
           COALESCE(m.paid_orders, 0) AS paid_orders,
           COALESCE(m.paid_value, 0) AS paid_value
    FROM customers AS c
    LEFT JOIN monthly_sales AS m
      ON m.customer_id = c.customer_id
)
SELECT customer_name, city, paid_orders, paid_value,
       RANK() OVER (
           PARTITION BY city
           ORDER BY paid_value DESC
       ) AS city_rank
FROM customer_report
ORDER BY city, city_rank, customer_name;

Outcome

The report is complete because the left join includes inactive customers. COALESCE converts missing aggregate results into zero, and the half-open date interval includes all January timestamps while excluding February. The ranking is computed separately for each city.

Learning Points

The case demonstrates query composition: filter early, aggregate at the correct grain, preserve required entities with an outer join, and use a window function when analysis must retain individual rows. It also illustrates why explicit date boundaries and documented null handling matter in operational reporting.

Performance, Security, and Governance

Query correctness precedes optimization, but performance matters in production. Index columns commonly used for selective predicates and joins, while recognising that indexes consume storage and can slow writes. Use EXPLAIN or the DBMS execution-plan tool to investigate actual access paths rather than guessing. Avoid functions on indexed columns in predicates when they prevent index use, unless a suitable expression index exists.

Use transactions for related changes. A transaction groups statements so that the database can commit all of them or roll them back as a unit. Appropriate isolation levels reduce anomalies but may increase locking or concurrency costs; the correct choice depends on workload and DBMS behavior.

Applications should use parameterized queries to reduce SQL-injection risk. Grant only the privileges required by each application account. Views, stored routines, and row-level security can limit exposure of sensitive data. Personal data should be collected and retained only for legitimate purposes, protected in transit and at rest, and accessed through auditable processes. Reports should avoid exposing more information than the business purpose requires.

Modern database tools increasingly use AI to suggest SQL, explain execution plans, or translate natural-language questions. Such tools can generate syntactically valid but semantically wrong queries, mishandle access controls, or expose confidential schemas to external services. Developers must review joins, null behavior, authorization, performance, and results against known test cases. AI assistance does not replace database governance or professional accountability.

Challenges and Limitations

SQL dialects differ in date functions, pagination, procedural languages, recursive syntax, and trigger definitions. Portability requires limiting vendor-specific features or isolating them behind database-specific modules. Null semantics can be unintuitive, especially with outer joins and NOT IN. Aggregation can double-count when joins multiply detail rows. Window functions provide powerful analysis but may require sorting large data sets. Stored logic and triggers centralize important rules, yet can create hidden coupling and deployment complexity. Testing should therefore include empty sets, duplicate values, nulls, boundary dates, ties, unauthorized users, and large data volumes.

Best-Practice Checklist

• State the required result grain before writing the query.
• Use aliases and explicit column lists.
• Distinguish row filters (WHERE) from group filters (HAVING).
• Handle NULL deliberately with IS NULL, COALESCE, or EXISTS.
• Check whether joins multiply rows before applying aggregates.
• Use half-open intervals for timestamp ranges where appropriate.
• Use CTEs to clarify stages, not as an assumption of automatic optimization.
• Define deterministic ordering for ranking and pagination.
• Prefer declarative constraints for simple integrity rules.
• Parameterize all user-supplied values.
• Review execution plans and test realistic data volumes.
• Document procedures and triggers, including side effects and privileges.

Chapter Summary

SQL provides a declarative language for selecting, filtering, combining, summarising, and analysing relational data. WHERE filters rows, joins combine related relations, subqueries express nested conditions, and set operations combine compatible results. Aggregates with GROUP BY produce summaries, while HAVING filters groups. CTEs make multi-stage queries readable, and window functions calculate rankings, running totals, and other analyses without collapsing rows. Stored procedures and functions encapsulate reusable database operations; triggers automate event-driven actions but should be used carefully. Reliable database programming also requires parameterization, least privilege, transaction awareness, explicit null handling, testing, and execution-plan analysis.

Key Terms

SQL: Structured Query Language used to work with relational databases.

Predicate: A condition that evaluates to true, false, or unknown.

NULL: A marker for missing, unknown, or inapplicable information.

Inner join: A join returning only rows with matches on both sides.

Outer join: A join preserving unmatched rows from one or both relations.

Self-join: A table joined to itself using aliases.

Subquery: A query nested inside another SQL statement.

Correlated subquery: A subquery referring to columns of an outer query.

Set operation: An operation such as UNION, INTERSECT, or EXCEPT combining result sets.

Aggregate function: A function summarising multiple rows.

Grouping: Partitioning rows into groups for aggregation.

HAVING: A clause filtering groups after aggregation.

Common table expression: A named temporary query defined with WITH.

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

Stored procedure: A named, executable database program.

Stored function: A database program that returns a value or result.

Trigger: Automatically executed database logic responding to a data event.

Parameterized query: A query separating SQL structure from supplied values.

Query optimizer: The DBMS component that selects an execution strategy.

Least privilege: Granting users or programs only the access they require.

Review Questions

Short Answer Questions

1. What is the difference between WHERE and HAVING?
2. Why must NULL be tested with IS NULL rather than = NULL?
3. What is the principal difference between an inner join and a left join?
4. State one difference between RANK() and ROW_NUMBER().
5. Give two reasons for using parameterized queries.

Long Answer Questions

1. Explain the conceptual processing order of a SQL query and discuss why it helps when diagnosing errors.
2. Compare scalar, multi-row, and correlated subqueries, giving an appropriate use for each.
3. Explain aggregation, grouping, and the roles of COUNT(*), COUNT(column), and HAVING.
4. Discuss the advantages and limitations of CTEs and window functions in reporting queries.
5. Compare stored procedures, functions, and triggers, including suitable uses and governance risks.

Analytical / Application Questions

1. Write a query that lists every customer, including customers with no orders, showing the number and total value of paid orders in March 2025.
2. A report joins customers, orders, and order items, then reports a total that is twice the expected value. Diagnose the likely cause and propose a correction.
3. Design a safe database operation for creating an order with multiple items. Explain which validations belong in constraints, which may belong in a procedure, and how transactions and privileges should be used.

Multiple Choice Questions with Answers

Question 1

Which clause filters individual rows before grouping?

A. WHERE

B. HAVING

C. ORDER BY

D. OVER

Correct Answer: A. WHERE

Question 2

Which condition correctly tests whether a column has no value?

A. column = NULL

B. column IS NULL

C. column == NULL

D. column IN NULL

Correct Answer: B. column IS NULL

Question 3

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 NULL values

Correct Answer: C. Every row from the left table

Question 4

Which set operation removes duplicate rows by default?

A. UNION

B. UNION ALL

C. CROSS JOIN

D. EXCEPT ALL

Correct Answer: A. UNION

Question 5

Which clause filters groups after aggregate calculations?

A. WHERE

B. FROM

C. HAVING

D. PARTITION BY

Correct Answer: C. HAVING

Question 6

What is the main characteristic of a window function?

A. It always deletes duplicate rows

B. It calculates across related rows without collapsing them

C. It can be used only in INSERT statements

D. It replaces all foreign keys

Correct Answer: B. It calculates across related rows without collapsing them

Question 7

Which construct is best suited to naming intermediate query results within one statement?

A. Trigger

B. Cursor

C. Common table expression

D. Foreign key

Correct Answer: C. Common table expression

Question 8

Why is NOT EXISTS often safer than NOT IN when nullable values may occur?

A. NOT EXISTS automatically creates an index

B. NOT IN can produce UNKNOWN when its comparison set contains NULL

C. NOT EXISTS always returns duplicate rows

D. NOT IN can only compare numbers

Correct Answer: B. NOT IN can produce UNKNOWN when its comparison set contains NULL

Question 9

Which database object executes automatically in response to an INSERT, UPDATE, or DELETE event?

A. View

B. Trigger

C. Index

D. Sequence

Correct Answer: B. Trigger

Question 10

Which practice most directly reduces SQL-injection risk?

A. Using SELECT *

B. Concatenating user input into SQL text

C. Using parameterized queries

D. Removing all indexes

Correct Answer: C. Using parameterized queries