Bases de SQL

💻 Travail n° 1 Installation de SQLiteStudio

🎯 Travail à faire :

  1. Télécharger en local depuis le NAS du labo l’installateur de SQLiteStudio (→ \sqlitestudio\SQLiteStudio-X.Y.Z-windows-x64-installer.exe)

  2. Procéder à son installation en sélectionnant toutes les options par défaut

  3. Lancer l’exécutable comme suggéré à la fin de l’installation

💻 Travail n° 2 Extraire les données d’une base de données

🎯 Travail à faire :

  1. Télécharger la base de données SQLite servant de support pour ce travail.

    Cette base de données — contenant des données sur le Tour de France 2012 — est constituée de 3 tables :

    • Pays : l’ensemble des pays répertoriés par un code CIO au niveau mondial

    • Equipes : les équipes participant au tour de France avec leur nom et leur pays

    • Cyclistes : les coureurs avec leur nom, prénom, pays d’origine, équipe, taille, poids, date de naissance

  2. Ouvrir cette base de données avec SQLiteStudio via le menu Database  Add a database et analyser sa structure (clé primaires, clés étrangères, types des champs …​)

    mcd tdf2012

  3. Sélectionner la base de données avec la souris puis s’y connecter soit en double-cliquant dessus soit via le menu contextuel (→ “Connect to the database”) soit via l’icône de la barre d’outil représentant une prise de courant (→ connect icon)

  4. Dans la fenêtre de l’onglet “SQL Editor” — accessible via le menu Tools  Open SQL Editor --, reproduire et exécuter la requête SQL qui sélectionne tous les enregistrements de la table Pays comme illustré ci-dessous :

    sqlitestudio sql editor

  5. S’appuyer sur le site SQL.sh link pour élaborer les requêtes SQL satisfaisant aux exigences énoncées ci-dessous. Les reporter dans le compte rendu.

    1. Afficher uniquement les colonnes id et nom de tous les enregistrements de la table Pays

      Résultat attendu

      205 enregistrements

      id nom

      1

      Afghanistan

      2

      Albanie

      …​

      …​

      205

      Zimbabwe

    2. Afficher l’enregistrement complet du pays dont le code CIO est SWZ

      Résultat attendu

      1 seul enregistrement

      id codeCIO nom

      179

      SWZ

      Swaziland

    3. Afficher uniquement le nom du pays dont le code CIO est IOA

      Résultat attendu

      1 seul enregistrement

      nom

      Athlètes Indépendants

    4. Afficher les enregistrements complets des pays par ordre alphabétique des noms

      Résultat attendu

      205 enregistrements

      id codeCIO nom

      1

      AFG

      Afghanistan

      158

      RSA

      Afrique du Sud

      2

      ALB

      Albanie

      3

      ALG

      Algérie

      73

      GER

      Allemagne

      …​

      …​

      …​

      95

      IVB

      Îles Vierges britanniques

      93

      ISV

      Îles Vierges des États-Unis

    5. Afficher les enregistrements complets des pays par ordre alphabétique inverse des codes CIO

      Résultat attendu

      205 enregistrements

      id codeCIO nom

      205

      ZIM

      Zimbabwe

      204

      ZAM

      Zambie

      203

      YEM

      Yémen

      …​

      …​

      …​

      1

      AFG

      Afghanistan

    6. Afficher les enregistrements complets des pays dont le nom débute avec la lettre ‘A’

      Résultat attendu

      16 enregistrements

      id codeCIO nom

      1

      AFG

      Afghanistan

      2

      ALB

      Albanie

      3

      ALG

      Algérie

      …​

      …​

      …​

      104

      KSA

      Arabie saoudite

      158

      RSA

      Afrique du Sud

    7. Afficher par ordre alphabétique les enregistrements complets des pays dont la longueur du nom est supérieure à 10 lettres

      Résultat attendu

      66 enregistrements

      id codeCIO nom

      1

      AFG

      Afghanistan

      158

      RSA

      Afrique du Sud

      6

      ANT

      Antigua-et-Barbuda

      …​

      …​

      …​

      95

      IVB

      Îles Vierges britanniques

      93

      ISV

      Îles Vierges des États-Unis

    8. Afficher par ordre alphabétique inverse les enregistrements complets des pays dont le nom débute par ‘A’ et comporte plus de 10 caractères

      Résultat attendu

      6 enregistrements

      id codeCIO nom

      13

      AZE

      Azerbaïdjan

      87

      IOA

      Athlètes Indépendants

      104

      KSA

      Arabie saoudite

      6

      ANT

      Antigua-et-Barbuda

      158

      RSA

      Afrique du Sud

      1

      AFG

      Afghanistan

    9. Afficher les enregistrements complets des 12 pays qui se trouvent à partir de la 181ième position de la table Pays.

      Résultat attendu

      12 enregistrements

      id codeCIO nom

      181

      TAN

      Tanzanie, République-Unie de

      182

      TGA

      Tonga

      183

      THA

      Thaïlande

      184

      TJK

      Tadjikistan

      …​

      …​

      …​

      192

      TUV

      Tuvalu

    10. Afficher les n° de dossard (colonne id), nom, prénom et équipe des coureurs classés par n° de dossard

      Résultat attendu

      198 enregistrements

      id nom prenom nom:1

      1

      EVANS

      Cadel

      BMC RACING TEAM

      2

      BURGHARDT

      Marcus

      BMC RACING TEAM

      3

      CUMMINGS

      Stephen

      BMC RACING TEAM

      …​

      …​

      …​

      …​

      218

      TIMMER

      Albert

      ARGOS-SHIMANO

      219

      VEELERS

      Tom

      ARGOS-SHIMANO

    11. Afficher les noms de tous les cyclistes français accompagnés du nom de leur équipe

      Résultat attendu

      43 enregistrements

      nom prenom nom:1

      MOINARD

      Amaël

      BMC RACING TEAM

      GALLOPIN

      Tony

      RADIOSHACK-NISSAN

      VÖECKLER

      Thomas

      TEAM EUROPCAR

      …​

      …​

      …​

      HUGUET

      Yann

      ARGOS-SHIMANO

      SPRICK

      Matthieu

      ARGOS-SHIMANO

    12. Afficher les n° de dossard (colonne id), nom, prénom et équipe des cyclistes appartenant aux équipes françaises

      Résultat attendu

      45 enregistrements

      id nom prenom nom:1

      21

      VÖECKLER

      Thomas

      TEAM EUROPCAR

      22

      ARASHIRO

      Yukiya

      TEAM EUROPCAR

      23

      BERNAUDEAU

      Giovanni

      TEAM EUROPCAR

      …​

      …​

      …​

      …​

      148

      ROY

      Jérémy

      FDJ-BIGMAT

      149

      VICHOT

      Arthur

      FDJ-BIGMAT

    13. Afficher les cylistes d’origine étrangère faisant partie des équipes françaises

      Résultat attendu

      8 enregistrements

      id nom prenom nom:1

      22

      ARASHIRO

      Yukiya

      TEAM EUROPCAR

      28

      MALACARNE

      Davide

      TEAM EUROPCAR

      79

      ROCHE

      Nicolas

      AG2R LA MONDIALE

      …​

      …​

      …​

      …​

      89

      ZINGLE

      Romain

      COFIDIS LE CREDIT EN LIGNE

      143

      HUTAROVICH

      Yauheni

      FDJ-BIGMAT

    14. Afficher les nom, prénom, date de naissance et âge des coureurs qui avaient au maximum 22 ans en 2012

      S’appuyer sur :
      Résultat attendu

      3 enregistrements

      nom prenom dateNaissance âge en 2012

      SAGAN

      Peter

      1990-01-26

      22

      PINOT

      Thibaut

      1990-05-29

      22

      CANTWELL

      Jonathan

      1992-01-08

      20

