Fundamentals and SQL

What it is: A system for organizing data in tables with rows and columns, created by Edgar Codd. Each table represents a real-world entity, and relationships between data are made explicit.

Why learn: The foundation of all modern SQL databases. Understanding the relational model is a prerequisite for any database work.

Key concepts: Table (relation), Row (tuple/record), Column (attribute), Domain (data type)

What it is: A PK uniquely identifies each record. An FK connects tables, establishing relationships between entities.

Why learn: Without keys, there's no integrity. FK prevents orphaned data and ensures relationships between tables are valid.

Key concepts: PRIMARY KEY, FOREIGN KEY, REFERENCES, ON DELETE CASCADE, referential integrity

What it is: Rules the database applies to ensure data validity (UNIQUE, NOT NULL, CHECK). These are declarative constraints in the schema.

Why learn: Invalid data is costly. Database validation is the last line of defense against inconsistencies.

Key concepts: UNIQUE, NOT NULL, CHECK, DEFAULT, layered validation

What it is: The process of reorganizing tables to reduce duplication and dependencies. It follows progressive normal forms.

Why learn: Unnormalized tables cause insertion, update, and deletion anomalies. Normalization eliminates redundancy.

Key concepts: 1NF (atomicity), 2NF (partial dependency), 3NF (transitive dependency)

What it is: Entity-Relationship Diagram that models the structure before creating tables. Visually represents entities and their connections.

Why learn: Building without modeling is like building without a blueprint. It prevents rework and ensures the schema meets the requirements.

Key concepts: Entity, Attribute, Relationship, Cardinality (1:1, 1:N, N:N)

What it is: Criteria for deciding whether a relational database is the best option for your use case. Not every problem is a nail.

Why learn: Not every problem needs SQL. Knowing when to use it (and when not to) helps avoid architecture problems.

Key concepts: OLTP, structured data, strong integrity, when NOT to use a relational database

What it is: DDL command to define tables with columns, types, and constraints. The starting point for any database.

Why learn: Everything starts with the table. Getting the structure wrong affects the entire system from the start.

Key concepts: CREATE TABLE, types (TEXT, INT, NUMERIC, SERIAL), PRIMARY KEY, REFERENCES

What it is: DML commands to insert new records and update existing ones. The foundation of CRUD.

Why learn: Basic CRUD. Every system needs to create and modify data safely and efficiently.

Key concepts: INSERT INTO...VALUES, UPDATE...SET...WHERE, RETURNING, upsert

What it is: Command to read data with filters, sorting, and a limit. The most commonly used SQL command.

Why learn: 80% of database work is reading. Mastering SELECT is essential for any professional.

Key concepts: SELECT, WHERE, ORDER BY, LIMIT, LIKE, IN, BETWEEN, IS NULL

What it is: An operation that combines data from two or more tables using keys. Reconstructs normalized information.

Why learn: Normalized data lives in separate tables. JOIN reconstructs the complete information when you need it.

Key concepts: INNER JOIN, LEFT JOIN, RIGHT JOIN, ON, table alias

What it is: Groups records and applies functions (COUNT, SUM, AVG, MAX, MIN). Turns raw data into information.

Why learn: Reports and dashboards depend on aggregations. This is the path from raw data to decisions.

Key concepts: GROUP BY, HAVING, COUNT, SUM, AVG, window functions (intro)

What it is: Command to safely remove records. Requires extreme care in production.

Why learn: DELETE without WHERE is a disaster. Learning safe patterns is essential for any DBA or developer.

Key concepts: DELETE FROM...WHERE, soft delete, TRUNCATE, backup before deleting