J’ai assuré pendant plusieurs années un enseignement des bases de SQL en utilisant le défunt module SQLite Manager pour Firefox. Pour cela, j’ai créé une base des communes de France et quelques documents (avec LaTeX). Le master pour lequel j’ai préparé tout cela est désormais de l’histoire ancienne et, au cas où cela pourrait servir, je place sur ce site tout mon petit bazar.
Réalisées avec beamer sous LaTeX, les deux présentations se situent à niveau élémentaire : après avoir exposé à grands traits les principes des bases de données relationnelles, on passe à SQL dont on n’étudie qu’une partie du Data Manipulation Language.
La première présentation se consacre ensuite aux manipulations concernant une seule table et s’achève avec les tris et les fonctions statistiques.
La
deuxième présentation
aborde les manipulations utilisant
plusieurs tables. On aborde donc les jointures. Les vues sont
abordées ensuite. La présentation se termine avec l’expression
CASE et le mot-clé DISTINCT.
Les sources sont fournies dans le fichier
diapo_sql_sources.zip, archive
du dossier diapo_sql_sources qui contient également
deux fichiers .sql présentant des instructions
compatibles avec SQLite dont plusieurs sont commentées. La base de
données utilisée est france.sqlite disponible dans
l’archive
france.sqlite.zip.
Enfin, le fichier diapo_sql.pdf regroupe le contenu des deux présentations précédentes.
J’ai extrait les données de fichiers disponibles sur le site de l’INSEE. J’ai créé la base, au format SQLite, grace à un script que je fournis. Je donne également un script pour PostGreSQL et un autre pour MySQL.
J’ai récupéré sur le site de l’INSEE les données concernant la
population légale de 2009 dans un fichier portant le nom évocateur
de BTX_CC_POP_2009.zip contenant un
classeur au format MS-Excel et un pdf dont le titre est Bases
de chiffres clés : documentation // Évolution et structure de la
population
. N’ayant pas gardé l’adresse exacte du document sur
le site de l’INSEE, je ne suis pas en mesure de la retrouver
aujourd’hui, d’autant que, depuis, la population légale présentée
est celle de 2012.
Je suis passé au format .ods pour traiter le fichier
avec LibreOffice Calc. J’en ai extrait les données sous forme de
fichiers .csv dont l’extension est
.data.
À la recherche d’une solution pour tirer partie de cette base en
cours — problème d’accessibilité, de compatibilités diverses, etc. —
j’ai envisagé tour à tour MySQL et PostGreSQL pour lesquels j’ai
écrit les scripts de création de la base. Sur la suggestion d’un
collègue, je me suis tourné vers SQLite et le module pour Firefox
qui résolvaient d’un coup tous les problèmes : pas besoin de créer
une base centralisée car la base est contenu dans un unique
fichier .sqlite donc pas de problème de gestion des
accès, des autorisations de manipulation, etc. ; pas
d’installation complexe car les salles étaient déjà équipées de
Firefox et qu’un module s’installe sans trop de peine, etc.
On a accès à la région dans laquelle se trouve une commune en
passant par le département et à l’arrondissement dont elle dépend
en passant par le canton. L’identifiant d’une commune ne porte pas
le même nom dans la table communes et dans la table
population. La base ne comporte pas de
NULL dans sa version 1, elle en comporte dans sa
version 2 (SQLite seulement). La base ne comporte pas de champs de
type date ou heure.
La base au format sqlite france.sqlite est
disponible
zippée ici. Elle
contient neuf tables :
dictionnaire: dictionnaire des champs apparaissant dans les autres tables ;
zeat: liste des zones d'études et d'aménagement du territoire, voir la définition sur le site de l’INSEE ;
regions: régions administratives avant réforme 2015 ;
departements: idem pour les départements ;
arrondissements: idem pour les arrondissements ;
cantons: idem pour les cantons ;
communes: idem pour les communes ;
population: donne les différents comptes de la population par commune comme présentés dans le dictionnaire ;
regadj: donne un moyen d’étudier l’adjacence des régions de France métropolitaine. Si deux régions apparaissent sur la même ligne, elles sont adjacentes, la direction donne une indication de la place de la deuxième par rapport à la première, le plan étant découpé en huit angles égaux de sommet le centre de la première région,
0 est
le nord, je crois me souvenir qu’ensuite on tourne dans le sens
des aiguilles d’une montre (sens indirect) mais c’est SGDG
;-).La version n° 2,
disponible zippée ici,
contient des NULL au lieu d’un texte vide
"" dans les champs ArtMaj et
ArtMin. Le fichier
rem_france2.txt donne la procédure suivie pour
la transformation.
Les sources sont données aziz
autrement dit telles
quelles et sans garanti aucune.
Pas grand’ chose mais il parait que c’est mieux que rien. D’abord les devoirs de ces deux dernières années (SQL et Excel) et les corrections des questions sur SQL. Puis quelques requêtes avec, parfois, quelques commentaires.