💻 Travail n° 3 Modification de données dans une base de données

  1. Ajouter un enregistrement dans la table Equipes pour créer une équipe française portant le nom de “Magicrème”

  2. Ajouter un enregistrement dans la table Cyclistes pour créer un nouveau coureur avec les attibuts suivants :

    Nom Prénom Pays Équipe

    LAMBERT

    Ghislain

    France

    Magicrème

    Les autres attributs n’étant pas renseignés

  3. Ajouter en une seule requête les coureurs suivants :

    Nom Prénom Pays Équipe

    FORTUNA

    Ricardo

    France

    Magicrème

    FOCODEL

    Maurice

    France

    Magicrème

    Les autres attibuts n’étant pas renseignés

  4. Détruire l’enregistrement associé à Maurice FOCODEL qui n’est bien sûr pas un coureur mais un directeur sportif

  5. Modifier l’enregistrement du coureur nommé “FORTUNA” pour changer son pays d’origne qui est l’Italie et non la France

  6. Ajouter le coureur suivant en trouvant un moyen de ne pas avoir à spécifier manuellement les valeurs numérique de l’id du Pays et de l’équipe dans la requête SQL mais plutôt en spécifiant leurs noms

    Nom Prénom Pays Équipe

    BOUILLON

    Fabrice

    France

    Magicrème

    Il est possible en SQL de remplacer un paramètre de la requête (valeur, table) par le résultat d’une autre requête : c’est ce qu’on appelle des requêtes imbriquées.

    Exemple :

    SELECT nom, codeCIO FROM (SELECT * FROM Pays WHERE codeCIO = 'FRA'); (1)
    1 Affichage du nom et du code CIO de l’ensemble des enregistrements de la table retournée par le SELECT situé derrière le FROM (1 seul enregistrement dans ce cas)

    Cet exemple est bien sûr sans intérêt ici puisqu’on obtient le même résultat avec la requête SQL plus simple suivante :

    SELECT nom, codeCIO FROM Pays WHERE codeCIO = 'FRA';

🞄  🞄  🞄