TP #2 : Requêtes SQL - "on connait la chanson"
Objectifs
L'objectif de ce TP est de commencer l'écriture de requêtes de consultation en SQL

Préparation du TP

Récupération des données

Dans un terminal (icone ), taper la commande suivante:

curl http://www.dequidt.me/uploads/gba/chansons.sql -o chansons.sql

Ouvrir le fichier avec un éditeur de texte, comme par exemple kate ou gedit (éviter si possible libreoffice ou openoffice).

Schéma conceptuel

Reconstruisez un schéma conceptuel à partir du fichier chansons.sql qui définit la création de relations et l’insertion de données de notre base.

Vous pouvez créer votre schéma sur papier ou en utilisant le logiciel modelio:

/usr/localTP/modelio/modelio

Initialisation de la base de données

Ensuite nous allons construire une base de données de chansons en utilisant le fichier chansons.sql. À partir du terminal ouvert précédemment (i.e. pour la récupération des données ou pour lancer modelio), taper les commandes suivantes en remplaçant votreLogin par votre login Polytech Lille (par exemple jdequidt), le mot de passe par défaut est postgres:

export PGHOST=serveur-etu.polytech-lille.fr
export PAGER='less -SFMX'
createdb -U votreLogin votreLoginChansons

Ensuite connectez vous à votre base de données chansons sur le serveur étudiant :

psql -U votreLogin votreLoginChansons

Vous pouvez maintenant créer les relations (c.a.d. les tables) et insérez les données en utilisant notre fichier sql :

\i chansons.sql

Finalement vous pouvez lister les relations avec la commande \d et vous pouvez lister toutes les bases de donées sur le serveur avec \l.

Requêtes SQL

Votre base de données est prête, maintenant vous pouvez interagir avec vos données. Par exemple, pour lister tous les tuples de la table CHANSON, utilisez la requête suivante :

SELECT * FROM chanson;

Pour les questions suivantes, il est conseillé d’écrire vos requêtes dans un fichier (ouvert avec kate ou gedit) enregistré au formal sql (par exemple: corrigeChanson.sql) et de copier-coller les requêtes dans le terminal au fur et à mesure.

  1. Chansons dont le titre commence par m.
  2. Libellé de la catégorie 2
  3. Numéros des catégories dont font partie les disques présents dans la base. (Eviter les doublons)
  4. Nombre de catégories différentes dont font partie les disques présents dans la base. On donnera un libellé correct au résultat.
  5. Liste des personnes dont on connaît le nom et le prénom. On affiche le résultat sur 1 colonne contenant le nom et le prénom. Pour cela, on a besoin de la concaténation sur les chaînes ||. On affichera tout en majuscule (opérateur upper). Trouver un libellé correct pour la colonne.
  6. Liste des disques de Springsteen avec pour chaque disque, le nombre de chansons
  7. Liste des disques qui ont un nombre de chansons supérieur à 3.
  8. Liste des interprêtes du disque sol en si'.
  9. Membres du groupe Téléphone.
  10. Listes des chansons avec le nombre de leurs interprêtes.
  11. Titres et années des chansons de Souchon les plus récentes (i.e.\ par rapport à toutes les chansons de Souchon).
  12. Liste des chansons parues avant 1995 dont le titre contient la.
  13. Liste des couples de chanteurs. Eviter les couples de chanteurs identiques et les couples symétriques (ex: (chamfort, souchon) et (souchon, chamfort)).
  14. Liste des couples de noms différents et non connus comme groupes. Eviter les symétries.
  15. Liste des disques dont le titre est le même que celui d’une de leurs chansons.

Requêtes optionnelles

Nous vous conseillons fortement de continuer avec les requêtes SQL suivantes pour vous améliorer:

  1. Chansons dont le titre contient mens.
  2. Titres des disques classés dans la catégorie rock.
  3. Titres des chansons antérieures à 1990.
  4. Titres des disques parues entre 1990 et 1995, triés par ordre alphabétique.
  5. Nombre de disques par catégorie.
  6. Nombre moyen de chansons par disque.
  7. Liste des interprêtes de rock.
  8. Nombre de chansons interprétées par Alain Souchon.
  9. Titres et années des chansons interprétées par Bruce Springsteen.
  10. Liste des noms des chanteurs qui ne sont pas des d’interprêtes.
  11. Titres et années des chansons les plus récentes (i.e. de l’année la plus récente).
  12. Utiliser l’opérateur UNION pour afficher la liste des interprêtes de rock ou de rap.
  13. Utiliser une requête avec sous-requête pour obtenir les noms qui ne sont pas des interprêtes (cette fois ci, on veut les noms, pas seulement les références).
  14. Liste des disques dont l’année est antérieure aux années des disques interprétés par springsteen
  15. Liste des disques dont l’année est antérieure à au moins l’un des disques interprétés par springsteen