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

SQL query

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.

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

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

SQL query

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.

SQL query

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.

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.

SQL query

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;