TP de découverte du langage SQL (sans jointure)

Exploration du classement des 500 meilleurs albums (magazine : rolling stone). Quel est l'artiste le plus récompensé ? Combien d'album des Red Hot Chili Pepers dans le classement ? Quel est le meilleur album des Stones ?
Le SQL est là pour nous aider ! 🤘🤘

Nous avons eu l'occasion d'étudier la structure d'une base de données relationnelle lors du chapitre Base de données : conception, nous allons maintenant apprendre à réaliser des requêtes, c'est-à-dire que nous allons apprendre à créer une base des données, créer des attributs, ajouter de données, modifier des données et enfin, nous allons surtout apprendre à interroger une base de données afin d'obtenir des informations.

Pour réaliser toutes ces requêtes, nous allons devoir apprendre un langage de requêtes : SQL (Structured Query Language). SQL est propre aux bases de données relationnelles, les autres types de bases de données utilisent d'autres langages pour effectuer des requêtes.

Nous allons utiliser un logiciel open source : sqlitestudio

Étape 1 : installer le logiciel (étape non nécessaire en classe)

Cliquez sur ce lien et sélectionnez le fichier d'installation correspondant à votre système d'exploitation. (.exe pour windows)

Lancez le logiciel, vous devriez obtenir un interface similaire à celle-ci :

Étape 2 : découverte de l'interface.

Voici une vue de l'interface de SQLiteStudio :

Quelques mots sur SQLite

SQLite est un système de gestion de base de données relationnelle très répandu. Noter qu'il existe d'autres systèmes de gestion de base de données relationnelle comme MySQL ou PostgreSQL. Dans tous les cas, le langage de requête utilisé est le SQL (même si parfois on peut noter quelques petites différences). Ce qui sera vu ici avec SQLite pourra, à quelques petites modifications près, être utilisé avec, par exemple, MySQL.

L'avantage principal, pour notre usage, est que contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais d'être directement intégrée aux programmes. Il en résulte une utilisation moins lourde, en effet, il n'y a pas de serveur à configurer ou à joindre. Cela en fait un système pratique pour les systèmes embarqués (téléphone, raspberry pi ...)

Création d'une base de donnée

Cliquez sur Database / Add Database , vous obtenez la fenêtre suivante :

Cliquez sur le plus pour créer le fichier contenant votre base de donnée.

Dans votre dossier NSI/SQL/ créez le fichier Albums1.db

Cette base de donnée contiendra les 500 plus grands albums de rock selon le classement du magazine "Rolling Stones".

Cliquez ensuite sur l'éditeur SQL

Copiez ensuite le code suivant dans l'éditeur, puis exécutez la requête (F9) ou cliquez sur la flèche bleue 

 

CREATE TABLE Albums (
    Classement   INT  PRIMARY KEY,
    Année        INT,
    Album        TEXT,
    Artiste      TEXT,
    Genre        TEXT,
    [Sous-genre] TEXT
);

La table (ou relation) Albums est ainsi créée avec les domaines correspondants. Cliquez sur la flèche à gauche de Tables pour faire apparaître la nouvelle relation.

Revenons sur cette première requête :

Le CREATE TABLE Albums ne devrait pas vous poser de problème : nous créons une nouvelle table nommée "Albums".

La suite est à peine plus complexe :

nous créons ensuite les attributs :

• Classement 
• Année
• Album
• Artiste
• Genre
• [Sous-genre]

Nous avons pour chaque attribut précisé son domaine : Classement : entier (INT), Album : chaîne de caractères (TEXT), Artiste  : chaîne de caractères, Année : entier (INT), Genre et [Sous-genre] : chaînes de caractères (TEXT)

L'attribut "Classement" va jouer ici le rôle de clé primaire. L'instruction PRIMARY KEY permet de fixer une clé primaire pour la table.

Pour l'instant, notre table est vide. Cliquez sur Données pour le constater.

Nous allons remédier à cela dans la suite de ce T.P.

Peuplement de la base de données.

Exécutez la requête SQL suivante :

 

