Exercices MySQL Série 01


Exercices MySQL Série 01

  1. Objectifs

    • Créer une table avec MySQL ou phpMyAdmin
    • Insérez des données, modifiez des données, supprimez des données avec phpMyAdmin
    • Insérez des données, modifiez des données, supprimez des données avec MySQL
    • Comprendre les avantages et inconvénients de chaque méthode vue en cours
  2. Rappel

    • Pour créer un index UNIQUE, vous utilisez l’instruction CREATE UNIQUE INDEX comme suit : CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);
  3. Exercice 01

    1. Énoncé
        • Ci-après, on donne la représentation textuelle simplifiée d’une base de données concernant l’entreprise “LAVOITURE” spécialisée dans la vente des voitures.
          • Voiture(numSerie, couleur, prixAchat, cout, marque, modele, generation, dateCirculation, serie, typeDeMoteur )
            Magasin(idMagasin, numSerie, dateEntree)
            Client(idClient, nom, prenom, adresse)
            Vendeur(idVendeur, nom, prenom, adresse, salairefixe,IDmagasin)
            Vente(IDVente, dateVente, prixVente, numserie, idClient, idVendeur)
          1. Utilisez une des méthodes vues en cours pour créer la base de données venteVoiture.
          2. Utilisez la deuxième méthode vue en cours pour créer les tables de la base (Attention à l’ordre de création par exemple la table Vente doive être crée en dernier étape).
          3. Sauvegardez les requêtes de création des tables dans un fichier text
          4. Rajoutez les contraintes suivantes :
            1. Un magasin ne peut contenir qu’un numSerie (il s’agit d’une contrainte UNIQUE) :
          5. Utilisez phpMyAdmin pour entrer un nouveau client que vous choisirez. Vérifiez avec l’onglet Afficher.
          6. Écrire les requêtes suivantes en SQL :
            1. Donner la liste des voitures (numéro de série) vendues après le 01 Janvier 2020.
            2. Donner la voiture qui rapportée le plus d’argent.
            3. Donner la liste de tous les voitures stockée dans le magasin numéro 1000120.
            4. Donner le vendeur ayant accordée le plus gros rabais.
            5. Les bénéfices de chaque magasin pour le mois de janvier 2020.
            6. Le meilleur client (celui ayant rapportée le plus d’argent à l’entreprise).
            7. La marque pour laquelle on a accordée le plus de rabais.
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !
  4. Exercice 02

    1. Énoncé
      • Cet exercice est tiré tu TP1 de Découverte de l’environnement MySQL/phpMyAdmin et mise en place d’un entrepôt de données sous MySQL de 2020/2021 de l’université
        1. Objectifs :

          • 1) Découvrir l’environnement MySQL/phpMyAdmin
          • 2) Mettre en place un entrepôt de données en ROLAP en utilisant le système de gestion de bases de données relationnelles MySQL
        2. Consignes pour l’écriture des instructions MySQL :

          • Dans ce TP, ainsi que dans tous les autres TPs, toutes les instructions SQL devront être écrites dans un fichier texte (par exemple avec NotePad, NotePad++ ou TextEdit) afin d’en garder la trace, de très facilement faire des copier/coller dans l’onglet SQL de phpMyAdmin et de pouvoir rendre le travail éventuellement demandé en fin de séance.
          • Le fichier Mini_HypermarcheSTID.csv est issu d’un traitement ETL (Extract-Transform-Load) tel que celui de Tableau Prep Builder, Il contient un sous-ensemble des données du fichier HypermarcheSTID.xls.
          • Pour des raisons de performance, et afin d’éviter de “planter” le serveur, nous n’y avons conservé qu’un nombre réduit de données (300 lignes). Mais le principe que nous allons suivre serait bien sûr exactement le même avec 10000 lignes…
          • Nous allons donc concevoir un entrepôt de données en MySQL pour analyser les ventes de notre hypermarché. Le modèle en étoile proposé pour cet entrepôt de données est le suivant :
          • Exercices MySQL Série 01

          • D’après ce modèle en étoile, le schéma relationnel sous MySQL devrait être le suivant :
          • Comme vous allez le constater, ce schéma relationnel va légèrement évoluer car nous nous apercevrons de quelques points délicats que nous résoudrons. De plus, il sera inutile de créer la table de dimension DATE car nous pourrons manipuler les dates directement avec le langage MySQL pour en extraire les années et les mois. Au final, nous obtiendrons le schéma relationnel suivant :
          • A partir du fichier Mini_HypermarcheSTID, nous allons dans un premier temps construire une table contenant toutes les données, puis à partir de cette table, nous construirons des tables normalisées pour la table de fait VENTES et les tables de dimension CLIENT et PRODUIT.
        3. Les étapes à suivre sont les suivantes :

          • 1) Dans une nouvelle base (ex login0), utilisez la méthode 3 vue en cours (Création d’une table à partir d’un fichier avec phpMyAdmin) pour importer le fichier Mini_HypermarcheSTID.csv
            • Cochez “La première ligne du fichier contient le nom des colonnes de la table”, sinon les noms des colonnes de la table créée ne correspondront pas à l’entête du fichier csv
            • Les séparateurs sont des “;
            • Vérifiez que vous avez réussi à importer 300 lignes dans la table TABLE 1.
          • 2) A partir de cette table, créez la table de dimension PRODUIT grâce à la méthode 4 vue en cours (Création d’une table résultat à partir de tables existantes) :
            • Complétez l’instruction SQL suivante avec les attributs attendus pour la table PRODUIT :
            • Cette instruction crée une nouvelle table PRODUIT dont le schéma est défini par les attributs de la clause SELECT et dont les n-uplets correspondent au résultat de la requête. Le mot DISTINCT est important pour éviter les doublons.
            • Exécutez-la dans l’onglet SQL et vérifiez que la table PRODUIT a été créée, et que 276 produits ont été insérés.
            • L’étape suivante consiste à préciser la clé primaire de la table PRODUIT : il s’agit de IDProduit.
            • Avec phpMyAdmin, essayez d’affecter IDProduit comme clé primaire pour la table PRODUIT : Onglet Structure : cliquez sur CléPrimaire pour l’attribut IDProduit
            • En exécutant, phpMyAdmin nous dit qu’il ne peut pas créer la clé primaire car le produit FOU-CL-10001059 est en doublon. Effectivement il y a eu une erreur et 2 produits différents (‘Cardinal Machine à relier, Clair’ et ‘Acco Couverture de classeur Recycle’ ont le même IDProduit. Vous pouvez le vérifier avec l’onglet Afficher sur la table PRODUIT.
              • FOU-CL-10001059 Cardinal Machine a relier, Clair
              • FOU-CL-10001059 Acco Couverture de classeur, Recycle
            • Nous allons donc modifier l’un des IDProduit avec l’instruction suivante :
            • Vérifiez maintenant que vous pouvez affecter IDProduit comme clé primaire pour la table PRODUIT :
            • Onglet Structure : cliquez sur CléPrimaire pour l’attribut IDProduit.
            • Écrivez la même instruction pour modifier également cet IDProduit dans la table TABLE 1 (car nous en aurons besoin pour créer la table VENTES) :
          • 3) Créez la table CLIENT
            • Si nous essayons de créer de la même manière la table de dimension CLIENT, nous nous apercevrons que le problème de clé primaire est identique : plusieurs clients différents possèdent le même IDClient : il s’agit en réalité des mêmes entreprises clientes situées dans des villes différentes : par ex, le client HF-15715 Hortense Fongemie, est localisé dans 3 villes différentes : Brest, Viroflay et Aix-la-Chapelle.
            • L’IDClient ne peut donc pas être clé primaire de la table CLIENT, de même qu’aucun autre des attributs de la table CLIENT.
            • Nous allons donc créer une clé primaire auto-incrémentée pour la table CLIENT.
            • Utilisez la méthode 2 vue en cours (Création des tables, puis insertion des données en SQL) pour :
            • Créer la table CLIENT avec une clé primaire auto-incrémentée en complétant l’instruction SQL suivante (vérifiez les types des données (ex VARCHAR(8) pour IDClient) dans la structure de TABLE 1) :
            • Une fois la table créée, vérifiez sa structure : Onglet Structure. En particulier vérifiez que la clé primaire est bien l’attribut IDC.
            • Insérez des données dans la table Client en complétant l’instruction SQL suivante :
            • Vérifiez que 138 clients ont été correctement insérés dans la table CLIENT : Onglet Afficher.
          • 4) Créez la table VENTES
            • Il nous reste à créer la table de faits VENTES à partir de TABLE 1.
            • La clé primaire de la table CLIENT ayant changé, la table VENTES doit donc avoir le schéma suivant :
            • VENTES (IDProduit, IDC, DateDeCommande, DateExpedition, IDLigne, IDCommande, ModeExpedition, MontantVente, Quantite, Remise, Profit)

            • où IDC REFERENCES CLIENT(IDC) et IDProduit REFERENCES PRODUIT(IDProduit) et où la clé primaire est (IDProduit, IDC, DateDeCommande, DateExpedition) d’après le modèle en étoile.
            • Utilisez de nouveau la méthode 4 vue en cours (Création d’une table résultat à partir de tables existantes) pour créer la table VENTES en complétant l’instruction SQL suivante :
            • Vérifiez que la table VENTES a été créée avec 300 n-uplets.
            • Constatez les problèmes suivants :
              • Les attributs DateDeCommande, DateExpedition, MontantVente, Remise et Profit ont été créés avec des types inadéquats (cela était déjà le cas dans TABLE 1) : DateDeCommande et DateExpedition auraient dû être de type DATE et MontantVente, Remise et Profit auraient dû être de type NUMERIC ou FLOAT. Nous corrigerons ces problèmes plus tard.
              • D’après le cours de SID, la clé primaire de la table VENTES devrait être la composition des clés primaires des tables de dimension (ici IDC, IDProduit, DateDeCommande, DateExpedition). Or ce n’est pas le cas : par exemple, le produit FOU-CL-10002485 a été commandé 2 fois par le client 74 le 14/06/2017 et expédié le 21/06/2017, mais sur deux lignes de commandes différentes (lignes 152 et 155) ! En réalité, la clé primaire de la table VENTES est IDLigne.
              • Affectez donc IDLigne comme clé primaire de la table VENTES (onglet Structure).
          • 5) supprimer la table TABLE 1
            • Toutes vos données ont été importées, vous pouvez donc supprimer la table TABLE 1.
          • 6) La table VENTES comporte 2 clés référentielles :
              • Clé référentielle 1 : IDC clé référentielle vers CLIENT(IDC)
                Clé référentielle 2 : IDProduit clé référentielle vers PRODUIT(IDProduit)
            • Pour préciser les clés référentielles de la table VENTES : sur la table VENTES, onglet Structure/Vue relationnelle.
            • Créez la clé référentielle 1 : lier la colonne IDC à la colonne IDC de la table CLIENT.
            • Vous pouvez choisir l’un des 4 types d’actions suivants à effectuer ON DELETE (idem avec ON UPDATE) :
            • CASCADE permet, lorsque vous supprimez un client dans la table CLIENT qui existe encore dans la table VENTES, de le supprimer “en cascade” dans la table CLIENT.
            • SET NULL permet, lorsque vous supprimez un client dans la table CLIENT qui existe encore dans la table VENTES, de le mettre à NULL dans la table VENTES.
            • NO ACTION laisse la table VENTES intacte lorsque vous supprimez un client dans la table CLIENT alors qu’il existe encore dans la table VENTES.
            • RESTRICT vous empêche de supprimer un client dans la table CLIENT alors qu’il existe encore dans la table VENTES.
            • Il est conseillé de choisir CASCADE ou RESTRICT afin de conserver une base de données “propre”.
            • Ajoutez une contrainte pour créer de la même manière la clé référentielle 2.
            • Vérifiez vos clés référentielles avec l’onglet Concepteur (il faut cliquer sur la base, par exemple front4 dans la figure suivante, et non sur une table de cette base) : vous devriez obtenir le schéma suivant :
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !



  5. Exercice 03

    1. Énoncé
      • Cet exercice est tiré tu TP2 de Découverte de l’environnement MySQL/phpMyAdmin et mise en place d’un entrepôt de données sous MySQL de 2020/2021 de l’université
      • Afin de ne pas risquer d’endommager les données de notre base de données crées , nous nous servirons dans cet exercice d’une nouvelle table fictive AVIS gérant les avis des clients sur les produits. Cette table est indépendante de notre entrepôt, mais peut être créée “à côté de l’entrepôt”, dans la même base de données que celle utilisée dans le TP1 (login0 par exemple).
      • Le schéma de cette table est le suivant :
      • Un client IDC peut donner son avis composé d’une note (obligatoire) et d’un commentaire (optionnel) sur un produit IDProduit. On enregistre la date de l’avis si elle est connue. Chaque avis est identifié par un id différent auto-incrémenté.
      • 1) Utilisez une des méthodes vues en cours pour créer la table AVIS.
        • Les attributs doivent être définis de la manière suivante :
          • id : de type INT, auto-incrémenté et clé primaire ;
          • IDC : clé référentielle vers CLIENT(IDC), donc du même type, et obligatoire ;
          • IDProduit : clé référentielle vers PRODUIT(IDProduit), donc du même type, et obligatoire ;
          • dateAvis : obligatoire, et dont la valeur par défaut est la date du jour ;
          • note : la note est obligatoirement égale à 1, 2, 3, 4 ou 5 ;
          • commentaire : de type TEXT, et optionnel.
      • 2) Rajoutez la contrainte suivante : un client peut donner son avis sur un produit une seule fois (il s’agit d’une contrainte UNIQUE) :
        • Indice : vous devez utiliser l’instruction SQL suivante qui permet de rajouter une contrainte à la définition de la table AVIS :
      • 3) Utilisez phpMyAdmin pour entrer un nouvel avis d’un client que vous choisirez sur un produit que vous choisirez. Vérifiez avec l’onglet Afficher.
      • 4) Utilisez phpMyAdmin pour tenter d’entrer un nouvel avis du même client sur le même produit. Vérifiez que cela n’a pas fonctionné grâce à la contrainte de la question 2).
      • 5) Ecrivez une instruction SQL pour donner un avis du client 76 sur le produit FOU-AR- 10000080. Vérifiez avec phpMyAdmin.

      • 6) Ecrivez une instruction SQL pour que le client « Audric Binet » insère un avis pour le produit « BIC Carnet de croquis, Bleu », sans connaitre à priori ni l’identifiant de ce client ni l’identifiant du produit. Vous choisirez la note et le commentaire que vous souhaitez donner à cet avis. Vérifiez avec phpMyAdmin.
      • 7) Ecrivez une instruction SQL pour que le client 78 donne la note de 3 à tous les produits, sans commentaire associé. Vérifiez avec phpMyAdmin.
      • 8) Ecrivez une instruction SQL pour que le client 79 donne la note de 5 à tous les produits qu’il a achetés, avec le commentaire “Très bien”. Vérifiez avec phpMyAdmin.
      • 9)
        • Écrivez une instruction SQL pour augmenter de 1 la note du client 78 à tous les produits.
        • Vérifier avec phpMyAdmin.
        • Indice : vous devez utiliser l’instruction UPDATE
      • 10)
        • Écrivez une instruction SQL pour augmenter encore de 1 la note du client 78 à tous les produits de la sous-catégorie Classeurs. Vérifiez avec phpMyAdmin.
        • Indice : vous devez compléter la clause WHERE de l’instruction de la question 9) avec une requête SELECT permettant de retrouver dans la table PRODUIT les IDProduit dont SousCategorie = ‘Classeurs’.
      • 11)
        • Dans la table CLIENT, la ville Irun (ville du Pays Basque espagnol) a été mal saisie : elle est écrite Ir ?n. Or cette ville peut être celle de plusieurs clients et on ne les connait pas tous. Il est donc préférable, plutôt que de passer par l’interface phpMyAdmin pour modifier le nom de cette ville, d’écrire une instruction SQL qui le fasse pour tous les clients. Ecrivez cette instruction, puis vérifiez avec phpMyAdmin.
        • Indice : vous pouvez utiliser le mot clé LIKE avec le caractère spécial _ pour remplacer le ? de la manière suivante : Ville LIKE ‘Ir_n’
      • 12) Dans la table PRODUIT, les sous-catégories etiquettes, elements de fixation et electromenager n’ont pas de majuscules alors que les autres sous-catégories commencent par une majuscule. Ecrivez les instructions SQL permettant de corriger
        cela.
      • 13)
        • Écrivez une instruction SQL pour supprimer les avis du client 78. Vérifier avec phpMyAdmin.
        • Indice : vous devez utiliser l’instruction DELETE
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !




Sommaire du cours Python



Riadh HAJJI

Abonnez vous à notre chaîne YouTube gratuitement