Databases & SQL

A database is an organized collection of related data, stored so that it can be searched, updated, and shared reliably. The software that stores and manages that data, and answers your questions about it, is a database management system (DBMS). When you look up a bank balance, buy a train ticket, or scroll a social feed, a DBMS is serving the data behind the scenes.

Why not just use files?

You could keep data in a spreadsheet or a plain text file, and for a small personal list that is fine. But as soon as data is large, shared by many users, or must never be corrupted, flat files fall apart. A DBMS solves problems that files cannot:

• Concurrent access: many users can read and write at the same time without clobbering each other's changes.
• Integrity: rules (called constraints) reject bad data, such as a negative age or a duplicate ID.
• Efficient querying: you ask what you want and the DBMS figures out how to find it fast, even across millions of rows.
• Durability and recovery: committed data survives a crash, and backups can restore it.
• Security: access can be granted per user and per table.

Kinds of databases

The most common and most important family is the relational database, which organizes data into tables and is queried with the language SQL (Structured Query Language). This whole course is about relational databases and SQL, because they run the majority of the world's business systems. You may also hear about NoSQL databases (document, key-value, graph, and column stores) that trade some of the relational guarantees for flexibility or scale. They are useful, but the relational model is the foundation every data professional needs first.

A tiny example

Imagine a small library. Instead of one giant file, a relational database splits the information into focused tables, for example a table of books and a table of members and a table of loans that records who borrowed what. Each table holds one kind of thing, and the tables are linked together so the DBMS can answer a question like "which members currently have an overdue book?" with a single query. Learning to design those tables and write those queries is exactly what the rest of this course teaches.

Declarative, not procedural

A defining feature of SQL is that it is declarative. You describe the result you want, not the step-by-step algorithm to compute it. You write "give me all customers in Ohio sorted by last name," and the DBMS's query optimizer chooses the fastest way to produce that. This is a big reason SQL has lasted for decades: your instructions stay simple while the engine underneath gets smarter.

The relational model, introduced by Edgar F. Codd in 1970, is the theory beneath every SQL database. Its central idea is beautifully simple: store all data in tables. A table (formally a relation) is a grid. Each row (a tuple) represents one entity or fact; each column (an attribute) represents one property of that entity.

Anatomy of a table

Here is a students table. The top row names the columns; each following row is one student.

student_id	first_name	last_name	major	gpa
1	Ada	Nguyen	Computer Science	3.8
2	Diego	Okafor	Biology	3.5
3	Priya	Santos	Computer Science	3.9

Some vocabulary that maps everyday words to the formal terms:

• A column / attribute is a named property, such as gpa. Every value in that column is the same kind of thing.
• A row / tuple / record is one complete entry, such as the student Ada Nguyen.
• The domain of a column is the set of allowed values and their type, for example gpa is a decimal number, last_name is text.
• The number of columns is the table's degree; the number of rows is its cardinality.

Data types

Every column has a data type that restricts what it can hold. Common SQL types include INTEGER for whole numbers, DECIMAL or NUMERIC for exact fractional numbers like money, VARCHAR(n) for variable-length text up to n characters, DATE for calendar dates, and BOOLEAN for true or false. Types are the first line of defense for data quality: you cannot accidentally store the word "hello" in an INTEGER column.

The special value NULL

Sometimes a value is unknown or does not apply. SQL represents this with NULL, a special marker meaning "no value here." NULL is not zero and not an empty string; it is the absence of a value. It behaves unusually: any comparison with NULL using = yields "unknown," not true, which is why SQL provides the special tests IS NULL and IS NOT NULL. You will see the consequences of NULL throughout the course, especially in filtering and joins.

Two key properties of relations

In the pure relational model, a table is a set of rows, so two properties follow. First, row order carries no meaning: the database is free to store rows in any order, and if you want output sorted you must ask for it explicitly. Second, each row should be uniquely identifiable, which leads directly to the idea of a key, the subject of the next lesson.