INSERT INTO Albums (
                       Classement,
                       Année,
                       Album,
                       Artiste,
                       Genre,
                       [Sous-genre]
                   )
                   VALUES (
                       1,
                       1967,
                       'Sgt. Pepper''s Lonely Hearts Club Band',
                       'The Beatles',
                       'Rock',
                       'Rock & Roll, Psychedelic Rock'
                   );

Puis allez dans l'onglet Données. Il peut être nécessaire d'actualiser les données la table (F5)

Nous pouvons constater que l'album des Beatles est bien inséré dans la base de donnée ! 🎸

Analysons cette requête :

INSERT INTO Albums (  ) VALUES (  );  permet d'insérer dans la table Albums les enregistrements souhaités.

Plus précisément, dans la première parenthèse, on peut préciser (ce n'est pas obligatoire) l'ordre des attributs.
Remarque : si cet ordre n'est pas précisé, cela doit être le même que celui utilisé lors de la création de la table.

Dans la parenthèse qui suit VALUES il faut écrire la suite de n-upplet correspondant aux valeurs de l'enregistrement (dans l'ordre choisit).

Un point important est que l'insertion doit respecter les contraintes d'intégrité de la table.  Ainsi un second enregistrement avec la même clé primaire n'est pas possible. Essayez le code suivant :

 

INSERT INTO Albums (
                       Classement,
                       Année,
                       Album,
                       Artiste,
                       Genre,
                       [Sous-genre]
                   )
                   VALUES (
                       1,
                       1966,
                       'Pet Sounds',
                       'The Beach Boys',
                       'Rock',
                       'Pop Rock, Psychedelic Rock'
                   );

Qu'est-ce que vous obtenez ? Pourquoi ?

Réponse (ne cliquez pas, passez juste la souris ici)

Continuons à insérer des albums. Il est possible d'en insérer plusieurs avec une requête :

 

INSERT INTO Albums (
                       Classement,
                       Année,
                       Album,
                       Artiste,
                       Genre,
                       [Sous-genre]
                   )
                   VALUES (
                       2,
                       1966,
                       'Pet Sounds',
                       'The Beach Boys',
                       'Rock',
                       'Pop Rock, Psychedelic Rock'
                   ),
				   (
                       3,
                       1966,
                       'Revolver',
                       'The Beatles',
                       'Rock',
                       'Psychedelic Rock, Pop Rock'
                   ),
				   (
                       4,
                       1965,
                       'Highway 61 Revisited',
                       'Bob Dylan',
                       'Rock',
                       'Folk Rock, Blues Rock'
                   );

Exécutez cette requête puis vérifiez que les enregistrements sont bien insérés dans la B.D.D.

Exercice 1 :

Insérez par vous-même les Albums par l'intermédiaire d'une requête SQL (attention à l'orthographe, copier+coller recommandé)

Astuce très importante, vous pouvez commencer à écrire un mot clé comme SEL puis appuyer sur CTRL+ESPACE pour enclencher la complétion.

Cela est très pratique, notamment pour les noms des attributs.

Classement	Année	Album	Artiste	Genre	Sous-genre
5	1965	Rubber Soul	The Beatles	Rock, Pop	Pop Rock
6	1971	What's Going On	Marvin Gaye	Funk / Soul	Soul

Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️) - (lien alternatif exercice1.txt )

Insertion des 494 autres albums.

Si vous avez suivi toutes les étapes, vous devez avoir une base de 6 albums. Il en manque donc encore 494 ! 🤪

Pour insérer ces derniers, ouvrez le fichier suivant : fichier - (lien alternatif 494_albums.txt ) puis : Attention, collez-le texte dans l'éditeur de requêtes, puis sélectionnez tout le texte (CTRL+A) puis appuyez sur exécuter pour exécuter toutes les requêtes en une fois.

Vous devriez avoir les 500 albums promis ! Vérifiez-le dans l'onglet Données.

Si vous n'arrivez pas à réaliser cette étape, téléchargez ce fichier (lien alternatif 500_greatest_album.sql ).

Les requêtes

Nous allons apprendre à effectuer des requêtes d'interrogation sur la base de données que nous venons de créer.

Toutes les requêtes se feront dans la fenêtre "Requêtes"  de l'onglet "Éditeur de requêtes"

SELECT ... FROM ...

Exécutez la requête suivante :

