SQLite

• Installation sous Windows
  Comment installer SQLite sous Windows et exécuter quelques commandes.
• Créer sa première base de données
  Comment créer une base de données contenant une première table.
Voir le code de la vidéo

.tables

CREATE TABLE pays(nom TEXT, capitale TEXT, population INTEGER, superficie INTEGER, ue BOOLEAN);

.tables

INSERT INTO pays VALUES('Albanie', 'Tirana', 3020209, 28748, FALSE);

SELECT * FROM pays;

INSERT INTO pays VALUES('Allemagne', 'Berlin', 80996685, 357022, TRUE);

SELECT * FROM pays;

.help mode

.mode box

INSERT INTO pays VALUES
    ('Andorre', 'Andora la Vella', 85458, 468, FALSE),
    ('Autriche', 'Vienne', 8223062, 83871, TRUE),
    ('Belgique', 'Bruxelles', 11239755, 30528, TRUE);

SELECT * FROM pays;

• Insérer modifier ou supprimer
  Comment manipuler une base de données : afficher SELECT, ajouter INSERT, mettre à jour UPDATE ou supprimer DELETEdes informations.
Voir le code de la vidéo

.open 'C:\demo\geographie.db'

.tables

.schema pays

SELECT * FROM pays;

.mode box

CREATE TABLE pays(nom TEXT, capitale TEXT, population INTEGER, superficie INTEGER, ue BOOLEAN);

INSERT INTO pays VALUES('Albanie', 'Tirana', 3020209, 28748, FALSE);

INSERT INTO pays VALUES('Allemagne', 'Berlin', 80996685, 357022, TRUE);

INSERT INTO pays VALUES
    ('Andorre', 'Andora la Vella', 85458, 468, FALSE),
    ('Autriche', 'Vienne', 8223062, 83871, TRUE),
    ('Belgique', 'Bruxelles', 11239755, 30528, TRUE);

INSERT INTO pays VALUES('Bélarus', 'Minsk', 9608058, 207600, FALSE);

-- Echec
INSERT INTO pays VALUES('Bélarus');

-- Réussite
INSERT INTO pays (nom) VALUES('Bélarus');

DELETE FROM pays WHERE nom = 'Bélarus';

INSERT INTO pays VALUES('Bélarus', 'Minsk', 9608058, 20760, FALSE);

UPDATE pays SET superficie = 207600 WHERE nom = 'Bélarus';

SELECT * FROM pays;

UPDATE pays SET population = 11239756 WHERE nom = 'Belgique';

UPDATE pays SET population = 11239755 + 1 WHERE nom = 'Belgique';

UPDATE pays SET population = population + 1 WHERE nom = 'Belgique';

SELECT * FROM pays WHERE nom = 'Belgique';

• Différentes manières d'utiliser SELECT
  Comment sélectionner des informations avec SELECT, ceci de différentes manières : avec ou sans WHERE, avec ou sans GROUP BY et HAVING, avec des requêtes imbriquées …
Voir le code de la vidéo

.mode box

PRAGMA foreign_keys = ON;

CREATE TABLE continent(
    code CHAR(2) NOT NULL,
    nom TEXT NOT NULL,
    PRIMARY KEY(code),
    UNIQUE(nom)
);

INSERT INTO continent (code, nom) VALUES
    ('AF', 'Afrique'),
    ('EU', 'Europe'),
    ('OC', 'Océanie'),
    ('SA', 'South America'),
    ('NA', 'North America'),
    ('AN', 'Antarctica'),
    ('AS', 'Asia');

CREATE TABLE pays(
    code CHAR(3) NOT NULL,
    nom TEXT NOT NULL,
    capitale TEXT DEFAULT NULL,
    population INTEGER DEFAULT NULL,
    superficie INTEGER DEFAULT NULL,
    ue BOOLEAN DEFAULT NULL,
    code_continent CHAR(2),
    PRIMARY KEY(code),
    UNIQUE(nom),
    FOREIGN KEY(code_continent) REFERENCES continent(code)
);