The power of the relational model comes from splitting data into focused tables and then linking them. Keys are what make that linking possible and reliable.

Primary keys

A primary key is one or more columns whose value uniquely identifies each row in a table. No two rows may share the same primary key, and a primary key can never be NULL. In the students table, student_id is the natural primary key: every student gets a distinct id. A column (or set of columns) that could serve as a primary key is a candidate key; you pick one candidate to be the primary key. Keys made of a single column are common, but a composite key spanning several columns is allowed when no single column is unique on its own.

Foreign keys link tables

A foreign key is a column in one table that refers to the primary key of another table. This is the glue of a relational database. Suppose we add an enrollments table recording which student takes which course:

enrollment_id	student_id	course_id	grade
10	1	CS340	A
11	3	CS340	A
12	2	BIO101	B

Here student_id in enrollments is a foreign key pointing at student_id in students. Row 10 means "student number 1 (Ada) is enrolled." Because the id is stored once in students and merely referenced elsewhere, the student's name lives in exactly one place. Update it there and every reference is instantly correct.

Referential integrity

A foreign key enforces referential integrity: the database refuses to store an enrollments row whose student_id does not exist in students. This prevents "orphan" records that point at nothing. The DBMS can also control what happens when a referenced row is deleted, for example blocking the delete, or cascading it to remove the dependent rows too.

Kinds of relationships

Relationships between entities come in three shapes, and recognizing them drives good design:

• One-to-one (1:1): each row in A matches at most one row in B. Example: a person and their single passport record.
• One-to-many (1:N): one row in A matches many in B, but each B row matches one A. Example: one student has many enrollments. This is the most common shape and is implemented by putting the foreign key on the "many" side.
• Many-to-many (M:N): rows in A match many in B and vice versa. Example: students and courses, since a student takes many courses and a course has many students. SQL cannot store this directly; you create a junction table (like enrollments) that holds foreign keys to both sides, turning one M:N relationship into two 1:N relationships.

Before you type any SQL, you should design the database. Entity-relationship (ER) modeling is the standard way to plan a schema. You look at the real world you are modeling and pick out its entities, their attributes, and the relationships between them, then draw a picture called an ER diagram.

The three building blocks

• An entity is a thing worth storing data about, and it usually becomes a table. In a school: Student, Course, Instructor. Entities are typically nouns.
• An attribute is a property of an entity, and it usually becomes a column. A Student has a name, a birth date, a GPA. One attribute (or a few together) is the identifier that becomes the primary key.
• A relationship is an association between entities, and it becomes a foreign key or a junction table. A Student enrolls in a Course; an Instructor teaches a Course.

Cardinality on the diagram

Each relationship is labeled with its cardinality, one-to-one, one-to-many, or many-to-many, exactly the shapes from the previous lesson. A popular notation is crow's foot, where a small three-pronged "foot" on the line means "many." So the line between Student and Course would show a crow's foot at both ends, marking a many-to-many relationship.

Here is a tiny ER sketch for a school, drawn as a diagram:

From diagram to tables

Translation follows mechanical rules:

1. Each entity becomes a table; each attribute becomes a column; the identifier becomes the primary key.
2. A one-to-many relationship becomes a foreign key on the "many" side.
3. A many-to-many relationship becomes a new junction table holding a foreign key to each entity.

So Student and Course become two tables, and the many-to-many "enrolls" becomes an enrollments junction table with student_id and course_id foreign keys, plus any attributes of the relationship itself, such as the grade. Doing this thinking on paper first saves you from painful redesigns later.

Normalization is the process of structuring tables to eliminate redundant data and the update problems it causes. A badly designed table repeats the same fact in many rows, which leads to anomalies: an update anomaly (you change a fact in one row but forget others, so the data disagrees with itself), an insertion anomaly (you cannot add one fact without inventing unrelated data), and a deletion anomaly (deleting one row accidentally erases a separate fact). Normalization fixes these by splitting data into well-focused tables.

