TP2 - SQL - Exercice

Cours de Bases de Données Avancées TP2 - SQL - Exercice
1 Introduction
Cet exercice ou tutorial va vous permettre de créer une petite base de donnée gestion de CD avec le langage
SQL. Cet exercice est divisé en trois parties:
- la partie 2 correspond au langage de définition de données et permet de créer la structure de la base
de données avec notamment les définitions des tables et des contraintes;
- la partie 3 correspond au langage de manipulation de données et permet d'insérer un jeu de données
dans la base
- la partie 4 correspond au langage de requête et permet d'obtenir des informations depuis les données
contenus dans la base
2 Langage de définition de données
Le schéma relationnel (qui est très simplifié et donc pas forcement un bon exemple de modélisation!)
correspondant à la base qu'on veut utiliser est le suivant.
Figure 1: Schéma relationnel de la base de données
Pour créer notre base dans le serveur de base de données il faut d'abord créer les différentes tables du modèle
ainsi que leurs différentes contraintes spécifiées entre parenthèses:
- Table Artiste: caractérisée par un Nom (identifiant), un genre (facultatif) et une nationalité
(facultatif)
- Table Album: caractérisée par un Titre (identifiant), une Année (facultatif, valeur par défaut 2004) et
un Artiste (clé étrangère référençant la table Artiste). Ajouter aussi une contrainte (CHECK) qui
spécifie que l'année doit être compris enter 1900 et 2100.
- Table Chanson: caractérisées par un Titre (identifiant) un Album (clé étrangère référençant la table
Album), un Numero (facultatif) et une durée. A noter que le couple Album et Numero doit être
unique dans toute la table.
3 Langage de définition de données
Une fois que la base est créée il faut insérer des données pour peupler les bases. Vous pouvez utiliser le jeu
de données suivants:
Artiste
Nom Genre Nationalité
Joe Satriani Rock USA
Gordon Haskell Jazz GB
Gotthard Rock CH
Metallica Hard Rock USA
Album
Titre Annee Artiste
Surfing With The Alien 1987 Joe Satriani
Harry''s Bar 2002 Gordon Haskell
Gotthard 1992 Gotthard

Cours de Bases de Données Avancées TP2 - SQL - Exercice
G. 1996 Gotthard
Metallica 1991 Metallica
Chanson
Titre Album Nombre Durée
How Wonderful You Are Harry''s Bar 1 3.07
All the Time in the World Harry''s Bar 2 3.42
Voodoo Dance Harry''s Bar 3 7.37
Freeway to Her Dreams Harry''s Bar 4 6.33
Sunshine in the Night Harry''s Bar 5 3.45
Al Capone Harry''s Bar 6 5.18
Feelin'' Loose Harry''s Bar 7 3.37
There Goes My Heart Again Harry''s Bar 8 5.03
Roll With It Harry''s Bar 9 5.45
Someone I Knew Harry''s Bar 10 5.55
A Little Help from You Harry''s Bar 11 3.09
All in the Scheme of Things Harry''s Bar 12 2.31
Surfing With the Alien Surfing With The Alien 1 7.47
Ice 9 Surfing With The Alien 2 4.08
Crushing Day Surfing With The Alien 3 4.47
Always With Me, Always With You Surfing With The Alien 4 6.40
Satch Boogie Surfing With The Alien 5 7.35
Hill of the Skull Surfing With The Alien 6 4.40
Circles Surfing With The Alien 7 6.44
Lords of Karma Surfing With The Alien 8 4.52
Midnight Surfing With The Alien 9 4.42
Echo Surfing With The Alien 10 3.52
Standing in the Light Gotthard 1 5.19
Downtown Gotthard 2 5.12
Firedance Gotthard 3 7.01
Hush Gotthard 4 2.13
Mean Street Rocket Gotthard 5 4.21
Get Down Gotthard 6 7.01
Take Me Gotthard 7 4.06
Angel Gotthard 8 7.25
Lonely Heartache Gotthard 9 5.38
Hunter Gotthard 10 7.45
All I Care For Gotthard 11 5.40
That''s It Gotthard 12 7.55
Sister Moon G. 1 6.40
Make My Day G. 2 4.13
Mighty Quinn (Quinn the Eskimo) G. 3 2.03
Movin'' On G. 4 5.47
Let It Be G. 5 4.48
Father Is That Enough? G. 6 7.44
Sweet Little Rock & Roller G. 7 4.48
First in Your Face G. 8 3.11
Ride On G. 9 4.23
In the Name G. 10 4.36
Lay Down the Law G. 11 2.38
One Life, One Soul G. 12 4.37
He Ain''t Heavy, He''s My Brother G. 13 5.58
Enter Sandman Metallica 1 4.33
Sad but True Metallica 2 6.11
Holier Than Thou Metallica 3 5.45
The Unforgiven Metallica 4 4.43
Wherever I May Roam Metallica 5 6.17
Don''t Tread on Me Metallica 6 4.21
Through the Never Metallica 7 4.32
Nothing Else Matters Metallica 8 4.55
Of Wolf and Man Metallica 9 7.31
The God That Failed Metallica 10 7.45
My Friend of Misery Metallica 11 3.08
The Struggle Within Metallica 12 4.45
Une fois les données insérées dans la base vous pouvez tester les choses suivantes:
- Créer une nouvelle table à partir d'une requête SQL à l'aide de l'instruction CREATE TABLE AS.
Par exemple vous pouvez créer une table ChansonsUSA qui contient toutes les noms et durées des
chansons dont les artistes sont de nationalité USA.
- Créer une vue à partir d'une requête SQL à l'aide de l'instruction CREATE VEW avec la même
requête que pour la table précédente. Notez ensuite les différences entre table et vue lors de la
modification des données dans la table originale.

Cours de Bases de Données Avancées TP2 - SQL - Exercice
- Modifier une table à partir d'une requête SQL à l'aide de l'instruction ALTER TABLE. Par exemple
vous pouvez modifier la table ChansonsUSA pour y rajouter une colonne "Artiste" avec clé
étrangère référençant la table Artiste.
- Modifier des données à l'aide de l'instruction UPDATE. Rajouter dans la table ChansonsUSA le nom
des artistes correspondants aux chansons grâce à une requête.
- Effacer des données à l'aide de l'instruction DELETE. Effacer toutes les chansons dans la table
ChansonsUSA qui ont une durée supérieur à 5 minutes.
- Effacer une table entière à l'aide de l'instruction DROP. Effacer la table ChansonsUSA.
4 Langage de requête
Amusez-vous à faire différents requêtes sur la base tels que:
- Obtenir le contenu d'un album spécifié par l'utilisateur. NB dans les requêtes on peut utiliser des
variables en utilisant la notation :nom_variable.
- Obtenir la liste des albums avec leurs artistes avec le nombre de chanson et leur durée totale.
- Etc.
5 Solutions
Solutions: les commandes SQL permettant de créer la structure de la base de données ainsi que de la peupler
avec le jeu de données indiqué sont placés dans le fichier "TP2 - SQL - Solutions.txt".