INSERT INTO pays (code, nom, capitale, population, superficie, ue, code_continent) VALUES
    ('ALB', 'Albanie', 'Tirana', 3020209, 28748, FALSE, 'EU'),
    ('DEU', 'Allemagne', 'Berlin', 80996685, 357022, TRUE, 'EU'),
    ('AND', 'Andorre', 'Andora la Vella', 85458, 468, FALSE, 'EU'),
    ('AUT', 'Autriche', 'Vienne', 8223062, 83871, TRUE, 'EU'),
    ('BEL', 'Belgique', 'Bruxelles', 11239755, 30528, TRUE, 'EU'),
    ('BLR', 'Bélarus', 'Minsk', 9608058, 207600, FALSE, 'EU'),
    ('BIH', 'Bosnie-Herzégovine', 'Sarajevo', 3871643, 51197, FALSE, 'EU'),
    ('BGR', 'Bulgarie', 'Sofia', 6924716, 110879, TRUE, 'EU'),
    ('CYP', 'Chypre', 'Nicosie', 1172458, 9251, TRUE, 'EU'),
    ('HRV', 'Croatie', 'Zagreb', 4470534, 56594, TRUE, 'EU'),
    ('DZA', 'Algérie', 'Alger', 44700000, 2381741, FALSE, 'AF'),
    ('AGO', 'Angola', 'Luanda', 33086278, 1246700, FALSE, 'AF'),
    ('AUS', 'Australie', 'Canberra', 25986100, 7692024, FALSE, 'OC'),
    ('ARG', 'Argentine', 'Buenos Aires', 45605826, 2780400, FALSE, 'SA'),
    ('DNK', 'Danemark', 'Copenhague', 5569077, 43094, TRUE, 'EU'),
    ('ESP', 'Espagne', 'Madrid', 47737941, 505370, TRUE, 'EU'),
    ('EST', 'Estonie', 'Tallinn', 1257921, 45228, TRUE, 'EU'),
    ('FIN', 'Finlande', 'Helsinki', 5268799, 338145, TRUE, 'EU'),
    ('FRA', 'France', 'Paris', 66259012, 643427, TRUE, 'EU'),
    ('GRC', 'Grèce', 'Athènes', 10816286, 131957, TRUE, 'EU'),
    ('HUN', 'Hongrie', 'Budapest', 9919128, 93028, TRUE, 'EU'),
    ('IRL', 'Irlande', 'Dublin', 4832765, 70273, TRUE, 'EU'),
    ('ISL', 'Islande', 'Reykjavík', 317351, 103000, FALSE, 'EU'),
    ('ITA', 'Italie', 'Rome', 61680122, 301340, TRUE, 'EU'),
    ('KAZ', 'Kazakhstan', 'Noursoultan', 17948816, 2724900, FALSE, 'EU'),
    ('LVA', 'Lettonie', 'Riga', 2165165, 64589, TRUE, 'EU'),
    ('LIE', 'Liechtenstein', 'Vaduz', 37313, 160, FALSE, 'EU'),
    ('LTU', 'Lituanie', 'Vilnius', 2943472, 65300, TRUE, 'EU'),
    ('LUX', 'Luxembourg', 'Luxembourg', 520672, 2586, TRUE, 'EU'),
    ('MKD', 'Macédoine du Nord', 'Skopje', 2091719, 25713, FALSE, 'EU'),
    ('MLT', 'Malte', 'La Valette', 412655, 316, TRUE, 'EU'),
    ('MDA', 'Moldavie', 'Chișinău', 3583288, 33851, FALSE, 'EU'),
    ('MCO', 'Monaco', 'Monaco', 30508, 2, FALSE, 'EU'),
    ('MNE', 'Monténégro', 'Podgorica', 650036, 13812, FALSE, 'EU'),
    ('NOR', 'Norvège', 'Oslo', 5147792, 323802, FALSE, 'EU'),
    ('NLD', 'Pays-Bas', 'Amsterdam', 16877351, 41543, TRUE, 'EU'),
    ('POL', 'Pologne', 'Varsovie', 38346279, 312685, TRUE, 'EU'),
    ('PRT', 'Portugal', 'Lisbonne', 10427301, 92090, TRUE, 'EU'),
    ('ROU', 'Roumanie', 'Bucarest', 21729871, 238391, TRUE, 'EU'),
    ('GBR', 'Royaume-Uni', 'Londres', 63742977, 243610, FALSE, 'EU'),
    ('RUS', 'Russie', 'Moscou', 146267288, 17098242, FALSE, 'EU'),
    ('RSM', 'Saint-Marin', 'Saint-Marin', 32742, 61, FALSE, 'EU'),
    ('SRB', 'Serbie', 'Belgrade', 7209764, 88361, FALSE, 'EU'),
    ('SVK', 'Slovaquie', 'Bratislava', 5443583, 49035, TRUE, 'EU'),
    ('SLO', 'Slovénie', 'Ljubljana', 1988292, 20273, TRUE, 'EU'),
    ('SWE', 'Suède', 'Stockholm', 9723809, 450295, TRUE, 'EU'),
    ('CHE', 'Suisse', 'Berne', 8061516, 41277, FALSE, 'EU'),
    ('CZE', 'Tchéquie', 'Prague', 10538275, 78867, TRUE, 'EU'),
    ('TUR', 'Turquie', 'Ankara', 76667864, 783562, FALSE, 'EU'),
    ('UKR', 'Ukraine', 'Kiev', 44291413, 603550, FALSE, 'EU'),
    ('VAT', 'Vatican', 'Vatican', 842, 1, FALSE, 'EU');

