L'extraction de données - Sous-requêtes et Fusions

Villes++.

A partir de maintenant nous allons utiliser une version plus complète de la base de données sur les villes , elle ajoute les chefs-lieu des régions et des départements ainsi que les codes postaux des villes.

Attardons nous justement sur les codes postaux, comme une ville peut avoir plusieurs code-postaux on ne peut pas ajouter une colonne code postal à la table ville, et comme un code postal peut aller dans différentes villes on ne peut pas non plus ajouter une colonne ville à une table code-postal, il faut créer une table qui va faire la liaison entre les deux.

Diagramme de la table Diagramme de la table

Sous requêtes.

Pour l'instant nous avons travaillé sur une table, par exemple, nous ne savons pas afficher les villes du Bas Rhin sans savoir que l'id du département est 68 (encore une fois mais c'est la dernière, l'id du département n'est pas le numéro de département). Un moyen d'y arriver est d'utiliser une sous-requête.

Exemple

On veut connaître les villes du département 67


		
donne

		

		
		

		
SELECT id_departement FROM Departements Where numero_departement = '67' est effectué en premier et donne une vue qui sera utilisée comme une table. Ici comme la vue donne un résultat (68) unique on peut mettre = à la place de in. Remarquez que le numéro est '67' et pas 67 (il faut tenir compte des départements Corse et le code ne peut pas pas être de type entier).

		
		


		
Exemple
		
On veut connaître les villes de la région Grand EST.

		

		
donne

		

		
		

		
Ici on part de Grand Est (rangé dans Regions.nom_region) et on veut aller dans le nom des villes (Villes.nom_villes), il faut bien regarder le schéma et les tables pour passer de la table Regions à la table Villes ,on peut passer directement par le chef-lieu (mais dans ce cas on se retrouve bloqué car on ne peut savoir simplement si deux villes sont dans la même région) ou passer avant par la table Departements. Le but est de trouver les clefs primaires qui permettent de passer.

		
Les étapes :

		
    
		
  1. On trouve le numéro de région qui correspond au nom "Grand Est".
    2. 
		
  2. On trouve les numéros de départements qui ont un numéro de région égal au numéro précédent.
    3. 
		
  3. On trouve les nom des villes qui ont un numéro de département dans l'ensemble précédent.
    4. 
		

		

		

		
		
Exercice
		
    
		
  1. Donner le nom du chef lieu de l’Occitanie.
    2. 
		
  2. En partant de "Wissembourg" le but est de trouver le nom de sa région.
		
    (Aide 
		
      
			
    1. Trouver le numéro de département associé à "Wissembourg".
      2. 
			
    2. Trouver le numéro de région associé au numéro de département précédent.
      3. 
			
    3. Trouver le nom de région associé au numéro de région précédent.
      4. 
			
    4. Regrouper les requêtes en une.
      5. 
		
    
		)
    
		
    3. 
		
  3. On veut trouver les codes postaux de Strasbourg.
		
      
		
    1. En partant de Strasbourg quelles sont les clefs externes que l'on doit trouver pour arriver aux codes Postaux ? (nom_ville ->  .....  -> ..... -> nom_code_postal).
      2. 
		
    2. Afficher les codes postaux de Strasbourg.
      3. 
		
    
		
    4. 
		
  4. Trouver la ville qui a le code postal 35170.
    5. 
		
  5. Aficher les départements de la région Grand Est.
    6. 
		
  6. Afficher les villes qui ont un code postal qui commence par 671.
    7. 
		
  7. Donner le nombre de villes dans le Bas Rhin.
    8. 
		
		

		

		
		
Fusion

		
On peut associer plusieurs tableaux en un à l'aide d'une opération appelée fusion ou jointure. Si on a deux tableaux A et B la fusion se fait simplement en écrivant A, B par contre le résultat est le produit cartésien de A et B soit une chose par forcément très utile de façon "brute".

		
Exemple
		
Prenons comme exemple les tableaux suivants :

		
		
La fusion A, B donne :

		

		
Rouge#FF0000Rougered

		
Rouge#FF0000Vertgreen

		
Vert#00FF00Rougered

		
Vert#00FF00Vertgreen

		
Bleu#0000FFRougered

		
Bleu#0000FFVertgreen

		

		
Comme vous voyez des lignes n'ont aucun sens, cependant on peut ajouter des contraintes du genre premier colonne du tableau A = première colonne du tableau B, on obtient :

		

		
Rouge#FF0000Rougered

		
Vert#00FF00Vertgreen

		

		 
		

		
SQL offre différentes syntaxes pour faire des jointures, Télécharger une base de données exemple. 
		
La simple

		

		
donne

		

		
		

		
Cette requête (le produit cartésien) offre peut d’intérêt (à mon sens).

		
La simple avec contrainte

		

		
donne

		

		
		

		
Là c'est intéressant, on peut remarquer qu'une ligne du champ A n’apparaît pas (la 5) de plus il y a deux champs identiques.

		
INNER JOIN ON

		

		
donne

		

		
		

		
C'est la même chose mais la nature de l'opération que l'on souhaite est plus explicite.

		
LEFT JOIN ON

		

		
donne

		

		
		

		
Toutes les lignes de la table A apparaissent parfois compléter avec des NULL si il n'y a pas de lien. ça peut être intéressant. SQLITE semble ne pas supporter RIGHT JOIN

		
Il existe encore d'autres syntaxes (CROSS JOIN qui est le produit cartésien, NATURAL JOIN qui est très pratique ... mais pour des raisons pédagogiques on va rester sur les cas évoqués).
 
		
		
		
		
		
Exemple
		
On veut connaître les préfectures des différents départements :

		

		
donne

		

		
		
		
		
		
		


		
		
Exemple
		
On veut connaître le nombre de communes par régions :

		

		
donne

		

		
		
	
		

		
Exemple
		
On veut connaître les trois communes les plus peuplées du Grand Est

		

		
donne

		

		
		
			
		
		

		
		
Exercice
		
    
			
  1. Afficher le nom des départements et le nom des préfectures associées.
    2. 
			
  2. Afficher le nom des régions et le nom des chef-lieu associées.
    3. 
			
  3. Afficher les régions et le nom des départements qui la constituent.
    4. 
			
  4. Afficher les départements et le nombre de villes qui associées.
    5. 
			
  5. Afficher le nom des départements ainsi que sa population et sa superficie.
    6. 
			
  6. Afficher les régions ainsi que sa population et sa superficie.
    7. 
			
  7. Afficher les villes du Bas Rhin dont la population est supérieure à la ville de Wissembourg.
    8. 
			
  8. Afficher les villes du Grand Est dont la population est supérieure à la ville de Wissembourg.
    9. 
			
  9. Afficher les villes qui ont le même code postal que celui de Wissembourg.
    10. 
		

		

		
		
Exercice
		
Les exercices suivants sont plus durs.

		
    
			
  1. La fonction LENGTH permet de connaître le nombre de lettres d'un attribut de type TEXT, donner la ville qui a le nom le plus long en France.
    2. 
			
  2. Afficher par région les villes qui ont les noms le plus longs.
    3. 
			
  3. Afficher le classement des noms des communes les plus utilisés (avec le nombre de fois où le nom a été donné).
    4. 
			
  4. Afficher les villes qui ont plus que 4 code postaux.
    5. 
			
  5. Afficher le nombre maximal de communes partageant un même code postal.
    6. 
			
  6. Afficher pour chaque région les trois plus grandes villes en habitants.
    7. 
		

		

		
		
Utiliser un base de données dans Python

		
Jusqu'ici nous avons fait des requêtes en passant directement par le SGBD, l'usage le plus courant (car il permet d'automatiser) est de passer par un langage de programmation. Par exemple si un site commercial veut afficher un article à vendre sur une page :
		
    
		
  1. Le serveur envoie une page HTML au client contenant un formulaire (par exemple une barre recherche).
    2.  
		
  2. Le client envoie l'information avec la méthode GET ou Post.
    3. 
		
  3. Le serveur va créer un page HTML à partir du code (en PHP par exemple) en donnant en paramètre le nom de l'article récupéré à l'étape précédente, au moment de la création va récupérer sur un autre serveur et en passant par un SGBD (pas en SQlite qui n'est pas fait pour) le nom précis de l'article, la marque, le prix, le stockage, les options possibles, les articles liées, les commentaires,...).
		
  4. La page HTML générée est envoyée au client.
    5. 
		

		
Exemple
		
Voici un exemple d'utilisation d'une base de données dans un programme (en Python), Python en lui même ne touche pas à la base de donnée il fait lance SQlite et envoie à SQlite les demandes puis récupère les résultats.