TP SQL
Le SQL (Structured Query Language) est un langage permettant de communiquer avec une base de données.
Partie 1 (Guidée): 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)
Vous pourrez trouver des informations pour chaque requête sur ce site par exemple :
https://www.w3schools.com/sql/
Remarque : Les mots clés sont habituellement écrits en MAJUSCULES et les requêtes finissent pas un ” ; “.
Quelques requêtes sur la base livre :
Vous pouvez cliquer sur les mots clés pour obtenir des exemples !
Structure de la base de données :
- Afficher le titre de tous les livres de la médiathèque ;
-> Mots clés utilisé : SELECT, FROM - Afficher l’isbn des livres empruntés de la bibliothèques ;
-> Mots clés utilisé : SELECT, FROM - Afficher le titre et l’année de parution de tous les livres parus entre 1970 et 1980
-> Mots clés utilisé : SELECT, FROM, WHERE, BETWEEN, AND - Afficher toutes les données des livres parus après 1990.
-> Mots clés utilisé : SELECT, FROM, WHERE, > - Afficher le nombre total de livres ;
-> Mots clés utilisé : SELECT, FROM, WHERE, COUNT() - afficher toutes les années de parution sans doublon;
-> Mots clés utilisé : SELECT, FROM, WHERE, DISTINCT - afficher l’isbn et la date de retour de tous les livres empruntés, triés par date de retour ;
-> Mots clés utilisé : SELECT, FROM, WHERE, ORDER BY - Afficher l’isbn du livre dont le titre est “Crime et Châtiment”
-> Mots clés utilisé : SELECT, FROM, WHERE - Afficher l’auteur du livre dont le titre est “Crime et Châtiment” (Utiliser une double requête imbriquée)
-> Mots clés utilisé : SELECT, FROM, WHERE - Donner tous les livres de “Tolstoï” (Utiliser une double requête imbriquée)
-> Mots clés utilisé : SELECT, FROM, WHERE
Partie 2 : Marvel
Vous travaillerez dans cette partie sur la base de données :
Avant de répondre aux questions n’oubliez pas de regarder à quoi ressemble la base de données.
Exercice 1 - Requêtes simples
1. Que sait-on de Hulk ? (1 ligne)
-> Mots clés utilisé : SELECT, FROM, WHERE
2. Quels sont les noms des personnages féminins ? (19 lignes)
-> Mots clés utilisé : SELECT, FROM, WHERE
3. Quelles sont les femmes qui n’ont pas de nom civil connu ? (3 lignes)
-> Mots clés utilisé : SELECT, FROM, WHERE, IS NULL
4. Donner les noms des personnages féminins de l’équipe numéro 1. (3 lignes)
-> Mots clés utilisé : SELECT, FROM, WHERE, AND
5. Afficher les noms des personnages des équipes 1 à 4 classés par numéro d’équipe et par sexe (28 lignes).
-> Mots clés utilisé : SELECT, FROM, WHERE, BETWEEN, AND, ORDER BY
6. Afficher les noms des personnages de l’équipe 1 classés par ordre alphabétique décroissant. (13 lignes).
-> Mots clés utilisé : SELECT, FROM, WHERE, ORDER BY, DESC
7. Donner les noms des personnages dont le nom termine par man. (5 lignes)
-> Mots clés utilisé : SELECT, FROM, WHERE, LOWER(), LIKE
8. Donner les noms des personnages qui ne sont pas d’alignement 1 ni 3. (21 lignes)
-> Mots clés utilisé : SELECT, FROM, WHERE, NOT, OR
Exercice 2 - Expressions et fonctions
1. Combien de personnages sont enregistrés ? (COUNT() )
2. Quel est la plus grande puissance de personnage ? (MAX())
3. Quel est la plus petite puissance de personnage ? (MIN())
4. Quel 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
Exemple : La requête suivante permet de trouver l’ensemble des films dans lesquels apparait Hulk.
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. Quel sont les pouvoirs de Hulk ? (6 lignes)
Exercice 4 - Jointure (JOIN)
Exemple : Afficher le nom des personnages et le nom de l’équipe de chaque personnage.
1. Noms et race des personnages ’mauvais’. (16 lignes)
JOIN
2. Donner la listes des personnages étant des génies scientifiques ou intellectuels (8 lignes).
3. Listes des pouvoirs des personnages de l’équipe ’The Avengers’ (8 lignes).
Exercice 5 - Aggrégation
1. Afficher pour chaque équipe, son nom et le nombre de membre. Les équipes sont triées par e˙ectifs décroissants.
2. Donner pour chaque équipe, son nom et sa puissance moyenne, arrondi au dixième près.
Exercice 6 - Corélation
1. Afficher les races qui comportent un personnage avec une puissance strictement supérieure à 150 (3 lignes)
Exercice 7 - Mise à jour
1. Dans la table personnage, mettez à jour le personnage ’Black Widow’ pour faire passer sa puissance à 40