SELECT * FROM pays;

SELECT COUNT(*) FROM pays;

SELECT * FROM pays LIMIT 10;

SELECT * FROM pays LIMIT 9,5;

SELECT nom FROM pays LIMIT 10;

SELECT nom, population FROM pays LIMIT 10;

SELECT nom, capitale FROM pays ORDER BY capitale LIMIT 10;

SELECT nom, capitale FROM pays ORDER BY capitale DESC LIMIT 10;

SELECT nom, capitale, code_continent FROM pays ORDER BY code_continent;

SELECT nom, capitale, code_continent FROM pays ORDER BY code_continent, capitale;

SELECT nom, capitale, code_continent FROM pays ORDER BY code_continent, capitale DESC;

SELECT COUNT(*) FROM pays;

SELECT COUNT(*) AS nb FROM pays;

-- Erreur
SELECT COUNT(*) AS Nombre de pays FROM pays;

SELECT COUNT(*) AS "Nombre de pays" FROM pays;

SELECT nom, population FROM pays WHERE population > 50000000;

SELECT nom, population, superficie FROM pays WHERE population > 50000000;

SELECT nom, population, superficie, population / superficie FROM pays WHERE population > 50000000;

SELECT nom, population, superficie, population / superficie FROM pays WHERE population > 50000000;

SELECT nom, population, superficie, iif(population /superficie > 200, "Forte", "Faible") AS densité FROM pays WHERE population > 50000000;

SELECT code || ' - ' || nom AS nom, superficie FROM pays WHERE superficie < 10000;

SELECT nom, population FROM pays WHERE population > 50000000;

SELECT nom, population FROM pays WHERE population > 50000000 AND population < 65000000;

SELECT nom, population FROM pays WHERE population BETWEEN 50000000 AND 65000000;

SELECT count(*) FROM pays;

SELECT min(population) FROM pays;

SELECT max(population) FROM pays;

SELECT min(population), max(population) FROM pays;

-- Attention
SELECT nom, min(population), max(population) FROM pays;

-- Attention
SELECT nom, max(population), min(population) FROM pays;

SELECT nom FROM pays WHERE population = (SELECT max(population) FROM pays);

SELECT nom, population FROM pays WHERE population = (SELECT max(population) FROM pays);

SELECT avg(population) FROM pays;

SELECT nom FROM pays WHERE nom LIKE 'al%';

SELECT nom FROM pays WHERE nom LIKE '%ie';

SELECT nom FROM pays WHERE nom LIKE '%é%';

SELECT nom FROM pays WHERE nom LIKE '%é%' AND nom LIKE '%e';

SELECT nom FROM pays WHERE nom LIKE '%é%e';

SELECT code_continent FROM pays;

SELECT DISTINCT code_continent FROM pays;

