Corrigé TP1 : Premiers pas avec PostgreSQL

Avril 2024

Exercice 1 : Base prénoms

1. Affichez les informations pour le prénom "CLAUDE".
SELECT * FROM prenoms WHERE prenom='CLAUDE' ;
2. Affichez la liste des prénoms qui commencent par un "Z" sans afficher les doublons.
SELECT DISTINCT prenom FROM prenoms WHERE prenom LIKE 'Z%' ;
3. Affichez la liste des prénoms donnés aux enfants de Paris nés en 2000, triés par ordre
alphabétique
SELECT DISTINCT prenom FROM prenoms WHERE departement='75' AND annee=2000 ORDER
BY prenom ;
4. Combien de personnes nées la même année que vous en France portent le même prénom que
vous ?
SELECT SUM(nombre) FROM prenoms WHERE prenom='CHLOÉ' AND annee='2018';
5. Affichez le nombre de naissances par année, pour votre prénom et dans votre département
de naissance.
SELECT annee, nombre FROM prenoms WHERE prenom='XXXXXX ' AND departement='XX'
ORDER BY annee ;
6. Combien d’enfants sont nés en 2000 ?
SELECT SUM(nombre) FROM prenoms WHERE annee=2000;
7. Combien de filles nommées "ALICE" sont nées entre 2010 et 2021 en Essonne ?
SELECT SUM(nombre) FROM prenoms WHERE prenom='ALICE' AND departement='91' AND
annee BETWEEN 2010 AND 2021;
8. Quel a été le prénom masculin le plus populaire en 2000 (hors prénoms rares) en Essonne ?
SELECT prenom FROM prenoms WHERE departement='91' AND annee =2000 AND sexe='1'
AND prenom != '_PRENOMS_RARES ' ORDER BY nombre DESC LIMIT 1 ;
9. Quel est le prénom masculin le plus populaire en 2021 en Île-de-France (hors prénoms rares) ?
SELECT prenom , SUM( nombre ) FROM PRENOMS WHERE sexe=' 1 ' AND departement IN
( '75' , '92' , '93' ,'94' , '95' , '91' , '78' ,'77' ) AND annee =2021 AND
prenom != '_PRENOMS_RARES ' GROUP BY prenom ORDER BY SUM( nombre ) DESC LIMIT 1 ;
10. Quel a été le prénom féminin le plus populaire en 2021 sur l’ensemble du territoire national ?
SELECT prenom , SUM( nombre ) FROM PRENOMS WHERE sexe='1' AND annee=2021 AND
prenom != '_PRENOMS_RARES' GROUP BY prenom ORDER BY SUM( nombre ) DESC LIMIT 1 ;

Page 1
Exercice 2 : Openflights
1. Premières requêtes
1. Afficher la liste des aéroports desservant la ville de Paris en France :
SELECT * FROM aeroport WHERE ville='Paris' AND nom_pays='France';
2. Afficher le nom de l’aéroport dont le code OACI est « LFRT »
SELECT nom_aeroport FROM aeroport WHERE code_oaci='LFRT';
3. Afficher la liste des aéroports en France, classé par le nom de code OACI :
SELECT * FROM aeroport WHERE nom_pays='France' ORDER BY code_oaci ;
4. Afficher le nombre de compagnies en activité et enregistrées aux USA :
SELECT COUNT(*) FROM compagnie WHERE nom_pays='United States' AND actif='Y';
5. La compagnie « Aigle Azur » n’est plus active. Changez son état dans la base
UPDATE compagnie SET actif='N' WHERE nom_compagnie='Aigle Azur' ;
6. Affichez les itinéraires desservis par le plus d’avions différents (vous vous baserez sur la
longueur du champ « type_avion »)
SELECT * FROM itineraire ORDER BY length(type_avion) DESC;
7. Afficher le nom de l’aéroport dont l’altitude est la plus élevée, ainsi que son altitude
exprimée en mètres (le champ « altitude » est exprimé en pieds. 1 mètre = 3,28 pieds) :
SELECT nom_aeroport,altitude/3.28 FROM aeroport ORDER BY altitude DESC LIMIT 1;
8. Afficher sans les doublons l’ensemble des destinations (code IATA, nom de la ville, nom du
pays) accessibles depuis l’aéroport de Paris-Charles-de-Gaulle, classées par pays puis par nom
de ville (code_iata_depart=CDG)
SELECT DISTINCT aeroport.code_iata, aeroport.ville, aeroport.nom_pays
FROM itineraire
INNER JOIN aeroport ON (itineraire.code_iata_arrivee=aeroport.code_iata)
WHERE itineraire.code_iata_depart='CDG'
ORDER BY aeroport.nom_pays, aeroport.ville ;
9. Afficher sans les doublons la liste des destinations (code IATA, nom de la ville, nom du pays)
desservies par la compagnie « Air France » :
SELECT DISTINCT aeroport.code_iata, aeroport.ville, aeroport.nom_pays
FROM itineraire INNER JOIN compagnie USING (id_compagnie)
INNER JOIN aeroport ON (itineraire.code_iata_arrivee=aeroport.code_iata)
WHERE compagnie.nom_compagnie='Air France';
10. Affichez sans les doublons la liste des destinations (code IATA, nom de la ville, nom du pays)
au départ de n’importe quel aéroport de la ville de New York :
SELECT DISTINCT a_arrivee.code_iata, a_arrivee.ville, a_arrivee.nom_pays
FROM itineraire INNER JOIN compagnie USING (id_compagnie)
INNER JOIN aeroport a_arrivee ON
(itineraire.code_iata_arrivee=a_arrivee.code_iata)
INNER JOIN aeroport a_depart ON
(itineraire.code_iata_depart=a_depart.code_iata)
WHERE a_depart.ville='New York';

