📚 Cheatsheet

SELECT rowid, * FROM utilisateurs;

CREATE TRIGGER log_insertion_utilisateur AFTER INSERT ON utilisateurs
BEGIN
    INSERT INTO logs (message) VALUES ('Nouvel utilisateur : ' || NEW.nom);
END;

CREATE TRIGGER empecher_suppression_admin BEFORE DELETE ON utilisateurs
WHEN OLD.role = 'admin'
BEGIN
    SELECT RAISE(ABORT, 'Impossible de supprimer un administrateur');
END;

CREATE VIEW vue_clients_actifs AS SELECT id, nom, email FROM utilisateurs WHERE actif = 1;

WITH clients_region AS (
    SELECT * FROM clients WHERE region = 'Europe'
)
SELECT nom FROM clients_region WHERE pays = 'France';

WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<10)
SELECT x FROM cnt;

SELECT nom, salaire, RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) as rang
FROM employes;

DROP TRIGGER log_insertion_utilisateur;

DROP VIEW vue_clients_actifs;

CREATE TABLE ma_table ( a TEXT, b REAL, PRIMARY KEY(a) ) WITHOUT ROWID;

SELECT * FROM vue_clients_actifs;