The problem, concretely

Suppose we cram everything into one table:

student_id	student_name	courses	advisor	advisor_office
1	Ada	CS340, MATH200	Dr. Lee	Room 210
2	Diego	BIO101	Dr. Lee	Room 210

This has three flaws: the courses cell holds a list (not a single value), Dr. Lee's office is repeated on every one of her advisees (redundant), and if Ada leaves we might lose the only record of the advisor's office. Normalization repairs all three, one form at a time.

First normal form (1NF)

A table is in 1NF if every cell holds a single atomic value and there are no repeating groups. The courses cell breaks this. Fix it by moving enrollments to their own table with one row per student-course pair. Now each cell holds exactly one value.

Second normal form (2NF)

A table is in 2NF if it is in 1NF and every non-key column depends on the whole primary key, not just part of it. This only bites when the primary key is composite. Imagine an enrollments(student_id, course_id, grade, course_title) table with a composite key of (student_id, course_id). The course_title depends only on course_id, half the key, so it is a partial dependency. Fix it by moving course_title into a courses table keyed by course_id.

Third normal form (3NF)

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column. Such an indirect link is a transitive dependency. In the original table, advisor_office depends on advisor, which depends on student_id; the office is only transitively tied to the student. Fix it by giving advisors their own table:

advisors(advisor_id, advisor_name, office)
students(student_id, student_name, advisor_id)   -- advisor_id is a foreign key

Now each fact lives in exactly one place. A useful summary of the goal, often stated as a memory aid, is that every non-key column must depend on the key, the whole key, and nothing but the key. That single sentence captures 1NF, 2NF, and 3NF together.

Functional dependencies

Underlying all of this is the functional dependency: we write A right-arrow B to mean "if you know A, you know B." Design consists of finding these dependencies and making sure each one is enforced by putting B in a table whose key is A. Normalization is not about following ritual; it is about honoring the real dependencies in your data so it can never contradict itself.

The SELECT statement is the workhorse of SQL: it reads data from tables. Its simplest form names the columns you want and the table to read from. We will use this employees table throughout the lesson:

id	name	department	salary	hire_year
1	Ada	Engineering	95000	2019
2	Diego	Sales	62000	2021
3	Priya	Engineering	88000	2020
4	Mateo	Sales	71000	2018

Choosing columns

SELECT name, salary
FROM employees;

This returns just the name and salary columns for every row. To get all columns, use the shorthand *:

SELECT *
FROM employees;

Filtering rows with WHERE

The WHERE clause keeps only the rows whose condition is true. To list engineers:

SELECT name, department
FROM employees
WHERE department = 'Engineering';

Text literals go in single quotes; numbers do not. The comparison operators are =, <> (not equal), <, <=, >, and >=. Combine conditions with AND, OR, and NOT:

SELECT name
FROM employees
WHERE department = 'Sales' AND salary > 65000;

Against the table above, that returns only Mateo (71000). Diego is in Sales but earns 62000, so he fails the salary test.

Handy special conditions

SQL offers compact operators for common tests:

• BETWEEN checks an inclusive range: WHERE salary BETWEEN 60000 AND 90000 matches salaries from 60000 up to and including 90000.
• IN checks membership in a list: WHERE department IN ('Sales', 'Marketing').
• LIKE does pattern matching on text, where % matches any run of characters and _ matches one: WHERE name LIKE 'A%' finds names starting with A.
• IS NULL / IS NOT NULL test for missing values, since = NULL never works.

SELECT name, hire_year
FROM employees
WHERE hire_year BETWEEN 2019 AND 2021
  AND name LIKE '%a%';

Reading it: keep employees hired from 2019 through 2021 whose name contains a lowercase letter a. That matches Ada and Priya. Mateo is excluded because he was hired in 2018 (failing the year test), and Diego is excluded because his name contains no lowercase a at all. Getting comfortable with WHERE is the single most useful SQL skill, because almost every query filters something.