Page 2
 2. Performances
Note importante pour les enseignants : effectuer les ajouts de clés et index sur
la base de données correspondant au groupe de TP afin de permettre la
démonstration devant les étudiants de l’amélioration des performances, et du
changement dans le plan d’exécution.

Les bases seront rechargées le jour du TP afin de revenir à la situation

initiale.
11. La requête précédente pourrait être optimisée pour permettre une exécution plus rapide.
Faites un bilan du plan d’exécution de cette requête, et dégagez des pistes permettant
d’améliorer les performances. Mettez-les en œuvre et indiquez les gains obtenus
La requête initiale indique un coût d’exécution de 1800 environ, avec un temps
d’exécution allant de 100 à 200ms.
On remarque que des lectures séquentielles sont réalisées sur l’ensemble des
tables, sauf pour l’index adossé à la clé primaire compagnie(id_compagnie).

La solution pour améliorer la situation est de créer des index sur toutes les
clés étrangères invoquées dans cette requête, ainsi qu’à la colonne « ville »
utilisée dans le filtre du SELECT :

CREATE INDEX ON itineraire(id_compagnie);

CREATE INDEX ON itineraire(code_iata_arrivee);
CREATE INDEX ON itineraire(code_iata_depart);
CREATE INDEX ON aeroport(ville);

Après la création de ces index, le temps d’exécution est désormais d’environ

80ms, avec un coût d’exécution estimé à 550 environ. On remarque qu’il n’y a plus
de lecture séquentielle des tables.
12. Il n’y a pas de clé primaire sur la table « itineraire ». Proposez une clé et mettez-là en place
La clé primaire doit être univaluée. Aucune colonne n’est candidate pour être clé
primaire seule. Il faut créer une clé primaire composée, sur les colonnes
code_compagnie, code_iata_depart, code_iata_arrivée par exemple.

ALTER TABLE itineraire

ADD PRIMARY KEY (code_compagnie,code_iata_depart,code_iata_arrivee) ;

3. Requêtes avancées
13. Classer les compagnies aériennes en fonction du nombre d’itinéraires desservis
SELECT compagnie.nom_compagnie, COUNT(*)
FROM itineraire INNER JOIN compagnie USING (id_compagnie)
GROUP BY compagnie.nom_compagnie
ORDER BY COUNT(*) DESC;
14. Afficher ce même classement, cette-fois ci en indiquant le rang de chaque compagnie en
fonction du nombre d’itinéraires desservis (la compagnie ayant le plus d’itinéraire étant n°1,
la seconde n°2, etc…)
SELECT compagnie.nom_compagnie,
rank() OVER (ORDER BY COUNT(*) DESC) as classement
FROM itineraire INNER JOIN compagnie USING (id_compagnie)
GROUP BY compagnie.nom_compagnie
ORDER BY classement;

Page 3
 15. Afficher les compagnies françaises marquées comme étant en activité mais qui ne sont
associées à aucun itinéraire dans cette base de données
SELECT compagnie.nom_compagnie
FROM itineraire RIGHT OUTER JOIN compagnie USING (id_compagnie)
WHERE itineraire.id_compagnie IS NULL
AND compagnie.actif='Y' AND compagnie.nom_pays='France';
16. Affichez le nom des compagnies qui utilisent un « Airbus A380-800 » sur leurs lignes. La
colonne itinéraire(type_avion) peut contenir plusieurs « code_court_avion ». Par conséquent,
faires votre jointure en vous basant sur le résultat de la commande position() décrite dans la
page de manuel suivante : https://www.postgresql.org/docs/current/functions-string.html
SELECT DISTINCT compagnie.nom_compagnie
FROM itineraire
INNER JOIN compagnie USING (id_compagnie)
INNER JOIN avion
ON (position(avion.code_court_avion IN itineraire.type_avion) > 0)
WHERE avion.nom_avion='Airbus A380-800'
ORDER BY compagnie.nom_compagnie ;
17. Indiquer la destination (ville) desservie par au moins 10 itinéraires différents au départ de
Paris, France (tout aéroports confondus)
SELECT a_arrivee.ville, COUNT(*)
FROM itineraire INNER JOIN compagnie USING (id_compagnie)
INNER JOIN aeroport a_arrivee ON
(itineraire.code_iata_arrivee=a_arrivee.code_iata)
INNER JOIN aeroport a_depart ON
(itineraire.code_iata_depart=a_depart.code_iata)
WHERE a_depart.ville='Paris' AND a_depart.nom_pays='France'
GROUP BY a_arrivee.ville
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC ;
18. Ecrire une requête SQL permettant d’identifier les différents itinéraires possibles pour
effectuer un voyage avec escale entre deux aéroports -- par exemple : entre Paris (CDG) et
Memphis, Tennessee (MEM). Vous n’afficherez que le code OACI de l’aéroport de départ, le
code OACI de l’escale et le code OACI de l’aéroport de destination. Vous pourrez cependant
ajouter le nom de l’aéroport de correspondance et la ville associée.
SELECT DISTINCT i1.code_iata_depart,i2.code_iata_depart,i2.code_iata_arrivee
FROM itineraire i1
INNER JOIN itineraire i2 ON (i1.code_iata_arrivee=i2.code_iata_depart)
WHERE i1.code_iata_depart='CDG' AND i2.code_iata_arrivee='MEM';

