Programming for All: SQL Edition

This repository contains materials for learning SQL programming, covering a wide range of topics from basic concepts to advanced techniques.

Table of contents

• Chapter 1: Introduction to SQL
• Chapter 2: Database Design and Normalization
• Chapter 3: SQL Data Types
• Chapter 4: Creating and Modifying Database Objects
• Chapter 5: Inserting, Updating, and Deleting Data
• Chapter 6: Retrieving Data with SELECT
• Chapter 7: SQL Functions
• Chapter 8: Stored Procedures and Functions
• Chapter 9: Transactions and Concurrency
• Chapter 10: Advanced SQL Concepts
• Chapter 11: SQL Best Practices and Optimization
• Chapter 12: SQL for Data Analysis
• Chapter 13: NoSQL and SQL
• Chapter 14: SQL and the Cloud
• Chapter 15: Conclusion and Future of SQL
• Appendix A: SQL Syntax Reference
• Appendix B: Glossary of SQL Terms

Each chapter is a separate Markdown file in the chapters directory, which keeps the chapters easy to find and read.

Getting Started

To get started with the book, simply click on the links above to access each chapter. You can read the chapters directly on GitHub or clone the repository to your local machine to read offline.

Contributing

If you find any errors or have suggestions for improvement, feel free to open an issue or submit a pull request. Contributions are welcome and encouraged!

License

This book is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. You are free to share and adapt the content for non-commercial purposes, as long as you give appropriate credit and share your adaptations under the same license.

Acknowledgments

Special thanks to all contributors who have helped make this book possible.

Chapter 1: Introduction to SQL

What is SQL?

SQL is a domain-specific language: it is designed for tasks related to databases. It is also declarative. You describe the result you want, and SQL works out the steps to get there.

SQL manages relational databases, which store data in tables made of columns and rows. Each table holds one kind of data, and each row is one instance of it. A customers table, for example, might have columns for name, address, and phone number, with one row per customer.

The language has several parts:

• Data Definition Language (DDL) creates and changes database objects such as tables, indexes, and constraints.
• Data Manipulation Language (DML) inserts, updates, and deletes the data in tables.
• Data Query Language (DQL) retrieves data from tables.
• Data Control Language (DCL) grants and revokes privileges to access database objects.

SQL is standardized, so the basic syntax is the same whichever database management system (DBMS) you use, whether that’s MySQL, Oracle, or Microsoft SQL Server. Each DBMS implements SQL a little differently, though, and some add features that aren’t part of the standard.

SQL is worth learning if you work with data, as a software developer, a data analyst, or a data scientist. Online courses, books, and tutorials on it are easy to find.

Key concepts of SQL

1. Relational databases: as described above, data lives in tables of columns and rows. Tables relate to each other through keys, which are columns that link one table to another.
2. SQL statements: you work with the data by running statements such as CREATE TABLE, INSERT, SELECT, UPDATE, and DELETE. They fall into categories like DDL, DML, and DQL.
3. Data types: every column in a table has a data type that sets what kind of data it can hold. SQL supports types such as integer, decimal, varchar, date, and Boolean.
4. SQL operators: these include arithmetic operators (+, -, *, /), comparison operators (>, <, =), and logical operators (AND, OR, NOT).
5. Joins: a join combines two or more tables based on a common key. Joins come in several types, including inner join, left join, right join, and full outer join.
6. Indexes: an index speeds up reading data from a table. It’s a separate data structure that lets the database quickly find the rows that match a condition.
7. Transactions: a transaction groups related operations into a single unit of work. If any part fails, the transaction can be rolled back, which keeps the database in a consistent state.
8. Views: a view is a virtual table built from the result of a SELECT statement. Views simplify complex queries and give you a convenient way to get at data you use often.
9. Stored procedures: a stored procedure is precompiled code stored in the database that you can run on demand. Stored procedures are often used for complex calculations or data manipulation.
10. Security: SQL provides several ways to secure data in a database, including user authentication and authorization, data encryption, and auditing of database activity.

Chapter 2: Database Design and Normalization

Designing a relational database

The relational model

The relational model organizes data in tables of rows and columns. It is based on the relation, a mathematical term for a set of values that share the same attributes.

In this model you create tables for different types of data and link them together with keys. That lets you query and manipulate the data in a flexible and efficient way.

The relational model is flexible, scales well, and is easy to use. Adding or removing data is simple, and you can run complex queries on large datasets.

The entity-relationship (ER) model

The ER model represents data as entities (objects) and the relationships between them. It’s often used in database design to model complex relationships between different types of data.

You create entities for the different types of data and connect them with relationships. This shows complex data structures in a clear and intuitive way.

The ER model can capture complex relationships between data, and it presents the data in a form that is easy to understand and modify.

Creating a data model

A data model describes the structure of the data in a database and the relationships within it. It can be a diagram or a written description, and it guides the design and development of the database.

To create one, identify the types of data you want to store and map out how they relate to each other. This can mean creating tables and columns, defining relationships between tables, and specifying data types and constraints.

When you build the model, use clear and consistent naming conventions, avoid redundant or duplicated data, and follow normalization principles.

Defining tables and columns

To define a table, you specify its name, the name and data type of each column, and any constraints or rules that apply to the data.

A good table definition uses clear, concise names for the table and its columns and an appropriate data type for each column. It also defines constraints and rules that protect data integrity.

Relationships between tables

A relationship links one table in a database to another. Relationships let you model complex connections between different types of data.

You define relationships by creating keys that link one table to another. This can mean creating primary keys and foreign keys, which let you join tables together in meaningful ways.

Give keys clear and concise names and appropriate data types, and avoid circular or redundant relationships.

Normalization

Normalization is the process of organizing and structuring the data in a database to reduce redundancy and improve data integrity. Its goal is to keep redundant and duplicated data to a minimum, because that kind of data leads to inconsistencies and errors. Removing those problems makes the stored data more accurate, consistent, and reliable.

