4. SQL Programming and Database Objects

Introduction and Context

A relational database becomes useful only when users can define its structure, retrieve information, change stored data, and control access to it. Structured Query Language (SQL) provides the principal means for performing these activities. SQL is not merely a reporting language. It is a comprehensive language for data definition, data manipulation, transaction control, authorization, and the creation of reusable database objects.

For data scientists, SQL is especially important because much of the data used for analysis originates in operational databases. Although analytical tools and programming languages such as Python and R are valuable, efficient analysis often begins with selecting, joining, filtering, and aggregating data close to its source. Poorly designed queries can produce incorrect results, duplicate observations, excessive data transfer, and unnecessary computational cost. Conversely, well-designed SQL supports reproducibility, data quality, security, and scalable analytical workflows.

This chapter develops SQL from basic statements to more advanced programming constructs. It focuses on relational reasoning: how tables are related, how queries transform sets of rows, and how database objects encapsulate rules and operations. Examples use broadly familiar SQL syntax. Specific implementations differ among PostgreSQL, MySQL, SQL Server, Oracle, and other systems, so production work should always be checked against the documentation of the selected database management system (DBMS).

Learning Objectives

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

1. distinguish DDL, DML, DQL, DCL, and transaction-control operations;

2. create and alter tables using appropriate data types and constraints;

3. formulate queries using filtering, joins, subqueries, set operations, aggregation, and window functions;

4. insert, update, and delete data safely;

5. explain the purpose and trade-offs of views, stored procedures, functions, and triggers;

6. apply privileges and roles according to the principle of least privilege;

7. identify common SQL errors, data-quality risks, and security vulnerabilities;

8. design SQL solutions for professional data-management and analytics scenarios.

SQL as a Language for Relational Work

SQL operates on relations, although database systems present relations as tables. A table contains rows and columns; a row represents an occurrence or record, while a column represents an attribute. A primary key identifies rows, and foreign keys express relationships between tables. SQL is largely declarative: the user describes the desired result rather than specifying the algorithm by which the DBMS must obtain it. The query optimizer then chooses an execution plan.

SQL is commonly classified into several groups:

• Data Definition Language (DDL): `CREATE`, `ALTER`, `DROP`, and `TRUNCATE`, used to define database structures.
• Data Manipulation Language (DML): `INSERT`, `UPDATE`, and `DELETE`, used to change rows.
• Data Query Language (DQL): principally `SELECT`, used to retrieve data.
• Data Control Language (DCL): `GRANT` and `REVOKE`, used to manage privileges.
• Transaction Control Language (TCL): `COMMIT`, `ROLLBACK`, and savepoint operations, used to manage transaction boundaries.

SQL uses three-valued logic. A comparison involving `NULL` does not evaluate to true or false; it evaluates to unknown. Consequently, `WHERE salary = NULL` is incorrect. The proper test is `WHERE salary IS NULL`. This distinction is central to reliable query design.

Defining Tables and Enforcing Data Quality

A table definition should express the business meaning of data as precisely as possible. Consider a simplified sales database:

```sql

CREATE TABLE customers (

customer_id INTEGER PRIMARY KEY,

customer_name VARCHAR(100) NOT NULL,

email VARCHAR(255) UNIQUE,

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE orders (

order_id INTEGER PRIMARY KEY,

customer_id INTEGER NOT NULL,

order_date DATE NOT NULL,

status VARCHAR(20) NOT NULL DEFAULT 'PENDING',

total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),

CONSTRAINT fk_order_customer

FOREIGN KEY (customer_id) REFERENCES customers(customer_id),

CONSTRAINT ck_order_status

CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'))

);

```

The `PRIMARY KEY` constraint provides entity integrity. `NOT NULL` prevents mandatory attributes from being absent. `UNIQUE` prevents duplicate values in a candidate key such as an email address. A `FOREIGN KEY` preserves referential integrity by requiring a referenced customer to exist. `CHECK` expresses domain restrictions. A `DEFAULT` supplies a value when one is not explicitly provided.