Page 4
TP2 : Base de données spatiale, bases de données
avancées
Mai 2024

Durée du TP : 3 heures.

Exercice 1 : Base de données spatiale : population de New York

1. Premières requêtes
1. Listez les arrondissements (boroughs) de la ville ?
SELECT DISTINCT boroname FROM nyc_neighborhoods
ORDER BY boroname;
2. Quelle est la population totale de la ville ?
SELECT SUM(popn_total) FROM nyc_census_blocks;
Si l’on veut ventiler la population par nom d’arrondissement :
SELECT boroname,SUM(popn_total) FROM nyc_census_blocks GROUP BY boroname;
3. Quelle est la liste des stations de métro d’arrondissement de Manhattan ?
SELECT DISTINCT name FROM nyc_subway_stations
WHERE Borough='Manhattan';
4. Afficher le nombre de station de métro par arrondissement.
SELECT Borough, COUNT(DISTINCT name) FROM nyc_subway_stations
GROUP BY Borough
ORDER BY Borough;

2. Géométrie
1. Quelle est l’aire en hectares de l’arrondissement de Manhattan ? (1 ha = 10000 m²)
SELECT SUM(ST_Area(geom))/10000 FROM nyc_neighborhoods
WHERE boroname='Manhattan' ;

-- La même requête sur la table nyc_census_blocks donne un résultat plus

-- proche de la réalité. Probablement le contour des « neighborhoods » n’est-il
-- pas très précis.

SELECT SUM(ST_Area(geom))/10000 FROM nyc_census_blocks

WHERE boroname='Manhattan';
Si l’on veut répondre à la question en faisant le calcul, arrondissement par arrondissement :
SELECT boroname,SUM(ST_Area(geom))/10000 FROM nyc_neighborhoods
GROUP BY boroname
2. Quelle est la longueur totale en kilomètres des rues de New York ?
SELECT SUM(ST_Length(geom))/1000 FROM nyc_streets;

Page 1
 3. Jointures spatiales
1. Dans quel quartier et quel arrondissement se trouve la 47ème rue ouest ? (« W 47th St »)
La jointure spatiale se fait non pas sur un critère « clé primaire = clé étrangère » comme c’est le
cas habituellement. Le critère de jointure est plutôt sur l’exécution d’une fonction géométrique
permettant de savoir si la forme géométrique décrite dans la table de gauche correspond (inclusion,
distance, etc.) à la forme géométrique décrite dans la table de droite. Ces fonctions géométriques
(ST_Intersects, ST_Touches…) renvoient un booléen : VRAI si les formes géométriques
correspondent, FAUX sinon.
Réponse en auto-jointure :
SELECT nyc_neighborhoods.boroname,nyc_neighborhoods.name
FROM nyc_streets, nyc_neighborhoods
WHERE nyc_streets.name='W 47th St'
AND ST_Intersects(nyc_streets.geom,nyc_neighborhoods.geom) ;
En jointure interne :
SELECT nyc_neighborhoods.boroname,nyc_neighborhoods.name
FROM nyc_streets INNER JOIN nyc_neighborhoods
ON (ST_Intersects(nyc_streets.geom,nyc_neighborhoods.geom))
WHERE nyc_streets.name='W 47th St' ;
2. Quelles sont les rues (nom et type) qui touchent la 47ème rue ouest ?
SELECT s1.name,s2.name FROM nyc_streets s1, nyc_streets s2
WHERE ST_Intersects(s1.geom,s2.geom)
AND s1.name='W 47th St' ;
3. Combien de personnes vivent dans des blocks situés à moins de 50 mètres de la 47ème rue
ouest ?
SELECT SUM(nyc_census_blocks.popn_total)
FROM nyc_streets, nyc_census_blocks
WHERE ST_DWithin(nyc_streets.geom,nyc_census_blocks.geom, 50)
AND nyc_streets.name='W 47th St' ;
4. Quelles sont les stations de métro et les lignes associées desservant le quartier de Chinatown ?
SELECT nyc_subway_stations.name,nyc_subway_stations.routes
FROM nyc_subway_stations, nyc_neighborhoods
WHERE ST_Contains (nyc_neighborhoods.geom, nyc_subway_stations.geom)
AND nyc_neighborhoods.name='Chinatown' ;
5. Affichez la densité de population exprimée par km² pour chaque quartier de Manhattan ?
-- L’exécution de cette requête donne un résultat approximatif. En effet,
-- avec cette commande certains blocks vont être comptés deux fois, ce qui va
-- fausser le calcul.
SELECT SUM(nyc_census_blocks.popn_total) /
(SUM(ST_Area(nyc_census_blocks.geom))/1000000)
AS densite, nyc_neighborhoods.name
FROM nyc_census_blocks, nyc_neighborhoods
WHERE ST_Intersects(nyc_census_blocks.geom, nyc_neighborhoods.geom)
GROUP BY nyc_neighborhoods.name

Page 2
--Proposition : faire une requête avec sous-question.
-- La sous-question contient deux requêtes (renvoyant les mêmes colonnes)
-- en utilisant deux fonctions de Postgis différentes :
-- ST_Intersects ==> renvoie les formes tangeantes avec le polygone
-- représentant le quartier
-- ST_Within ==> renvoie les formes stritement contenues dans le
-- polygone représentant le quartier
--
-- Les valeurs (population et superficie) des blocs tangeants sont
-- divisés par deux afin de limiter l'effet de bord d'un compte double...
--