Normalization breaks a database down into smaller, more manageable parts and organizes them in a logical and efficient way. To do that, you identify the functional dependencies between data and make sure each piece of data is stored in only one place.

The benefits are better data integrity, less redundancy and duplication, and a more flexible and scalable database.

There are several normal forms, including First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF). Each normal form is a higher level of normalization and removes more redundancy.

First normal form (1NF)

First Normal Form (1NF) is the first level of normalization. It requires all data in a table to be atomic and indivisible: each column in a table holds only one piece of data, and there are no repeating groups of data.

To get a table into 1NF, move any repeating groups of data into separate tables and make sure each column holds only one piece of data.

1NF brings the same benefits as normalization in general.

Second normal form (2NF)

Second Normal Form (2NF) is the second level of normalization. It requires a table to be in 1NF and every non-key attribute to be fully functionally dependent on the primary key: it must depend on the whole key, not on just part of it. This only matters when the primary key has more than one column. In a table keyed on order_id and product_id, for example, a product_name column depends on product_id alone, so the table isn’t in 2NF.

To get a table into 2NF, identify all of its functional dependencies and move any columns that depend on only part of the primary key into separate tables.

Third normal form (3NF)

Third Normal Form (3NF) is the third level of normalization. It requires the non-key attributes in a table to be independent of each other, so each column relates to the primary key and not to any other non-key attribute.

To get a table into 3NF, identify its transitive dependencies and move any columns that depend on other non-key attributes into separate tables.

3NF brings those same benefits.

Boyce-Codd normal form (BCNF)

Boyce-Codd Normal Form (BCNF) is a slightly stricter version of 3NF. It requires that in every functional dependency in a table, the column or columns that determine the others form a candidate key. Most tables in 3NF are already in BCNF. The two differ only in tables with more than one candidate key, where the keys share columns.

To get a table into BCNF, identify all of its functional dependencies and make sure the candidate keys determine them. You may need to break the table into smaller tables that meet the BCNF requirements.

BCNF has those benefits too. Because only candidate keys determine other columns, BCNF rules out update anomalies and other inconsistencies that can occur in less normalized databases. The data is more accurate and reliable as a result, which is essential when you use it to make business decisions. More normalization doesn’t automatically make queries faster, though. Splitting data into more tables means a query may need more joins to put it back together.

Chapter 3: SQL Data Types

Understanding data types

Every column in a table has a data type, and the data type determines what kind of data the column can store. Different data types are designed for different kinds of data, such as numbers, dates, and strings. You need to understand them to design tables and queries that work well with the data in your database.

Common SQL data types

Some of the most common data types in SQL are:

• INTEGER for whole numbers, such as 1, 2, and 3.
• FLOAT or REAL for decimal numbers, such as 1.23 and 4.56.
• CHAR or VARCHAR for character strings, such as “hello” and “world”.
• DATE for dates, such as “2022-03-14”.

SQL has many other data types, depending on the database system you use. Some databases also have variations on these basic types, such as TEXT instead of VARCHAR, or TIMESTAMP instead of DATE.

Creating tables with the correct data types

When you create a table, choose the right data type for each column so that the data is stored and manipulated correctly. A table for customer information, for example, might look like this:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(20)
);

This creates a table called customers with four columns: id, name, email, and phone. The id column is the table’s primary key, and the name, email, and phone columns use the VARCHAR data type. The name column is limited to 50 characters and the email column to 255. The phone column is limited to 20 characters, which should be enough for most phone numbers.

With appropriate data types, the data is stored accurately and efficiently. Storing whole numbers in a FLOAT column instead of an INTEGER column, for example, risks losing precision. A FLOAT holds an approximate binary value, so large whole numbers can’t all be stored exactly, and comparing two floats with = can fail when you expect it to succeed. A VARCHAR column that is too short for a value would truncate it, and you could lose data.

Chapter 4: Creating and Modifying Database Objects

Creating tables

Creating tables is one of the fundamental tasks in SQL. A table is a collection of related data organized into rows and columns. You create one with the CREATE TABLE statement:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

This creates a table called users with three columns: id, username, and email. The id column is the primary key, which means it uniquely identifies each row in the table. The username and email columns are both VARCHAR columns and can store up to 50 and 100 characters respectively.

Altering tables

Sometimes you need to change an existing table, either to add new columns or to change the structure of an existing column. You do this with the ALTER TABLE statement.

Adding a column

ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

This adds a new column called full_name to the users table. SQL Server and Oracle leave out the word COLUMN: ALTER TABLE users ADD full_name VARCHAR(100);.

Modifying a column

ALTER TABLE users MODIFY COLUMN email VARCHAR(255);

This increases the maximum length of the email column from 100 to 255 characters.

That’s MySQL’s syntax. Changing a column is one of the places where databases differ most:

-- PostgreSQL
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

-- SQL Server
ALTER TABLE users ALTER COLUMN email VARCHAR(255);

-- Oracle
ALTER TABLE users MODIFY email VARCHAR2(255);

SQLite can’t change a column at all. You create a new table with the new definition, copy the rows across, and drop the old table.

Renaming a column

ALTER TABLE users RENAME COLUMN full_name TO display_name;

This renames the full_name column you added above to display_name. MySQL 8.0 and later, PostgreSQL, Oracle, and SQLite all accept RENAME COLUMN. SQL Server uses a stored procedure instead: EXEC sp_rename 'users.full_name', 'display_name', 'COLUMN';.

Dropping tables

If you no longer need a table, the DROP TABLE statement deletes it from the database:

DROP TABLE users;

This drops the users table from the database. Be careful with this statement, because it permanently deletes all the data in the table. Make sure you have a backup of your data before you use DROP TABLE.

If you’re following along, run the CREATE TABLE statement from the start of this chapter again before you go on. Chapter 5 adds rows to the users table.

