TP SQL

Le SQL (Structured Query Language) est un langage permettant de communiquer avec une base de données.

Partie 1 (Guidé): Médiathèque

Avertissement : Lisez attentivement les instructions.

Pour ce TP vous allez commencer par télécharger et installer le logiciel suivant : DB Browser for SQLite (Cliquez pour télécharger !)

Pendant l’installation cochez « Ajouter un raccourcis sur le bureau ».

Puis la base de donnée suivante :

Lancez (façon de parler) le logiciel DB Browser for SQLite, vous devriez obtenir quelque chose comme ça :

 

Cliquez sur « ouvrir une base de données » et sélectionnez la base livre.db téléchargée précédemment.

Rendez-vous dans « parcourir les données » pour bien visualiser la base de données :

Vous remarquez que la base de données contient 5 tables : auteur, auteur_de, emprunt, livre, usager.

Observez les données dans ces tables pour comprendre à quoi elles correspondent.

Pour la suite vous vous placerez dans « exécuter le SQL« , c’est ici que vous allez faire vos différentes requêtes.

Tapez la requête suivante ‘le symbole ⏵ pour exécuter la requête) :

Cette requête pourrait se traduire par : « Sélectionner tout dans la table auteur » (le * signifie all)

« Sélectionner tout dans la table auteur » s’écrit donc :

SELECT * FROM auteur;

Un autre exemple permettant d’afficher les titre des livres :

SELECT titre FROM livre;

 

Quelques requêtes à effectuer :

Dans cette première partie nous n’utiliserons que les mots clés donnés dans le document suivant :

Vous trouverez de nombreux exemples ici : https://sql.sh/ ou ici https://www.w3schools.com/sql/

Et maintenant à vous de jouer !

Effectuez les requêtes suivantes (vous pouvez cliquer sur les mots clés pour avoir des exemples d’utilisation):

  1. Afficher l’ensemble de la table livre (128 lignes)
    Mots clé à utiliser : SELECT FROM
  2. Afficher l’ensemble des titres de la table livre (128 lignes)
    Mots clé à utiliser : SELECT FROM
  3. Afficher les informations du livre dont le titre est ‘Akira’ (une ligne)
    Mots clé à utiliser : SELECT FROM, WHERE
  4. Afficher les titres des livres dont l’année est 2000 (3 résultats)
    Mots clé à utiliser : SELECT FROM, WHERE
  5. Afficher les informations relatives à l’utilisateur (table usager) dont le nom est ‘BERNARD’ (une ligne)
    Mots clé à utiliser : SELECT, FROM, WHERE
  6. Afficher la liste des auteurs classés par ordre alphabétique
    Mots clé à utiliser : SELECT, FROM, ORDER BY
  7. Afficher le nombre d’usager (une valeur)
    Mots clé à utiliser : SELECT, FROM, count()
  8. Afficher l’isbn du livre de titre « Lolita »
    Mots clé à utiliser : SELECT, FROM, WHERE
  9. Avec deux requêtes successives, donnez le nom de l’auteur du livre dont l’isbn est : 978-2221197691
    Mots clé à utiliser : SELECT, FROM, WHERE (pour les deux requêtes)
  10. En imbriquant deux requêtes, donnez le nom de l’auteur du livre dont l’isbn est : 978-2221197691 (niveau première)
    Mots clé à utiliser : SELECT, FROM, WHERE
  11. Pouvez-vous trouver avec une requête le titre des livres empruntés par Mlle SIMON (niveau terminale)
    Mots clé à utiliser : SELECT, FROM, WHERE, IN

Corrigé de la dernière requête :

SELECT titre 
FROM livre 
WHERE isbn IN (SELECT isbn 
FROM emprunt 
WHERE code_barre = (SELECT code_barre 
FROM usager 
WHERE nom = "SIMON"
)
);

N’hésitez jamais à consulter le Web pour en savoir plus. Par exemple cette page peut vous aider : https://sql.sh/

Partie 2 (mise en pratique) : Marvel

Dans cette deuxième partie nous allons travailler sur la base Marvel.bd qui voici :