SELECT name,
SUM(popn_total)/SUM((area)/1000000)
FROM (
-- Début de la sous-question
-- R1
SELECT nyc_neighborhoods.name, popn_total/2 as popn_total,
ST_Area(nyc_census_blocks.geom)/2 as area
FROM nyc_census_blocks, nyc_neighborhoods
WHERE nyc_neighborhoods.boroname='Manhattan'
AND ST_Overlaps(nyc_census_blocks.geom, nyc_neighborhoods.geom)

UNION -- Mot clé permettant de concaténer les résultats de R1 et R2

-- R2
SELECT nyc_neighborhoods.name, popn_total,
ST_Area(nyc_census_blocks.geom) as area
FROM nyc_census_blocks, nyc_neighborhoods
WHERE nyc_neighborhoods.boroname='Manhattan'
AND ST_Within(nyc_census_blocks.geom,nyc_neighborhoods.geom)
) tab -- Alias de la sous-question. Obligatoire.
GROUP BY name;

-- En décomposant le fonctionnement de cette requête :

-- Tout d’abord, première sous-question « ST_Overlaps ». Ces blocs seront
-- comptés deux fois. Pour limiter l’effet de ce double compte, la valeur
-- de la population de chaque quartier, ainsi que sa superficie, seront
-- divisées par deux…

Page 3
-- Ensuite, seconde sous-question « ST_Within »

Si j’exécute ces deux premières requêtes pour montrer les polygones renvoyés par
la requête 1 et 2 :

- Requête 1 / Requête 2

-- Vous aurez remarqué que dans ces deux requêtes, le nom des colonnes renvoyé
-- est identique (« name », « popn_total », « area »). Cela permet ensuite de
-- combiner ces deux requêtes avec le mot clé « UNION ».
-- L’opérateur UNION va exécuter les commandes et afficher dans la même réponse
-- les lignes renvoyées par les deux requêtes.
-- SELECT COUNT(*) de R1 + SELECT COUNT(*) de R2 = SELECT COUNT(*) avec UNION
R1 = 1180 lignes affectées.

Page 4
R2 = 3135 lignes affectées.
UNION = 4315 lignes affectées. (le compte est bon !)

-- L’exécution de la requête avec UNION affiche le même type de colonnes que

-- les requêtes R1 et R2 :

-- Enfin, la requête R1 UNION R2 est utilisée comme sous-requête permettant

-- le calcul global.
--
-- La syntaxe d’une sous-requête est « SELECT … FROM ( sous-requête ) nom »
-- Le « nom » est l’alias donné à la table dans le cadre de cette sous-requête.

Page 5
Exercice 2 : Location de films
1. Requêtes SQL
1. Affichez la liste des acteurs
SELECT * FROM actor;
2. Affichez la liste des acteurs et de tous les films dans lesquels ils ont joué
-- Auto-jointure
SELECT actor.first_name, actor.last_name, film.title
FROM actor, film_actor, film
WHERE actor.actor_id=film_actor.actor_id
AND film_actor.film_id=film.film_id;

-- Jointure interne
SELECT actor.first_name, actor.last_name, film.title
FROM actor INNER JOIN film_actor USING (actor_id)
INNER JOIN film USING (film_id);
3. Affichez le nombre de paiements reçus par employés pour le mois de mars 2024
-- Utilisation de BETWEEN
SELECT staff.first_name, staff.last_name,COUNT(*) FROM staff, payment
WHERE staff.staff_id=payment.staff_id
AND payment.payment_date BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY staff.first_name, staff.last_name;

-- Utilisation de l’opérateur d’inégalité

SELECT staff.first_name, staff.last_name,COUNT(*) FROM staff, payment
WHERE staff.staff_id=payment.staff_id
AND payment.payment_date >= '2024-03-01'
AND payment.payment_date <= '2024-03-31'
GROUP BY staff.first_name, staff.last_name;

-- Utilisation de la fonction EXTRACT()

SELECT staff.first_name, staff.last_name,COUNT(*) FROM staff, payment
WHERE staff.staff_id=payment.staff_id
AND EXTRACT(month FROM payment.payment_date) = '3'
AND EXTRACT (year FROM payment.payment_date) = '2024'
GROUP BY staff.first_name, staff.last_name;

-- Autre exemple : quels sont les paiements reçus les samedis (jour 6)
-- Groupés par nom d’employés
SELECT staff.first_name, staff.last_name,COUNT(*) FROM staff, payment
WHERE staff.staff_id=payment.staff_id
AND EXTRACT(DOW FROM payment.payment_date) = '6'
GROUP BY staff.first_name, staff.last_name;

Page 6
-- Autre exemple : affichage du nombre de paiements reçus groupés
-- par agent et par jour de la semaine.
SELECT staff.first_name, staff.last_name,EXTRACT(DOW FROM
payment.payment_date),COUNT(*) FROM staff, payment
WHERE staff.staff_id=payment.staff_id
AND payment.payment_date BETWEEN '2020-01-01' AND '2020-01-31'
GROUP BY staff.first_name, staff.last_name,EXTRACT(DOW FROM
payment.payment_date);
4. Affichez le montant total des paiements reçus de chaque client
-- Autojointure
SELECT customer.first_name, customer.last_name, SUM(payment.amount)
FROM payment, customer
WHERE payment.customer_id=customer.customer_id
GROUP BY customer.first_name, customer.last_name
ORDER BY customer.last_name;

