Articles

Intro to Sql

October 31, 2026
Databases Notes
EN

SQL (Structured Query Language) expresses database requests and changes declaratively. Start by reading a result set, then work through filters, joins, groups, missing values, and controlled writes. This chapter uses SQLite so the examples can be run locally; it does not claim every syntax choice wo...

Data Manipulation Language Dml

October 31, 2026
Databases Notes
EN

DML changes rows with INSERT, UPDATE, and DELETE; SELECT reads data and is sometimes classified separately as query language. This chapter treats them together because applications must reason about both the targeted rows and the outcome. A successfully executed statement may affect zero rows, which...

Aggregate Functions

October 31, 2026
Databases Notes
EN

An aggregate reduces qualifying rows to a summary, either once for the whole input or once per GROUP BY key. COUNT(*) counts rows, while aggregates such as AVG(column) generally skip NULL inputs. Before writing a report, identify its grain (one result per what?), which rows qualify, and whether miss...

Data Definition Language Ddl

October 31, 2026
Databases Notes
EN

Data Definition Language (DDL) changes database structure, not just the current rows. Schema work must protect existing data and dependent code: CREATE TABLE on an empty database is straightforward, while changing a populated table is a migration with compatibility, validation, and rollback requirem...

Transaction Control Language Tcl

October 31, 2026
Databases Notes
EN

A checkout combines stock reservation, order creation, and line items. A transaction supplies an atomic commit boundary, not a complete checkout algorithm: the caller must still check affected-row counts, handle errors, and coordinate external effects. This lesson distinguishes what COMMIT, ROLLBACK...

Triggers

October 31, 2026
Databases Notes
EN

A trigger runs automatically in response to specified writes; its effects are part of the database write, not a separate background job. This chapter makes the hidden work visible: first log a real price change, then test an unchanged value, rollback, and a rejected state transition. Prefer an ordin...

Stored Procedures and Functions

October 31, 2026
Databases Notes
EN

A stored routine gives database logic a callable interface. Use a function when a query needs a calculated value or set; use a procedure for an explicitly invoked operation. This chapter uses PostgreSQL because routine signatures, privileges, side effects, and transaction behavior are dialect-specif...

Window Functions

October 31, 2026
Databases Notes
EN

A window function calculates a value over related rows without collapsing the result into one row per group. PARTITION BY chooses the group, ORDER BY establishes the calculation order, and the frame (where applicable) controls which neighboring rows contribute. The most common errors come from ambig...

Hierarchical Data

October 31, 2026
Databases Notes
EN

A category hierarchy is a set of parent–child relationships. Adjacency lists store only the immediate parent, while recursive queries reconstruct ancestors or descendants. Later representations cache more traversal information in return for harder updates. The critical invariant is not just a valid ...

Joins Subqueries and Views

October 31, 2026
Databases Notes
EN

The bookstore keeps customers, orders, products, and lines as separate facts. This chapter shows how to combine without copying them: joins pair related rows, subqueries express dependent tests and calculations, and views name reusable queries. The central skill is keeping track of result grain—whet...

Azure Services

October 31, 2026
Databases Notes
EN

Azure provides several managed relational, NoSQL, cache, and distributed data services. The correct service depends on the data model, access pattern, transaction requirements, compatibility needs, recovery objectives, and team expertise...

Clickhouse

October 31, 2026
Databases Notes
EN

ClickHouse is a column-oriented SQL database designed for analytical workloads over large datasets. It is commonly used for...

Elasticsearch and Opensearch

October 31, 2026
Databases Notes
EN

OpenSearch and Elasticsearch are search-oriented distributed data engines built around Apache Lucene concepts such as inverted indexes, analyzers, mappings, shards, and replicas...

Distributed Sql

October 31, 2026
Databases Notes
EN

Distributed SQL databases keep a relational SQL model while distributing data and replication across multiple nodes...

Cassandra

October 31, 2026
Databases Notes
EN

Apache Cassandra is a distributed wide-column database designed for high write throughput, horizontal scale, multi-node availability, and predictable key-oriented access...

Choosing Database

October 31, 2026
Databases Notes
EN

Choosing the right database can significantly influence your project’s reliability, performance, cost, and ability to scale. A good database choice is not only about picking a popular engine; it is about matching the database to your data shape, query patterns, consistency needs, latency requirement...