Constraints should be selected according to meaning, not merely convenience. If an email address is optional, `UNIQUE` may still allow one or more nulls depending on the DBMS. If an order may be deleted only after its customer is removed, a foreign-key action such as `ON DELETE CASCADE` could be appropriate; however, cascading deletion must be used cautiously because one deletion may remove a large related data set.

Schema evolution is performed with `ALTER TABLE`:

```sql

ALTER TABLE customers ADD COLUMN loyalty_level VARCHAR(20) DEFAULT 'STANDARD';

ALTER TABLE customers ALTER COLUMN customer_name SET NOT NULL;

```

In production, schema changes should be version-controlled, tested, reviewed, and deployed through migration tools. `DROP` removes an object, whereas `TRUNCATE` removes all rows while retaining the table structure. Both may have significant operational consequences.

Querying: From Selection to Relational Composition

The basic form of a query is:

```sql

SELECT column_list

FROM table_name

WHERE condition

ORDER BY column_name;

```

For example:

```sql

SELECT customer_id, customer_name

FROM customers

WHERE loyalty_level = 'PREMIUM'

ORDER BY customer_name;

```

The logical processing order is approximately `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `DISTINCT`, `ORDER BY`, and `LIMIT` or `FETCH`. This explains why a select-list alias often cannot be used in `WHERE`: the filtering operation is logically evaluated before the select list is produced.

A query may calculate derived values:

```sql

SELECT order_id,

total_amount,

total_amount * 0.18 AS tax_amount

FROM orders

WHERE status = 'PAID';

```

`DISTINCT` removes duplicate result rows, but it should not be used to hide an incorrectly designed join. If duplicates appear unexpectedly, the relationship cardinality should be investigated first.

Joins and Relationship Reasoning

A join combines rows from two or more tables according to a matching condition. The most common form is an inner join:

```sql

SELECT c.customer_name, o.order_id, o.order_date, o.total_amount

FROM customers AS c

JOIN orders AS o

ON o.customer_id = c.customer_id

WHERE o.status = 'PAID';

```

Only customers with matching orders are returned. A left outer join retains every row from the left table, even when no match exists:

```sql

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;

```

This is useful for finding customers who have never ordered:

```sql

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 many-to-many relationship generally requires a bridge table. For example, students and courses may be related through `enrolments(student_id, course_id, enrolled_on)`. Joining students directly to courses without the bridge table would not represent the relationship correctly.

Join errors are among the most consequential SQL errors. Joining on a non-unique attribute can multiply rows. Omitting a join condition can produce a Cartesian product. A data scientist should know the expected cardinality before executing a complex join and should validate row counts afterward.

Subqueries and Common Table Expressions

A subquery is a query nested inside another query. A scalar subquery returns one value; a table subquery returns rows and columns. Correlated subqueries refer to values from the outer query and may be evaluated conceptually once for each outer row.

The `EXISTS` predicate tests whether at least one related row exists:

```sql

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` is often clearer than joining when the purpose is existence testing rather than returning columns from the related table. A common error is using `NOT IN` with a subquery that can return `NULL`; `NOT EXISTS` is usually safer in such cases.

A common table expression (CTE) gives a name to an intermediate result:

```sql

WITH customer_spend AS (

SELECT customer_id, SUM(total_amount) AS spend

FROM orders

WHERE status = 'PAID'

GROUP BY customer_id

)

SELECT c.customer_name, cs.spend

FROM customers AS c

JOIN customer_spend AS cs

ON cs.customer_id = c.customer_id

WHERE cs.spend > 10000;

```

CTEs improve readability and can support recursive queries, such as traversing organizational hierarchies. They do not automatically guarantee better performance; the optimizer and DBMS implementation determine how they are executed.

Set Operations