-- Jointure interne
SELECT customer.first_name, customer.last_name, SUM(payment.amount)
FROM payment INNER JOIN customer USING (customer_id)
GROUP BY customer.first_name, customer.last_name
ORDER BY customer.last_name;
5. Affichez le titre et l’année de sortie des films de la catégorie « famille » (Family)
-- Autojointure
SELECT film.title, film.release_year FROM film, film_category, category
WHERE film.film_id=film_category.film_id
AND film_category.category_id=category.category_id
AND category.name='Family' ;

-- Jointure interne
SELECT film.title, film.release_year
FROM film INNER JOIN film_category USING (film_id)
INNER JOIN category USING (category_id)
WHERE category.name='Family' ;
6. Vous voulez faire un mailing à tous vos clients de France. Effectuez la requête permettant
d’avoir leurs noms, prénoms et adresse e-mail.
-- Jointure interne
SELECT customer.first_name, customer.last_name, customer.email
FROM customer INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
WHERE country.country='France' ;

-- Jointure interne
SELECT city.country_id AS city, country.country_id AS country,
customer.first_name, customer.last_name, customer.email
FROM customer, address, city, country
WHERE customer.address_id=address.address_id
AND address.city_id=city.city_id
AND city.country_id=country.country_id
AND country.country='France';

Page 7
 7. Affichez le nombre de clients par pays
SELECT country.country, COUNT(*)
FROM customer INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
GROUP BY country.country ;
8. Affichez la somme des paiements reçus en fonction des pays des clients
SELECT country.country, SUM(payment.amount)
FROM customer INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
INNER JOIN payment USING (customer_id)
GROUP BY country.country
ORDER BY SUM(payment.amount) DESC;

2. Fonctionnalités avancées
1. Etudiez le déclencher (« trigger ») « last_updated() ». Quelle est sa fonction ?
Le déclencheur « last_updated » permet d’insérer automatiquement la valeur dans
la colonne du même nom de plusieurs tables de cette base de données. On peut
vérifier son code en affichant les propriétés du déclencheur dans l’interface
PgAdmin.
On peut le vérifier en insérant / modifiant une ligne, puis en affichant le
contenu après chaque opération :

INSERT INTO actor (first_name, last_name) VALUES ('Sean', 'Connery') ;

SELECT * FROM actor WHERE last_name='CONNERY' ;
UPDATE actor SET last_name='CONNERY' WHERE last_name='Connery';
SELECT * FROM actor WHERE last_name='CONNERY' ;
DELETE FROM actor WHERE last_name='CONNERY' ;
2. Observez la table « payment ». Que remarquez-vous ? A quoi cela peut-il servir ?
La table « payment » est une table partitionnée. Cela signifie qu’elle est
découpée en plusieurs tables, ici en fonction de la date du paiement. Cela permet
de simplifier les opérations de recherche lorsqu’on souhaite afficher tous les
paiements reçus entre telle et telle date par exemple : il n’y aura pas besoin de
scanner toute la table.

Dans cet exemple, la partition se fait mois par mois.

On remarque qu’en faisant une requête sur « payment », on obtient bien des
réponses. En revanche, lorsqu’on souhaite faire une requête sur cette seule table
« payment », il n’y a pas de réponse : la table « payment » est vide.

SELECT * FROM payment WHERE payment_date BETWEEN '2024-01-01' AND '2024-01-31' ;

SELECT * FROM ONLY payment ;

On se propose de créer une table pour le mois d’avril 2024, que l’on rajoute
comme partition de la table « payment » :

CREATE TABLE public.payment_p2024_04 (

payment_id integer DEFAULT nextval('public.payment_payment_id_seq'::regclass)
NOT NULL,

Page 8
 customer_id integer NOT NULL,
staff_id integer NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp with time zone NOT NULL
);
ALTER TABLE ONLY public.payment ATTACH PARTITION public.payment_p2024_04 FOR
VALUES FROM ('2024-04-01 00:00:00+00') TO ('2024-05-01 00:00:00+00');

Si on insère une ligne dans « payment » avec une date au mois d’avril 2024 (ce
que renvoie la fonction NOW() ) :
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, 1, '10.99', NOW())

Alors cette nouvelle ligne sera créée dans la table payment_p2024_04 :

SELECT * FROM payment_p2024_04;

SELECT * FROM payment WHERE payment_date > '2024-04-01'
3. Observez le type de données « mpaa_ratings ». Que permet cette définition ?
Il s’agit d’une définition d’un nouveau type de données, en fait une énumération
de chaînes de caractères (dans cet exemple il s’agit de la classification des
films permettant de connaître l’âge minimum recommandé). L’intérêt : être sûr que
la donnée reste de qualité car une donnée absente de la liste sera rejetée.

Cet exemple d’UPDATE tombe en erreur car le mpaa_rating « TOTO » n’existe pas.
UPDATE film SET rating='TOTO' WHERE film_id=1 ;

3. Requêtes avancées
1. Affichez tous les films dont la description contient les mots « Student » et « Boat ». Faites la
recherche en utilisant LIKE et @@
EXPLAIN ANALYZE SELECT * FROM film
WHERE description ILIKE '%student%' AND description ILIKE '%boat%';
EXPLAIN ANALYZE SELECT * FROM film
WHERE fulltext @@ to_tsquery('Boat&Student') ;
2. Pour chaque location non rendue, calculez le montant à payer par client (le montant pour
chaque location correspond au tarif du film multiplié par le nombre de jour depuis la
location).
SELECT customer.first_name, customer.last_name,
SUM(film.rental_rate * EXTRACT (days FROM NOW()-rental.rental_date))
AS montant_location_total
FROM film INNER JOIN inventory USING (film_id)
INNER JOIN rental USING (inventory_id)
INNER JOIN customer USING (customer_id)
WHERE rental.return_date IS NULL
GROUP BY customer.first_name, customer.last_name;

