Bases de SQL 🖮 Travail n° 1 Installation de SQLiteStudio Télécharger en local depuis le NAS du labo l’installateur de SQLiteStudio (→ \sqlitestudio\SQLiteStudio-X.Y.Z-windows-x64-installer.exe) Procéder à son installation en sélectionnant toutes les options par défaut Lancer l’exécutable comme suggéré à la fin de l’installation4 🖮 Travail n° 2 Extraire les données d’une base de données 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 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 …) 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 (→ ) Dans la fenêtre de l’onglet “SQL Editor”, reproduire et exécuter la requête SQL qui sélectionne tous les enregistrements de la table Pays comme illustré ci-dessous : S’appuyer sur le site SQL.sh pour élaborer les requêtes SQL satisfaisant aux exigences énoncées ci-dessous. Les reporter dans le compte rendu. 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 Afficher l’enregistrement complet du pays dont le code CIO est SWZ Résultat attendu 1 seul enregistrement id codeCIO nom 179 SWZ Swaziland Afficher uniquement le nom du pays dont le code CIO est IOA Résultat attendu 1 seul enregistrement nom Athlètes Indépendants 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 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 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 Afficher par ordre alphabétique les enregistrements complets des pays dont la longueur du nom est supérieure à 10 lettres Résultat attendu 67 enregistrements id codeCIO nom 1 AFG Afghanistan 158 RSA Afrique du Sud 73 GER Allemagne 6 ANT Antigua-et-Barbuda … … … 95 IVB Îles Vierges britanniques 93 ISV Îles Vierges des États-Unis 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 7 enregistrements id codeCIO nom 13 AZE Azerbaïdjan 87 IOA Athlètes Indépendants 104 KSA Arabie saoudite 6 ANT Antigua-et-Barbuda 73 GER Allemagne Allemagne 158 RSA Afrique du Sud 1 AFG Afghanistan 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 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 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 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 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 Afficher les nom, prénom, date de naissance et âge des coureurs qui avaient au maximum 22 ans en 2012 S’appuyer sur : Date And Time Functions How to Calculate the Difference Between Two Dates in SQLite 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 Ajouter un enregistrement dans la table Equipes pour créer une équipe française portant le nom de “Magicrème” 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 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 Détruire l’enregistrement associé à Maurice FOCODEL qui n’est bien sûr pas un coureur mais un directeur sportif Modifier l’enregistrement du coureur nommé “FORTUNA” pour changer son pays d’origne qui est l’Italie et non la France Ajouter le coureur suivant en trouvant un moyen de ne pas avoir à rechercher et spécifier l’id du Pays et de l’équipe dans la requête SQL. 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. Exemple (sans trop d’intérêt…) : 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 Pays retournés par le SELECT situé derrière le FROM (1 seul enregistrement dans ce cas) 🞄 🞄 🞄 SN1/2IR/EC - Bases de données Qt