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:
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:
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:
Ensuite connectez vous à votre base de données chansons sur le serveur étudiant :
Vous pouvez maintenant créer les relations (c.a.d. les tables) et insérez les
données en utilisant notre fichier 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 :
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.
Chansons dont le titre commence par m.
Libellé de la catégorie 2
Numéros des catégories dont font partie les disques présents dans la base.
(Eviter les doublons)
Nombre de catégories différentes dont font partie les disques présents
dans la base. On donnera un libellé correct au résultat.
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.
Liste des disques de Springsteen avec pour chaque disque, le nombre de
chansons
Liste des disques qui ont un nombre de chansons supérieur à 3.
Liste des interprêtes du disque sol en si'.
Membres du groupe Téléphone.
Listes des chansons avec le nombre de leurs interprêtes.
Titres et années des chansons de Souchon les plus récentes (i.e.\ par rapport
à toutes les chansons de Souchon).
Liste des chansons parues avant 1995 dont le titre contient la.
Liste des couples de chanteurs. Eviter les couples de chanteurs identiques et
les couples symétriques (ex: (chamfort, souchon) et (souchon, chamfort)).
Liste des couples de noms différents et non connus comme groupes. Eviter
les symétries.
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:
Chansons dont le titre contient mens.
Titres des disques classés dans la catégorie rock.
Titres des chansons antérieures à 1990.
Titres des disques parues entre 1990 et 1995, triés par ordre alphabétique.
Nombre de disques par catégorie.
Nombre moyen de chansons par disque.
Liste des interprêtes de rock.
Nombre de chansons interprétées par Alain Souchon.
Titres et années des chansons interprétées par Bruce Springsteen.
Liste des noms des chanteurs qui ne sont pas des d’interprêtes.
Titres et années des chansons les plus récentes (i.e. de l’année la plus récente).
Utiliser l’opérateur UNION pour afficher la liste des interprêtes de rock ou
de rap.
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).
Liste des disques dont l’année est antérieure aux années des disques
interprétés par springsteen
Liste des disques dont l’année est antérieure à au moins l’un des disques
interprétés par springsteen