Page 9
Corrigé du TP3
Mai 2024

Exercice 1 : PostgreSQL : Stations-service

Questions :

1. Combien y a-t-il de stations-service en France ?

SELECT COUNT(*) FROM pdv ;
13678
2. Combien y a-t-il de stations-service dans le département de l’Essonne (91) ?
SELECT COUNT(*) FROM pdv WHERE cp LIKE '91%' ;
225
3. Quelle est l’horodatage du prix le plus récent en base de données ?
SELECT * FROM prix ORDER BY date_maj DESC NULLS LAST LIMIT 10;
2023-05-10 11:46:00
4. Indiquez le prix moyen, en avril 2023, du carburant dans la ville de Lyon ?
SELECT AVG(prix.valeur)
FROM pdv INNER JOIN prix ON (pdv.id=prix.id_pdv)
WHERE pdv.ville ILIKE 'lyon'
AND prix.date_maj BETWEEN '2023-04-01' AND '2023-04-30';

1.7731045029736619
5. Indiquez le prix médian, en avril 2023, du carburant en France ?
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY prix.valeur)
FROM pdv INNER JOIN prix ON (pdv.id=prix.id_pdv)
WHERE prix.date_maj BETWEEN '2023-04-01' AND '2023-04-30';

1.881

Page 1
6. Indiquez le prix médian, en avril 2023, du carburant en France, par type de carburant ?
SELECT carburants.nom_carburant,
percentile_cont(0.5) WITHIN GROUP (ORDER BY prix.valeur)
FROM pdv INNER JOIN prix ON (pdv.id=prix.id_pdv)
INNER JOIN carburants ON (prix.id_carbu=carburants.id)
WHERE prix.date_maj BETWEEN '2023-04-01' AND '2023-04-30'
GROUP BY carburants.nom_carburant;

nom_carburant | percentile_cont
---------------+-----------------
E10 | 1.909
E85 | 1.119
Gazole | 1.779
GPLc | 0.997
SP95 | 1.942
SP98 | 1.99
7. Pour la station-service de votre choix, créez un tableau montrant l’évolution du prix du carburant
de votre choix (Gazole, SP95, E10…), depuis le 1er janvier 2023.
--Exemple ici station de l’Intermarché de Croissy-Beaubourg (77)
SELECT prix.date_maj,prix.valeur
FROM pdv INNER JOIN prix ON (pdv.id=prix.id_pdv)
INNER JOIN carburants ON (prix.id_carbu=carburants.id)
WHERE carburants.nom_carburant='E10' AND pdv.id='77183001'
ORDER BY prix.date_maj;
8. A partir de la requête précédente, déterminez le pourcentage d’évolution du prix du carburant
choisi entre deux relevés.
SELECT prix.date_maj,prix.valeur,
100.0*(prix.valeur-lag(prix.valeur) OVER (order by prix.date_maj))
/lag(prix.valeur) OVER (order by prix.date_maj) as variation
FROM pdv INNER JOIN prix ON (pdv.id=prix.id_pdv)
INNER JOIN carburants ON (prix.id_carbu=carburants.id)
WHERE carburants.nom_carburant='E10' AND pdv.id='77183001'
ORDER BY prix.date_maj;
9. Le printemps 2023 a été émaillé d’un mouvement social notamment dans l’industrie du pétrole.
Combien de stations ont été au moins une fois en rupture de gazole en mars 2023 ? (attention à
ne pas compter plusieurs fois les stations-service qui ont été en rupture plusieurs fois)
SELECT COUNT(DISTINCT pdv.id)
FROM pdv INNER JOIN rupture ON (pdv.id=rupture.id_pdv)
INNER JOIN carburants ON (rupture.id_carbu=carburants.id)
WHERE rupture.debut_rupture BETWEEN '2023-03-01' AND '2023-03-31'
AND carburants.nom_carburant='Gazole';
3529

Page 2
10. En moyenne en mars 2023, pour les stations qui ont déclaré des ruptures de carburant pendant
plus de 6 heures, combien de temps durait une rupture, tout carburant confondu ? Indice : vous
pouvez exprimer une durée avec un type de données « interval »
https://www.postgresql.org/docs/current/functions-datetime.html .
SELECT AVG(rupture.fin_rupture - rupture.debut_rupture)
FROM pdv INNER JOIN rupture ON (pdv.id=rupture.id_pdv)
WHERE rupture.debut_rupture BETWEEN '2023-03-01' AND '2023-03-31'
AND rupture.fin_rupture - rupture.debut_rupture > interval '6 hours' ;

3 days 22:35:34.950545
11. Mise en situation : vous visitez en voiture le parc naturel régional d’Armorique dans le
département du Finistère. Vous faites une halte sur le plus haut sommet de Bretagne (à Saint-
Michel de Brasparts, altitude 381 mètres, longitude -3.9469141, latitude 48.3498875). Lorsque
vous revenez à votre véhicule, vous vous rendez compte que vous n'avez presque plus de
carburant. Sachant que nous sommes un dimanche, quelles sont les stations ouvertes et
approvisionnées les plus proches (à vol d’oiseau) où vous pourrez faire le plein ?
SELECT DISTINCT pdv.ville,
ST_Distance(pdv.geom,ST_MakePoint(-3.9469141,48.3498875),true) as dst
from pdv INNER JOIN horaires ON (pdv.id=horaires.id_pdv)
LEFT OUTER JOIN rupture ON (pdv.id=rupture.id_pdv)
WHERE rupture.debut_rupture IS NULL
OR (rupture.debut_rupture IS NOT NULL AND rupture.fin_rupture IS NOT NULL)
AND horaires.jour='Dimanche' AND horaires.ferme=false
ORDER BY dst ;