Chapter 5: Inserting, Updating, and Deleting Data

Inserting data into a table

To add data to a table, use the INSERT INTO statement:

INSERT INTO users (id, username, email)
VALUES (1, 'john_doe', '[email protected]');

This inserts a new row into the users table, with 1 in the id column, 'john_doe' in the username column, and '[email protected]' in the email column.

You can also insert several rows at once:

INSERT INTO users (id, username, email)
VALUES (2, 'jane_doe', '[email protected]'),
       (3, 'bob_smith', '[email protected]');

This inserts two new rows into the users table.

Updating data in a table

To change data that’s already in a table, use the UPDATE statement:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

This updates the email column in the row where id equals 1. The new value of email is '[email protected]'.

You can update several columns at once:

UPDATE users
SET email = '[email protected]',
    username = 'new_username'
WHERE id = 1;

This updates both the email and username columns in the row where id equals 1.

Deleting data from a table

To remove data from a table, use the DELETE FROM statement:

DELETE FROM users
WHERE id = 1;

This deletes the row where id equals 1 from the users table.

You can also delete every row in a table:

DELETE FROM users;

This deletes all rows from the users table. The data is deleted permanently, so be careful with this statement and make sure you have a backup of your data before you use DELETE FROM.

Chapter 6: Retrieving Data with SELECT

The SELECT statement reads data from tables. This chapter covers choosing columns, filtering rows with WHERE, sorting them with ORDER BY, removing duplicates with DISTINCT, and limiting how many rows come back. It works with one table at a time. Chapter 7 covers the functions you can use in a query, chapter 10 covers subqueries, and chapter 12 covers joins and grouping.

The sample table

The examples use the users table from chapters 4 and 5, with two more columns, age and city. Run this script to create the table and add six rows:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  age INTEGER,
  city VARCHAR(50)
);

INSERT INTO users (id, username, email, age, city)
VALUES (1, 'john_doe', '[email protected]', 34, 'London'),
       (2, 'jane_doe', '[email protected]', 29, 'London'),
       (3, 'bob_smith', '[email protected]', 41, 'Toronto'),
       (4, 'ana_lima', '[email protected]', 25, 'Lisbon'),
       (5, 'raj_patel', '[email protected]', 38, 'Toronto'),
       (6, 'sita_rai', '[email protected]', NULL, 'Kathmandu');

DROP TABLE IF EXISTS removes any users table left over from the earlier chapters, so you can run the script more than once. No age has been recorded for sita_rai, so that row’s age is NULL, which means the value is missing or unknown.

The results in this chapter come from SQLite. Other databases return the same rows, and the places where they differ are pointed out.

Selecting columns

To read every column of every row, use SELECT *:

SELECT * FROM users;

The * means all columns, and FROM names the table to read. A SELECT only reads the table. It never changes it.

Without ORDER BY, which comes later in this chapter, a database can return rows in any order. SQLite returns this table’s rows in id order, but don’t rely on that.

To get only some of the columns, list them in the order you want them:

SELECT username, city FROM users;

SELECT * is handy for a quick look at a table. In application code, name the columns you need, as chapter 11 recommends. The query then returns less data, and its result keeps the same columns when someone adds a column to the table.

Aliases and calculated columns

A column in the result can be a calculation instead of a stored value. AS gives a result column a new name, called an alias:

SELECT username AS name, age, age + 10 AS age_in_ten_years
FROM users;

The alias only renames the column in the result. The table’s column is still called username. Arithmetic with NULL gives NULL, because an unknown age plus 10 is still unknown.

Filtering rows with WHERE

WHERE keeps only the rows for which its condition is true:

SELECT username, age
FROM users
WHERE age > 30;

Chapter 5 used WHERE to choose which rows UPDATE and DELETE change. In a SELECT, it chooses which rows to return. The comparison operators are:

• = equal to
• <> not equal to (most databases also accept !=)
• < less than, and > greater than
• <= less than or equal to, and >= greater than or equal to

Put text values in single quotes:

SELECT username, age
FROM users
WHERE city = 'London';

Whether 'london' would also match depends on the database. MySQL and SQL Server ignore case when they compare text, unless you set them up otherwise. PostgreSQL, Oracle, and SQLite don’t, so in those city = 'london' returns no rows.

Combining conditions

AND keeps a row only if both conditions are true, and OR keeps it if either one is. NOT reverses a condition.

SELECT username, city, age
FROM users
WHERE (city = 'London' OR city = 'Toronto') AND age > 35;

SQL evaluates AND before OR, so without the parentheses the condition would mean city = 'London' OR (city = 'Toronto' AND age > 35), and john_doe and jane_doe would be in the result too. When a condition mixes AND and OR, use parentheses to say what you mean.

IN, BETWEEN, and LIKE

Three operators make common conditions shorter.

IN checks a value against a list, so city IN ('London', 'Lisbon') means the same as city = 'London' OR city = 'Lisbon'. NOT IN keeps the rows whose value isn’t in the list:

SELECT username, city
FROM users
WHERE city NOT IN ('London', 'Toronto');

BETWEEN checks a range, and the range includes both ends:

SELECT username, age
FROM users
WHERE age BETWEEN 25 AND 34;

LIKE matches text against a pattern. In the pattern, % stands for any number of characters, including none, and _ stands for exactly one character:

SELECT username, city
FROM users
WHERE city LIKE 'L%';

Whether LIKE ignores case also varies between databases. SQLite’s LIKE ignores the case of English letters, even though its = doesn’t.

Checking for NULL

You can’t compare NULL with =. The result of any comparison with NULL is unknown, and WHERE keeps only the rows where the condition is true. That’s why sita_rai was missing from the age > 30 result, and the row wouldn’t appear for age <= 30 either. Even WHERE age = NULL returns no rows. Use IS NULL instead:

SELECT username, age
FROM users
WHERE age IS NULL;