Because a table is an unordered set, a query returns rows in no guaranteed order unless you ask. Three tools shape the final result set: ORDER BY to sort, a row limit to trim, and DISTINCT to remove duplicates.

ORDER BY

Add ORDER BY and a column to sort the output. By default the order is ascending (ASC): smallest to largest, or A to Z. Add DESC for descending order.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

This lists employees from the highest salary to the lowest. You can sort by several columns; ties in the first are broken by the next:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

That groups rows by department alphabetically, and within each department shows the highest earner first.

Limiting rows

To return only the first few rows, most databases use LIMIT. Combined with ORDER BY, it answers "top N" questions:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

This returns the three highest-paid employees. Note the standard SQL keyword is technically FETCH FIRST 3 ROWS ONLY, and some systems (such as SQL Server) write SELECT TOP 3, but LIMIT is used by PostgreSQL, MySQL, and SQLite and is the form you will meet most often. You can skip rows with OFFSET, so LIMIT 10 OFFSET 20 returns rows 21 through 30, the pattern behind page-by-page results.

DISTINCT

Sometimes a projection produces duplicate rows and you want each unique value once. DISTINCT removes duplicates from the result:

SELECT DISTINCT department
FROM employees;

From our four employees this returns just two rows, Engineering and Sales, rather than repeating each department once per employee. Remember that DISTINCT applies to the whole selected row, so SELECT DISTINCT department, salary keeps a row for each unique combination of department and salary, not each unique department.

Because good design spreads data across tables, most real questions need data from more than one. A join combines rows from two tables by matching values in related columns, almost always a foreign key matching a primary key. We will use these two small tables:

employees		departments
name	dept_id	dept_id	dept_name
Ada	1	1	Engineering
Diego	2	2	Sales
Priya	1	3	Marketing
Sam	NULL

INNER JOIN

An INNER JOIN returns only rows that have a match on both sides. To pair each employee with their department name:

SELECT e.name, d.dept_name
FROM employees AS e
INNER JOIN departments AS d
  ON e.dept_id = d.dept_id;

The ON clause states the matching condition. The AS e and AS d are table aliases that keep the query short. The result pairs Ada, Diego, and Priya with their departments. Sam is dropped because his dept_id is NULL and matches nothing, and Marketing is dropped because no employee is in it. An inner join keeps only matches on both sides.

name	dept_name
Ada	Engineering
Diego	Sales
Priya	Engineering

LEFT JOIN

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table, and fills the right-side columns with NULL where there is no match. To list all employees even if unassigned:

SELECT e.name, d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
  ON e.dept_id = d.dept_id;

Now Sam appears with a NULL dept_name, because the left join preserves him. This is the standard way to find rows that lack a match: add WHERE d.dept_id IS NULL and you get exactly the employees with no department.

RIGHT JOIN

A RIGHT JOIN is the mirror image: it keeps every row from the right table and NULL-fills the left. Using the same tables, a right join from employees to departments keeps Marketing (which has no employees) with a NULL employee name. In practice people usually write LEFT JOINs and simply put the table they care about on the left, but RIGHT JOIN exists for symmetry.

Summary of who survives

Join type	Rows kept
INNER JOIN	Only rows matched in both tables
LEFT JOIN	All left rows; right side NULL when unmatched
RIGHT JOIN	All right rows; left side NULL when unmatched

The mental model: decide which table's rows you must not lose, then pick the join that guarantees they stay.

Often you do not want individual rows but a summary: a total, an average, a count. SQL's aggregate functions collapse many rows into a single value. The five you will use constantly are:

• COUNT - how many rows (or non-NULL values).
• SUM - the total of a numeric column.
• AVG - the average of a numeric column.
• MIN and MAX - the smallest and largest value.