SELECT nom, code_continent FROM pays WHERE code_continent = 'AF';

SELECT nom, code_continent FROM pays WHERE code_continent <> 'EU';

SELECT nom, code_continent FROM pays WHERE code_continent IN ('AF', 'SA');

SELECT nom, code_continent FROM pays WHERE code_continent = 'AF' OR code_continent = 'OC';

SELECT COUNT(*) FROM pays GROUP BY code_continent;

SELECT code_continent, COUNT(*) AS nb FROM pays GROUP BY code_continent;

SELECT code_continent, COUNT(*) AS nb FROM pays GROUP BY code_continent HAVING nb >= 2;

SELECT * FROM continent;

SELECT COUNT(*) AS nb, continent.nom FROM pays INNER JOIN continent ON code_continent = continent.code GROUP BY code_continent HAVING nb >= 2;

SELECT continent.nom, COUNT(*) AS nb FROM pays INNER JOIN continent ON code_continent = continent.code GROUP BY code_continent HAVING nb >= 2 ORDER BY nb DESC;

SELECT continent.nom, COUNT(*) AS nb FROM pays INNER JOIN continent ON code_continent = continent.code GROUP BY code_continent HAVING nb >= 2 ORDER BY nb DESC LIMIT 1;

SELECT code_continent, COUNT(*) AS nb FROM pays GROUP BY code_continent HAVING nb >= 2;

SELECT code_continent, COUNT(*) AS nb FROM pays GROUP BY code_continent;

SELECT * FROM ( SELECT code_continent, COUNT(*) AS nb FROM pays GROUP BY code_continent ) WHERE nb >= 2;

• Signification de NULL
  Quelle est la signification de NULL et comment utiliser ce mot-clé du langage SQL
Voir le code de la vidéo

CREATE TABLE pays(
    code CHAR(3) PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,
    capitale TEXT DEFAULT NULL,
    population INTEGER DEFAULT NULL,
    superficie INTEGER DEFAULT NULL,
    ue BOOLEAN DEFAULT NULL
);

INSERT INTO pays VALUES
    ('DEU', 'Allemagne', 'Berlin', 80996685, 357022, TRUE),
    ('AND', 'Andorre', 'Andora la Vella', 85458, 468, FALSE),
    ('BLR', 'Bélarus', 'Minsk', 9608058, 207600, FALSE),
    ('BIH', 'Bosnie-Herzégovine', 'Sarajevo', 3871643, 51197, FALSE),
    ('CYP', 'Chypre', 'Nicosie', 1172458, 9251, TRUE),
    ('HRV', 'Croatie', 'Zagreb', 4470534, 56594, TRUE);

INSERT INTO pays (code, nom, population) VALUES
    ('BEL', 'Belgique', 11239755),
    ('BGR', 'Bulgarie', 6924716);

INSERT INTO pays (code, nom, population, superficie, ue) VALUES
    ('AUT', 'Autriche', 8223062, 83871, TRUE);

-- Echec
SELECT * FROM pays WHERE capitale = "";

-- Echec
SELECT * FROM pays WHERE capitale = NULL;

-- Réussite
SELECT * FROM pays WHERE capitale is NULL;

SELECT * FROM pays WHERE capitale is NOT NULL;

-- Echec
INSERT INTO pays (code, capitale, superficie) VALUES ('ALB', 'Tirana', 28748);

-- Réussite
INSERT INTO pays (code, nom, capitale, superficie) VALUES ('ALB', 'Albanie', 'Tirana', 28748);

-- Erroné
SELECT capitale FROM pays ORDER BY capitale;

-- Réussite
SELECT capitale FROM pays WHERE capitale is NOT NULL ORDER BY capitale;

SELECT COUNT(*) FROM pays WHERE ue is NOT NULL;

• Utiliser une clé primaire
  Comment définir une clé primaire dans une table et comprendre la contrainte d'unicité de valeur de cette clé.
Voir le code de la vidéo

CREATE TABLE pays(
    code CHAR(3) NOT NULL,
    nom TEXT NOT NULL,
    capitale TEXT DEFAULT NULL,
    population INTEGER DEFAULT NULL,
    superficie INTEGER DEFAULT NULL,
    ue BOOLEAN DEFAULT NULL,
    PRIMARY KEY(code)
);

