Geography: answers

Geography: Answer 1

Show, by region and department, the number of towns with more than 100,000 inhabitants.

Links between tables

SQL query

SELECT R.Nomregion, D.Nomdept, Count(Ville) AS "Number of towns" 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;

(Back to questions)

Geography: Answer 2

Display a list of all one-way connections between two cities where the population of one of the two cities is less than 80,000 inhabitants.

Links between tables

Besides the links define above, it is necessary to check that the string "V1.Ville+V.Ville " is not in the table "Distances", because it would mean that the connection between the two cities is a two way connection. This condition is verified in the embedded selection clause.

SQL query

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);

(Back to questions)

Geography: Answer 3

Display the list of towns, separated by 2 sections of road, and the distance between them.

Links between tables

SQL query

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;

(Back to questions)

Geography: Answer 4

Display the list of cities, which may be distant from 4 road sections (there may indeed be shorter connections in 2 or 3 sections), and the distance that separates them.

Links between tables

SQL query

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;

We arrive here to the limits of the SQL language because this language is not recursive. Addressing such a question involves a broad use of recursiveness.

(Back to questions)

Geography: Answer 5

Display the number of inhabitants per region and department. It is assumed that the population of the area is that of the cities that make up the road network.

Links between tables

SQL query

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

(Back to questions)

Geography: Answer 6

Show the name of the region with the longest road network and the number of kilometers of this network.

To handle this question 2 queries must be created:

6.1-A request to create a "WorkTable" composed of the attributes: Numregion, Nomregion and Distance for all double-way connections, between 2 cities of the same region. This request is linked to a"UNION" request that adds to the previous one all unidirectional connections between 2 cities in the same region.

6.2-A select query, from the "WorkTable" table created above.

Links between tables (requête 6.1)

SQL query 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;

Links between tables (requête 6.2)

SQL query 6.2

The first line of the following query provides the response to the 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;

(Back to questions)