Set operations combine compatible query results. `UNION` removes duplicates, while `UNION ALL` retains them and is generally more efficient when duplicates are meaningful or impossible. `INTERSECT` returns common rows, and `EXCEPT` returns rows in the first query but not the second. Corresponding queries must have compatible numbers and types of columns.

```sql

SELECT customer_id FROM orders WHERE status = 'PAID'

UNION

SELECT customer_id FROM customers WHERE loyalty_level = 'PREMIUM';

```

Set operations are different from joins: a join combines columns horizontally, whereas a set operation combines rows vertically.

Aggregation and Window Analysis

Aggregate functions summarize multiple rows. Common functions include `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`. `COUNT(*)` counts rows, while `COUNT(column)` excludes null values in that column.

```sql

SELECT customer_id,

COUNT(*) AS order_count,

SUM(total_amount) AS total_spend,

AVG(total_amount) AS average_order

FROM orders

WHERE status = 'PAID'

GROUP BY customer_id

HAVING SUM(total_amount) > 10000;

```

`WHERE` filters rows before grouping; `HAVING` filters groups after aggregation. Every selected expression that is neither aggregated nor functionally dependent on the grouping columns must normally appear in `GROUP BY`.

Window functions perform calculations across related rows without collapsing them into one row per group. A ranking example is:

```sql

SELECT customer_id, order_id, order_date, total_amount,

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY total_amount DESC

) AS order_rank

FROM orders

WHERE status = 'PAID';

```

`ROW_NUMBER` assigns unique sequence numbers. `RANK` gives equal values the same rank but leaves gaps; `DENSE_RANK` gives equal values the same rank without gaps. Other window functions include `LAG`, `LEAD`, and cumulative aggregates:

```sql

SELECT order_date,

total_amount,

SUM(total_amount) OVER (

ORDER BY order_date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS running_sales

FROM orders

WHERE status = 'PAID';

```

The distinction between aggregation and windowing is conceptually important. Aggregation reduces row cardinality; a window function normally preserves it. Window functions are valuable in customer segmentation, time-series analysis, cohort reporting, anomaly detection, and top-N analysis.

Changing Data Safely

Rows are inserted, updated, and deleted through DML statements:

```sql

INSERT INTO customers (customer_id, customer_name, email)

VALUES (101, 'Amina Rao', '[email protected]');

UPDATE orders

SET status = 'PAID'

WHERE order_id = 5001;

DELETE FROM orders

WHERE order_id = 5001

AND status = 'CANCELLED';

```

An `UPDATE` or `DELETE` without an appropriate `WHERE` clause can affect every row. Professional practice includes previewing the target rows with `SELECT`, using a transaction, checking the affected-row count, and committing only after verification:

```sql

BEGIN;

UPDATE orders

SET status = 'CANCELLED'

WHERE order_id = 5001;

-- verify results

COMMIT;

```

If the result is incorrect, `ROLLBACK` can undo the uncommitted work. Transactions support atomicity: a set of related changes is treated as one unit. Isolation and concurrency behavior vary with transaction isolation levels, which balance consistency against performance and locking.

Parameterized statements should be used instead of constructing SQL by string concatenation. They reduce SQL-injection risk and help separate code from data.

Database Objects Beyond Tables

Views

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

```sql

CREATE VIEW paid_customer_orders AS

SELECT c.customer_id, c.customer_name, o.order_id,

o.order_date, o.total_amount

FROM customers AS c

JOIN orders AS o ON o.customer_id = c.customer_id

WHERE o.status = 'PAID';

```

Views can simplify repeated queries, provide a stable interface when base tables change, and hide sensitive columns. A view is not automatically a security boundary; permissions on the underlying objects and the DBMS's view-security behavior must be configured correctly. Materialized views store query results physically and can accelerate expensive reports, but they require refresh management and may become stale.

Stored Procedures and Functions

Stored procedures encapsulate operations, often including several SQL statements, transaction logic, and validation. Functions generally return a value or table and may be used in expressions, depending on the DBMS. For example, a function might calculate a discount according to a centrally maintained policy.