INSERT INTO pays VALUES
    ('ALB', 'Albanie', 'Tirana', 3020209, 28748, FALSE),
    ('DEU', 'Allemagne', 'Berlin', 80996685, 357022, TRUE),
    ('AND', 'Andorre', 'Andora la Vella', 85458, 468, FALSE),
    ('AUT', 'Autriche', 'Vienne', 8223062, 83871, TRUE),
    ('BEL', 'Belgique', 'Bruxelles', 11239755, 30528, TRUE),
    ('BLR', 'Bélarus', 'Minsk', 9608058, 207600, FALSE),
    ('BIH', 'Bosnie-Herzégovine', 'Sarajevo', 3871643, 51197, FALSE),
    ('BGR', 'Bulgarie', 'Sofia', 6924716, 110879, TRUE),
    ('CYP', 'Chypre', 'Nicosie', 1172458, 9251, TRUE),
    ('HRV', 'Croatie', 'Zagreb', 4470534, 56594, TRUE);

-- Echec
INSERT INTO pays (code, nom) VALUES ('AND', 'Argentine');

-- Réussite
INSERT INTO pays (code, nom) VALUES ('ARG', 'Argentine');

-- Echec
INSERT INTO pays (nom) VALUES ('Danemark');

-- Réussite
INSERT INTO pays (code, nom) VALUES ('DNK', 'Danemark');

-- Echec
UPDATE pays SET code='BEL' WHERE code='DNK';

• Utiliser une clé étrangère
  Comment définir une clé étrangère vers une table et comprendre la contrainte d'intégrité référentielle des valeurs de cette clé étrangère.
Voir le code de la vidéo

PRAGMA foreign_keys = ON;

CREATE TABLE continent(
    code CHAR(2) NOT NULL,
    nom TEXT NOT NULL,
    PRIMARY KEY(code),
    UNIQUE(nom)
);

INSERT INTO continent (code, nom) VALUES
    ('AF', 'Afrique'),
    ('EU', 'Europe'),
    ('OC', 'Océanie'),
    ('SA', 'South America'),
    ('NA', 'North America'),
    ('AN', 'Antarctica'),
    ('AS', 'Asia');

CREATE TABLE pays(
    code CHAR(3) NOT NULL,
    nom TEXT NOT NULL,
    capitale TEXT DEFAULT NULL,
    population INTEGER DEFAULT NULL,
    superficie INTEGER DEFAULT NULL,
    ue BOOLEAN DEFAULT NULL,
    code_continent CHAR(2),
    PRIMARY KEY(code),
    UNIQUE(nom),
    FOREIGN KEY(code_continent) REFERENCES continent(code)
);

INSERT INTO pays (code, nom, capitale, population, superficie, ue, code_continent) VALUES
    ('ALB', 'Albanie', 'Tirana', 3020209, 28748, FALSE, 'EU'),
    ('DEU', 'Allemagne', 'Berlin', 80996685, 357022, TRUE, 'EU'),
    ('AND', 'Andorre', 'Andora la Vella', 85458, 468, FALSE, 'EU'),
    ('AUT', 'Autriche', 'Vienne', 8223062, 83871, TRUE, 'EU'),
    ('BEL', 'Belgique', 'Bruxelles', 11239755, 30528, TRUE, 'EU'),
    ('BLR', 'Bélarus', 'Minsk', 9608058, 207600, FALSE, 'EU'),
    ('BIH', 'Bosnie-Herzégovine', 'Sarajevo', 3871643, 51197, FALSE, 'EU'),
    ('BGR', 'Bulgarie', 'Sofia', 6924716, 110879, TRUE, 'EU'),
    ('CYP', 'Chypre', 'Nicosie', 1172458, 9251, TRUE, 'EU'),
    ('HRV', 'Croatie', 'Zagreb', 4470534, 56594, TRUE, 'EU'),
    ('DZA', 'Algérie', 'Alger', 44700000, 2381741, FALSE, 'AF'),
    ('AGO', 'Angola', 'Luanda', 33086278, 1246700, FALSE, 'AF'),
    ('AUS', 'Australie', 'Canberra', 25986100, 7692024, FALSE, 'OC'),
    ('ARG', 'Argentine', 'Buenos Aires', 45605826, 2780400, FALSE, 'AS');