SELECT Classement,Album,Année,Genre,"Sous-genre" FROM Albums

Vous devriez obtenir un résultat comme ceci :

Comme vous pouvez le constater, notre requête SQL a permis d'afficher tous les albums. Nous avons ici 2 mots clés du langage SQL SELECT qui permet de sélectionner les attributs qui devront être "affichés" (je mets "affichés" entre guillemets, car le but d'une requête sql n'est pas forcément d'afficher les données) et FROM qui indique la table qui doit être utilisée.

Il est évidemment possible d'afficher seulement certains attributs (ou même un seul) :

SELECT Classement, Album FROM Albums

 

 

Exercice 2

Écrivez une requête qui permet d'afficher uniquement les Classements, les Artistes et l'année des 500 albums.

Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️ ou pour vérifier vos réponses) (lien alternatif exercice2.txt )

---

Notez qu'il est possible d'afficher tous les attributs grâce au symbole *

Ainsi la requête suivante est équivalente à la première que nous avons faîte.

SELECT * FROM Albums

---

SELECT ... FROM ... WHERE ...

Pour l'instant nos requêtes affichent tous les albums, il est possible d'utiliser la clause WHERE afin d'imposer une (ou des) condition(s) permettant de sélectionner uniquement certains enregistrements.

La condition doit suivre le mot-clé WHERE : l'expression doit être une expression booléenne. Elle peut être construite à partir des opérateurs (<, <=, >, >=, = et <> ou !=), d'opérations arithmétiques (+, -, *, /, %), de constantes, de nom d'attributs, d'opérateurs logiques (AND, OR et NOT) et d'opérateurs spéciaux pour les textes tels que LIKE.

Par exemple, on veut obtenir la listes des albums sortis entre 1980 et 1990, on pourra écrire :

SELECT Album,Année FROM Albums WHERE Année >= 1980 AND Année <= 1990;

Essayez cette requête et vérifiez que vous obtenez 90 albums

Autre exemple : cherchons les albums des "Red Hot Chili Peppers" OU de "Oasis", la requête sera :

 

SELECT Album,
       Année,
       Artiste
  FROM Albums
 WHERE (Artiste = "Red Hot Chili Peppers" OR 
        Artiste = "Oasis");

Notez que : 

• il est possible de mettre des parenthèses pour améliorer la lisibilité ;
• les espaces et indentations ne sont pas significatifs et sont là aussi pour améliorer la lisibilité ;
• un bouton "Format SQL"  de l'éditeur de SQL peut se charger d'améliorer la mise en forme de vos requêtes.

