Database
The organised collection of data and its rules.
From business problem to technical capability
SQL does more than read data.
Discover how a relational database can structure, control, change, automate, secure and speed up an organisation's information.
Practice availableExplanation availableLaboratory in preparation
Four different concepts
Language, data and software are not synonyms.
DBMS
The software that stores, controls and executes operations.
Server
The environment that runs a database service for its clients.
SQL
A language used by several DBMSs, with a shared foundation and dialects.
PostgreSQL, MySQL and SQL Server are DBMSs. They use SQL, but their types, functions, procedures, administration tools and some syntax differ.
12 families
Start with the problem, then choose the tool.
Explanation availableBeginner
Model
- Business problem
- Customers, orders and payments are mixed together or repeatedly copied.
- Simple explanation
- Tables, types, schemas, keys and relationships give every fact a place and meaning.
- Example
- An order stores id_client instead of copying the customer's name and address.
- Dialect
- Standard SQL; detailed types vary by DBMS
Practice availableBeginner to advanced
Query
- Business problem
- A team needs a reproducible answer rather than a manually calculated total.
- Simple explanation
- SELECT, filters, aggregates, joins, window functions and CTEs turn a question into a result.
- Example
- Calculate revenue by city and rank the highest result.
- Dialect
- Broad standard SQL foundation
Explanation availableBeginner / intermediate
Change
- Business problem
- An application must record a sale, correct an address or remove a draft.
- Simple explanation
- INSERT adds, UPDATE changes and DELETE removes; UPSERT or MERGE handles conflicts depending on the system.
- Example
- Update one identified customer's phone without changing other rows.
- Dialect
- Common INSERT/UPDATE/DELETE; dialect-specific UPSERT/MERGE
Explanation availableBeginner / intermediate
Guarantee integrity
- Business problem
- A negative price, duplicate email or orphaned order enters the database.
- Simple explanation
- Types, NOT NULL, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY reject invalid states in the right place.
- Example
- CHECK (prix >= 0) rejects a negative price before a report discovers it.
- Dialect
- Standard SQL with nuances
Laboratory in preparationIntermediate
Manage transactions
- Business problem
- An order is created but payment fails, leaving incomplete data.
- Simple explanation
- A transaction groups changes; COMMIT confirms, ROLLBACK cancels and SAVEPOINT enables partial recovery.
- Example
- Record the order and payment together, or neither of them.
- Dialect
- SQL foundation; isolation and concurrency depend on the DBMS
Explanation availableIntermediate
Create views
- Business problem
- Three reports define the same monthly revenue differently.
- Simple explanation
- A view names a reusable query; a materialized view stores a result that must be refreshed.
- Example
- Expose ventes_mensuelles to several tools without copying the query.
- Dialect
- Views are widely shared; materialization depends on the system
Laboratory in preparationIntermediate / advanced
Automate with functions, procedures and triggers
- Business problem
- Every price change must leave a trace, regardless of the calling process.
- Simple explanation
- A function encapsulates a calculation, a procedure runs an operation and a trigger reacts to a table event.
- Example
- After a price UPDATE, store OLD.prix and NEW.prix in a history table.
- Dialect
- Highly DBMS-dependent; future examples use PostgreSQL
Explanation availableIntermediate
Optimise with indexes and EXPLAIN
- Business problem
- Finding an account by email slows down as the table grows.
- Simple explanation
- EXPLAIN shows the chosen plan; an index can speed up reads while adding storage and write cost.
- Example
- Compare the plan before and after an index on lower(email).
- Dialect
- Shared principles; DBMS-specific plans
Explanation availableIntermediate
Secure with roles and permissions
- Business problem
- A reporting tool should read selected views without changing source tables.
- Simple explanation
- Users, roles, GRANT, REVOKE and sometimes row-level security enforce least privilege.
- Example
- Grant SELECT on a view to reporting and deny UPDATE on the source table.
- Dialect
- SQL + administration; RLS depends on the DBMS
Explanation availableIntermediate / advanced
Work with JSON, text or geography
- Business problem
- Some data is semi-structured, textual or spatial.
- Simple explanation
- JSON/JSONB, full-text search, geographic types and extensions broaden uses when supported.
- Example
- Store variable attributes in JSONB while indexing frequently searched keys.
- Dialect
- Strongly dependent on system and extensions
Explanation availableAdvanced
Administer, back up and replicate
- Business problem
- The service must be recoverable, monitored and available despite a failure.
- Simple explanation
- Backup, restore, replication, monitoring and high availability mainly belong to DBMS and infrastructure administration.
- Example
- Test a restore from a dump instead of assuming a backup works.
- Dialect
- DBMS-specific tools and procedures
Explanation availableBeginner to advanced
Feed applications and BI tools
- Business problem
- An application, an API and Power BI need consistent definitions.
- Simple explanation
- SQL selects, controls and prepares data; connectors, APIs and ETL/ELT pipelines move or expose it.
- Example
- Prepare an order-grain view for Power BI without duplicating amounts.
- Dialect
- SQL within a wider architecture
Choose the simplest tool
When should you not use a trigger?
A constraint is enough
To reject a negative price, CHECK is more visible and direct than triggered logic.
The logic would become hidden
A trigger runs without appearing in the calling query. It can surprise users and make debugging harder.
The volume is high
An action per row can slow down a large update. Measure the cost on the real case.
Recursion is possible
A trigger that changes the same table can call itself. Explicit design and safeguards are required.
Suitable cases: auditing, history and selected automations strictly tied to a data event, after comparison with a constraint or application logic.
Starting from zero?
Begin with a no-code situation, then run your first query in under 45 minutes.