1. Meet SQL and relational databases
BeginnerUnderstand what databases store, how relational tables are organized, and how SQL statements work.
Databases and database systems
A database stores organized data so applications can retrieve and update it reliably.
Tables, rows, and columns
A relational table organizes records into rows and attributes into columns.
SQL statement families
SQL includes commands for defining structures, changing data, querying data, and managing access or transactions.
Module checkpoint: Sketch a small relational database for a library. Name two tables, list several columns for each, and identify a value that could uniquely identify each row.
2. Design tables and choose data types
BeginnerCreate table structures and use data types, primary keys, foreign keys, and constraints to protect data quality.
Create and change tables
CREATE TABLE defines a table, while ALTER TABLE changes its structure.
Common SQL data types
A column's data type controls which kinds of values it can store.
Keys and integrity constraints
Keys identify rows and connect tables; constraints enforce rules on stored data.
Module checkpoint: Create customers and orders tables. Give each a primary key, connect orders to customers with a foreign key, and add at least one NOT NULL and one CHECK constraint.
3. Retrieve data with SELECT
BeginnerRead records from one or more columns and shape result sets with aliases, expressions, and DISTINCT.
Select columns and rows
SELECT retrieves data from a table or other queryable source.
Column aliases and expressions
Aliases give result columns readable names, and expressions can calculate values in a query.
Remove duplicate results with DISTINCT
DISTINCT removes duplicate result rows from the selected columns.
Module checkpoint: Write three SELECT queries against a products table: one selecting named columns, one creating a calculated expression with an alias, and one listing distinct categories.
4. Filter records with WHERE
BeginnerControl which rows appear by applying comparisons, logical operators, ranges, patterns, and membership tests.
Comparison operators and WHERE
WHERE keeps only rows for which its condition evaluates to true.
Combine conditions with AND, OR, and NOT
Logical operators combine predicates to express more specific filters.
IN, BETWEEN, LIKE, and NULL
SQL predicates provide concise ways to test membership, ranges, patterns, and missing values.
Module checkpoint: Create filters for products in two selected categories, products within a price range, names beginning with a chosen letter, and rows where an optional column is NULL.
5. Sort, paginate, and handle NULL values
BeginnerOrder query results predictably and return manageable subsets for reports and application screens.
Sort results with ORDER BY
ORDER BY controls the order of rows returned by a query.
Limit results and paginate
Pagination divides a large ordered result into smaller sections.
Sort NULL values and use safe result ordering
NULL sort placement and ordering expressions can vary across database systems.
Module checkpoint: Build a product listing query sorted by price and name. Return a page of 10 records and ensure the ordering includes a unique tie-breaker.
6. Summarize data with aggregate functions
IntermediateCalculate counts, totals, averages, minimums, and maximums, then group and filter summarized results.
COUNT, SUM, AVG, MIN, and MAX
Aggregate functions calculate one summary value from a set of rows.
Group rows with GROUP BY
GROUP BY forms groups of rows that share the same grouping values.
Filter groups with HAVING
HAVING filters groups after grouping and aggregate calculations.
Module checkpoint: Report each customer's completed order count and total spending. Use WHERE for completed orders and HAVING to keep customers whose total spending exceeds a chosen threshold.
7. Combine data with JOINs
IntermediateRetrieve related information from multiple tables using inner, outer, cross, and self joins.
INNER JOIN related tables
INNER JOIN returns rows where the join condition matches in both inputs.
LEFT JOIN and unmatched records
LEFT JOIN preserves every row from the left input and supplies NULLs for unmatched right-side columns.
RIGHT, FULL, CROSS, and self joins
Different join types express different matching and row-preservation requirements.
Module checkpoint: Create a report that lists every customer and their orders, including customers with no orders. Then create a second report that lists only customers with matching orders.
8. Use subqueries and set operations
IntermediateBuild queries inside queries and combine compatible result sets to express more complex data requirements.
Scalar and multi-row subqueries
A subquery is a SELECT statement nested inside another SQL statement.
EXISTS and correlated subqueries
EXISTS tests whether a subquery returns at least one row.
UNION and other set operations
Set operations combine compatible result sets vertically.
Module checkpoint: Find customers who have at least one order, customers who have no orders, and email addresses appearing in either of two source tables. Use EXISTS, NOT EXISTS, and UNION.
9. Insert, update, and delete data
IntermediateChange table contents safely using DML statements, multi-row operations, and dialect-aware upsert patterns.
Insert rows with INSERT
INSERT adds new records to a table.
Update existing rows
UPDATE changes values in rows that satisfy its WHERE condition.
Delete rows and understand upserts
DELETE removes rows, while an upsert inserts or updates according to a conflict rule.
Module checkpoint: Practice inserting several products, updating one selected product, and deleting one test row. Before each destructive statement, write a SELECT with the same WHERE condition to verify its target.
10. Manage transactions and data consistency
IntermediateUse transaction boundaries to group related changes and understand isolation, locking, and failure recovery.
Understand ACID properties
ACID describes important properties expected from reliable database transactions.
Commit and roll back changes
COMMIT makes a transaction's changes permanent, while ROLLBACK cancels uncommitted changes.
Isolation levels and concurrent changes
Isolation settings and locks help coordinate transactions that run at the same time.
Module checkpoint: Write a transaction for a bank transfer between two accounts. Check that the source account has sufficient funds, update both balances, and commit only when every step succeeds.
11. Use CTEs, views, and window functions
AdvancedStructure complex SQL with common table expressions, reusable views, and calculations across related rows.
Common table expressions
A CTE gives a named temporary result that can be referenced by the main statement.
Create and use views
A view stores a query definition that can be queried like a table.
Calculate values with window functions
Window functions calculate over related rows without collapsing them into one row per group.
Module checkpoint: Create a CTE for customer spending totals, expose a reusable active-customer view, and rank each customer's orders from newest to oldest with ROW_NUMBER.
12. Improve database design and query performance
AdvancedApply normalization principles, understand indexes and query plans, and diagnose inefficient queries.
Normalize relational data
Normalization organizes data to reduce unnecessary duplication and update anomalies.
Indexes and access paths
An index can help the database find rows without scanning every row in a table.
Inspect query plans and tune queries
A query plan shows how the database intends to access and combine data.
Module checkpoint: Choose two frequently used queries in your sample application. Identify their filters, joins, and sort order, propose suitable indexes, and compare query plans before and after.
13. Use SQL safely in real applications
AdvancedApply least-privilege access, parameterized queries, backup awareness, and reliable database integration practices.
Users, roles, and permissions
Database permissions control which operations a user or role can perform.
Prevent SQL injection
Parameterized queries keep user-supplied values separate from SQL command text.
Migrations, backups, and database testing
Reliable database work includes controlled schema changes, recoverable backups, and repeatable tests.
Module checkpoint: Design a secure database access plan for a small application. Define separate application and reporting roles, parameterize user-driven queries, describe a migration, and document how a backup restore would be tested.