Votre requête est correcte si elle vous retourne 3 albums (c'est trop peu à mon goût 😜 ) !

• Blood Sugar Sex Magik    1991    Red Hot Chili Peppers
• (What's the Story) Morning Glory?    1995    Oasis
• Californication    1999    Red Hot Chili Peppers

Exercice 3 : requêtes simples sans jointure ni imbrications

Créez les requêtes permettant d'obtenir les informations suivantes :

1. Tous les Albums de la base de donnée Albums (avec doublons pour l'instant)
2. Tous les Artistes de la base de donnée Albums (avec doublons pour l'instant)
3. Les Albums publiés strictement avant 1980
4. Les Classements, Albums et Artistes dont le Classement est compris entre 10 et 20 (bornes comprises)
5. Les Albums, Année et Artistes dont l'album est sorti en 1981 ou en 1991 ou en 2001 ou en 2011.
6. Les Albums, Année et Artistes dont l'album n'est pas sorti entre 2000 et 2010.

Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️ ou pour vérifier vos réponses) - (lien alternatif  exercice3.txt )

 

---

Mots clés supplémentaires : ORDER BY et DISTINCT.

Il est aussi possible de rajouter la clause SQL ORDER BY afin d'obtenir les résultats classés dans un ordre précis. Essayez la requête suivante :

SELECT Album,
       Artiste,
       Année
  FROM Albums
 WHERE Année >= 1980
 ORDER BY Année;

Vous obtiendrez ainsi les albums par ordre croissant d'année de publication. Il est possible de passer à l'ordre décroissant en ajoutant le mot clé : DESC

 

SELECT Album,
Artiste,
       Année
  FROM Albums
 WHERE Année >= 1980
 ORDER BY Année DESC;

Testez ces requêtes pour vérifier leur fonctionnement (pensez à la complétion semi-automatique avec CTRL+ESPACE)

Enfin, il est possible d'enlever les doublons d'un résultat de requête. Par exemple la requête suivante :

 

SELECT Artiste FROM Albums ORDER BY Artiste;

Génère des lignes identiques, et c'est normal. En effet, un artiste peut apparaître à plusieurs reprises dans ce classement.

L'ajout du mot clé DISTINCT après SELECT permet de retirer les doublons, essayez la requête suivante :

SELECT DISTINCT Artiste FROM Albums ORDER BY Artiste;

Vous constaterez la disparition des doublons, cela permet en particulier de répondre à la question : "Quels sont les différents artistes présents dans ce top 500 ?"

 

Exercice 4 :requêtes simples sans jointure ni imbrications

Créez les requêtes permettant d'obtenir les informations suivantes :

1. Tous les Artistes de la base de donnée Albums (sans doublons)
2. Toutes les années de la base sans doublons.
3. Toutes les années de la base sans doublons triées par années décroissantes.
4. Les Albums et Années des  albums publiés strictement avant 1980 triés par années décroissantes
5. Les Classements, Albums et Artistes dont le Classement est compris entre 10 et 20 (bornes comprises) triés par ordre alphabétique des artistes
6. Les Albums, Année et Artistes dont l'album n'est pas sorti entre 1980 et 2000, triés par année croissante.

Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️ ou pour vérifier vos réponses) - (lien alternatif exercice4.txt )

---

Compléments : COUNT, AVG, MIN, MAX et LIKE.

Il est aussi possible d'appeller des fonctions d'agrégation. Ces dernières permettent d'appliquer une fonction à l'ensemble des valeurs d'une colonne et de renvoyer le résultat comme une table ayant une seule case (une ligne et une colonne).

En voici quelques-unes :

• COUNT : permet de compter le nombre de résultats d'une requête
• SUM : permet de faire la somme des résultats d'une colonne (numérique) d'une requête
• MIN, MAX : permet de trouver le minimum (resp : maximum) des résultats d'une colonne d'une requête
• AVG : permet de faire la moyenne (average) des résultats d'une colonne (numérique) d'une requête

Au passage, parlons aussi de LIKE, ce mot clé permet de recherche dans les chaines de caractère des résultat approchant ce que l'on souhaite.

Par exemple :

SELECT Année,
       Album,
       Artiste
  FROM Albums
 WHERE Artiste LIKE "%CHILI%"

 

est une requête qui permet de chercher dans les artistes ceux dont le nom contient "CHILI" (la casse ne compte pas). Les caractères % sont des jokers qui peuvent être substitués par n'importe qu'elle chaine. Ainsi, cette requête donnera comme résultat :

---

Exercice 5 :

A titre d'entrainement, écrire une requête qui donne :

1. Tous les noms d'artistes (distincts) qui contienne le mot : stone
2. Tous les noms d'album qui commencent par le mot : the
3. Tous les noms d'album qui finissent par un point d'exclamation.
4. Tous les classement, artistes, genre et nom d'albums qui appartiennent au genre rock (sous-genre exclus)

Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️ ou pour vérifier vos réponses) - (lien alternatif  exercice5.txt )

---

Revenons à nos fonction d'agrégation, en voici un exemple :

SELECT count( * ) as total
  FROM Albums
 WHERE Artiste = "The Beatles";

 

Nous avons renommé la colonne avec le mot clé AS. La fonction COUNT pouvant s'appliquer sur n'importe quelle colonne (le résultat sera le même), il est courant de l'appeller via COUNT(*).

Cette requête nous apprend que 10 albums de cette liste viennent des beatles.

Les autres fonctions d'agrégation fonctionnent de la même manière, testez la requête suivante :

SELECT avg(Année) AS moyenne,
        sum(Année) AS Somme
  FROM Albums

 

Cela vous donnera la moyenne (et la somme) des années des albums de ce top 500.

Et enfin, testez la requête :

SELECT min(Artiste),
        max(Artiste)
  FROM Albums

 

On notera que les fonctions min et max peuvent s'appliquer sur des attributs dont les domaines ne sont pas numériques (pour des chaines de caractères, c'est alors l'ordre alphanumérique qui s'applique)

