Exercices vintages 💾

TP2

Author

Ludovic Deneuville

Introduction

Lors du premier TP, nous avons utilisé DuckDB pour lire les données d’un fichier parquet. Aujourd’hui nous allons créer notre propre base de données PostgreSQL, puis y déclarer des tables et insérer de données. Enfin nous allons requêter ces données.

Lors de ce TP, vous allez :

Créer votre base de données PostgreSQL
Lancer des scripts de création de tables et d’insertion de données
Relier des tables en utilisant les jointures
Sélectionner et filtrer des données

Important

Écrivez des requêtes jolies !
À la fin du TP, exportez ou copiez vos requêtes dans un fichier de votre VM ou machine personnelle

1 Lancement des services

Pour plus de détails, allez dans l’onglet Datalab.

Connectez-vous à un Datalab
Lancez un service PostgreSQL
Lancez un service cloudBeaver
- ouvrez ce service
- vérifiez que vous êtes connectés à la base de données PostgreSQL
- ouvrez la connexion (clic droit > Open), un petit rond vert apparait 🟢

Important

cloudBeaver est parfois capricieux, en cas de blocage, n’hésitez pas à rafraichir la page (F5).

En attendant le lancement des services, voici un peu de lecture ci-dessous.

2 Méthodo : Écrire une requête

Il est parfois difficile d’écrire du premier coup LA requête qui répond directement à la question posée.

Voici une méthode qui peut vous aider à écrire vos requêtes pas à pas :

Imaginons que l’on vous demande de lister les noms et prénoms des joueuses du club de la ville de Saint Quentin, dont le prénom commence par un A classées par elo décroissant.

joueuse(id_joueuse, nom, prenom, elo, #id_club)

club(id_club, nom, ville)

2.1 Listez les tables nécessaires

Commençons par lister les tables nécessaires, puis joignons-les.

Nous avons besoin ici des 2 tables que nous pouvons joindre en utilisant la colonne id_club.

SELECT *
  FROM joueuse j
  JOIN club c USING(id_club);

Tip

Nous utilisons pour l’instant SELECT * pour sélectionner toutes les colonnes
- nous enleverons les colonnes inutiles plus tard
Nous utilisons des alias, cela rendra la requête plus courte et plus claire
- j pour joueuse
- c pour club

2.2 Appliquez les filtres

Filtrons pour ne conserver que les lignes qui nous intéressent.

Ville du club : Saint Quentin
Prénom qui commence par un A

SELECT *
  FROM joueuse j
  JOIN club c USING(id_club)
 WHERE c.ville = 'Saint Quentin'
   AND j.prenom LIKE 'A%';

2.3 Ordonnez les résultats

Ordonnons par elo décroissant.

SELECT *
  FROM joueuse j
  JOIN club c USING(id_club)
 WHERE c.ville = 'Saint Quentin'
   AND j.prenom LIKE 'A%'
 ORDER BY j.elo DESC;

2.4 Sélectionner les colonnes requises

Il est demandé de ne garder que les noms et prénoms des joueuses.

Remplaçons le SELECT * par les noms des colonnes.

SELECT j.nom,
       j.prenom
  FROM joueuse j
  JOIN club c USING(id_club)
 WHERE c.ville = 'Saint Quentin'
   AND j.prenom LIKE 'A%'
 ORDER BY j.elo DESC;

Maintenant, place à quelques exercices historiques.

3 Exercices

3.2 Location de DVD 💿

Il fut un temps où des entreprises se spécialisaient dans la location de DVD. Dans cet exercice, une telle entreprise souhaite informatiser la gestion des prêts.

Voici le Modèle de données :

Création de tables

Utiliser le code ci-dessous pour créer le schéma et les 3 tables de droite

DROP SCHEMA IF EXISTS dvd CASCADE;

CREATE SCHEMA dvd;

CREATE TABLE dvd.realisateur(
    num_realisateur    INT PRIMARY KEY,
    nom_realisateur    TEXT,
    prenom_realisateur TEXT);

CREATE TABLE dvd.film(
    num_film       INT PRIMARY KEY,
    titre          TEXT,
    genre          TEXT,
    date_sortie    DATE);

CREATE TABLE dvd.a_realise(
    num_film         INT REFERENCES dvd.film(num_film),
    num_realisateur  INT REFERENCES dvd.realisateur(num_realisateur),
    PRIMARY KEY (num_film, num_realisateur));

Écrivez les requêtes qui permettent de créer les 3 autres tables
Chargez les données avec ce script sql

Tip

Attention, ici l’ordre de création des tables a son importance.

Reprenons et modifions l’exemple ci-dessus :

imaginez que vous essayez de créer en premier la table a_realise
le champ num_film de cette table est une clé étrangère
il référence la clé primaire num_film de la table film
or si la table film n’existe pas, à votre avis, que va-t-il se passer ?

3.3 Rugby World Cup 🏉

Pour les plus rapides 🚀

Nous souhaitons informatiser les résultats de la coupe du monde de rugby. Pour cela nous avons créé les deux tables suivantes : equipe et matches.

Commencez par créez et charger les tables avec ce script.

Donner les noms des équipes qui ont joué à Nantes
Contre quelles équipes a joué l’équipe de France ?
Pour tous les matchs, affichez les informations suivantes :
- Date du match
- Nom de la première équipe
- nombre de points de la première équipe
- nombre de points de la deuxième équipe
- nom de la deuxième équipe et lieu du match
Quels sont les noms d’équipes qui ont gagné au moins deux matchs
Quels sont les noms des équipes qui ont perdu tous leurs matchs
Pour chaque équipe donner le nombre de matchs qu’elles ont gagné ou perdu
Donner le nombre total de points obtenus par équipe en classant les équipes de celle ayant marqué le plus à celle ayant marqué le moins

Solutions

Correction TP2

Arrêtez vos services

C’est la fin du TP, vous pouvez maintenant sauvegarder votre travail et libérer les ressources réservées :

Copiez-collez vos scripts SQL sur votre machine
Arrêter les services du Datalab

Exercices vintages 💾

Introduction

1 Lancement des services

2 Méthodo : Écrire une requête

2.1 Listez les tables nécessaires

2.2 Appliquez les filtres

2.3 Ordonnez les résultats

2.4 Sélectionner les colonnes requises

3 Exercices

3.1 École de musique 🎸

3.2 Location de DVD 💿

Création de tables

Requêtes

3.3 Rugby World Cup 🏉

Arrêtez vos services