Database-side programming can reduce network traffic, promote consistency, and place critical rules near the data. However, excessive procedural logic may create vendor dependence, complicate testing, and obscure application behavior. A suitable design separates rules that must be enforced for every client from rules that belong in an application or analytical pipeline.

Triggers

A trigger executes automatically when an event such as `INSERT`, `UPDATE`, or `DELETE` occurs. It may record an audit entry, maintain a derived value, or reject an invalid operation. For example, an update trigger might store old and new salary values in an audit table.

Triggers are powerful but can create hidden side effects, recursive behavior, performance problems, and difficult debugging. They should be short, documented, deterministic where possible, and reserved for rules that genuinely require automatic database enforcement. Explicit procedures are often easier to understand for complex workflows.

Indexes and Sequences

An index is a data structure that can accelerate searches, joins, sorting, and uniqueness checks. Indexes on foreign keys and frequently filtered columns may be useful, but every index consumes storage and adds overhead to inserts and updates. Index design should be based on workload evidence and execution plans, not intuition alone. Composite index order matters because the DBMS may use leading columns more effectively.

Sequences or identity columns generate surrogate key values. A surrogate key identifies a row without necessarily carrying business meaning. It does not eliminate the need for unique constraints on meaningful business identifiers.

Security, Privileges, and Governance

Authorization should follow the principle of least privilege: users and applications receive only the permissions necessary for their roles. A typical arrangement creates roles for analysts, application services, and administrators:

```sql

CREATE ROLE reporting_analyst;

GRANT SELECT ON paid_customer_orders TO reporting_analyst;

REVOKE DELETE ON orders FROM reporting_analyst;

```

Exact syntax varies among DBMS products. Privileges may apply to databases, schemas, tables, views, sequences, procedures, or columns. Role-based access control is easier to manage than granting individual permissions to every user.

SQL governance includes data classification, audit logging, retention rules, separation of duties, and controlled change management. Sensitive personal or financial data should be minimized, masked where appropriate, encrypted according to organizational policy, and accessed only for legitimate purposes. Query results can also disclose confidential information through combinations of seemingly harmless attributes. Ethical data practice therefore requires attention not only to technical access but also to purpose, consent, fairness, and accountability.

An Applied Case: Improving a Subscription Analytics Database

Background

The illustrative company StreamLearn provides online courses. Its operational database stores learners, subscriptions, invoices, and course activity. Analysts report that monthly revenue figures differ between dashboards, and queries joining invoices to activity tables sometimes produce inflated totals.

Situation and Analysis

An investigation finds that one learner can have many activity records and several invoices. A direct join of both child tables creates a many-to-many multiplication: each invoice is repeated for every activity record. Analysts also filter cancelled invoices inconsistently and use `COUNT(*)` where null activity rows should not be counted.

Approach

The data team first defines business rules: only paid invoices contribute to revenue, and learner engagement is measured separately. It creates a CTE that aggregates invoices by learner before joining it to an independently aggregated activity CTE:

```sql

WITH revenue AS (

SELECT learner_id, SUM(amount) AS paid_revenue

FROM invoices

WHERE status = 'PAID'

GROUP BY learner_id

), engagement AS (

SELECT learner_id, COUNT(DISTINCT course_id) AS courses_started

FROM activity

GROUP BY learner_id

)

SELECT l.learner_id,

l.name,

COALESCE(r.paid_revenue, 0) AS paid_revenue,

COALESCE(e.courses_started, 0) AS courses_started

FROM learners AS l

LEFT JOIN revenue AS r ON r.learner_id = l.learner_id

LEFT JOIN engagement AS e ON e.learner_id = l.learner_id;

```

A view exposes the approved revenue definition. A role grants analysts access to the view rather than to payment-card columns in the invoice table. A validation query compares totals with a controlled sample, and an automated test checks that each learner appears once in the final result.

Outcome

