SELECT is just a family of operations
Analytics is a great entry point: SELECT, filters, joins and aggregations quickly answer questions. But a business application must first create its tables, record facts, prevent invalid states, manage several operations and limit access.
The exact SQL depends on the DBMS, and some tasks are administrative rather than language related. The map nevertheless remains useful for understanding the extent to which a relational database participates in the system.
Create structure and ensure integrity
CREATE TABLE and ALTER TABLE describe columns, types, identifiers and relationships. NOT NULL, UNIQUE, CHECK and FOREIGN KEY place simple rules as close as possible to the data.
A constraint does not replace the ergonomic validation of the application. It constitutes a final defense common to all processes that write to the database.
CREATE TABLE produits (
id_produit INTEGER PRIMARY KEY,
designation TEXT NOT NULL,
prix NUMERIC NOT NULL CHECK (prix >= 0)
);Edit without leaving an operation incomplete
INSERT, UPDATE and DELETE alter the facts. A transaction frames several entries which must remain consistent. If the second fails, ROLLBACK cancels the whole thing; COMMIT confirms when everything is valid.
SAVEPOINT allows you to return to an intermediate step. Isolation addresses how two competing transactions view changes. These behaviors must be tested on the real engine.
BEGIN;
INSERT INTO commandes (id_commande, id_client, montant)
VALUES (108, 941, 120);
INSERT INTO paiements (id_paiement, id_commande, montant)
VALUES (77, 108, 120);
COMMIT;Reuse logic and automate with caution
A view makes a query reusable. A materialized view keeps a result to refresh. A function calculates or returns data; a procedure can orchestrate an operation depending on the engine.
A trigger automatically reacts to a INSERT, UPDATE or DELETE event. It is well suited for some audits and histories, but its logic is less visible in the calling query. For a simple rule like positive price, prefer a constraint.
Accelerate and limit access
An index can speed up a search, but consumes space and slows down writes. EXPLAIN reveals the intended plan; statistics help the planner. Optimizing means measuring a real case, not adding indexes to all columns.
The roles, GRANT and REVOKE enforce least privilege. PostgreSQL also offers line-level security. The application still needs to authenticate its users and use a properly protected connection.
CREATE ROLE reporting;
GRANT SELECT ON ventes_mensuelles TO reporting;
REVOKE UPDATE, DELETE ON commandes FROM reporting;SQL powers larger systems
Applications, APIs, Power BI and ETL/ELT pipelines connect to databases or warehouses. SQL prepares, controls and exposes data, but does not replace the interface, API, monitoring, backup or network.
PostgreSQL adds JSONB, text search and geographic extensions. These features are powerful, but they are not part of an identical SQL on all engines.
Which tool to choose for each problem?
Try this combination: negative price → constraint; order and payment together → transaction; old price to log → trigger; reusable monthly report → view; slow email search → index; commercial limited to its customers → permissions or line security.
The SQL Power page now indicates, for each family, what is really practicable in SQL.tn, only explained or still in preparation.
See how far SQL can go.
Explore SQL capabilities, their dialect and their actual availability on SQL.tn.