Applied to the whole table, they return one row. Using the employees table (Ada 95000 Engineering, Diego 62000 Sales, Priya 88000 Engineering, Mateo 71000 Sales):

SELECT COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees;

This returns a single row: headcount 4, average salary 79000, top salary 95000. (The four salaries sum to 316000; divided by 4 that is 79000.)

GROUP BY: one summary per category

The real power appears with GROUP BY, which splits rows into groups sharing a value and computes the aggregate per group. To get the average salary in each department:

SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

This returns one row per department: Engineering has 2 employees averaging 91500 (95000 and 88000), and Sales has 2 employees averaging 66500 (62000 and 71000). A firm rule: every column in the SELECT list must either be inside an aggregate function or be listed in the GROUP BY. Mixing a bare column with an aggregate without grouping it is an error, because the database would not know which row's value to show.

HAVING: filtering groups

To keep only some of the groups, you cannot use WHERE, because WHERE filters individual rows before grouping. Instead use HAVING, which filters groups after aggregation:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

Against our data this returns only Engineering, whose average of 91500 exceeds 80000; Sales at 66500 is filtered out. The distinction is worth memorizing: WHERE filters rows before grouping; HAVING filters groups after grouping. You can use both in one query, WHERE first to discard rows, then GROUP BY, then HAVING to discard groups.

Logical order of a query

It helps to know the order the database evaluates clauses, which differs from the order you write them: FROM then WHERE then GROUP BY then HAVING then SELECT then ORDER BY then LIMIT. That is why HAVING can reference an aggregate (grouping already happened) but WHERE cannot.

A subquery (or nested query) is a SELECT statement placed inside another statement. It lets you use the result of one query as an input to another, which is perfect for questions that reference an intermediate value, such as "who earns more than the company average?" We will use the same employees table (Ada 95000, Diego 62000, Priya 88000, Mateo 71000).

Scalar subqueries

A scalar subquery returns a single value and can be used anywhere a value is expected. To find employees paid above the overall average:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query computes the average (79000), and the outer query keeps everyone above it: Ada (95000) and Priya (88000). This is impossible in a single flat query because you need the aggregate before you can compare each row to it.

Subqueries with IN

A subquery can return a list of values for use with IN. Suppose we also have an enrollments table listing which employees enrolled in training. To find employees who did enroll:

SELECT name
FROM employees
WHERE id IN (SELECT employee_id FROM enrollments);

The subquery yields the set of enrolled ids, and the outer query keeps employees whose id is in that set. Using NOT IN instead would find employees who never enrolled (take care that the subquery returns no NULLs, which can make NOT IN behave surprisingly).

EXISTS

EXISTS tests whether a subquery returns any row at all, and is often used for a correlated subquery, one that references the outer row. It is true as soon as one matching row is found:

SELECT name
FROM employees AS e
WHERE EXISTS (
  SELECT 1 FROM enrollments AS en
  WHERE en.employee_id = e.id
);

For each employee, the inner query checks whether any enrollment references that employee; if so, the employee is kept. EXISTS often reads more naturally than IN for "is there at least one related row?" questions and can be faster because it stops at the first match.

Subquery or join?

Many subqueries can be rewritten as joins and vice versa. As a guideline: use a join when you want columns from both tables in the output; use a subquery when you only need one table's rows but the filter depends on a computed value or another table's contents. Both are correct; choose whichever makes the query easiest to read.

So far we have only read data. The three data manipulation statements that change it are INSERT, UPDATE, and DELETE. They are simple, but two of them carry a famous hazard: forgetting a WHERE clause.

INSERT: adding rows

INSERT adds new rows. List the columns, then the matching values:

INSERT INTO employees (name, department, salary, hire_year)
VALUES ('Nadia', 'Engineering', 90000, 2023);

Naming the columns explicitly is good practice; it keeps the statement working even if the table later gains a column, and it lets the database fill unlisted columns with their defaults or NULL. You can insert several rows at once by listing multiple value tuples separated by commas:

INSERT INTO employees (name, department, salary, hire_year)
VALUES ('Omar', 'Sales', 68000, 2022),
       ('Lena', 'Marketing', 74000, 2023);

UPDATE: changing rows

UPDATE modifies existing rows. The SET clause assigns new values, and the WHERE clause chooses which rows to change. To give Nadia a raise:

UPDATE employees
SET salary = 95000
WHERE name = 'Nadia';

The WHERE clause is critical. If you omit it, UPDATE changes every row in the table. The statement UPDATE employees SET salary = 95000; would set every employee's salary to 95000. Always write and double-check the WHERE clause before running an UPDATE. You can update several columns at once by separating assignments with commas:

UPDATE employees
SET salary = salary * 1.05, department = 'Engineering'
WHERE name = 'Omar';

Note that salary = salary * 1.05 reads the old value and writes a 5 percent raise, a common pattern.

DELETE: removing rows

DELETE removes rows that match its WHERE clause:

DELETE FROM employees
WHERE name = 'Lena';

Like UPDATE, DELETE without a WHERE clause empties the whole table: DELETE FROM employees; removes every row. Because of referential integrity, the database may also refuse to delete a row that other tables still reference through a foreign key, unless the relationship is set to cascade. A safe habit: before running a risky UPDATE or DELETE, run the same WHERE as a SELECT first to see exactly which rows you are about to affect.

Reading and changing data assumes the tables already exist. The data definition statements create and alter that structure. The most important is CREATE TABLE, which defines a table's columns, their types, and the constraints that keep its data valid.

A complete CREATE TABLE