The dashboard becomes consistent because revenue and engagement are calculated at their proper grains. Analysts receive a reusable, documented interface, while sensitive base columns are restricted. Query plans are reviewed and indexes are added only where measurements show a benefit.

Learning Points

The case demonstrates that SQL correctness depends on understanding grain and cardinality, not simply on writing syntactically valid statements. Aggregation should occur before joining tables with independent one-to-many relationships. Views and roles can support governed self-service analytics, while tests and reconciliation checks are essential for trustworthy reporting.

Common Errors and Professional Best Practices

Frequent mistakes include confusing `WHERE` and `HAVING`, treating null as an ordinary value, using `NOT IN` with nullable subqueries, forgetting join predicates, relying on implicit column order in `INSERT`, updating without a restrictive predicate, and interpreting `UNION` as a substitute for a join. Another error is assuming that a query's apparent result is correct without testing duplicate counts, null behavior, boundary dates, and empty groups.

Good SQL practice includes explicitly naming columns, using meaningful aliases, formatting complex queries, documenting assumptions about grain, avoiding unnecessary `SELECT *`, parameterizing inputs, and checking execution plans. Development databases should contain representative but protected data. Schema migrations, procedures, views, and privileges should be reviewed like application code. Performance tuning should begin with measurement: indexes, query rewrites, partitioning, and materialized views should respond to observed workload problems.

Current and Emerging Directions

Modern database work increasingly combines SQL with cloud warehouses, lakehouse systems, streaming platforms, and semantic layers. Many systems support semi-structured data such as JSON, geospatial operations, temporal queries, and vector similarity. SQL engines are also used in distributed environments where data locality, partition pruning, and columnar storage influence performance.

Generative AI assistants can draft SQL, explain query plans, and help discover schemas, but their output requires verification. An apparently plausible query may mishandle nulls, security restrictions, time zones, or relationship cardinality. Organizations should therefore combine automated assistance with tests, code review, access controls, and human accountability.

Chapter Summary

SQL provides a declarative foundation for defining relational structures, querying data, modifying rows, controlling transactions, and managing access. DDL creates tables and constraints; DML changes data; queries use joins, subqueries, set operations, aggregation, and window functions to transform relational information. Constraints protect data quality, while transactions protect consistency during change. Views, procedures, functions, triggers, indexes, and roles extend the database into a programmable and governable platform. Reliable SQL depends on understanding nulls, grain, cardinality, security, and performance rather than syntax alone. In professional analytics, independently aggregating related data before joining, validating results, and applying least-privilege access are particularly important practices.

Key Terms

SQL: A language for defining, querying, manipulating, and controlling relational data.

DDL: SQL statements that define or modify database structures.

DML: SQL statements that insert, update, or delete rows.

Primary key: Attribute or set of attributes that uniquely identifies each row.

Foreign key: Attribute or set of attributes referencing a key in another table.

Constraint: A rule enforced by the DBMS to protect data validity.

NULL: A marker representing an unknown, missing, or inapplicable value.

Join: An operation that combines rows from relations using a matching condition.

Subquery: A query nested within another SQL statement.

CTE: A named temporary query result introduced with `WITH`.

Set operation: An operation such as `UNION`, `INTERSECT`, or `EXCEPT` that combines query results.

Aggregation: The reduction of multiple rows into summarized values.

Window function: A function that calculates across related rows while normally preserving row detail.

Transaction: A logical unit of database work governed by commit or rollback.

View: A virtual table defined by a stored query.

Materialized view: A physically stored result of a query that must be refreshed.

Trigger: Database code executed automatically in response to a data event.

Index: An auxiliary structure designed to accelerate data access.

Role: A named collection of privileges assigned to users or other roles.

SQL injection: An attack in which untrusted input alters the intended meaning of a SQL statement.

Review Questions

Short Answer Questions

1. Distinguish DDL, DML, DQL, DCL, and TCL.