ville | dst
----------------------------------------+------------------
HUELGOAT | 14804.30367322
LE FAOU | 17520.63247299
CHâTEAULIN | 19714.76540384

Page 3
Exercice 2 : Base noms
1. Récupérez sur la page de l’INSEE le fichier .txt de la rubrique "fichiers par département de
naissance". Décompressez-le (unzip nom_fichier.zip). Étudiez également la documentation
pour comprendre la signification des champs.
2. Étudiez le fichier. Il s’agit d’un format TSV (Tab-Separated Values). Quelles sont les colonnes
de ce fichier ?
Ce fichier contient les colonnes suivantes :
1. NOM : nom de famille
2. DEP : département de naissance
3. _1891_1900 : naissances entre 1891 et 1900
4. _1901_1910 : naissances entre 1901 et 1910
5. _1911_1920 : naissances entre 1911 et 1920
6. _1921_1930 : naissances entre 1921 et 1930
7. _1931_1940 : naissances entre 1931 et 1940
8. _1941_1950 : naissances entre 1941 et 1950
9. _1951_1960 : naissances entre 1951 et 1960
10. _1961_1970 : naissances entre 1961 et 1970
11. _1971_1980 : naissances entre 1971 et 1980
12. _1981_1990 : naissances entre 1981 et 1990
13. _1991_20000 : naissances entre 1991 et 2000

3. A partir de vos observations, créez dans la base "prenoms" la table "noms" dotée des colonnes
que vous avez identifiées. ATTENTION : il n’est pas nécessaire de définir une clé primaire
pour cette table. IMPORTANT : il est préférable de créer la table manuellement avec une
instruction SQL CREATE TABLE.
CREATE TABLE noms (nom text, dep text, an1891_1900 int, an1901_1910 int, an1911_1920
int, an1921_1930 int, an1931_1940 int, an1941_1950 int, an1951_1960 int, an1961_1970
int, an1971_1980 int, an1981_1990 int, an1991_2000 int);
4. Étudiez la page de manuel de la commande COPY de Postgres :
https://docs.postgresql.fr/11/sql-copy.html. Importez le fichier dans la table
"noms". Nota bene : le caractère tabulation doit être indiqué de la façon suivante lorsque
vous définissez le séparateur de champ : e’\t’.
L’outil PgAdmin exécute une commande de la forme « COPY noms
FROM ’/CHEMIN/VERS/noms2008dep.txt’ DELIMITER E’\t’ HEADER CSV » ;
5. Affichez quelques statistiques sur votre nom ou celui de votre choix : combien de personnes
nées entre 1991 et 2000 portent ce nom ?
SELECT SUM( an1991_2000 ) FROM noms WHERE nom='XXXX' ;
6. Effectuez un classement par rang des départements de naissance pour votre nom ou celui de
votre choix (exemple : 22 n°1 parce qu’il y a eu le plus de naissance pour ce nom, 35 n°2,
etc.). Utilisez pour ce faire une fonction de fenêtrage permettant de calculer le rang en
fonction du nombre d’occurrences.
SELECT nom,dep, _1891_1900+_1901_1910+_1911_1920+_1921_1930+
_1931_1940+_1941_1950+_1951_1960+_1961_1970+_1971_1980+
_1981_1990+_1991_2000,
RANK() OVER (ORDER BY _1891_1900+_1901_1910+_1911_1920+_1921_1930+

Page 4
 _1931_1940+_1941_1950+_1951_1960+_1961_1970+_1971_1980+
_1981_1990+_1991_2000 DESC)
FROM nom
WHERE nom='XXXX';
Autre solution : créer une vue permettant d’avoir une colonne total calculée :
CREATE VIEW vue_nom AS
SELECT nom,dep, _1891_1900+_1901_1910+_1911_1920+_1921_1930+
_1931_1940+_1941_1950+_1951_1960+_1961_1970+_1971_1980+
_1981_1990+_1991_2000 AS nombre FROM nom;

SELECT nom,dep, nombre,

RANK() OVER (ORDER BY nombre DESC)
FROM vue_nom
WHERE nom='XXXXX';

Page 5
Corrigé TP4 : Premiers pas avec MongoDB
Mai 2024

Exercice 1 : Avis « AirBNB »

1. Affichez le document dont l’identifiant (_id) est ‘102995’
db.listingsAndReviews.find({_id : '102995'})

Ou avec le pipeline d’agrégation :

[
{
$match:
/**
* query: The query in MQL.
*/
{
_id: "102995",
},
},
]

Page 1
 2. Affichez seulement l’adresse de ce document
Find :
db.listingsAndReviews.find({_id : '102995'},{address : 1, _id : 0})

Agrégation :

[
{
$match:
/**
* query: The query in MQL.
*/
{
_id: "102995",
},
},
{
$project:
/**
* specifications: The fields to
* include or exclude.
*/
{
_id: 0,
address: 1,
},
},
]

Page 2
 3. Affichez l’adresse des locations se trouvant au Canada.
Find :
db.listingsAndReviews.find({"address.country" : "Canada"},{address : 1, _id : 0})

