Discographie đ”
Introduction
- Ăcrivez des requĂȘtes jolies !
Les notions vues dans ce TP :
- agrégations
- jointures
- insertion de données
- utilisation de séquence
- bonus : CTE (vu au dernier cours)
1 Lancement des services
-
- ouvrez ce service et ouvrez la connexion PostgreSQL
En cas de besoin, vous trouverez plus dâinformations dans lâonglet Datalab.
Ă la fin du TP :
2 Les données
đ” đȘ đ· Le thĂšme du jour sera la musique. đ„ đž đȘ
2.1 Chargez les données
-
- Cliquez sur le bouton orange avec un triangle ⯠dans un parchemin đ
- Raccourci : ALT + X
2.2 Description
Les tables sont les suivantes :
- artiste(id_artiste, nom, code_pays, date_naissance, actif)
- album(id_album, titre, annee, #id_artiste)
- chanson(id_chanson, #id_artiste_principal, titre, duree, #id_album, annee)
- playlist(id_playlist, nom, date_creation, description)
- playlist_chanson(#id_playlist, #id_chanson, ordre, date_ajout)
2.3 ModÚle de données
3 Exercice
3.1 Exploration des tables
Lorsque vous prenez en main une base de donnĂ©es, la premiĂšre chose Ă faire est de regarder ce que contiennent les tables. đ
-
- lâartiste principal de la chanson
- lâartiste de lâalbum qui contient cette chanson
Faisons quelques statistiques descriptives. đ
-
- ordonnez par nombre de chansons décroissant
-
- đĄ Utilisez la fonction date_trunc() pour arrondir Ă la seconde
Est-ce quâil y a plusieurs chansons ayant le mĂȘme titre ?
-
- i.e. mĂȘme titre et mĂȘme artiste
Si vous en trouvez une, essayez de la supprimer. Ă votre avis, pourquoi cela ne fonctionne pas ? đïž
-
- triez par titre
3.2 Place aux artistes
-
- Classez par ordre décroissant du nombre de chansons
Il y a des artistes solo et des groupes. Pour les groupe la date de naissance ne devrait pas ĂȘtre renseignĂ©e.
-
- đ± si vous avez fait une bĂ©tise, pas de panique, il faut relancer les deux scripts du dĂ©but de TP (create and pop)
Interessons nous maintenant aux pays des artistes.
3.3 Créez votre playlist
Aucune de ces chansons ne correspond Ă vos goĂ»ts musicauxâŠ
Vous ĂȘtes vraiment sĂ»r ?
Allez un petit effort, il y a des chansons et des artistes trĂšs sympas.
Nous allons maintenant ajouter Ă votre playlist toutes les chansons de lâalbum Californian Soil.
Pour crĂ©er votre requĂȘte dâinsertion, vous allez avoir besoin des donnĂ©es suivantes :
- id_playlist, vous pouvez soit :
- insĂ©rer la valeur âen durâ
- utiliser
CURRVAL('playlist_id_playlist_seq')pour obtenir la derniÚre valeur de id_playlist i.e. celui de la playlist que vous venez de créer
- id_chanson : il va falloir une reqûete pour les récupérer
- ordre : vous allez créer et utiliser une séquence
- date_ajout : vous utiliserez simplement :
CURRENT_DATE
Letâs go, step by step :
-
- comme vous avez déjà inséré 5 chansons, les valeurs de la colonne ordre de 1 à 5 sont normalement déjà prises
- commencez votre séquence à la valeur 6
INSERT INTO joueuse_copy(nom, prenom, numero, date_creation)
SELECT nom,
'pierre',
nextval('seq_numero'),
CURRENT_DATE
FROM joueuse;3.4 Votez pour la meilleure playlist
Interessons-nous maintenant aux playlists.
-
- Colonnes Ă afficher : nom de la playlist, ordre, titre de la chanson
- Classez par noms de playlists, puis ordre
Vous remarquez quâĂ lâaffichage vous avez deux colonnes appelĂ©es ânomâ. Pour faire la diffĂ©rence, vous allez modifier lâaffichage de ces colonnes.
Proposez des requĂȘtes pour rĂ©pondre aux questions ci-dessous :
Nous allons maintenant rechercher les artistes mal-aimés i.e. ceux qui ne sont dans aucune playlist.
Procédons par étape:
-
- en utilisant
NOT INet une sous-requĂȘte - conservez uniquement les id_artiste qui ne sont pas dans la requĂȘte prĂ©cĂ©dente
- en utilisant
-
- la syntaxe est un peu moins intuitive mais la requĂȘte est plus optimisĂ©e
Concentrons-nous maintenant sur les artistes et les chansons les plus populaires.
Vous avez Ă©crit prĂ©cĂ©demment une requĂȘte listant les playlists avec les artistes et les chansons. Repartez de cette requĂȘte.
-
- affichez le titre de la chanson, le nom de lâartiste, et le nombre de playlists
- ordonnez par nombre de playlists décroissant
-
- Est-ce que « Tu feras la différence. Ton coeur prend des vacances. Il danse avec les loups. » ?
3.5 Iâm WITH u
Les CTE seront évoquées au dernier cours. Cela parait compliqué à premiÚre vue mais le principe est trÚs simple.
Une CTE (Common Table Expression) est une table temporaire nommĂ©e dĂ©finie au dĂ©but dâune requĂȘte avec le mot-clĂ© WITH.
à quoi ça sert ?
- Rendre une requĂȘte plus lisible (au lieu dâimbriquer des sous-requĂȘtes)
- RĂ©utiliser un mĂȘme sous-rĂ©sultat plusieurs fois
- Servir de base Ă des requĂȘtes rĂ©cursives
Que fait cette requĂȘte ?
SELECT DISTINCT ON (code_pays) code_pays,
nom,
date_naissance
FROM music.artiste
WHERE NOT groupe
ORDER BY code_pays,
date_naissance ASC;Nous allons ici construire pas Ă pas une requĂȘte donnant le mĂȘme rĂ©sultat en utilisant une CTE.
Nous souhaitons maintenant récupérer le nom du plus viel artiste de chaque pays.
LâidĂ©e pour obtenir ce nom :
- stocker le rĂ©sultat de notre derniĂšre requĂȘte dans une table temporaire viel_artiste_pays
- joindre cette table Ă la table artiste via les colonnes pays et date_naissance
WITH viel_artiste_pays AS (
...
)
SELECT ...
FROM ...
JOIN viel_artiste_pays pv ON ...
...Pour vous aider, voici un autre exemple.
Si lâon veut les noms des artistes qui sont les seuls Ă reprĂ©senter leur pays :
- nous commençons par lister les pays avec un seul artiste
- nous joignons avec la table artiste pour récupérer dans un second temps les noms
WITH artiste_seul_dans_pays AS (
SELECT code_pays,
COUNT(*) AS nb_artistes
FROM artiste
GROUP BY code_pays
HAVING COUNT(*) = 1
)
SELECT a.*
FROM artiste a
JOIN artiste_seul_dans_pays n ON a.code_pays = n.code_pays;