2. Why is `IS NULL` used instead of `= NULL`?

3. Explain the difference between an inner join and a left outer join.

4. What is the difference between `WHERE` and `HAVING`?

5. Why can a direct join of two independent one-to-many tables inflate totals?

Long Answer Questions

1. Explain how primary keys, foreign keys, `NOT NULL`, `UNIQUE`, `CHECK`, and `DEFAULT` constraints contribute to relational data quality.

2. Discuss subqueries, CTEs, and set operations, including situations in which each is appropriate.

3. Compare grouped aggregation with window functions and illustrate their different effects on row cardinality.

4. Evaluate the benefits and risks of views, stored procedures, functions, and triggers.

5. Explain how transactions, parameterized queries, roles, and least privilege contribute to secure database operation.

Analytical / Application Questions

1. A report joins customers, invoices, and usage events and reports revenue three times higher than the accounting total. Diagnose the likely relational cause and design a corrected query strategy.

2. Design a SQL solution that identifies each customer's highest-value paid order while retaining customers who have never placed an order. Explain your choice of join and window function.

3. An organization wants analysts to access monthly revenue but not payment details. Propose a combination of view design, privileges, validation, and governance controls, and discuss its limitations.

22. Multiple Choice Questions with Answers

Question 1

Which constraint most directly prevents an order from referencing a customer who does not exist?

A. CHECK

B. FOREIGN KEY

C. UNIQUE

D. DEFAULT

Correct Answer: B. FOREIGN KEY

Question 2

A query must retain all customers, including those without orders. Which join is most appropriate?

A. INNER JOIN

B. CROSS JOIN

C. LEFT OUTER JOIN

D. SELF JOIN

Correct Answer: C. LEFT OUTER JOIN

Question 3

Which clause filters groups after aggregate calculations have been performed?

A. WHERE

B. ORDER BY

C. FROM

D. HAVING

Correct Answer: D. HAVING

Question 4

What is the main difference between a grouped aggregate and a window function?

A. A grouped aggregate normally reduces rows, while a window function normally preserves them

B. A window function can only be used with text columns

C. A grouped aggregate cannot use SUM

D. A window function always removes duplicate rows

Correct Answer: A. A grouped aggregate normally reduces rows, while a window function normally preserves them

Question 5

Why can joining invoices and activity records directly inflate total revenue?

A. NULL values are automatically converted to zero

B. Independent one-to-many relationships can multiply matching rows

C. UNION removes invoice identifiers

D. Foreign keys prevent aggregation

Correct Answer: B. Independent one-to-many relationships can multiply matching rows

Question 6

Which condition correctly tests whether a column has no value?

A. column = NULL

B. column == NULL

C. column IS NULL

D. column IN NULL

Correct Answer: C. column IS NULL

Question 7

Which SQL feature is most suitable for assigning a rank to orders within each customer while retaining every order row?

A. GROUP BY

B. ROW_NUMBER() OVER (PARTITION BY ...)

C. UNION

D. DISTINCT

Correct Answer: B. ROW_NUMBER() OVER (PARTITION BY ...)

Question 8

What is the principal security advantage of a parameterized query?

A. It automatically creates indexes

B. It prevents all transaction failures

C. It separates input data from SQL code and reduces injection risk

D. It removes the need for access privileges

Correct Answer: C. It separates input data from SQL code and reduces injection risk

Question 9

Which statement about UNION and UNION ALL is correct?

A. UNION always retains duplicates

B. UNION ALL always sorts the result

C. UNION removes duplicate rows, whereas UNION ALL retains them

D. They combine columns horizontally like joins

Correct Answer: C. UNION removes duplicate rows, whereas UNION ALL retains them

Question 10

What does the principle of least privilege require?

A. Every analyst should receive administrator rights

B. Users should receive only the permissions necessary for their duties

C. All tables should be publicly readable

D. Privileges should never be reviewed

Correct Answer: B. Users should receive only the permissions necessary for their duties