cchakri.aiLearn All courses
// COURSE · DATABASES

Database with SQL

A practical relational database course covering schema design, querying, transactions, indexes, and operations.

01

Relational foundations

Syllabus

  1. 01Tables, rows, and relationships
  2. 02SQL query structure
  3. 03Insert, update, delete, and constraints

Detailed content

Tables, rows, and relationships

A relational database stores data in tables whose rows represent records and whose columns have defined types. Primary keys identify rows; foreign keys enforce relationships. Constraints protect invariants even when data is written by multiple applications. Start by naming the entities and relationships in the domain.

SQL query structure

A SELECT query describes columns, source tables, filters, grouping, and ordering. SQL is declarative: it says what result is wanted, while the database chooses how to produce it. Use explicit column names, parameterized values, and bounded result sets. Understand that NULL represents unknown or missing information and changes comparison behavior.

Insert, update, delete, and constraints

INSERT creates rows, UPDATE changes matching rows, and DELETE removes them. Always inspect the filter on a mutation and use transactions for multi-step changes. NOT NULL, UNIQUE, CHECK, and foreign-key constraints reject invalid states at the database boundary, protecting correctness across all application code paths.

02

Querying and analysis

Syllabus

  1. 01Joins and aggregation
  2. 02Subqueries and common table expressions
  3. 03Window functions

Detailed content

Joins and aggregation

Joins combine related rows using matching keys. Inner joins keep matches; outer joins retain unmatched rows from one side. GROUP BY forms groups for aggregate functions such as COUNT and SUM. Check cardinality carefully: joining two one-to-many relationships can multiply rows and silently inflate totals.

Subqueries and common table expressions

A subquery embeds one query inside another, often to filter or calculate a value. A common table expression names an intermediate result so a complex query can be read in stages. Use these tools to expose intent, but inspect the execution plan when the query processes substantial data.

Window functions

Window functions calculate across related rows without collapsing them into one row per group. PARTITION BY defines the group; ORDER BY defines sequence. Ranking, running totals, and lag/lead comparisons are common uses. Specify ordering and frame behavior explicitly so results remain correct when ties or new rows appear.

03

Schema, transactions, and correctness

Syllabus

  1. 01Normalization and data modeling
  2. 02Transactions and ACID
  3. 03Isolation and concurrency anomalies

Detailed content

Normalization and data modeling

Normalization separates facts that belong to different entities, reducing duplication and update anomalies. Choose keys that remain stable, represent many-to-many relationships with a linking table, and enforce domain constraints. Denormalization can help a measured read pattern, but requires a plan for keeping duplicated values consistent.

Transactions and ACID

A transaction groups operations into one logical unit. Atomicity prevents partial completion; consistency preserves constraints; isolation controls concurrent visibility; durability protects committed work. Use the smallest transaction that preserves the invariant. Long transactions hold resources and can block other work, so keep network calls outside them.

Isolation and concurrency anomalies

Concurrent transactions can observe or overwrite each other in surprising ways. Lost updates, non-repeatable reads, and phantom rows depend on isolation level and database implementation. Choose an isolation level for the invariant at stake, and use row locks or optimistic version checks when a specific record must not be changed concurrently.

04

Indexes and database operations

Syllabus

  1. 01Indexes and query plans
  2. 02Migrations, backups, and recovery
  3. 03Performance diagnosis

Detailed content

Indexes and query plans

An index is an auxiliary structure that helps locate rows without scanning a whole table. Composite index column order matters because it determines which filters and sort orders can use it. EXPLAIN shows the planner’s chosen operations and estimates. Indexes speed some reads but consume storage and add write work.

Migrations, backups, and recovery

Schema migrations should be versioned, reviewed, and safe for the running application during deployment. Large table changes may need an expand-and-contract rollout. Backups must include the data and recovery procedure; perform restore drills to verify they work. Define recovery point and recovery time targets based on business impact.

Performance diagnosis

Measure slow queries with representative parameters and data volume. Inspect query plans, row estimates, index usage, lock waits, and connection pool saturation. Reduce unnecessary rows and columns before adding infrastructure. Optimize from evidence, then compare latency and resource use to confirm the change helped.