{"cells":[{"metadata":{},"cell_type":"markdown","source":"Dans ce TP nous allons découvrir le **langage SQL** (Structured Query Language) qui est le langage utilisé pour effectuer des requêtes sur une base de données relationnelle.\n\n# Analyse préliminaire de la base de données\n## Sur quoi allons-nous travailler ?\nNous allons manipuler une base de données permettant de traduire efficacement les informations contenues dans ce tableau résumé (*exemple du cours*) :\n\n\nTitre | Nom Auteur | Prenom Auteur | Annee Naissance | Annee Publi | Langue | Themes\n--- | --- | --- | ---| --- | --- | ---\n1984 | Orwell | George | 1903 | 1949 | Anglais | Totalitarisme, science-fiction, anticipation, Dystopie\nDune | Herbert | Frank | 1920 | 1965 | Anglais | science-fiction, anticipation\nFondation | Asimov | Isaac | 1920 | 1951 | Anglais | science-fiction, Economie\nLe meilleur des mondes | Huxley | Aldous | 1894 | 1931 | Anglais | Totalitarisme, science-fiction, anticipation, Dystopie\nFahrenheit 451 | Bradbury | Ray | 1920 | 1953 | Anglais | science fiction, dystopie\nUbik | K. Dick | Philip | 1928 | 1969 | Anglais | science-fiction, anticipation\nChroniques martiennes | Bradbury | Ray | 1920 | 1950 | Anglais | science-fiction, anticipation\nLa nuit des temps | Barjavel | René | 1911 | 1968 | Anglais | science-fiction, tragédie\nBlade Runner | K. Dick | Philip | 1928 | 1968 | Anglais | Intelligence artificielle, science fiction\nLes Robots | Asimov | Isaac | 1920 | 1950 | Anglais | Totalitarisme, science-fiction, anticipation, Dystopie\nLa Planète des singes | Boulle | Pierre | 1912 | 1963 | Francais | Totalitarisme, science-fiction, anticipation, Dystopie\nRavage | Barjavel | René | 1911 | 1943 | Francais | Science-Fiction, aventure\nLe Maître du Haut Château| K. Dick | Philip | 1928 | 1962 | Anglais | Dystopie, Uchronie\nLe monde des A | Van Vogt | Alfred | 1912 | 1945 | Anglais | science fiction, IA\nLa Fin de l’éternité | Asimov | Isaac | 1920 | 1955 | Anglais | science-fiction, voyage dans le temps\nDe la Terre à la Lune| Verne | Jules | 1828 | Francais | Anglais | Science-Fiction, aventure\n\n\nLa base est déjà créée, et partiellement remplie.\n\nL'objectif est d’interroger et de faire des mises à jour dans cette base. Cette base est constituée de 5 tables :\n- Auteur\n- Livre\n- Langue\n- Theme\n- RelationLivreTheme\n\n## Schéma des tables\nLe schéma relationnel des tables est donné dans le cours.\n\nPrendre le temps de consulter le schéma détaillé de chaque table : observer en particulier les types des attributs et relever les clefs primaires (PRIMARY KEY) ou clefs étrangères (FOREIGN KEY) dans chaque table.\n\nPar exemple, l'attribut IdLangue est la clef primaire de la relation Langue. C'est aussi une clef étrangère pour la relation Auteur."},{"metadata":{},"cell_type":"markdown","source":"## Lire le contenu d'une table : SELECT * FROM Table;\n\nNous allons écrire une première **requête d’interrogation** avec la commande **SELECT** afin de récupérer le contenu d'une table. Ces requêtes peuvent être beaucoup plus sophistiquées comme on le verra dans le 2ème TP. Pour le moment, nous nous contenterons de la forme la plus simple :"},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM Langue;","execution_count":1,"outputs":[{"output_type":"execute_result","execution_count":1,"data":{"text/plain":"IdLangue\tLangue\n1\tAnglais\n2\tFrançais","text/html":"
IdLangueLangue
1Anglais
2Français
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"\nOn voit ainsi apparaître le contenu de la table.\n\n**Attention :** Les requêtes SQL **se terminent toujours par un point-virgule**.\nElles peuvent s'écrire sur plusieurs lignes.\nElles sont non sensibles à la casse, mais c'est une bonne pratique que d'écrire toutes les commandes SQL en majuscules. \n"},{"metadata":{},"cell_type":"markdown","source":"## Insérer des enregistrements : INSERT INTO Table VALUES (données) ;\n\n### Exemple : table Langue\n\nVisualiser son contenu actuel :"},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer\n","execution_count":2,"outputs":[{"output_type":"execute_result","execution_count":2,"data":{"text/plain":"IdLangue\tLangue\n1\tAnglais\n2\tFrançais","text/html":"
IdLangueLangue
1Anglais
2Français
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"Pour remplir une table avec des données, il faut utiliser une requête **INSERT**.\n\nVoici par exemple comment on a rempli la table Langue :\n\n INSERT INTO Langue VALUES (1, \"Anglais\"), (2, \"Français\");\n\nOn peut refaire une autre requête INSERT à la suite si on souhaite ajouter encore des données\nau bout de la table.\n\nAjouter ainsi une langue Espagnol et Allemand. Puis afficher (requête d’interrogation) le contenu de la table."},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer\n","execution_count":3,"outputs":[{"output_type":"execute_result","execution_count":3,"data":{"text/plain":"IdLangue\tLangue\n1\tAnglais\n2\tFrançais\n3\tEspagnol\n4\tAllemand","text/html":"
IdLangueLangue
1Anglais
2Français
3Espagnol
4Allemand
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"\nEssayer d’exécuter la requête suivante :"},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Langue VALUES (2, \"Turc\");","execution_count":4,"outputs":[{"output_type":"stream","text":"Error: UNIQUE constraint failed: Langue.IdLangue","name":"stderr"},{"output_type":"error"}]},{"metadata":{},"cell_type":"markdown","source":"Expliquer l’origine du message d’erreur qui apparaît."},{"metadata":{},"cell_type":"markdown","source":"## Table Auteur\n\nCompléter la table Auteur afin que celle-ci reflète les informations suivantes (penser à\nrevoir le schéma de la table) :\n\n\nNom | Prenom | annee naissance | langue\n--- |--- |--- | --- \nOrwell | George | 1903 | Anglais\nHerbert | Frank | 1920 | Anglais\nAsimov | Isaac | 1920 | Anglais\nHuxley | Aldous | 1894 | Anglais\nBradbury | Ray | 1920 | Anglais\nK. Dick | Philip | 1928 | Anglais\nBarjavel | René | 1911 | Français\nBoulle | Pierre | 1912 | Français\nVan Vogt | Alfred Elton | 1912 | Anglais\nVerne | Jules | 1828 | Français\n\nUne nouveauté apparaît ici : la table Auteur possède une **clef étrangère** définie par la ligne :\n\n FOREIGN KEY(IdLangue) REFERENCES Langues(IdLangue)\n\nCelle-ci permet de déclarer une **contrainte de référence** sur cette clef afin d’indiquer à au SGBD\nque IdLangue est une clef étrangère. LE SGBD sera alors responsable de maintenir la cohérence\nentre les deux tables que l’on a ainsi reliées.\nSi on essaie d’entrer un enregistrement avec une valeur qui n’existe pas pour une\nclef étrangère, le SGBD envoie un message d’erreur."},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer\n","execution_count":5,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"-- cellule de contrôle\nSELECT * FROM Auteur;","execution_count":6,"outputs":[{"output_type":"execute_result","execution_count":6,"data":{"text/plain":"IdAuteur\tNomAuteur\tPrenomAuteur\tIdLangue\tAnneeNaissance\n1\tOrwell\tGeorge\t1\t1903\n2\tHerbert\tFrank\t1\t1920\n3\tAsimov\tIsaac\t1\t1920\n4\tHuxley\tAldous\t1\t1894\n5\tBradbury\tRay\t1\t1920\n6\tK. Dick\tPhilip\t1\t1928\n7\tBarjavel\tRené\t2\t1911\n8\tBoulle\tPierre\t2\t1912\n9\tVan Vogt\tAlfred Elton\t1\t1912\n10\tVerne\tJules\t2\t1828","text/html":"
IdAuteurNomAuteurPrenomAuteurIdLangueAnneeNaissance
1OrwellGeorge11903
2HerbertFrank11920
3AsimovIsaac11920
4HuxleyAldous11894
5BradburyRay11920
6K. DickPhilip11928
7BarjavelRené21911
8BoullePierre21912
9Van VogtAlfred Elton11912
10VerneJules21828
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"## La table Livre\n\n1. Observer le schéma de la table Livre et afficher son contenu avec une requête SELECT.\n\n2. Ajouter les 3 livres manquants : \"La planète des singes\", \"Le monde des A\" et \"De la\nTerre à la Lune\". *(les informations nécessaires sont résumées dans le tableau présenté en début de TP)*\n\n3. Aurait-on pu créer ces livres avant d’avoir complété la table Auteur ? Justifier."},{"metadata":{"trusted":true},"cell_type":"code","source":"-- à vous de jouer (afficher le contenu de la relation Livre)\n","execution_count":7,"outputs":[{"output_type":"execute_result","execution_count":7,"data":{"text/plain":"IdLivre\tTitre\tIdAuteur\tAnneePubli\n1\t1984\t1\t1949\n2\tDune\t2\t1965\n3\tFondation\t3\t1951\n4\tLe meilleur des mondes\t4\t1931\n5\tFahrenheit 451\t5\t1953\n6\tUbik\t6\t1969\n7\tChroniques martiennes\t5\t1950\n8\tLa nuit des temps\t7\t1968\n9\tBlade Runner\t6\t1968\n10\tLes Robots\t3\t1950\n11\tRavage\t7\t1943\n12\tLe Maître du Haut Château\t6\t1962\n13\tLa fin de l'éternité\t3\t1955","text/html":"
IdLivreTitreIdAuteurAnneePubli
1198411949
2Dune21965
3Fondation31951
4Le meilleur des mondes41931
5Fahrenheit 45151953
6Ubik61969
7Chroniques martiennes51950
8La nuit des temps71968
9Blade Runner61968
10Les Robots31950
11Ravage71943
12Le Maître du Haut Château61962
13La fin de l'éternité31955
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (ajouter les 3 livres manquants)\n","execution_count":8,"outputs":[{"output_type":"execute_result","execution_count":8,"data":{"text/plain":"IdLivre\tTitre\tIdAuteur\tAnneePubli\n1\t1984\t1\t1949\n2\tDune\t2\t1965\n3\tFondation\t3\t1951\n4\tLe meilleur des mondes\t4\t1931\n5\tFahrenheit 451\t5\t1953\n6\tUbik\t6\t1969\n7\tChroniques martiennes\t5\t1950\n8\tLa nuit des temps\t7\t1968\n9\tBlade Runner\t6\t1968\n10\tLes Robots\t3\t1950\n11\tRavage\t7\t1943\n12\tLe Maître du Haut Château\t6\t1962\n13\tLa fin de l'éternité\t3\t1955\n14\tLa planète des singes\t8\t1963\n15\tLe monde des A\t9\t1945\n16\tDe la Terre à la Lune\t10\t1865","text/html":"
IdLivreTitreIdAuteurAnneePubli
1198411949
2Dune21965
3Fondation31951
4Le meilleur des mondes41931
5Fahrenheit 45151953
6Ubik61969
7Chroniques martiennes51950
8La nuit des temps71968
9Blade Runner61968
10Les Robots31950
11Ravage71943
12Le Maître du Haut Château61962
13La fin de l'éternité31955
14La planète des singes81963
15Le monde des A91945
16De la Terre à la Lune101865
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"## La table Theme\nTraitons à présent la problématique des Thèmes. Cette table doit lister tous les thèmes qui\npeuvent correspondre aux livres de la base.\n\n1. Observer le schéma de la table Theme et afficher son contenu avec une requête SELECT.\n\n2. Trouver le thème qui a été oublié, et l’ajouter à la table.\n\n*(les informations nécessaires sont résumées dans le tableau présenté en début de TP)*"},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (Afficher tous les thèmes)\n","execution_count":9,"outputs":[{"output_type":"execute_result","execution_count":9,"data":{"text/plain":"IdTheme\tIntitule\n1\tScience-fiction\n2\tTotalitarisme\n3\tAnticipation\n4\tDystopie\n5\tEconomie\n6\tTragédie\n7\tUchronie\n8\tVoyage dans le temps\n9\tAventure","text/html":"
IdThemeIntitule
1Science-fiction
2Totalitarisme
3Anticipation
4Dystopie
5Economie
6Tragédie
7Uchronie
8Voyage dans le temps
9Aventure
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (ajouter le thème manquant, puis afficher la liste des thèmes)\n","execution_count":10,"outputs":[{"output_type":"execute_result","execution_count":10,"data":{"text/plain":"IdTheme\tIntitule\n1\tScience-fiction\n2\tTotalitarisme\n3\tAnticipation\n4\tDystopie\n5\tEconomie\n6\tTragédie\n7\tUchronie\n8\tVoyage dans le temps\n9\tAventure\n10\tIntelligence artificielle","text/html":"
IdThemeIntitule
1Science-fiction
2Totalitarisme
3Anticipation
4Dystopie
5Economie
6Tragédie
7Uchronie
8Voyage dans le temps
9Aventure
10Intelligence artificielle
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"## Une dernière table manquante\nLa saisie de notre base de donnée est incomplète ! Nous avons en effet saisi tous les auteurs, tous les livres, toutes les langues, tous les thèmes et pourtant il manque une information. Laquelle ?\n\nPour solutionner ce problème, la table RelationLivreTheme contient l’information manquante en mettant en relation les livres et les thèmes associés.\n\n1. Observer le schéma de la table RelationLivreTheme et afficher son contenu avec une requête SELECT.\n2. Quelle est sa clef primaire ? Quelle est particularité par rapport aux clefs primaires qu’on a rencontrées dans les autres tables ?\n3. Possède-t-elle des clefs étrangères ? Si oui, lesquelles ?\n4. Ajouter à cette table les enregistrements nécessaires pour que les Livres qui font références au thème \"Intelligence artificielle\" soient correctement pris en compte.\n5. Enfin, ajouter à cette table les enregistrements décrivant les thèmes des 3 livres ajoutés plus tôt."},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (afficher le contenu de la table RelationLivreTheme)\n","execution_count":11,"outputs":[{"output_type":"execute_result","execution_count":11,"data":{"text/plain":"IdLivre\tIdTheme\n1\t2\n1\t1\n1\t3\n1\t4\n2\t1\n2\t3\n3\t1\n3\t5\n4\t2\n4\t1\n4\t4\n5\t1\n5\t4\n6\t1\n6\t3\n7\t1\n7\t3\n8\t1\n8\t6\n9\t1\n10\t1\n11\t1\n11\t4\n12\t1\n12\t3\n13\t4\n13\t8","text/html":"
IdLivreIdTheme
12
11
13
14
21
23
31
35
42
41
44
51
54
61
63
71
73
81
86
91
101
111
114
121
123
134
138
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (Ajouter les livres avec le thème Intelligence artififielle)\n","execution_count":12,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"-- a vous de jouer (Ajouter les thèmes des derniers livres ajoutés)\n","execution_count":13,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Contrôle du travail jusqu’à présent\nVérifier que les livres sont bien associés aux bons thèmes en exécutant la commande suivante\n*(explications de cette requête dans la suite du TP...)* :"},{"metadata":{"trusted":true,"scrolled":true},"cell_type":"code","source":"SELECT Titre, Intitule FROM RelationLivreTheme\nJOIN Livre ON Livre.IdLivre = RelationLivreTheme.IdLivre\nJOIN Theme ON Theme.IdTheme = RelationLivreTheme.IdTheme\nORDER BY Titre;","execution_count":14,"outputs":[{"output_type":"execute_result","execution_count":14,"data":{"text/plain":"Titre\tIntitule\n1984\tTotalitarisme\n1984\tScience-fiction\n1984\tAnticipation\n1984\tDystopie\nBlade Runner\tScience-fiction\nBlade Runner\tIntelligence artificielle\nChroniques martiennes\tScience-fiction\nChroniques martiennes\tAnticipation\nDe la Terre à la Lune\tScience-fiction\nDe la Terre à la Lune\tAventure\nDune\tScience-fiction\nDune\tAnticipation\nFahrenheit 451\tScience-fiction\nFahrenheit 451\tDystopie\nFondation\tScience-fiction\nFondation\tEconomie\nLa fin de l'éternité\tDystopie\nLa fin de l'éternité\tVoyage dans le temps\nLa nuit des temps\tScience-fiction\nLa nuit des temps\tTragédie\nLa planète des singes\tScience-fiction\nLa planète des singes\tDystopie\nLe Maître du Haut Château\tScience-fiction\nLe Maître du Haut Château\tAnticipation\nLe meilleur des mondes\tTotalitarisme\nLe meilleur des mondes\tScience-fiction\nLe meilleur des mondes\tDystopie\nLe monde des A\tIntelligence artificielle\nLe monde des A\tScience-fiction\nLes Robots\tScience-fiction\nLes Robots\tIntelligence artificielle\nRavage\tScience-fiction\nRavage\tDystopie\nUbik\tScience-fiction\nUbik\tAnticipation","text/html":"
TitreIntitule
1984Totalitarisme
1984Science-fiction
1984Anticipation
1984Dystopie
Blade RunnerScience-fiction
Blade RunnerIntelligence artificielle
Chroniques martiennesScience-fiction
Chroniques martiennesAnticipation
De la Terre à la LuneScience-fiction
De la Terre à la LuneAventure
DuneScience-fiction
DuneAnticipation
Fahrenheit 451Science-fiction
Fahrenheit 451Dystopie
FondationScience-fiction
FondationEconomie
La fin de l'éternitéDystopie
La fin de l'éternitéVoyage dans le temps
La nuit des tempsScience-fiction
La nuit des tempsTragédie
La planète des singesScience-fiction
La planète des singesDystopie
Le Maître du Haut ChâteauScience-fiction
Le Maître du Haut ChâteauAnticipation
Le meilleur des mondesTotalitarisme
Le meilleur des mondesScience-fiction
Le meilleur des mondesDystopie
Le monde des AIntelligence artificielle
Le monde des AScience-fiction
Les RobotsScience-fiction
Les RobotsIntelligence artificielle
RavageScience-fiction
RavageDystopie
UbikScience-fiction
UbikAnticipation
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"Vérifier les autres informations sur les livres en exécutant la commande suivante\n*(explications de cette requête dans la suite du TP...)* :"},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT Titre, NomAuteur, PrenomAuteur, AnneeNaissance, Langue, AnneePubli\nFROM Livre JOIN Auteur ON Auteur.IdAuteur = Livre.IdAuteur\nJOIN Langue ON Langue.IdLangue = Auteur.IdLangue;","execution_count":15,"outputs":[{"output_type":"execute_result","execution_count":15,"data":{"text/plain":"Titre\tNomAuteur\tPrenomAuteur\tAnneeNaissance\tLangue\tAnneePubli\n1984\tOrwell\tGeorge\t1903\tAnglais\t1949\nDune\tHerbert\tFrank\t1920\tAnglais\t1965\nFondation\tAsimov\tIsaac\t1920\tAnglais\t1951\nLe meilleur des mondes\tHuxley\tAldous\t1894\tAnglais\t1931\nFahrenheit 451\tBradbury\tRay\t1920\tAnglais\t1953\nUbik\tK. Dick\tPhilip\t1928\tAnglais\t1969\nChroniques martiennes\tBradbury\tRay\t1920\tAnglais\t1950\nLa nuit des temps\tBarjavel\tRené\t1911\tFrançais\t1968\nBlade Runner\tK. Dick\tPhilip\t1928\tAnglais\t1968\nLes Robots\tAsimov\tIsaac\t1920\tAnglais\t1950\nRavage\tBarjavel\tRené\t1911\tFrançais\t1943\nLe Maître du Haut Château\tK. Dick\tPhilip\t1928\tAnglais\t1962\nLa fin de l'éternité\tAsimov\tIsaac\t1920\tAnglais\t1955\nLa planète des singes\tBoulle\tPierre\t1912\tFrançais\t1963\nLe monde des A\tVan Vogt\tAlfred Elton\t1912\tAnglais\t1945\nDe la Terre à la Lune\tVerne\tJules\t1828\tFrançais\t1865","text/html":"
TitreNomAuteurPrenomAuteurAnneeNaissanceLangueAnneePubli
1984OrwellGeorge1903Anglais1949
DuneHerbertFrank1920Anglais1965
FondationAsimovIsaac1920Anglais1951
Le meilleur des mondesHuxleyAldous1894Anglais1931
Fahrenheit 451BradburyRay1920Anglais1953
UbikK. DickPhilip1928Anglais1969
Chroniques martiennesBradburyRay1920Anglais1950
La nuit des tempsBarjavelRené1911Français1968
Blade RunnerK. DickPhilip1928Anglais1968
Les RobotsAsimovIsaac1920Anglais1950
RavageBarjavelRené1911Français1943
Le Maître du Haut ChâteauK. DickPhilip1928Anglais1962
La fin de l'éternitéAsimovIsaac1920Anglais1955
La planète des singesBoullePierre1912Français1963
Le monde des AVan VogtAlfred Elton1912Anglais1945
De la Terre à la LuneVerneJules1828Français1865
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"name":"sql","display_name":"SQL","language":"sql"}},"nbformat":4,"nbformat_minor":2}