Géographie : réponses

Géographie : Réponse 1

Afficher, par région et département, le nombre de villes de plus de 100000 habitants.

Relations entre tables

Requête SQL

SELECT R.Nomregion, D.Nomdept, Count(Ville) AS "Nombre de villes" FROM REGION R, DEPARTEMENT D, VILLES V WHERE V.NumDept=D.Numdept AND D.NumRegion=R.NumRegion AND Population>100000 GROUP BY R.Nomregion, D.Nomdept;

(Retour aux Questions)

Géographie : Réponse 2

Afficher la liste de toutes les liaisons à sens unique entre deux villes pour lesquelles la population de l'une des 2 villes est inférieure à 80000 habitants.

Relations entre tables

En complément des liaisons définies ci-dessus, il faut vérifier que la chaîne "V1.Ville+V.Ville" ne se trouve pas dans la table "Distances", car cela voudrait dire que la liaison entre les 2 villes est à double sens. Cette condition est vérifiée grâce à la clause Select imbriquée.

Requête SQL

SELECT V.Ville, V1.Ville FROM VILLES V, VILLES V1, DISTANCES D WHERE V.Ville=D.VilleDepart AND V1.Ville=D.VilleArrivee AND (V.Population < 80000 OR V1.Population < 80000) AND V1.Ville NOT IN (SELECT VilleDepart FROM DISTANCES WHERE V.Ville=VilleArrivee);

(Retour aux questions)

Géographie : Réponse 3

Afficher la liste des villes, distantes de 2 tronçons routiers, et la distance qui les sépare.

Relations entre tables

Requête SQL

SELECT D.VilleDepart, D.VilleArrivee, D1.VilleArrivee, D.Distance+D1.Distance AS "Distances" FROM DISTANCES D, DISTANCES D1 WHERE D.VilleArrivee=D1.Villedepart AND D1.VilleArrivee<>D.VilleDepart ORDER BY D.VilleDepart;

(Retour aux questions)

Géographie : Réponse 4

Afficher la liste des villes, qui peuvent être distantes de 4 tronçons routiers (il peut en effet y avoir des liaisons plus courtes en 2 ou 3 tronçons), et la distance qui les sépare.

Relations entre tables

Requête SQL

SELECT D.VilleDepart, D.VilleArrivee, D1.VilleArrivee, D2.VilleArrivee, D3.VilleArrivee, D.Distance+D1.Distance+D2.Distance+D3.Distance AS "Distances" FROM DISTANCES D, DISTANCES D1, DISTANCES D2, DISTANCES D3 WHERE D.VilleArrivee=D1.VilleDepart AND D1.VilleArrivee=D2.VilleDepart AND D2.VilleArrivee=D3.VilleDepart AND D1.VilleArrivee<>D.VilleDepart AND D2.VilleArrivee<>D.VilleDepart AND D3.VilleArrivee<>D.VilleDepart AND D2.VilleArrivee<>D1.VilleDepart AND D3.VilleArrivee<>D1.VilleDepart AND D3.VilleArrivee<>D2.VilleDepart ORDER BY D.VilleDepart;

Nous atteignons ici les limites du langage SQL car ce langage n'est pas récursif et le traitement général de question de ce type implique un large recours à la récursivité.

(Retour aux questions)

Géographie : Réponse 5

Afficher le nombre d'habitants par région et département. On supposera que la population d'une région se résume à celle des villes qui font partie du réseau routier.

Relations entre tables

Requête SQL

SELECT R.Nomregion, D.Nomdept, Sum(V.Population) AS "Population totale" FROM VILLES V, REGION R, DEPARTEMENT D WHERE D.Numdept = V.NumDept AND R.Numregion = D.Numregion GROUP BY R.Nomregion, D.Nomdept;

(Retour aux questions)

Géographie : Réponse 6

Donner le nom de la région qui possède le plus long réseau routier et le nombre de kilomètres de ce réseau.

Pour traiter cette question, il est nécessaire de créer 2 requêtes :

6.1- Requête de création d'une table de travail "WorkTable" constituée des attributs : Numregion, Nomregion et Distance pour toutes les liaisons, à double sens, entre 2 villes de la même région.
Cette requête est liée par un prédicat "UNION" à une autre requête qui ajoute à la précédente toutes les liaisons (à sens unique) entre 2 villes de la même région.

6.2- Requête de sélection, à partir de la table "WorkTable" constituée précédemment.

Relations entre tables (requête 6.1)

Requête SQL 6.1

CREATE VIEW WorkTable(Numregion, Nomregion, Distance) AS
SELECT R.numregion, R.Nomregion, Sum(D.Distance)/2
FROM Distances D, Villes V, Villes V1, Departement P, Departement P1, Region R, Region R1
WHERE D.Villedepart=V.Ville
AND V.Numdept=P.Numdept
AND P.Numregion=R.numregion
AND D.Villearrivee=V1.Ville
AND V1.Numdept=P1.Numdept
AND P1.Numregion=R1.numregion
AND R.Numregion=R1.Numregion
AND D.villedepart IN
     (SELECT villearrivee FROM Distances WHERE D.villearrivee=villedepart)
GROUP BY R.Numregion, R.Nomregion
UNION
SELECT R.numregion, R.Nomregion, Sum(D.Distance)
FROM Distances D, Villes V, Villes V1, Departement P, Departement P1, Region R, Region R1
WHERE D.Villedepart=V.Ville
AND V.Numdept=P.Numdept
AND P.Numregion=R.numregion
AND D.Villearrivee=V1.Ville
AND V1.Numdept=P1.Numdept
AND P1.Numregion=R1.numregion
AND R.Numregion=R1.Numregion
AND D.villedepart NOT IN
     (SELECT villearrivee FROM Distances WHERE D.villearrivee=villedepart)
GROUP BY R.Numregion, R.Nomregion;

Relations entre tables (requête 6.2)

Requête SQL 6.2

La première ligne de la requête suivante donne la réponse à la question.

SELECT W.numregion, W.Nomregion, Sum(W.Distance) AS "Longueur du réseau"
FROM WorkTable AS W
GROUP BY W.numregion, W.Nomregion
ORDER BY 3 DESC;

(Retour aux questions)