Agrégation :
[
{
$match:
/**
* query: The query in MQL.
*/
{
"address.country" : "Canada"
},
},
{
$project:
/**
* specifications: The fields to
* include or exclude.
*/
{
_id: 0,
address: 1,
},
},
]

Page 3
 4. Affichez les différentes zones (« market ») des locations au Canada.
db.listingsAndReviews.find({"address.country" : "Canada"},{"address.market" : 1,
_id : 0})

Agrégation :
[
{
$match:
/**
* query: The query in MQL.
*/
{
"address.country" : "Canada"
},
},
{
$project:
/**
* specifications: The fields to
* include or exclude.
*/
{
_id: 0,
"address.market" : 1
},
},
]

5. Combien y a-t-il de documents dans cette collection ?

db.listingsAndReviews.count()

Agrégation :
[
{
$group:
/**
* query: The query in MQL.
*/
{
_id: null,
nb_total: {
$sum: 1,
},
},
},
]
6. Affichez les noms des locations dont l’hôte est un « superhôte »
db.listingsAndReviews.find({"host.host_is_superhost": true},{_id:0,name:1})
7. Affichez le nombre de locations qui ont reçu des commentaires pour la dernière fois en 2019
db.listingsAndReviews.find({last_review: {$gte: new ISODate('2019-01-
01')}}).count()

Page 4
 8. Combien de locations ont une piscine (« Pool ») ?
# La donnée recherchée est dans un tableau.
# version de base. Ici on utilise l’opérateur $all pour chercher dans l’ensemble
du tableau amenities.
db.listingsAndReviews.find({amenities: {$all:["Pool"]}}).count()

# Si on veut pouvoir tenir compte du fait que parfois il y aura des caractères
avant/après (« swimming pool ») ou que les majuscules/minuscules ne seront pas
respectées, on utilise une expression rationnelle
db.listingsAndReviews.find({amenities: {$all:[/[Pp]ool/]}}).count()
9. Affichez le nombre de locations qui ont plus de 3 chambres.
db.listingsAndReviews.find({bedrooms: {$gte: 3}}).count()
10. Affichez le nombre de locations par pays, classé par ordre décroissant
db.listingsAndReviews.aggregate(
[{
$group: {
_id: '$address.country',
compte: {
$sum: 1
}
}
}, {
$sort: {
compte: -1
}
}])
11. Quel est le prix moyen de la nuitée ?
db.listingsAndReviews.aggregate(
[{
$group: {
_id: null,
moyenne: {
$avg: "$price"
}
}
}])
12. Quel est le prix moyen de la nuitée en fonction de la zone (« market ») ?
db.listingsAndReviews.aggregate(
[{
$group: {
_id: "$address.market",
moyenne: {
$avg: "$price"
}
}
}])

13. Quel est le prix médian de la nuitée ?

Page 5
[
{
$group: {
_id: null,
valeur_mediane: {
$median: {
input: "$price",
method: "approximate",
},
},
},
},
]
14. Combien y a-t-il de commentaires par location, en moyenne ?
db.listingsAndReviews.aggregate([{
$project: {
nombreCommentaires: {
$size: '$reviews'
}
}
}, {
$group: {
_id: null,
moyenneCommentaires: {
$avg: '$nombreCommentaires'
}
}
}])
15. Affichez le nombre de location par hôte (cf champ unique host.host_id), en triant par ordre
décroissant.
db.listingsAndReviews.aggregate(
[{
$group: {
_id: '$host.host_id',
nombreLocation: {
$sum: 1
}
}
}, {
$sort: {
nombreLocation: -1
}
}])

Page 6
Exercice 2 : Cartographie des épaves aux Etats-Unis
1. Affichez les différents niveaux d’eau pouvant (ou pas) recouvrir les épaves ?
db.shipwrecks.find({},{watlev : 1, _id :0})
2. Trouvez l’épave qui se trouve le plus au nord.
db.shipwrecks.find().sort({latdec: -1}).limit(1)
3. Indiquez la profondeur moyenne des épaves qui ne sont pas émergées en permanence.
db.shipwrecks.aggregate(
[{
$match: {
depth: {
$gt: 0
}
}
}, {
$group: {
_id: null,
profmoyenne: {
$avg: '$depth'
}
}
}]
)
4. Indiquez le nombre d’épaves par type (champ feature_type)
db.shipwrecks.aggregate(
[{
$group: {
_id: "$feature_type",
nombre: {
$sum: 1
}
}
}]
)

5. Affichez les épaves situées à 10 kilomètres de la Statue de la Liberté

[
{
$geoNear: {
near: {
type: "Point",
coordinates: [-74.0446292, 40.6891533],
},
distanceField: "distance",
maxDistance: 10000,
spherical: true,
},
},
]

Page 7
 6. Affichez les épaves au large de la Californie.
[
{
$match:
/**
* query: The query in MQL.
*/
{
coordinates: {
$geoWithin: {
$geometry: {
type: "Polygon",
coordinates: [
[
[-126, 41],
[-126, 33],
[-117, 33],
[-124, 41],
[-126, 41],
],
],
},
},
},
},
},
{
$count:
/**
* Provide the field name for the count.
*/
"nb",
},
]
7. Affichez la distance en kilomètres entre l’ENSAE et l’épave américaine la plus proche.
db.shipwrecks.aggregate(
[{
$geoNear: {
near: {
type: 'Point',
coordinates: [
2.205458,
48.7110095
]
},
distanceField: 'distance',
spherical: true
}
}, {
$limit: 1
}, {
$project: {
distkm: {
$divide: [

Page 8
 '$distance',
1000
]
}
}
}]
)

Page 9