IS NOT NULL does the opposite and returns the five rows that have an age.

Sorting with ORDER BY

ORDER BY sorts the result. It sorts in ascending order unless you add DESC after the column:

SELECT username, age
FROM users
ORDER BY age;

Databases disagree about where NULL goes. SQLite, MySQL, and SQL Server sort it before every other value, so sita_rai comes first here. PostgreSQL and Oracle sort it after every other value, so the row would come last.

To sort by more than one column, list them. The second column sets the order of rows that tie on the first, and each column gets its own direction:

SELECT city, username, age
FROM users
ORDER BY city, age DESC;

The cities are sorted from A to Z, and within London and Toronto the older user comes first. DESC applies only to age. You can write ASC for ascending, but it’s the default, so you don’t have to. If two rows have the same values in every ORDER BY column, their order is still not guaranteed.

Removing duplicates with DISTINCT

DISTINCT removes duplicate rows from the result:

SELECT DISTINCT city
FROM users
ORDER BY city;

Six users live in four cities, so the result has four rows. With more than one column, DISTINCT removes a row only if every column matches another row. SELECT DISTINCT city, age would return all six rows here, because no two users share both a city and an age.

Limiting the number of rows

LIMIT returns only the first rows of the result. Together with ORDER BY, it answers questions like “who are the three oldest users?”:

SELECT username, age
FROM users
WHERE age IS NOT NULL
ORDER BY age DESC
LIMIT 3;

The WHERE clause leaves out the user with no age. Without it, PostgreSQL and Oracle would sort that row first in descending order, and it would take one of the three places.

Without ORDER BY, LIMIT returns whichever rows the database finds first. OFFSET skips rows before the limit starts, so LIMIT 3 OFFSET 3 returns the fourth to sixth rows. That’s a common way to show results one page at a time.

LIMIT works in MySQL, PostgreSQL, and SQLite. The SQL standard writes it as FETCH FIRST 3 ROWS ONLY at the end of the query, which PostgreSQL and Oracle accept. SQL Server puts TOP 3 right after SELECT instead: SELECT TOP 3 username, age.

The order of the clauses

The clauses of a SELECT statement must be written in this order:

SELECT columns
FROM table
WHERE condition
ORDER BY columns
LIMIT count;

Every clause after FROM is optional, but the ones you use must come in this order. GROUP BY, which chapter 12 uses, goes between WHERE and ORDER BY.

The database doesn’t run the clauses in the order you write them. It reads the table in FROM, keeps the rows that pass WHERE, works out the columns in SELECT, sorts them with ORDER BY, and then applies LIMIT. That’s why ORDER BY can use an alias from the SELECT list and WHERE can’t:

SELECT username, age + 10 AS age_in_ten_years
FROM users
WHERE age + 10 > 45
ORDER BY age_in_ten_years;

WHERE repeats the calculation, because the alias doesn’t exist yet when WHERE runs. MySQL, PostgreSQL, and SQL Server report an error for WHERE age_in_ten_years > 45. SQLite accepts it, but a query written that way won’t run in most other databases.

Chapter 7: SQL Functions

Mathematical functions

SQL has many mathematical functions that you can use in your queries. Here are a few:

• ABS() returns the absolute value of a number.
• ROUND() rounds a number to a specified number of decimal places.
• CEIL() returns the smallest integer greater than or equal to a number.
• FLOOR() returns the largest integer less than or equal to a number.
• POWER() returns a number raised to a specified power.

Date and time functions

SQL also has several functions for working with dates and times, including:

• NOW() returns the current date and time.
• DATE() extracts the date part of a datetime value.
• TIME() extracts the time part of a datetime value.
• YEAR() extracts the year from a date value.
• MONTH() extracts the month from a date value.
• DAY() extracts the day of the month from a date value.

String functions

For working with strings, SQL provides functions such as these:

• LENGTH() returns the length of a string.
• SUBSTRING() returns a portion of a string.
• CONCAT() concatenates two or more strings.
• UPPER() converts a string to uppercase.
• LOWER() converts a string to lowercase.

Aggregate functions

Aggregate functions perform calculations on groups of rows. For example:

• COUNT() counts the number of rows in a group.
• SUM() calculates the sum of a group of values.
• AVG() calculates the average of a group of values.
• MAX() returns the maximum value in a group.
• MIN() returns the minimum value in a group.

Chapter 8: Stored Procedures and Functions

Creating and using stored procedures

A stored procedure is a collection of SQL statements that is saved in the database and can be called by name. Stored procedures can simplify complex operations and improve database performance. Each database system has its own syntax for them. The examples in this chapter use MySQL’s. Here’s how to create one:

DELIMITER //

CREATE PROCEDURE sp_get_users_by_age (IN p_age INT)
BEGIN
    SELECT * FROM users WHERE age = p_age;
END //

DELIMITER ;

This creates a stored procedure called sp_get_users_by_age that takes an input parameter, p_age. The procedure runs a SELECT query on the users table and returns every row where the age column matches the input parameter.

The parameter is named p_age, not age, on purpose. Inside a procedure, MySQL reads a name that matches both a parameter and a column as the parameter, so WHERE age = age would compare the parameter with itself and return every row.

The DELIMITER lines are for the mysql command-line client. The procedure body contains semicolons, so the client is told to treat // as the end of a statement until the procedure has been created, and then switched back to ;.

To run the stored procedure, call it by name:

CALL sp_get_users_by_age(25);

This returns all users aged 25.

Creating and using user-defined functions

A user-defined function is similar to a stored procedure, but it returns a single value instead of executing a collection of statements. You can use user-defined functions to perform calculations or manipulate data. Here’s how to create one:

DELIMITER //