Sql Server

October 31, 2026
Databases Notes
EN

Microsoft SQL Server is a relational database engine widely used in enterprise applications, business systems, reporting, and Microsoft-centric environments. It supports transactional workloads, analytical features, mature administration tooling, and deep integration with the .NET and Azure ecosyste...

Aws Services

October 31, 2026
Databases Notes
EN

Choose a service by data model, access pattern, transaction requirements, and operational constraints. A keyword such as “financial,” “serverless,” or “high scale” is a clue, not a complete architecture decision. Check service availability and compatibility before designing a new deployment...

Gcp Services

October 31, 2026
Databases Notes
EN

Match the workload to a data model and consistency requirement before selecting a managed product. Relational transactions, large analytical scans, document queries, and cached state have different needs. Managed operations reduce some administrative work but do not remove schema design, capacity pl...

Redis

October 31, 2026
Databases Notes
EN

Redis is an in-memory data store built around specialized data structures such as strings, hashes, lists, sets, sorted sets, streams, JSON, geospatial indexes, probabilistic structures, and newer vector-oriented capabilities...

Data Integrity

October 31, 2026
Databases Notes
EN

Data integrity means the stored data satisfies the rules the system relies on. A constraint is a rule declared in a table's definition so the database can check it whenever relevant data changes...

Indexing Strategies

October 31, 2026
Databases Notes
EN

An index is an additional structure that helps the database find selected rows. It is comparable to a book's index: look up a topic, find its location, and then read the relevant pages. The database still has to retrieve and return the answer...

Denormalization

October 31, 2026
Databases Notes
EN

Normalization separates facts so each has a clear home. Denormalization deliberately stores an additional copy or precomputed result to make a particular read cheaper. It creates a new responsibility: keep that copy correct, or explicitly allow it to lag...

Requirements Analysis

October 31, 2026
Databases Notes
EN

Before choosing tables, find out what the application must do. A requirement is a statement about a needed behavior or rule. “Use PostgreSQL” is a technology choice; “an order must retain the price originally charged” is a requirement that the design must support...

Distributed Database Systems

October 31, 2026
Databases Notes
EN

A distributed database places related data and computation across independently failing nodes while exposing a usable database interface. Distribution is not a single feature: placement, replication, transaction coordination, and failure handling make different promises...

Database Hardening and Patch Management

October 31, 2026
Databases Notes
EN

Hardening reduces the number of ways a database can be attacked or accidentally misused...

Capacity Planning

October 31, 2026
Databases Notes
EN

Capacity planning is the strategic process of determining the necessary resources required to meet current and future demands of an application or system. It involves analyzing workloads, forecasting growth, and ensuring that the infrastructure can handle anticipated loads while maintaining optimal ...

Backup and Recovery Strategies

October 31, 2026
Databases Notes
EN

A backup is useful only if it can restore the required data within the required time. Choose backup types and schedules against recovery point and recovery time objectives, then test restoration. Production impact matters, but a fast backup that cannot be recovered does not meet the goal...

Crash Recovery in Databases

October 31, 2026
Databases Notes
EN

Crash recovery is an important component of database systems that ensures data consistency and durability despite unexpected events like power outages, hardware failures, or software crashes. By design, databases must be capable of returning to a reliable state after a failure occurs. This is largel...

Auditing Compliance and Data Governance

October 31, 2026
Databases Notes
EN

A secure database should answer...

Incident Response and Disaster Recovery Drills

October 31, 2026
Databases Notes
EN

A security or reliability incident is the wrong time to discover that...

Performance Monitoring and Tuning

October 31, 2026
Databases Notes
EN

Performance tuning should start with a measured problem...

Encryption Secrets and Key Management

October 31, 2026
Databases Notes
EN

Database security depends on protecting data in several different states...

Database Security

October 31, 2026
Databases Notes
EN

Database security encompasses a comprehensive set of measures designed to protect database management systems against threats that could compromise their confidentiality, integrity, and availability. As databases often store sensitive and critical information, safeguarding them is important for prot...

Identity Authentication and Access Control

October 31, 2026
Databases Notes
EN

Database security starts with answering three different questions...