CREATE TABLE employees (
    id          INTEGER      PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE,
    department  VARCHAR(50)  DEFAULT 'Unassigned',
    salary      DECIMAL(10,2) CHECK (salary >= 0),
    dept_id     INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Read it column by column. Each line gives a column name, a data type, and zero or more constraints. This one statement encodes a great deal of protection.

The constraint toolbox

• PRIMARY KEY makes id unique and non-NULL, the row's identifier.
• NOT NULL requires name to always have a value; an insert that omits it is rejected.
• UNIQUE forbids two rows from sharing an email, without making it the primary key.
• DEFAULT supplies a value ('Unassigned') when an insert does not specify department.
• CHECK enforces a custom rule; here salary can never be negative. Any insert or update that violates it fails.
• FOREIGN KEY ... REFERENCES ties dept_id to the departments table, enforcing referential integrity.

Constraints are the database's built-in guarantees. Rather than hoping every program that touches the data remembers the rules, you declare the rules once and the DBMS enforces them for everyone, forever. This is one of the strongest arguments for a real database over a pile of files.

Changing and dropping tables

Two more statements round out schema work. ALTER TABLE modifies an existing table, for instance adding a column:

ALTER TABLE employees ADD COLUMN start_date DATE;

And DROP TABLE deletes a table and all its data permanently:

DROP TABLE employees;

Handle DROP with great care, it is irreversible and takes the data with it. A related statement, TRUNCATE TABLE, quickly removes all rows but keeps the empty table. In short: CREATE builds structure, ALTER changes it, DROP destroys it, and constraints defend the data living inside.

As tables grow to millions of rows, a query with a WHERE clause could, in the worst case, examine every row, a full table scan that is O(n) and slow. An index is a separate data structure the database maintains to find rows fast, turning many O(n) scans into roughly O(log n) lookups. It is the single most important tool for query performance.

The book-index analogy

An index in a database works like the index at the back of a textbook. Without it, finding every mention of "normalization" means reading every page. With it, you jump to an alphabetized list, find the term, and go straight to the right pages. A database index similarly keeps the values of a column in a sorted, searchable structure (commonly a B-tree) that points to the matching rows, so the engine can locate them without scanning the whole table.

Creating an index

CREATE INDEX idx_employees_dept
ON employees (department);

After this, a query like SELECT * FROM employees WHERE department = 'Sales' can use the index to jump to the matching rows instead of scanning. Primary keys and columns marked UNIQUE are indexed automatically by most databases, which is why looking a row up by its primary key is fast.

Which columns to index

Index the columns that appear often in WHERE filters, in JOIN conditions (especially foreign keys), and in ORDER BY. These are the places the database searches or sorts, so an index pays off most there. Indexing a column that is rarely searched wastes space and effort.

The trade-off: indexes are not free

An index dramatically speeds up reads, but it has costs:

• Extra storage. Each index is an additional structure that takes disk space.
• Slower writes. Every INSERT, UPDATE, or DELETE must also update every affected index, so heavy write workloads with many indexes get slower.

The art is balance: add indexes that accelerate your common queries, but do not blanket every column. A table that is written far more than it is read may want few indexes; a table that is queried constantly benefits from more. Many databases offer an EXPLAIN command that shows whether a query used an index or fell back to a full scan, which is how professionals verify their indexing choices.

Some operations must happen as an all-or-nothing unit. The classic example is a bank transfer: subtract 100 from account A and add 100 to account B. If the system crashes after the subtraction but before the addition, 100 dollars vanishes. A transaction groups several statements so they either all take effect or none do, protecting data from partial, inconsistent changes.

Controlling a transaction

You begin a transaction, run statements, then either COMMIT to make every change permanent or ROLLBACK to undo them all:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

If anything goes wrong between BEGIN and COMMIT, you issue ROLLBACK instead and the database restores the state as if the transaction never happened. Nothing partial is ever left behind.

The ACID properties

A reliable DBMS guarantees four properties for transactions, remembered by the acronym ACID:

Property	Meaning
Atomicity	All statements in the transaction succeed together, or none do. No half-finished transfers.
Consistency	A transaction moves the database from one valid state to another, never violating constraints.
Isolation	Concurrent transactions do not interfere; each behaves as if it ran alone.
Durability	Once committed, changes survive crashes and power loss; they are safely on disk.

Why isolation is subtle

Isolation is the hardest of the four because databases run many transactions at once for speed. Without care, one transaction could read another's half-finished work (a "dirty read") or see data change under its feet. Databases provide isolation levels that trade strictness for speed: stricter levels prevent more anomalies but allow less concurrency. The default in most systems prevents dirty reads while still permitting good throughput. You do not need to master every level now, only to know that isolation is what keeps concurrent users from corrupting each other's transactions.

Why this matters

ACID transactions are the reason you trust a bank, a store, or an airline to get your data right even when thousands of people act at once and hardware occasionally fails. When you wrap related changes in a transaction and let the database enforce ACID, you inherit decades of engineering that guarantees your data stays correct and complete. Together with constraints and indexes, transactions complete the picture of what makes a relational database dependable at scale.

This capstone ties the whole course together by building a tiny bookstore database from scratch: design, creation, data, and queries. Follow the reasoning and you will have exercised every major skill.

Step 1: design

The scenario: a bookstore sells books, each written by one author, and records customer orders. The entities are authors, books, customers, and orders. An author writes many books (one-to-many, so the foreign key goes on books). A customer places many orders (one-to-many, foreign key on orders). Each order is for one book here, to keep it simple. This design is already in 3NF: each fact (an author's name, a book's title, a customer's email) lives in exactly one table.

Step 2: create the tables

CREATE TABLE authors (
    author_id  INTEGER PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id    INTEGER PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    price      DECIMAL(6,2) CHECK (price >= 0),
    author_id  INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id     INTEGER,
    order_date  DATE DEFAULT '2026-01-01',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Step 3: add data

INSERT INTO authors (author_id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Toni Morrison');

INSERT INTO books (book_id, title, price, author_id) VALUES
  (10, 'A Wizard of Earthsea', 12.99, 1),
  (11, 'The Dispossessed', 14.50, 1),
  (12, 'Beloved', 13.00, 2);

INSERT INTO customers (customer_id, name, email) VALUES
  (100, 'Ren', 'ren@example.com'),
  (101, 'Sol', 'sol@example.com');

INSERT INTO orders (order_id, customer_id, book_id, order_date) VALUES
  (1000, 100, 10, '2026-02-10'),
  (1001, 100, 12, '2026-02-11'),
  (1002, 101, 11, '2026-03-01');

Step 4: ask real questions

List each order with the customer and book title (a three-table join):

SELECT o.order_id, c.name AS customer, b.title
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
INNER JOIN books AS b     ON o.book_id = b.book_id
ORDER BY o.order_id;

How many books has each author sold? (join plus grouping). Because Ren bought Le Guin's book 10 and Morrison's book 12, and Sol bought Le Guin's book 11, Le Guin has 2 sales and Morrison has 1:

SELECT a.name, COUNT(*) AS books_sold
FROM orders AS o
INNER JOIN books AS b   ON o.book_id = b.book_id
INNER JOIN authors AS a ON b.author_id = a.author_id
GROUP BY a.name
ORDER BY books_sold DESC;

Which books have never been ordered? (a subquery). All three books here were ordered, so this returns no rows, but the pattern is essential:

SELECT title
FROM books
WHERE book_id NOT IN (SELECT book_id FROM orders);

You have now designed a normalized schema, created it with typed columns and constraints, populated it, and answered layered questions with joins, aggregation, and a subquery. That is the full arc of working with a relational database, and it is exactly what the rest of your data career builds upon.

You have now written every core kind of SQL. This closing lesson adds one more genuinely useful tool, the view, and gathers the professional habits that keep your queries correct as they grow. We continue with the bookstore database from the previous lesson.

What a view is

A view is a saved query that you can treat like a table. It stores no data of its own; instead it holds the SQL text, and every time you query the view the database runs that underlying query and returns fresh results. Views let you name a complicated query once and reuse it everywhere, which keeps your code clear and consistent.

CREATE VIEW order_details AS
SELECT o.order_id, c.name AS customer, b.title, b.price
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
INNER JOIN books AS b     ON o.book_id = b.book_id;

Now the three-table join has a name. Anyone can ask a simple question against it without repeating the join:

SELECT customer, title
FROM order_details
WHERE price > 13.00
ORDER BY customer;

View versus table

	Table	View
Stores data?	Yes, rows on disk	No, just a saved query
Always current?	Reflects the last write	Recomputed from base tables each query
Main benefit	Holds the actual data	Simplifies and standardizes complex queries

Because a view reads from its base tables, when those tables change the view's results change too, automatically. Views are also useful for security: you can grant a user access to a view that exposes only certain columns or rows, without giving them the whole underlying table.

Habits that keep queries correct

The difference between a beginner and a confident practitioner is often a handful of disciplines. Adopt these:

1. Filter first, verify with SELECT. Before an UPDATE or DELETE, run the same WHERE as a SELECT to see exactly which rows you will change.
2. Qualify columns in joins. Write c.name and b.title, not bare name, so an ambiguous column never silently picks the wrong table.
3. Mind NULL. Remember that = NULL never matches; use IS NULL, and be careful with NOT IN when the subquery might return a NULL.
4. Group correctly. Every non-aggregated column in a grouped SELECT must appear in GROUP BY.
5. Wrap multi-step changes in a transaction. If two or more writes must all succeed or all fail, put them between BEGIN and COMMIT so ACID protects them.
6. Let the database enforce rules. Prefer constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) over hoping application code remembers them.

Put together, these habits and the tools from the whole course, design and normalization, SELECT and WHERE, sorting and limiting, joins, aggregation, subqueries, data changes, schema and constraints, indexes, and transactions, give you a complete, dependable command of relational databases. That toolkit is the foundation of nearly every data-driven system you will ever build.