Geography: answers

Geography: Answer 1

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

Links between tables

Diagram of data for Database Geography question 1

SQL query

(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

Diagram of data for Database Geography question 2

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

(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

Diagram of data for Database Geography question 3

SQL query

(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

Diagram of data for Database Geography question 4

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.

(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

Diagram of data for Database Geography question 5

SQL query

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

Diagram of data for Database Geography question 6, part one

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)

Diagram of data for Database Geography question 6, part two

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)