CREATE FUNCTION get_total_sales_by_user (p_user_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE total_sales INT;
    SELECT SUM(sales_amount) INTO total_sales FROM sales WHERE user_id = p_user_id;
    RETURN total_sales;
END //

DELIMITER ;

This creates a user-defined function called get_total_sales_by_user that takes an input parameter, p_user_id. The function runs a SELECT query on the sales table to add up all the sales amounts for that user. It stores the result in a local variable, total_sales, and returns it.

A function’s parameters are always inputs, so unlike a procedure’s they don’t take the IN keyword. READS SQL DATA tells MySQL that the function reads tables but doesn’t change them. MySQL asks for a characteristic like this when binary logging is on, which it is by default.

To use the function, call it in a SELECT statement:

SELECT user_id, get_total_sales_by_user(user_id) as total_sales FROM sales GROUP BY user_id;

This returns a list of user IDs, each with its total sales amount.

Chapter 9: Transactions and Concurrency

Transactions

A transaction is a logical unit of work: a sequence of database operations performed as a single unit. Transactions are a fundamental feature of database systems, and they keep data consistent and intact. The SQL standard defines the concept, and all modern database management systems support it.

Transactions have several benefits, including:

• Atomicity: either all of a transaction’s operations complete successfully or none of them do. If a transaction fails, all of its changes are rolled back to the previous state, so the data stays consistent.
• Consistency: the database stays in a consistent state throughout the transaction. Any change made during a transaction must follow the rules defined by the database schema.
• Isolation: each transaction is isolated from the others and sees a consistent snapshot of the database.
• Durability: once a transaction is committed, its changes persist and survive system failures.

To run a transaction in SQL, you use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements. BEGIN TRANSACTION marks the start of a transaction, and COMMIT marks the end of a successful one. If something fails, ROLLBACK returns the transaction to its starting state.

Concurrency control

Concurrency control manages concurrent access to shared resources. In a database system, that means managing access by multiple users or applications at the same time. The main goal is to keep data consistent and intact while that access is going on.

Concurrency control techniques fall into two categories, pessimistic and optimistic. Pessimistic concurrency control locks resources to prevent concurrent access. Optimistic concurrency control allows concurrent access and detects conflicts after they occur.

In SQL, transactions ensure consistency and integrity, and concurrency control techniques manage concurrent access. Database systems use several concurrency control mechanisms, including locking, timestamp ordering, and multiversion concurrency control (MVCC).

Locking is a pessimistic technique in which a transaction acquires locks on resources to prevent concurrent access. SQL provides several types of locks, including shared locks and exclusive locks.

Timestamp ordering assigns a timestamp to each transaction when it starts and uses those timestamps to settle conflicts: if an operation would break the timestamp order, its transaction is rolled back and restarted. It’s a technique inside some database engines, not something you write in SQL. (The TIMESTAMP data type only stores dates and times, and SET TRANSACTION sets options such as the isolation level.)

MVCC keeps multiple versions of a data item so that it can be accessed concurrently. SQL databases that support MVCC use snapshot isolation, where each transaction sees a consistent snapshot of the database.

Here is an example of how concurrency control keeps the data in a database consistent.

A bank has a database with two tables, accounts and transactions. The accounts table has the columns account_number, balance, and customer_name. The transactions table has the columns transaction_id, account_number, transaction_type, and amount.

Suppose two transactions run at the same time.

Transaction 1 transfers $100 from account A to account B:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_number = A;
UPDATE accounts SET balance = balance + 100 WHERE account_number = B;
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (A, 'debit', 100);
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (B, 'credit', 100);

COMMIT;

Transaction 2 deposits $200 into account A:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance + 200 WHERE account_number = A;
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (A, 'credit', 200);

COMMIT;

Without concurrency control, the two transactions could interfere with each other and leave the database in an inconsistent state. Say transaction 1 runs its first UPDATE statement to subtract $100 from account A’s balance, and before it commits, transaction 2 runs its UPDATE statement to add $200 to account A’s balance. The final balance of account A will then be wrong.

A concurrency control mechanism such as locking prevents this. When transaction 1 starts, the database system can acquire an exclusive lock on account A’s row and another on account B’s row. No other transaction can modify either row until transaction 1 is committed or rolled back.

A database system that supports MVCC can use snapshot isolation instead, which allows concurrent access and still keeps the data consistent. Each transaction sees a consistent snapshot of the database as it was when the transaction started, and changes that other transactions make after that point are not visible to it. In this example, transaction 1 and transaction 2 would both see the initial balance of account A, even if transaction 1 modifies the balance before transaction 2 finishes.

Snapshots only cover reads, though. When both transactions update the same row, as they do with account A here, the database still has to stop one change from overwriting the other. Systems that use MVCC, such as PostgreSQL and MySQL’s InnoDB engine, also lock each row a transaction updates, so the second UPDATE on account A waits until the first transaction commits or rolls back. Depending on the isolation level, the waiting transaction then either applies its change to the newly committed balance or fails with an error and has to be retried.

Locking and MVCC each have pros and cons, and which one to use depends on the specific requirements of the application.

Chapter 10: Advanced SQL Concepts

Subqueries

A subquery is a SQL statement nested inside another SQL statement. The subquery runs first, and the outer query uses its results for further processing. You can use subqueries to simplify complex queries, to calculate from intermediate results, and to retrieve data that a single query can’t.

There are two types of subqueries: correlated and non-correlated. A non-correlated subquery runs once, and the outer query uses its results. A correlated subquery runs once for each row of the outer query. Correlated subqueries are less efficient than non-correlated ones, but they can be useful in some situations.

Here is an example of a non-correlated subquery:

SELECT customer_name, balance
FROM accounts
WHERE balance > (SELECT AVG(balance) FROM accounts);

This query retrieves the name and balance of every customer whose balance is greater than the average balance of all customers.

Views

A view is a virtual table based on the result of a SQL query. Views can simplify complex queries, restrict access to certain data, and give multiple users a consistent view of the data.

You define a view with the CREATE VIEW statement and query it just like a regular table. Depending on the underlying tables and the view’s definition, you may also be able to update, insert into, and delete from a view.

Here is an example of a view:

CREATE VIEW customer_summary AS
SELECT customer_name, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_name;

This view summarizes each customer’s balance by adding up the balances of all their accounts. You can query it like this:

SELECT * FROM customer_summary;

Triggers

A trigger is a special type of stored procedure that runs automatically in response to certain events, such as an insert, update, or delete on a table. Triggers can enforce complex business rules, maintain data integrity, and automate tasks.

You define a trigger with the CREATE TRIGGER statement. A trigger has three parts. The trigger event is the type of event that makes the trigger run. The trigger condition must be true for the trigger to run. The trigger action is the code that runs when the trigger fires.

Here is an example of a trigger:

CREATE TRIGGER update_balance_after_insert
AFTER INSERT ON accounts
FOR EACH ROW
BEGIN
  UPDATE customers
  SET balance = (SELECT SUM(balance) FROM accounts WHERE customer_id = NEW.customer_id)
  WHERE customer_id = NEW.customer_id;
END;

CREATE TRIGGER update_balance_after_delete
AFTER DELETE ON accounts
FOR EACH ROW
BEGIN
  UPDATE customers
  SET balance = (SELECT COALESCE(SUM(balance), 0) FROM accounts WHERE customer_id = OLD.customer_id)
  WHERE customer_id = OLD.customer_id;
END;

These triggers keep the balance column of the customers table equal to the total of that customer’s accounts. Many database systems, including MySQL and SQLite, allow only one event per trigger, so there’s one trigger for inserts and one for deletes.

Inside a row trigger, NEW holds the row as it is after the change and OLD holds it as it was before. An INSERT trigger only has NEW, because there was no row before, and a DELETE trigger only has OLD, because there’s no row after. That’s why the insert trigger looks up NEW.customer_id and the delete trigger looks up OLD.customer_id. An UPDATE trigger has both, and one for this table would recalculate the balance for OLD.customer_id and for NEW.customer_id, in case the account moved to a different customer. COALESCE turns the NULL that SUM returns for a customer with no accounts left into 0.

Chapter 11: SQL Best Practices and Optimization

Best practices for SQL programming

SQL programming means writing queries, stored procedures, and scripts that work with databases. Following best practices keeps your code readable, maintainable, and fast. Here are some of the most important ones:

1. Use descriptive names: choose meaningful names for tables, columns, and variables so the code is easier to read and understand. Avoid abbreviations or cryptic names that may confuse other developers, or you, later on.
2. Follow coding standards: stick to the coding standards and conventions your team or organization has set. Consistent formatting, indentation, and naming make code easier to maintain and to work on together.
3. Parameterize queries: to prevent SQL injection attacks, use parameterized queries instead of building SQL statements dynamically from user input. Parameterization also improves query performance and makes code easier to reuse.
4. Modularize code: break complex SQL logic into smaller, modular components such as stored procedures, functions, or views. Modular code is easier to maintain, reuse, and troubleshoot.
5. Document code: add comments that explain what your SQL code does, how its logic works, and any side effects it may have. Comments help you understand, debug, and maintain SQL scripts over time.

Optimizing SQL performance

Optimizing SQL performance means finding and fixing bottlenecks so that queries run faster and use resources more efficiently. Here are some strategies:

1. Analyze query execution plans: use database tools to analyze execution plans and spot inefficient query patterns, such as full table scans or index scans. Once you understand a query’s execution plan, you can optimize indexes, rewrite the query, and improve overall performance.
2. Create indexes carefully: index the columns you often use in search conditions or join operations to speed up data retrieval. Don’t over-index, though. Extra indexes take up storage and slow down write operations.
3. Optimize joins and subqueries: use the appropriate join type (for example, INNER JOIN or LEFT JOIN) and optimize join conditions to minimize the number of rows processed. Weigh subqueries against joins, and choose whichever approach is more efficient for the query’s requirements and the distribution of the data.
4. Limit result sets: retrieve only the columns and rows you need by listing columns explicitly and filtering data with WHERE clauses. Smaller result sets reduce network traffic, memory consumption, and query execution time.
5. Monitor and tune: monitor database performance metrics such as CPU usage, memory usage, and disk I/O to find bottlenecks. Use that monitoring data to tune configuration settings, query plans, and indexes so the database uses its resources well.

Workloads change over time, so keep monitoring and fine-tuning your SQL queries and database configuration to keep performance at its peak.

Chapter 12: SQL for Data Analysis

This chapter covers techniques for analyzing data with SQL, including window functions and common table expressions (CTEs).

Analyzing data with SQL

SQL has a wide set of features for data analysis that let you aggregate, filter, and manipulate datasets to draw conclusions from them. Whether you’re working with transactional data, log files, or sensor readings, SQL can help you find the patterns, trends, and anomalies in your data.

Aggregation functions

SQL’s built-in aggregation functions, such as SUM, AVG, COUNT, MIN, and MAX, summarize data across rows or groups of rows. You can use them to calculate metrics such as total sales, average revenue, or the number of transactions in a given time period.

-- Calculate total sales for each product category
SELECT category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY category;

Filtering data

The WHERE clause filters rows on specific criteria, such as dates, categories, or numerical ranges. Filters let you focus your analysis on the relevant subsets of data and leave out outliers or irrelevant observations.

-- Filter transactions for the month of January 2024
SELECT *
FROM transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31';

Join operations

JOIN operations combine data from multiple tables based on common keys or relationships. You need joins to bring together data from different sources or tables, which lets you do cross-sectional analysis and see your data as a whole.

-- Combine sales data with product information
SELECT s.*, p.product_name
FROM sales s
JOIN products p ON s.product_id = p.product_id;

Window functions

Window functions perform calculations across a set of rows related to the current row. They let you calculate aggregated values, rankings, and moving averages without complex subqueries or self-joins.

Example of window functions

With window functions you can:

• Calculate cumulative totals or running sums over a time series or other ordered dataset.
• Rank rows by specific criteria, such as sales performance or customer satisfaction scores.
• Compute moving (rolling) averages to smooth out fluctuations in your data.

-- Calculate cumulative sales over time
SELECT transaction_date, sales_amount,
       SUM(sales_amount) OVER (ORDER BY transaction_date) AS cumulative_sales
FROM sales;

Common table expressions (CTEs)

A common table expression (CTE) defines a temporary result set within a SQL query, which makes complex queries easier to read and maintain. CTEs are particularly useful for breaking a complex analysis into smaller, more manageable steps.

Advantages of CTEs

• Breaking complex logic into smaller, named components makes a query easier to read.
• A common subquery defined once can be referenced several times within a single query, so you can reuse code.
• A recursive CTE (WITH RECURSIVE) can follow hierarchical data, such as an organizational chart, to any depth, which a plain query can’t do.

A CTE is mainly about readability. On its own it doesn’t make a query run faster than the equivalent subquery.

Example of CTEs in data analysis

CTEs work well for:

• Recursive queries that traverse hierarchical data, such as organizational charts or product category hierarchies.
• Preprocessing steps that transform or filter data before further analysis.
• Multi-step analysis workflows involving several queries or data transformations.

Take an employees table where each row names its manager. John has no manager, so he’s at the top:

A recursive CTE works out how far down the chart each employee sits. The first SELECT finds the top of the chart, and the second keeps adding the employees who report to someone already found:

-- Calculate hierarchical structure of employees
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY employee_id;

With aggregation functions, window functions, and common table expressions, you can do a wide range of data analysis directly in your database, from exploring trends in sales data to identifying outliers in financial transactions or running cohort analysis on user behavior.

Chapter 13: NoSQL and SQL

This chapter looks at how NoSQL and SQL databases differ and how SQL can be used alongside NoSQL databases.

Differences between NoSQL and SQL

NoSQL (Not Only SQL) databases and SQL databases take different approaches to storing and managing data. Knowing how they differ helps you choose the right database for your application’s requirements.

Characteristics of NoSQL databases

• Flexible schema: NoSQL databases often have no schema or a flexible one, so developers can store and retrieve data without defining a schema first.
• Scalability: NoSQL databases are designed to scale horizontally, which makes them suitable for large volumes of data spread across distributed systems.
• Non-relational: NoSQL databases usually don’t store data in tables with fixed relationships between them. They use document, key-value, columnar, or graph-based data models instead.

Characteristics of SQL databases

• Structured Query Language (SQL): SQL databases define and manipulate data with SQL, a standardized query language, which makes relational data models easy to work with.
• ACID transactions: SQL databases support ACID (Atomicity, Consistency, Isolation, Durability) transactions, which protect data integrity and reliability.
• Relational model: SQL databases follow a relational model of tables with predefined schemas and relationships between them.

Using SQL with NoSQL databases

NoSQL databases have benefits of their own, but in some situations SQL can complement them and add functionality and flexibility.

Integration via SQL interfaces

Some NoSQL databases provide SQL interfaces or compatibility layers that let developers work with the database using SQL-like syntax. These interfaces bridge the gap between SQL and NoSQL, so you can use the SQL skills and tools you already have with a NoSQL database.

Data aggregation and analysis

SQL can handle data aggregation, analysis, and reporting even when the data is stored in a NoSQL database. Developers can export the data into SQL-compatible formats or use SQL-on-Hadoop solutions, then run complex analytical queries on it and draw insights from it.

Hybrid architectures

A hybrid architecture uses SQL and NoSQL databases together to get the strengths of each. For example, SQL databases may handle transactional data storage and relational querying, while NoSQL databases handle unstructured or semi-structured data and provide horizontal scalability.

Whether you choose NoSQL, SQL, or a hybrid approach, the right database technology depends on factors such as data structure, query requirements, scalability, and performance goals.

Chapter 14: SQL and the Cloud

This chapter covers SQL databases in cloud computing: the advantages of cloud-based database services and the role SQL plays in cloud environments.

Cloud-based database services

Cloud-based database services are a convenient, scalable way to store, manage, and access data in the cloud. They take over the complex work of database administration and infrastructure management, so developers can focus on building applications.

Advantages of cloud-based database services

• Scalability: cloud-based databases can scale horizontally and vertically to keep up with growing data volumes and user demand.
• Managed services: cloud providers handle routine database tasks such as provisioning, backup, replication, and security, which reduces the operational work for development teams.
• High availability: cloud databases are designed for high availability and fault tolerance. Built-in redundancy and automatic failover keep the data available and durable.
• Pay-per-use pricing: cloud database services usually charge on a pay-as-you-go basis, so organizations pay only for the resources they consume and can scale up or down as needed.

SQL in the cloud

SQL is still a widely used language for working with databases in the cloud, and many cloud providers offer SQL-compatible database services. With these services, developers can use the SQL skills and tools they already have while getting the benefits of cloud computing.

Types of SQL database services in the cloud

1. Managed relational databases: cloud providers offer managed relational database services that support SQL, such as Amazon RDS (Relational Database Service) and Azure SQL Database. These services provide fully managed instances of popular SQL databases like MySQL, PostgreSQL, and SQL Server, with automated backups, scaling, and monitoring.

2. SQL data warehouses: cloud platforms offer SQL-based data warehousing, such as Amazon Redshift, Google BigQuery, and Azure Synapse Analytics. These services are optimized for analytical workloads. You can use them to run complex SQL queries against large datasets for business intelligence and analytics.

3. SQL-compatible NoSQL databases: some NoSQL databases in the cloud provide SQL compatibility layers or interfaces, so you can query data using SQL-like syntax. Examples include Amazon DynamoDB with PartiQL and Google Cloud Firestore in Datastore mode with GQL.

Benefits of SQL in the cloud

• Familiarity: SQL is widely adopted and its syntax is standardized, so developers and analysts already know it. That makes accessing and analyzing data in cloud environments simpler.
• Interoperability: SQL works across different cloud services and platforms, so you can query and integrate data from multiple sources in one common language.
• Tooling ecosystem: cloud-based SQL databases are compatible with a wide range of SQL client tools, IDEs, and libraries, so they fit into existing development workflows.

With SQL in the cloud, organizations can build scalable, flexible, and cost-effective data systems for modern applications and analytics workloads. Whether you’re migrating existing SQL databases to the cloud or building cloud-native applications, SQL remains a core part of how data is managed there.

Chapter 15: Conclusion and Future of SQL

This last chapter sums up the main points of the book and looks at where SQL is likely to go next.

Summary

SQL (Structured Query Language) has been the foundation of data management for decades, and it gives you a standardized way to work with relational databases. This book has covered many aspects of SQL, including best practices, optimization techniques, its role in data analysis, how it relates to NoSQL databases, and how it works with cloud computing. The main takeaways:

• Best practices: following best practices and optimization techniques is essential if you want SQL-based systems to be efficient, scalable, and maintainable.
• Data analysis: SQL has many features for data analysis, including aggregation functions, filtering, join operations, window functions, and common table expressions (CTEs).
• NoSQL and SQL: NoSQL databases offer flexibility and scalability, but SQL is still relevant for certain use cases, and you can use it alongside NoSQL databases to add functionality and flexibility.
• SQL and the cloud: cloud-based database services offer scalable, managed SQL databases, so organizations can use SQL in the cloud and gain agility, scalability, and cost efficiency.

Future of SQL

Several trends and developments are likely to influence SQL’s future:

• Integration with emerging technologies: SQL is expected to keep evolving so it can work with technologies such as machine learning, artificial intelligence, and big data analytics. SQL-based platforms and tools will play a major part in advanced data processing and analysis.
• Advancements in database management systems: ongoing work in distributed computing, in-memory processing, and real-time analytics will shape what SQL databases can do.
• Cloud-native SQL solutions: cloud-native SQL databases and services will keep gaining popularity, giving organizations scalable, flexible, and cost-effective ways to manage and analyze their data in the cloud.
• Hybrid architectures: architectures that combine SQL and NoSQL databases will become more common, letting organizations use the strengths of both to meet different application requirements.

SQL remains a fundamental technology for managing and analyzing data. Organizations that follow best practices, keep up with emerging trends, and use SQL together with other technologies will get more out of their data.

Appendix A: SQL Syntax Reference

This appendix is a quick reference to the syntax of the statements and constructs you’ll use most often in SQL queries.

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY column_name datatype;

DROP TABLE

DROP TABLE table_name;

Data Manipulation Language (DML)

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE FROM

DELETE FROM table_name
WHERE condition;

Data Query Language (DQL)

SELECT

SELECT column1, column2, ...
FROM table_name
WHERE condition;

DISTINCT

SELECT DISTINCT column1, column2, ...
FROM table_name;

ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;

GROUP BY

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Data Control Language (DCL)

GRANT

GRANT privilege_name
ON object_name
TO {user_name | PUBLIC | role_name};

REVOKE

REVOKE privilege_name
ON object_name
FROM {user_name | PUBLIC | role_name};

Transaction Control Language (TCL)

COMMIT

COMMIT;

ROLLBACK

ROLLBACK;

SAVEPOINT

SAVEPOINT savepoint_name;

RELEASE SAVEPOINT

RELEASE SAVEPOINT savepoint_name;

Appendix B: Glossary of SQL Terms

This appendix defines common SQL terms.

A

ACID

ACID stands for Atomicity, Consistency, Isolation, and Durability, the four properties that guarantee database transactions are reliable.

C

Column

A column, also called a field or attribute, is a vertical entity in a database table that contains all the information associated with a specific data type.

Constraint

A constraint is a rule or condition enforced on a database to maintain data integrity. Uniqueness, primary key, foreign key, and check constraints are examples.

D

Database

A database is an organized collection of structured data stored and managed in a computer system, usually by a database management system (DBMS).

DDL (Data Definition Language)

DDL is the subset of SQL used to define, modify, and remove the structure of database objects such as tables, indexes, and views.

DML (Data Manipulation Language)

DML is the subset of SQL used to retrieve, insert, update, and delete data in a database, usually through SELECT, INSERT, UPDATE, and DELETE statements.

I

Index

An index is a data structure that speeds up data retrieval from a database table by giving quick access to rows based on the values of specific columns.

J

Join

A join is an SQL operation that combines rows from two or more tables based on a related column between them, so you can retrieve related data from several tables in a single query.

K

Key

A key is a column or set of columns used to identify rows or to link tables. A primary key or unique key identifies each row in its table. A foreign key holds values of a key in another table, so it links the two tables and doesn’t have to be unique in its own table.

Q

Query

A query is a request for data from a database, usually a SELECT statement that retrieves the rows matching specified criteria. The word is also used loosely for any SQL statement, including INSERT, UPDATE, and DELETE, which change data instead of retrieving it.

R

Relational database

A relational database is a database that organizes data into tables with rows and columns and uses keys to establish relationships between the tables. The software that manages one is a relational database management system (RDBMS).

S

Schema

A schema is a collection of database objects, including tables, views, indexes, and constraints, that defines the structure and organization of a database.

T

Transaction

A transaction is a sequence of one or more SQL statements executed as a single unit of work. Transactions keep database operations consistent and preserve their integrity.

V

View

A view is a virtual table generated by an SQL query. It presents data from one or more tables in a predefined format, and you can query it like a table without repeating the SQL behind it. A view doesn’t store its own copy of the data, so changing data through a view changes the underlying tables.