-- Echec
INSERT INTO pays VALUES ('ARG', 'Argentine', 'Buenos Aires', 45605826, 2780400, FALSE, 'AM');

-- Réussite
INSERT INTO pays VALUES ('ARG', 'Argentine', 'Buenos Aires', 45605826, 2780400, FALSE, 'SA');

-- Echec
DELETE FROM continent WHERE code ='EU';

• Sélectionner des informations avec une jointure
  Comment sélectionner des informations avec une jointure interne ou comment récupérer des informations contenues dans des tables différentes
Voir le code de la vidéo

PRAGMA foreign_keys = ON;

CREATE TABLE continent(
    code CHAR(2) NOT NULL,
    nom TEXT NOT NULL,
    PRIMARY KEY(code),
    UNIQUE(nom)
);

INSERT INTO continent (code, nom) VALUES
    ('AF', 'Afrique'),
    ('EU', 'Europe'),
    ('OC', 'Océanie'),
    ('SA', 'South America'),
    ('NA', 'North America'),
    ('AN', 'Antarctica'),
    ('AS', 'Asia');

CREATE TABLE pays(
    code CHAR(3) NOT NULL,
    nom TEXT NOT NULL,
    capitale TEXT DEFAULT NULL,
    population INTEGER DEFAULT NULL,
    superficie INTEGER DEFAULT NULL,
    ue BOOLEAN DEFAULT NULL,
    code_continent CHAR(2),
    PRIMARY KEY(code),
    UNIQUE(nom),
    FOREIGN KEY(code_continent) REFERENCES continent(code)
);

INSERT INTO pays (code, nom, capitale, population, superficie, ue, code_continent) VALUES
    ('ALB', 'Albanie', 'Tirana', 3020209, 28748, FALSE, 'EU'),
    ('DEU', 'Allemagne', 'Berlin', 80996685, 357022, TRUE, 'EU'),
    ('AND', 'Andorre', 'Andora la Vella', 85458, 468, FALSE, 'EU'),
    ('AUT', 'Autriche', 'Vienne', 8223062, 83871, TRUE, 'EU'),
    ('BEL', 'Belgique', 'Bruxelles', 11239755, 30528, TRUE, 'EU'),
    ('BLR', 'Bélarus', 'Minsk', 9608058, 207600, FALSE, 'EU'),
    ('BIH', 'Bosnie-Herzégovine', 'Sarajevo', 3871643, 51197, FALSE, 'EU'),
    ('BGR', 'Bulgarie', 'Sofia', 6924716, 110879, TRUE, 'EU'),
    ('CYP', 'Chypre', 'Nicosie', 1172458, 9251, TRUE, 'EU'),
    ('HRV', 'Croatie', 'Zagreb', 4470534, 56594, TRUE, 'EU'),
    ('DZA', 'Algérie', 'Alger', 44700000, 2381741, FALSE, 'AF'),
    ('AGO', 'Angola', 'Luanda', 33086278, 1246700, FALSE, 'AF'),
    ('AUS', 'Australie', 'Canberra', 25986100, 7692024, FALSE, 'OC'),
    ('ARG', 'Argentine', 'Buenos Aires', 45605826, 2780400, FALSE, 'AS');

SELECT * FROM continent;

SELECT * FROM pays;

SELECT nom, capitale, code_continent FROM pays;

-- Echec
SELECT nom, capitale, code_continent
    FROM pays INNER JOIN continent ON code_continent = code;

-- Echec
SELECT pays.nom, capitale, code_continent
    FROM pays INNER JOIN continent ON code_continent = code;

-- Incomplet
SELECT pays.nom, capitale, continent.nom
    FROM pays INNER JOIN continent ON code_continent = continent.code;

-- Réussite
SELECT pays.nom AS Pays, capitale AS Capitale, continent.nom AS Continent
    FROM pays INNER JOIN continent ON code_continent = continent.code;