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 !

1 Lancement des services

1.1 Datalab du GENES ou SSPCloud

Connectez-vous à l’une des 2 instances suivantes d’Onyxia (elles sont quasiment identiques) :

Datalab du GENES
Datalab SSPCloud de l’INSEE
- si besoin de créer un compte, utilisez votre mail ENSAI

Tip

L’instance du GENES est plus récente donc encore en cours de rodage. Celle de l’INSEE est plus ancienne et donc plus robuste.

Pour éviter les problèmes de charges, vous utiliserez selon votre profil :

élèves fonctionnaires ➡️ SSPCloud
élèves ingénieurs ➡️ SSPCloud ou Datalab du GENES

1.2 Base de données PostgreSQL

Lancez le service PostgreSQL
- Lien direct de lancement : Datalab GENES ou SPPCloud
- ou manuellement : Catalogue de services ➡️ Databases ➡️ Lancer
- Configuration Postgresql ➡️ Primary : Mettez tous les paramètres au minimum (Persistent volume size, CPU, Memory)
  - Cela permettra d’éviter de réserver inutilement des ressources dont vous n’aurez pas besoin
- Cliquez sur Lancer
Copiez-collez dans un fichier texte les informations de connexion PostgreSQL : Hostname, Port, Database, Username, Password

1.3 CloudBeaver

Nous venons de créer un SGBD, nous avons maintenant besoin d’un outil de gestion pour s’y connecter et envoyer des requêtes.

Lancez le service CloudBeaver
- Catalogue de services ➡️ Databases
Copiez-collez également les informations de connexion au service CloudBeaver
Ouvrez ce service

Tip

Normalement la connexion à la base de données PostgreSQL est détectée automatiquement.

Dans le cas contraire, il faut cliquer sur le +, puis sur New connection et renseigner les éléments de connexion.

Dans l’explorateur à gauche, clic droit sur bdd PostgreSQL ➡️ SQL Editor

Cela ouvre une fenêtre où vous pouvez écrire du code SQL pour interagir avec votre base de données.

1.4 VM ENSAI

Uniquement en cas de soucis avec les Datalabs, vous pouvez faire le TP sur la VM.

DBeaver sur la VM

Un serveur héberge une base de données PostgreSQL.

Pour vous connecter à votre bdd, lancez le logiciel DBeaver depuis votre VM. Ensuite, uniquement la première fois, vous devrez effectuer le paramètrage ci-dessous et créer la connexion à votre bdd.

Paramètrage

Menu Fenêtre > Preference
- Formatage SQL
  - Casse des mots clefs : UPPER
  - Insert spaces for tabs
  - Appliquer
- Metadonnées
  - Décocher Ouvrir une connexion séparée pour la lecture des métadonnées
- Editeur SQL
  - Décocher Ouvrir une connexion séparée pour chaque éditeur

Création de la connexion PostgreSQL

Cliquer sur l’icone Nouvelle connexion en haut à gauche sous fichier
Sélectionner PostgreSQL puis suivant
- Host : sgbd-eleves.domensai.ecole
- Port : 5432
- Database : idxxxx
- Nom d’utilisateur : idxxxx
- Mot de passe : idxxxx
- Test de connexion
- Acceptez les mises à jour des pilotes
- Terminer

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

Commencez par lister les tables nécessaires, puis joignez les. Ici nous avons besoin des 2 tables.

Utilisez pour l’instant SELECT * pour sélectionner toutes les colonnes.

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

2.2 Appliquez les filtres

Pour ne garder 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

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

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;

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

Arrêtez votre service

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

Copiez votre code et collez le sur votre machine dans un fichier .sql
- par exemple dans P:/Cours1A/UE3-Bases-de-donnees-relationnelles/TP2/tp2.sql
- ou en téléchargeant depuis CloudBeaver le fichier sql (petit bouton avec un dossier et une flèche vers le bas)
Retournez sur la page d’accueil du Datalab
Allez dans Mes Services et supprimez votre service

Exercices vintages

Introduction

1 Lancement des services

1.1 Datalab du GENES ou SSPCloud

1.2 Base de données PostgreSQL

1.3 CloudBeaver

1.4 VM ENSAI

Paramètrage

Création de la connexion PostgreSQL

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 votre service