<jupyter_start><jupyter_text>Le but de ce TP est de découvrir le langage SQL sur des données réelles simples. Nous utiliserons des données issues de la base ouverte[opendata.lillemetropole.fr/](https://opendata.lillemetropole.fr/),plus précisément les données sur la population des communes du Nord en2012 [téléchargeables ici](https://opendata.lillemetropole.fr/explore/dataset/evolution-et-structure-de-la-population-france-2012-categories-socio-professionn/information/).Les données ont été récupérées et organisées en deux tables pour simplifier leur manipulation :* la table *ville*  - composée de cinq attributs de type TEXT appelés `code`, `region`,  `departement`, `nom`, `coordonnees`.   - l'attribut `code` est la clé primaire. *  la table *evolution*   - composée de quatre attributs appelés `code`, `categorie`,	 `genre`, `effectif`.   -  les attributs `code`, `categorie`, `sexe` sont de type TEXT et	  `effectif` est de type INTEGER.   - l'attribut `code` est une clé étrangère référençant l'attribut    `code` de la table *ville*.   Préparation Récupérez le fichier[categories-socio-nord.db](./categories-socio-nord.db) et ouvrez-leà l'aide de l'application[DB Browser for SQLite - sqlitebrowser.org/](https://sqlitebrowser.org/). Cette application est un client du SGBD (Système de gestion de basesde données) SQLite qui permet de manipuler les tables et leur contenude façon simplifiée et graphique.  Vous devez le télécharger et l'installer sur votre poste. Consultation des donnéesLe DB Browser permet de consulter les données présentes dans chaque table,à l'aide de l'onglet "Parcourir les données".* Les attributs, aussi appelés champs, sont représentés en colonnes.* Les enregistrements, aussi appelés entrées, sont représentés en lignes.Vous pouvez noter le nombre d'enregistrements des deux tables, cela vous aidera à mieux comprendre ce qu'il se passe lors de l'exécution de certaines requêtes. Extraction de donnéesLe DB Browser permet d'exécuter des requêtes SQL, à l'aide de l'onglet"Exécuter le SQL".* `SELECT` : en SQL, permet de sélectionner tout ou partie des données d'une ou plusieurs tables de la base. Testez les requêtes suivantes, en essayant de deviner à l'avance ce quela requête va afficher et le nombre de lignes qui seront affichées.Certaines requêtes génèrent des erreurs, c'est voulu.Si vous copiez-collez plusieurs requêtes à la suite,seule l'exécution de la dernière sera visible. Vous pouvez exécuter lesrequêtes une par une en mettant les autres en commentaire en ajoutant`--` devant celles-ci. Vous pouvez aussi surligner la requête que voussouhaitez exécuter et cliquer sur le bouton pour exécuter le code.     Extraction de données d'une seule table```sql    SELECT * FROM ville;SELECT nom, code FROM ville;```→ On peut renommer les colonnes pour un affichage plus explicite```sqlSELECT nom AS LIBELLE, code FROM ville;```→ `DISTINCT` élimine les doublons```sqlSELECT code FROM evolution;SELECT DISTINCT code FROM evolution;```→ `ORDER BY` ordonne les résultats```sql    SELECT DISTINCT code FROM evolution ORDER BY code DESC;```→ La clause `WHERE` permet de préciser un critère pour filtrer les données```sqlSELECT * FROM ville WHERE code='59140';SELECT nom, code FROM ville WHERE code='67340';SELECT nom, code FROM ville WHERE code='59140' OR code='59260';SELECT * FROM evolution WHERE code='59140';```→ On peut récupérer le `MIN`, le `MAX`, le nombre de lignes avec`COUNT` ou la moyenne d'un attribut numérique avec `AVG` (_average_,moyenne en anglais)```sqlSELECT MIN(nom) AS MIN FROM ville;SELECT COUNT(*) AS NbLignes FROM ville;SELECT MAX(effectif) AS MAX, MIN(effectif) AS MIN, SUM(effectif) AS TOTAL, AVG(effectif) AS MOYENNE FROM evolution;```  C'est à vous !Construisez les requêtes permettant d'afficher les données qui répondentaux questions suivantes.1. Quels sont les différents libellés des catégories socioprofessionnelles par ordre alphabétique croissant ?2. Combien de catégories différentes sont utilisées dans ce jeu de données ?3. Quel est le code INSEE de Caullery ?4. Affichez toutes les informations de la table évolution pour la ville de Caullery (en utilisant le code INSEE), triées par effectifs croissants.5. Quels sont les codes postaux des villes ayant des catégories socioprofessionnelles dont les effectifs dépassent 2000 individus ?6. Combien y a-t-il de femmes agricultrices dans le Nord ?7. Quel est le nombre moyen d'employés par commune dans le Nord ?<jupyter_code>%%sql

SELECT DISTINCT categorie
FROM evolution
ORDER BY categorie ASC;
%%sql

SELECT COUNT(DISTINCT categorie)
FROM evolution
%%sql

SELECT code
FROM ville
WHERE nom='Caullery';
%%sql

SELECT * 
FROM
evolution
WHERE code = (SELECT code
FROM ville
WHERE nom='Caullery')
ORDER BY  effectif ASC;
%%sql

SELECT DISTINCT code 
FROM
evolution
WHERE
effectif > 2000;
%%sql

SELECT
SUM(effectif)
FROM
evolution
WHERE genre = 'Femmes' AND categorie LIKE '%Agriculteurs%';
%%sql

SELECT ROUND(AVG(effectif),1)
FROM evolution
WHERE categorie LIKE '%Employés%'<jupyter_output>Done.<jupyter_text>Extraction croisée de données de plusieurs tablesObservez les lignes, le nombre de lignes et le nombre de colonnesdes résultats des requêtes montrées en exemple.Essayez d'en déduire le croisement qui a été fait.→ Produit cartésien```sqlSELECT * FROM ville, evolution;SELECT * FROM ville, evolution WHERE nom='Caullery';SELECT code, nom    FROM ville, evolution    WHERE nom='Caullery';```→ Attributs qui ont le même nom dans des tables différentesLorsque des attributs ont le même nom dans des tables différentes,il est nécessaire de préciser le nom de la table dont viennent lesdonnées que l'on veut afficher.```sqlSELECT ville.code AS code1, evolution.code AS code2, nom    FROM ville, evolution    WHERE nom='Caullery';```→ On peut renommer les tables```sqlSELECT nom, v.code AS code1, effectif    FROM ville AS v, evolution AS e    WHERE nom='Caullery';```→ Si on renomme une table, seul le nouveau nom existe dans la requête```sqlSELECT nom, ville.code AS code1, e.code AS code2, effectif    FROM ville AS v, evolution AS e    WHERE nom='Caullery';```→ Utilisation de la jointure entre tables : `JOIN````sqlSELECT v.code, nom AS ville, categorie, genre, effectif    FROM ville AS v JOIN evolution AS e ON v.code=e.code    WHERE nom='Caullery';```Attention ! Il faut préciser sur quelles clés la jointure doit êtrefaite.→ Un exemple de sous-requête, pas explicitement au programme```sqlSELECT v.code AS code, nom, categorie, genre, effectif    FROM ville AS v JOIN evolution AS e ON v.code=e.code    WHERE  effectif=(SELECT max(effectif) FROM evolution);```<jupyter_code>%%sql

--requête qui n'a aucun sens, on associe toutes les lignes de ville à toutes les lignes d'evolution

SELECT * FROM ville, evolution WHERE nom='Caullery';<jupyter_output>Done.<jupyter_text>C'est à vous!Construisez les requêtes permettant d'afficher les données qui répondentaux questions suivantes.1. Affichez les catégories socioprofessionnelles, genres et effectifspour la ville de Caullery, sans utiliser le code INSEE, triées par effectifs croissants.2. Affichez les noms des villes, ainsi que les catégories et genresdes données ayant des effectifs à 0, triés par catégories.3. Quelles sont les noms des villes ayant des catégories socioprofessionnelles dont les effectifs dépassent 2000 individus ?4. Ordonnez le résultat par effectifs décroissants. 5. Même question en supprimant la ville de Lille des résultats   possibles.<jupyter_code>%%sql

SELECT categorie, genre, effectif
FROM ville JOIN evolution
ON ville.code=evolution.code
WHERE nom='Caullery'
ORDER BY effectif ASC;
%%sql

SELECT categorie, genre, nom, effectif
FROM ville JOIN evolution
ON ville.code=evolution.code
WHERE effectif=0
ORDER BY categorie ASC;
%%sql
SELECT DISTINCT nom 
FROM
(
SELECT nom, categorie, SUM(effectif)
FROM ville JOIN evolution
ON ville.code=evolution.code
GROUP BY nom, categorie
HAVING SUM(effectif) > 2000
)
;
%%sql
SELECT nom, categorie, SUM(effectif)
FROM ville JOIN evolution
ON ville.code=evolution.code
GROUP BY nom, categorie
HAVING SUM(effectif) > 2000
ORDER BY effectif DESC
;
%%sql
SELECT nom, categorie, SUM(effectif)
FROM ville JOIN evolution
ON ville.code=evolution.code
WHERE nom != 'Lille'
GROUP BY nom, categorie
HAVING SUM(effectif) > 2000
ORDER BY effectif DESC
;
%%sql

SELECT nom
	FROM ville AS v JOIN evolution AS e ON v.code=e.code
	WHERE effectif > 2000 AND nom != "Lille"
	ORDER BY effectif DESC;<jupyter_output>Done.
{'fraction_non_alphanumeric': 0.07484038779853393, 'fraction_numerical': 0.009576732087964057, 'mean_word_length': 5.2985852568875655, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 15, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '21755', 'license': 'CC0-1.0', 'n_tokens_mistral': 2769, 'n_tokens_neox': 2660, 'n_words': 1094, 'path': 'bloc4/BDD_Introduction/sandbox/TP-Lille-Villes/Exo1-Lille-BDD.ipynb', 'repo_name': 'frederic-junier/DIU-Junier', 'size': '8458'}