marvel

Exercice 1 – Requêtes simples (Seconde – première – Terminale)

  1. Que sait-on de Hulk ? (1 ligne) (SELECT, FROM, WHERE)
  2. Quels sont les noms des personnages féminins ? (19 lignes) (SELECT, FROM, WHERE)
  3. Quelles sont les femmes qui n’ont pas de nom civil connu ? (3 lignes) (SELECT, FROM, WHERE, AND, IS NULL)
    Remarque : On ne peut pas écrire = NULL, il faut écrire IS NULL
  4. Donner les noms des personnages féminins de l’équipe numéro 1. (3 lignes) (SELECT, FROM, WHERE)
  5. Afficher les noms des personnages des équipes 1 à 4 classés par numéro d’équipe et par sexe (28 lignes). (SELECT, FROM,WHERE, ORDER BY, BETWEEN, AND)
  6. Afficher les noms des personnages de l’équipe 1 classés par ordre alphabétique décroissant. (13 lignes). (SELECT, FROM,WHERE, ORDER BY, BETWEEN, AND, DESC)
  7. Donner les noms des personnages dont le nom termine par man. (5 lignes) (SELECT, FROM, WHERE, lower() , LIKE)
  8. Donner les noms des personnages qui ne sont pas d’alignement 1 ni 3. (20 lignes) (SELECT, FROM, WHERE, NOT, OR)
  9. Donner la liste des films classés par note décroissante.

Exercice 2 – Expressions et fonctions (Terminale)

  1. Combien de personnages sont enregistrés ? (COUNT())
  2. Quelle est la plus grande puissance de personnage ? (MAX() )
  3. Quelle est la plus petite puissance de personnage ? (MIN() ) (War machine quoi…)
  4. Quelle est la puissance moyenne des personnages ? (AVG() )
  5. Quelle est la puissance cumulée de tous les personnages ? ( SUM() )

.

Exercice 3 – Requêtes imbriquées (Terminale)

Exemple : Voici la requête qui permet de l’alignement du personnage qui s’appelle Black Widow

SELECT nom FROM alignement
WHERE idalignement = (SELECT idalignement FROM personnage WHERE nom='Black Widow');
  1. Quel est le nom de l’équipe d’Hulk ?
  2. Quelle est la particularité de la race du personnage Ronan ?
  3. Listes les noms des personnes qui ont un alignement mauvais par ordre alphabétique. (16 lignes)
  4. Qui sont les membres de l’équipe d’Hulk ? (13 lignes)
  5. Donner la liste des humains, classés par puissance. (45 lignes)
  6. Dans quels films apparait Thor ? (7 lignes)
  7. Donner la liste des personnages apparaissant dans le film Avengers ? (7 lignes)
  8. Quels sont les pouvoirs de Hulk ? (6 lignes)

Exercice 4 – Jointure (Terminale)

  1. Quels sont les membres de l’équipe de ‘The Avengers’ ? (13 lignes) (INNER JOIN )
  2. Noms et race des personnages ’mauvais’. (16 lignes)
  3. Donner la listes des personnages étant des génies scientifiques ou intellectuels (8 lignes). (DISTINCT)
  4. Listes des pouvoirs des personnages de l’équipe ’The Avengers’ (17 lignes).

Exercice 5 – Agrégation (Terminale)

  1. Afficher pour chaque équipe, son nom et le nombre de membre. Les équipes sont triées par effectifs
    décroissants.
  2. Donner pour chaque équipe, son nom et sa puissance moyenne, arrondi au dixième près.

Exercice 6 – Corrélation (Terminale)

  1. Afficher les races qui comportent un personnage avec une puissance strictement supérieure à 150 (3 lignes)

Exercice 7 – Mise à jour (Terminale)

  1. Dans la table personnage, mettez à jour le personnage ’Black Widow’ pour faire passer sa puissance à 40

Ensuite, vous pouvez vous exercer à quelques requêtes sur ce site : http://chirouble.univ-lyon2.fr/~jdarmont/tutoriel-sql/ (C’est interactif, 20 questions de difficulté croissante)