---

Exercice 6:

Créez les requêtes permettant d'obtenir les informations suivantes :

1. Le nombre d'albums des "The Rolling Stones" dans ce classement
2. Les classements minimal et maximal des albums de "Bob Dylan"
3. La moyenne des années des albums dont le genre contient "Blues"
4. La moyenne des années des albums dont le genre contient "Electronic"
5. Les noms des albums contenant le mot "love" le mieux classé et le moins bien classé.

 Réponse (ne cliquez qu'en dernier recours ☠️☠️☠️ ou pour vérifier vos réponses) - (lien alternatif  exercice6.txt )

SELECT *
  FROM ALBUMS
       JOIN
       ARTISTES ON ALBUMS.ID_artiste = ARTISTES.ID_artistes;

SELECT *
  FROM ARTISTES
       JOIN
       ALBUMS ON ALBUMS.ID_artiste = ARTISTES.ID_artistes;

SELECT Album, Artiste, ALBUMS.ID_artiste
  FROM ARTISTES
       JOIN
       ALBUMS ON ALBUMS.ID_artiste = ARTISTES.ID_artistes;

SELECT Classement,
       Album,
       Artiste
  FROM ARTISTES
       JOIN
       ALBUMS ON ALBUMS.ID_artiste = ARTISTES.ID_artistes
 WHERE Classement <= 20;

SELECT avg(Classement), Artiste
  FROM ARTISTES
       JOIN
       ALBUMS ON ALBUMS.ID_artiste = ARTISTES.ID_artistes
 WHERE ID_artistes = 1;

SELECT nom,
       prenom,
       Album,
       num_collectionneur
  FROM ALBUMS
       JOIN
       POSSEDER ON ALBUMS.Classement = POSSEDER.num_classement
       JOIN
       COLLECTIONNEURS ON POSSEDER.num_collectionneur = COLLECTIONNEURS.id_collectionneur
 ORDER BY nom;

SELECT nom,
       prenom,
       Album,
       Art.Artiste
  FROM ALBUMS AS A
       JOIN
       POSSEDER AS P ON A.Classement = P.num_classement
       JOIN
       COLLECTIONNEURS AS C ON P.num_collectionneur = C.id_collectionneur
       JOIN
       ARTISTES AS Art ON Art.ID_artistes = A.ID_artiste
 ORDER BY nom;

SELECT Album
  FROM ALBUMS
 WHERE Année = (
                   SELECT MIN(Année) 
                     FROM ALBUMS
               );

SELECT Album
  FROM ALBUMS
 WHERE Année = (
                   SELECT Année
                     FROM ALBUMS
                    WHERE Album = "Kid A"
               );

SELECT Album, Année
  FROM ALBUMS
 WHERE Année = (
                   SELECT Année
                     FROM ALBUMS
                    WHERE Classement <= 20
               );

SELECT Album, Année
  FROM ALBUMS
 WHERE Année IN (
                   SELECT Année
                     FROM ALBUMS
                    WHERE Classement <= 20
               );

DELETE FROM ALBUMS WHERE ID_artiste=1;

SELECT * FROM POSSEDER WHERE num_collectionneur = 1;

DELETE FROM COLLECTIONNEURS WHERE id_collectionneur = 1 OR id_collectionneur = 2;

UPDATE COLLECTIONNEURS SET nom = 'JACOBI' WHERE nom = 'JACOB';

UPDATE ALBUMS SET Année = Année + 1000 WHERE Genre = 'Electronic';

INSERT INTO COLLECTIONNEURS VALUES (101, "LE YETI", "Mike");

INSERT INTO COLLECTIONNEURS (nom,prenom,id_collectionneur) VALUES ( "LE YETI", "Mike", 101);

INSERT INTO COLLECTIONNEURS (nom, prenom, id_collectionneur)
    VALUES 
        ( "TURING", "Alan", 102),
        ( "VON NEUMANN", "John", 103),
        ( "LOVELACE", "Ada", 104),
        ( "BERNERS-LEE", "Tim", 105);