Nous avons vu dans la séquence précédente comment créer des tables et les remplir.
Nous avons maintenant une base de données, c’est-à-dire un ensemble de tables, contenant des données cohérentes vis à vis de nos contraintes d’intégrité.
Nous allons maintenant voir deux autres utilisations d’un SGBD :
- la sélection de données ;
- la mise à jour des données.
La sélection va consister en l’écriture de requêtes SQL permettant de trouver toutes les données de la base vérifiant un certain critère. Le premier rôle du programmeur de bases de données va donc être celui qui consiste à traduire des questions que l’on se pose sur les données du langage naturel au langage
SQL, afin que le SGBD puisse y répondre.
Reprenons la bascs de données de la médiathèque municipale.
Quelles questions peut-on poser à la base de données? Il semble naturel que le SGBD puisse répondre aux questions suivantes, nécessaire au bon fonctionnement
de la médiathèque.
- Étant donné un code barre, quels sont les livres empruntés par l’utilisateur correspondant?
- Étant donné un ISBN. le livre correspondant est-il emprunté?
- Quels sont les utilisateurs en retard, c'est-à-dire ceux dont la date de retour est inférieure à une date donnée ?
- Quels sont tous les livres écrits par Voltaire qui ne sont pas empruntés?
- Quel est le nombre total de livres empruntés?
Une autre fonction importante du SGBD est la mise à jour des données.
Elle peut consister en une modification d’une ligne existante (par exemple, pour changer l'adresse d’un utilisateur ayant déménagé, sans modifier son
code barre, son nom où son e-mail) ou une suppression (par exemple lorsqu'un utilisateur rend un livre, il faut supprimer la ligne correspondante dans la table emprunt).
Pour les exemples plus parlants, nous utilisons dans la suite le SGBD libre PostgreSQL, avec une base de données de médiathèque fic-
tive. Les fichiers permettant de créer cette base sont disponibles librement
sur le site https://www.nsi-terminale.fr/. Ils utilisent la syntaxe SQL
standard et sont donc compatibles avec n'importe quel SGBD relationnel
respectant la norme SQL (et ont été testés avec un certain nombre d’entre
eux, libres et propriétaires).
Commençons par une requête simple. On considère la table livre, créée par l’ordre suivant :
CREATE TABLE livre (titre VARCHAR(300) NOT NULL,
editeur VARCHAR(90) NOT NULL,
annee INT NOT NULL,
isbn CHAR(14) PRIMARY KEY);
Où les entités suivantes sont insérées :
INSERT INTO livre VALUES ('Les Aventures de Huckleberry Finn', 'Flammarion', '2020', '978-2081509511');
INSERT INTO livre VALUES ('Fondation et Empire', 'Editions Denoël', '1999', '978-2207249123');
INSERT INTO livre VALUES ('Akira', 'Glénat', '2000', '978-2723428262');
INSERT INTO livre VALUES ('Les Robots', 'Editions Milan', '2017', '978-2745989857');
INSERT INTO livre VALUES ('Astérix chez les Pictes', 'Editions Albert René', '2013', '978-2864972662');
INSERT INTO livre VALUES ('Les Monades urbaines', 'Robert Laffont', '2016', '978-2221197691');
INSERT INTO livre VALUES ('Les Voyages de Gulliver', 'Primento', '2015', '978-2335008586');
INSERT INTO livre VALUES ('Lolita', 'Penguin UK', '2012', '978-0141391601');
INSERT INTO livre VALUES ('La Nuit des temps', 'Presses de la Cité', '2014', '978-2258116429');
INSERT INTO livre VALUES ('Ravage', 'Editions Gallimard', '2014', '978-2072534911');
INSERT INTO livre VALUES ('Les Lauriers de César', 'Educa Books', '2008', '978-2012101500');
INSERT INTO livre VALUES ('Niourk', 'French Pulp éditions', '2018', '979-1025100639');
INSERT INTO livre VALUES ('Le Meilleur des mondes', 'Plon', '2013', '978-2259221702');
INSERT INTO livre VALUES ('Berlin Alexanderplatz', 'Editions Gallimard', '1933', '978-2070219292');
INSERT INTO livre VALUES ('Fahrenheit 451', 'Simon and Schuster', '2011', '978-1439142677');
INSERT INTO livre VALUES ('La Mort d''Ivan Ilitch', 'Flammarion', '2015', '978-2081364509');
INSERT INTO livre VALUES ('Croisière sans escale', 'Editions Denoël', '1990', '978-2207500293');
INSERT INTO livre VALUES ('Le Vieil Homme et la Mer', 'Editions Gallimard', '2018', '978-2072762093');
INSERT INTO livre VALUES ('Mrs Dalloway', 'Flammarion', '2015', '978-2081358881');
INSERT INTO livre VALUES ('L''Idiot', 'Les Editions de Londres', '2019', '978-1911572909');
INSERT INTO livre VALUES ('Le Carnet d''or', 'Le Livre de poche', '1980', '978-2253025320');
INSERT INTO livre VALUES ('Les Grandes Espérances', 'BoD - Books on Demand', '2019', '978-2322185801');
INSERT INTO livre VALUES ('Astérix et Cléopâtre', 'Dargaud', '1999', '978-2012100060');
INSERT INTO livre VALUES ('Madame Bovary', 'UPblisher', '2016', '978-2759902293');
INSERT INTO livre VALUES ('Les Frères Karamazov', 'Les éditions Pulsio', '2016', '978-2371131118');
INSERT INTO livre VALUES ('Moby Dick', 'Campfire Graphic Novels', '2010', '978-8190732673');
INSERT INTO livre VALUES ('Demain les chiens', 'J''ai Lu', '2015', '978-2290112168');
INSERT INTO livre VALUES ('Le Tour de Gaule d''Astérix', 'Educa Books', '2007', '978-2012101685');
INSERT INTO livre VALUES ('1984', 'Houghton Mifflin Harcourt', '1983', '978-0547249643');
INSERT INTO livre VALUES ('Don Quichotte', 'Les éditions Pulsio', '2016', '978-2371130418');
INSERT INTO livre VALUES ('Le Château de Lord Valentin', 'Robert Laffont', '2017', '978-2221216361');
INSERT INTO livre VALUES ('Le Père Goriot', 'Primento', '2012', '978-2806231697');
INSERT INTO livre VALUES ('Le Procès', 'Flammarion', '2014', '978-2081351981');
INSERT INTO livre VALUES ('L''Homme qui rétrécit', 'Editions Gallimard', '2017', '978-2072457340');
INSERT INTO livre VALUES ('Chroniques martiennes', 'Editions Gallimard', '2016', '978-2072455162');
INSERT INTO livre VALUES ('Le Roi Lear', 'Éditions Actes Sud', '2015', '978-2330052768');
INSERT INTO livre VALUES ('Le Cadeau de César', 'Educa Books', '2005', '978-2012101531');
INSERT INTO livre VALUES ('La Planète des singes', 'Julliard', '2011', '978-2260019183');
INSERT INTO livre VALUES ('Orgueil et Préjugés', 'Fleurus', '2015', '978-2215130475');
INSERT INTO livre VALUES ('Une maison de poupée', 'Éditions Actes Sud', '2016', '978-2330068349');
INSERT INTO livre VALUES ('Vermilion Sands', 'Carroll & Graf Pub', '1988', '978-0881844221');
INSERT INTO livre VALUES ('La Grande Traversée', 'Seuil Jeunesse', '2014', '979-1023500448');
INSERT INTO livre VALUES ('L''Étranger', 'Editions Gallimard', '2012', '978-2072376429');
INSERT INTO livre VALUES ('L''Île des morts', 'POL Editeur', '2010', '978-2846825573');
INSERT INTO livre VALUES ('Par-delà le mur du sommeil', 'République des Lettres', '2018', '978-2824904269');
INSERT INTO livre VALUES ('Le Papyrus de César', 'Editions Albert René', '2015', '978-2864972716');
INSERT INTO livre VALUES ('La Main de Zeï', 'Bragelonne Classic', '2016', '978-2820511034');
INSERT INTO livre VALUES ('Beloved', 'Christian Bourgois', '2015', '978-2267028133');
INSERT INTO livre VALUES ('La Conscience de Zeno', 'République des Lettres', '2015', '978-2824902371');
INSERT INTO livre VALUES ('Delirium Circus', 'Bragelonne', '2013', '978-2820508935');
INSERT INTO livre VALUES ('Médée', 'Hatier', '2013', '978-2218972324');
INSERT INTO livre VALUES ('Nostromo', 'Oxford University Press', '2009', '978-0199555918');
INSERT INTO livre VALUES ('Au carrefour des étoiles', 'J''ai Lu', '1997', '978-2277118473');
INSERT INTO livre VALUES ('Le Vagabond', 'BnF collection ebooks', '2016', '978-2346014453');
INSERT INTO livre VALUES ('Les Buddenbrook', 'LGF/Le Livre de Poche', '1993', '978-2253063193');
INSERT INTO livre VALUES ('Les Métamorphoses', 'Le Livre de Poche', '2011', '978-2253158677');
INSERT INTO livre VALUES ('Jack Barron et l''Éternité', 'J''ai Lu', '2016', '978-2290105504');
INSERT INTO livre VALUES ('Hacker''s Delight', 'Addison-Wesley Professional', '2003', '978-0201914658');
INSERT INTO livre VALUES ('Astérix et les Normands', 'Dargaud', '2005', '978-2012101418');
INSERT INTO livre VALUES ('Le Temps incertain', 'Robert Laffont', '2011', '978-2221119709');
INSERT INTO livre VALUES ('Astérix en Corse', 'Dargaud', '2005', '978-2012101524');
INSERT INTO livre VALUES ('Les Fils de la Médina', 'Arles [France] : Actes sud', '2003', '978-2742744824');
INSERT INTO livre VALUES ('Le Grand secret', 'Presses de la Cité', '2014', '978-2258116405');
INSERT INTO livre VALUES ('Le Devin', 'Educa Books', '2010', '978-2012101517');
INSERT INTO livre VALUES ('Le Noir Dessein', 'Livre de poche', '1998', '978-2253062820');
INSERT INTO livre VALUES ('Astérix légionnaire', 'Educa Books', '2011', '978-2012101784');
INSERT INTO livre VALUES ('Romancero gitano', 'Greenbooks editore', '2020', '978-8832957402');
INSERT INTO livre VALUES ('The Practice of Programming', 'Addison-Wesley Professional', '1999', '978-0201615869');
INSERT INTO livre VALUES ('Crime et Châtiment', 'Editions Humanis', '2012', '979-1021900486');
INSERT INTO livre VALUES ('La Promenade au phare', 'LGF/Le Livre de Poche', '1983', '978-2253031536');
INSERT INTO livre VALUES ('L''Homme sans qualités', 'Contemporary French Fiction', '2011', '978-2757803691');
INSERT INTO livre VALUES ('Le Bruit et la Fureur', 'Gallimard Education', '1972', '978-2070361625');
INSERT INTO livre VALUES ('Les Plus qu''humains', 'adsaa', '1999', '000-0000000162');
INSERT INTO livre VALUES ('La Main gauche de la nuit', 'Robert Laffont', '2012', '978-2221128121');
INSERT INTO livre VALUES ('Mémoires d''Hadrien', 'Gallimard Education', '1974', '978-2070369218');
INSERT INTO livre VALUES ('Contes de l''absurde', 'Presses Pocket', '1978', '978-2266006095');
INSERT INTO livre VALUES ('Astérix et la Transitalique', 'Editions Albert René', '2017', '978-2864973270');
INSERT INTO livre VALUES ('L''Odyssée d''Astérix', 'Educa Books', '2008', '978-2864972051');
INSERT INTO livre VALUES ('Les Singes du temps', 'Robert Laffont', '2011', '978-2221119693');
INSERT INTO livre VALUES ('Les Contes de Canterbury', 'Gallimard Education', '2000', '978-2070406340');
INSERT INTO livre VALUES ('Sécheresse', 'La Cheminante', '2014', '978-2371270060');
INSERT INTO livre VALUES ('The Art of Computer Programming', 'Addison-Wesley Professional', '1997', '978-0321635747');
INSERT INTO livre VALUES ('L''Aveuglement', 'Contemporary French Fiction', '2000', '978-2020403436');
INSERT INTO livre VALUES ('Le Berceau du chat', 'Contemporary French Fiction', '2010', '978-2757820919');
INSERT INTO livre VALUES ('Anna Karénine', 'Bibliothèque russe et slave', '2018', '978-2371240087');
INSERT INTO livre VALUES ('La Montagne magique', 'Fayard', '2016', '978-2213703848');
INSERT INTO livre VALUES ('Le Domaine des dieux', 'French & European Publications', '1992', '978-0785909903');
INSERT INTO livre VALUES ('Cent ans de solitude', 'Contemporary French Fiction', '1995', '978-2020238113');
INSERT INTO livre VALUES ('Gargantua et Pantagruel', 'Livre de Poche Jeunesse', '2009', '978-2013230827');
INSERT INTO livre VALUES ('Contes', 'J''ai Lu', '2015', '978-2290117965');
INSERT INTO livre VALUES ('Guerre et Paix', 'Archipoche', '2016', '978-2352879183');
INSERT INTO livre VALUES ('Énéide', 'Belles Lettres', '1993', '978-2251013039');
INSERT INTO livre VALUES ('Seconde Fondation', 'adsaa', '1979', '000-0000000097');
INSERT INTO livre VALUES ('Les Jeux de l''esprit', 'FeniXX', '1971', '978-2402281775');
INSERT INTO livre VALUES ('Middlemarch', 'Wordsworth Editions', '1994', '978-1853262371');
INSERT INTO livre VALUES ('Œdipe roi', 'J''ai Lu', '2013', '978-2290080207');
INSERT INTO livre VALUES ('L''Amour aux temps du choléra', 'Grasset', '2009', '978-2246819554');
INSERT INTO livre VALUES ('Fictions', 'Gallimard Education', '1974', '978-2070366149');
INSERT INTO livre VALUES ('Astérix chez les Bretons', 'Dargaud', '2002', '978-2012100084');
INSERT INTO livre VALUES ('Le Château', 'Points', '2011', '978-2757827413');
INSERT INTO livre VALUES ('Le Voyageur imprudent', 'Editions Gallimard', '2014', '978-2072535031');
INSERT INTO livre VALUES ('Je suis une légende', 'Editions Gallimard', '2013', '978-2072457388');
INSERT INTO livre VALUES ('Le Maître du Haut Château', 'J''ai Lu', '2017', '978-2290157268');
INSERT INTO livre VALUES ('Les Âmes mortes', 'Bibliothèque russe et slave', '2018', '978-2371240001');
INSERT INTO livre VALUES ('Le Tambour', 'Contemporary French Fiction', '1997', '978-2020314305');
INSERT INTO livre VALUES ('Polymath', 'iMinds Pty Ltd', '2014', '978-1921746864');
INSERT INTO livre VALUES ('Seigneur de lumière', 'Editions Gallimard', '2014', '978-2072487958');
INSERT INTO livre VALUES ('Ulysse', 'Nathan', '2012', '978-2092532195');
INSERT INTO livre VALUES ('Pedro Páramo', 'New York : Grove Press', '1959', '000-0000000069');
INSERT INTO livre VALUES ('Ubik', 'Houghton Mifflin Harcourt', '2012', '978-0547728247');
INSERT INTO livre VALUES ('Algorithms', 'Addison-Wesley Professional', '2011', '978-0132762564');
INSERT INTO livre VALUES ('Fifi Brindacier', 'Hachette Romans', '2013', '978-2011179043');
INSERT INTO livre VALUES ('Le monde s''effondre', 'Editions Présence Africaine', '1972', '978-2708701915');
INSERT INTO livre VALUES ('La Naissance des dieux', 'Glénat BD', '2017', '978-2331035531');
INSERT INTO livre VALUES ('Hamlet', 'Primento', '2012', '978-2806240187');
INSERT INTO livre VALUES ('Les Enfants de minuit', 'Gallimard Education', '2010', '978-2070402632');
INSERT INTO livre VALUES ('Dune', 'Penguin', '2003', '978-1101658055');
INSERT INTO livre VALUES ('La Couleur tombée du ciel', 'Tiers Livre Éditeur', '2014', '978-2814510012');
INSERT INTO livre VALUES ('L''Éducation sentimentale', 'FeniXX', '1990', '978-2402282697');
INSERT INTO livre VALUES ('Obélix et Compagnie', 'Educa Books', '2008', '978-2012101555');
INSERT INTO livre VALUES ('Le Journal d''un fou', 'Bibebook', '2013', '978-2824709420');
INSERT INTO livre VALUES ('Les Hauts de Hurlevent', 'Le Livre de Poche', '2012', '978-2253174561');
INSERT INTO livre VALUES ('La Plaie', 'FeniXX', '1967', '978-2402255462');
INSERT INTO livre VALUES ('Astérix chez les Belges', 'Dargaud', '1979', '978-2012101562');
INSERT INTO livre VALUES ('Le Rouge et le Noir', 'Les Éditions de l''Ebook malin', '1971', '978-2367881171');
INSERT INTO livre VALUES ('À la recherche du temps perdu', 'Books LLC, Wiki Series', '2010', '978-1153611725');
INSERT INTO livre VALUES ('La storia', 'Editions Gallimard', '2004', '978-2070315017');
INSERT INTO livre VALUES ('L''Homme total', 'Presses Universitaires de France - PUF', '2011', '978-2130592150');
Rentrez les expressions ci-dessus dans votre base de donnée dans l'onglet SQL de phpmyadmin.
Mettre le résultat ici de la requête sql.
Maintenant que la table livre est mise en place, on souhaite trouver les titres de tous les livres publiés après 1990 dans la base de données de la médiathèque.
Une telle requête peut s’écrire en SQL :
SELECT titre FROM livre WHERE annee >= 1990;
Sélectionner la base de donnée dans phpmyadmin et exécuter la requête SQL. Mettre le résultat ci-dessous et conclure.
Dans cette requête, la partie « FROM livre » indique la table sur laquelle porte la requête.
La partie « WHERE ... » indique que l’on ne sélectionne
que les lignes de la table Livre pour lesquelles la valeur de l’attribut annee est plus grande que 1990.
Enfin, la partie SELECT titre indique qu'on ne veut renvoyer que les valeurs de l’attribut titre des lignes trouvées. On remarque que le résultat d'une requête «SELECT ... » est une table, ici possédant une unique colonne titre.
","title":"Requête sur une table"},{"edit":"Mettre le résultat ici (code et figure).
L'expression se trouvant dans la partie WHERE doit être une expression booléenne.
Elle peut être construite à partir d'opérateurs de comparaison (<, <=, >, >=, = et <>), d'opérateurs arithmétiques (+, -, *, /, %), de constantes, de noms d’attributs, d'opérateurs logiques (AND, OR et NOT) et d'opérateurs spéciaux tels que l'opérateur de comparaison de textes LIKE.
Par exemple, si l’on souhaite afficher les titres de tous les livres publiés par Dargaud entre 1970 et 1980, on pourra écrire :
SELECT titre FROM livre WHERE annee >= 1970
AND annee <= 1980
AND editeur = 'Dargaud';
Tester la requête et mettre le résultat ci-dessous.
Si l’utilisation de l'égalité « = » est appropriée ici, on pourrait vouloir faire une requête approchée.
Par exemple, trouver les titres des livres qui contiennent le mot « Astérix » dans le titre.
Une telle requête s’écrira
SELECT titre FROM livre WHERE titre LIKE '%Astérix%';
Tester la requête et mettre le résultat ci-dessous.
La chaîne de caractères « '%Astérix%' » est un motif. L'opération s LIKE m s’évalue à vrai si et seulement si la chaîne de caractères s correspond au motif m.
Dans un motif, le symbole « % » est un joker et peut être substitué par n'importe quelle chaîne.
Le symbole «_» quant à lui représente n'importe quel caractère.
Ainsi, le motif « '%Astérix%' » correspond à n'importe quelle chaîne dans laquelle les caractères Astérix sont précédés ou suivis de caractères quelconques.
Mettre le résultat ici (code et figure).
La clause SELECT peut prendre trois formes ;
La première est celle où l’on liste explicitement les
attributs que l’on désire renvoyer. Si on veut renvoyer les titres et l’'ISBN des livres publiés après 1990, on écrira ceci :
SELECT titre, isbn FROM livre WHERE annee >= 1990;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Le résultat est de nouveau une table, mais cette fois avec les colonnes (ou attributs) titre et isbn.
Il est possible de renommer les colonnes au moyen du mot clé AS.
SELECT titre AS le_titre, isbn AS nom ,serie
FROM livre
WHERE annee >= 1990;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Ainsi, si la clause WHERE d’une requête permet de restreindre les lignes de la table que l’on renvoie (en ne gardant que celle vérifiant la condition), la clause SELECT permet de restreindre la liste des colonnes.
La seconde forme de la clause SELECT est celle que l’on utilise lorsqu'on veut conserver toutes les colonnes. En effet, il serait fastidieux de récrire toutes les colonnes d’une table. On peut utiliser à cette fin le symbole « * » qui signifie « toutes les colonnes de la table ».
SELECT * FROM livre WHERE annee >= 1990;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
La troisième utilisation de la clause SELECT est celle permettant d'appeler des fonctions d’agrégation. Ces dernières permettent d'appliquer une fonction à l’ensemble des valeurs d’une colonne et de renvoyer le résultat comme une table ayant une seule case (une ligne et une colonne).
Nous présentons quelques unes de ces fonctions :
- COUNT qui permet d'obtenir le nombre de résultats,
- AVG (pour l’anglais average) qui permet de calculer la moyenne d'une colonne,
- SUM qui permet d’en faire la somme,
- MIN et MAX qui permettent de trouver respectivement le minimum et maximum d’une colonne.
Si on souhaite savoir combien de livres contiennent la chaîne « Astérix » dans leur titre (plutôt que de renvoyer ces titres), on écrira la requête suivante :
SELECT COUNT(titre) AS total
FROM livre
WHERE titre LIKE '%Astérix%';
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Notons que nous avons choisi de renommer la colonne. En effet, le résultat n'étant pas directement une colonne d’une table existante, les SGBD choisissent un nom arbitraire, souvent peu parlant.
La fonction « COUNT » ne faisant que compter la taille de la colonne, elle peut s’appliquer à n'importe quel nom de colonne et même au symbole « * ». Il est donc courant d'écrire une requête de la forme
SELECT COUNT(*) AS total
FROM livre
WHERE titre LIKE '%Astérix%';
qui donnera le même résultat que précédemment.
Les fonctions AVG et SUM ne peuvent s'appliquer qu’à des colonnes dont le domaine est un nombre
On peut écrire par exemple
SELECT SUM(annee) AS somme FROM livre;
SELECT AVG(annee) AS moyenne FROM livre;
Tester les 2 requêtes dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Ici, en l’absence de clause WHERE, toutes les lignes sont sélectionnées. La somme et la moyenne des années sont calculées et renvoyées comme une
table.
Enfin, les fonctions MIN() et MAX() peuvent s'appliquer sur n'importe quelle colonne et ls comparaison pour son type sera utilisée pour déterminer le plus petit ou le plus grand élément.
SELECT MIN(annee) AS inf FROM livre;
SELECT MAX(annee}) AS sup FROM livre;
Tester les 2 requêtes dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Mettre le résultat ici.
Comme nous avons pu l'observer lors de nos premières requêtes, les résultats sont affichés par le SGBD dans un ordre a priori quelconque. La situation est même plus complexe. En effet, en fonction de certains paramètres, le SGBD peut choisir entre différentes
façons de calculer la requête.
L'ordre peut donc être modifié entre deux exécutions de la même requête. Si l’on désire obtenir les résultats dans un ordre particulier, on peut utiliser la clause ORDER BY en fin de requête.
SELECT titre FROM livre
WHERE annee >= 1990
ORDER BY titre ASC;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Ici, on demande au SGBD de trier les résultats par titre croissants (ASC pour l’anglais ascending).
Si on souhaite trier par valeurs décroissantes il suffit d'utiliser le mot clé DESC (pour l'anglais descending) à la place de ASC.
Supposons maintenant que l’on souhaite connaître toutes les années dans lesquelles un livre à été publié.
Nous envoyons la requête suivante :
SELECT annee FROM livre;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Vous constatez que toutes les années, mais la même année peut apparaître plusieurs fois. Si on souhaite retirer les doublons d’un résultat, le mot clé DISTINCT
peut être ajouté à la clause SELECT.
SELECT DISTINCT annee FROM livre;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Attention cependant, chaque ligne entière de résultat est considérée lors de la comparaison.
SELECT DISTINCT annee, isbn FROM livre;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
C’est pourquoi, la requête ci-dessus continuera d'afficher plusieurs fois la même année. En effet, comme l’isbn est unique pour chaque ligne, tous les couples annee, isbn de la table sont différents deux à deux (ils diffèrent par leur isbn même s'ils ont la même
année). Le mot clé DISTINCT n'aura donc ici aucun effet.
Mettre le résultat ici (code et figure).
Les requêtes que nous avons vues nous permettent assez facilement de déterminer les livres qui ont été empruntés. Ces derniers sont simplement ceux dont l'ISBN est présent dans la table emprunt.
SELECT * FROM emprunt;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Cette réponse n’est cependant pas très satisfaisante. En effet, il serait plus naturel de pouvoir afficher les titres de ces livres plutôt que leur ISBN. Le problème est que les titres des livres sont présents uniquement dans la table livre.
L'opération de jointure de deux tables apporte une réponse à ce problème. Elle a déjà été étudiée en première dans le cadre du traitement de données en tables.
Étant données deux tables A et B, la jointure consiste à créer toutes combinaisons de lignes de A et de B
ayant un attribut de même valeur. Ici, on souhaiterait obtenir une « grande table » dont les colonnes sont celles de la table emprunt et celle de la table livre, en réunissant les lignes ayant ie même isbn.
Cela peut être fait au moyen de la directive JOIN.
SELECT * FROM emprunt
JOIN livre ON emprunt.isbn = livre.isbn;
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Cette requête crée la jointure des deux tables.
Comme on peut le voir, toutes les colonnes des deux tables ont été recopiées dans la sortie.
Chaque ligne est le résultat de la fusion de deux lignes ayant le même ISBN. Le choix de ces lignes est donné
par la condition de jointure indiquée par le mot clé ON.
La condition indique au SGBD dans quel cas deux lignes doivent être fusionnées.
Ici, on joint les lignes pour lesquelles les ISBN sont égaux.
On écri donc l'expression booléenne
« emprunt.isbn = livre.isbn ».
La notation portant le même nom.
La jointure peut être combinée avec les clauses SELECT et WHERE.
Par exemple, si on souhaite afficher uniquement les titres et les dates des livres empruntés qui sont à rendre avant le 1er février 2020, on peut écrire la requête suivante :
SELECT livre.titre, emprunt.retour
FROM emprunt
JOIN livre ON emprunt.isbn = livre.isbn
WHERE emprunt retour < '2020-02-01';
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
Même s’il n’y a pas d’ambiguïté ici, une bonne pratique consiste à préfixer les noms d’attributs par leur table dès que l’on utilise plus d’une table dans la requête. On n’est évidemment pas limité à une seule jointure.
Si on souhaite afficher les noms et prénoms des utilisateurs ayant emprunté ces livres, il suffit de joindre la table usager, en rajoutant une nouvelle clause JOIN ON, cette fois sur le code_barre de l'usager.
SELECT usager.nom, usager.prenom, livre.titre, emprunt.retour
FROM emprunt
JOIN livre ON emprunt.isbn = livre.isbn
JOIN usager ON usager.code_barre = emprunt.code_barre
WHERE emprunt.retour <'2020-02-01';
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
La requête ci-dessus fonctionne parfaitement mais est un peu fastidieuse à écrire. Il est possible de créer dans une requête un alias pour un nom de table au moyen du mot clé AS, comme pour le renommage de colonne.
La requête peut donc être réécrite de la manière suivante :
SELECT u.nom, u.prenom, l.titre, e.retour
FROM emprunt AS e
JOIN livre AS l ON e.isbn = l.isbn
JOIN usager AS u ON u.code_barre = e.code_barre
WHERE e.retour < '2020-02-01';
Tester la requête dans phpmyadmin et mettre le résultat ci-dessous et conclure.
La jointure est une opération fondamentale des bases de données relationnelles. En effet, comme nous l'avons vu, la modélisation relationnelle des données impose parfois un découpage des données.
Les relations entre ces dernières sont maintenues par des contraintes, notamment les contraintes de référence.
La jointure permet de reconstituer ce lien, en construisant « à la volée » de grandes tables contenant toutes les informations liées.
Mettre le résultat ici (code et figure).
Considérons la requête
SELECT livre.titre, emprunt.retour
FROM emprunt
JOIN livre ON emprunt.isbn = livre.isbn
WHERE emprunt.retour < '2020-02-01';
Puisque l’on peut mettre des conditions arbitraires dans la clause WHERE, une manière alternative d'écrire la requête est la suivante :
SELECT livre.titre, emprunt.retour
FROM emprunt, livre
WHERE emprunt.isbn = livre.isbn
AND emprunt.retour < '2020-02-01';
Pour n'importe quel SGBD, ces deux requêtes sont équivalentes, tant du point de vue du résultat que des performances.
La seconde version est « l’ancienne » syntaxe SQL, utilisée avant l'introduction du mot clé JOIN dans la version du standard de 1992. La bonne pratique consiste à privilégier l’utilisation du mot clé JOIN. En effet, il rend plus lisible les grandes requêtes en séparant clairement prédicats de jointure et filtres
sur les données. Il permet aussi de communiquer clairement l'intention d'effectuer une jointure. Enfin, lors d’une utilisation avancée, il permet de changer la méthode de jointure (QUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc.) tout en conservant la même syntaxe.
Mettre le résultat ici (code et figure).
Les données stockées dans un SGBD ne sont a priori pas figées et peuvent être modifiées au cours du temps. Nous allons montrer deux types de modifications pouvant être faites sur les tables :
- la suppression d’un ensemble de lignes et
- la mise à jour de certains attributs d’un ensemble de lignes.
L'ordre DELETE FROM t WHERE c permet de supprimer de la table t toutes les lignes vérifiant la condition c.
Dans l'exemple de notre médiathèque, supposons que l'utilisateur Sébastien Petit, dont le code_barre est '934701281931582', ait rendu ses livres. Il faut supprimer de la table emprunt toutes les lignes pour lesquelles le code barre vaut '934701281931582', ce qui donne l’ordre suivant :
DELETE FROM emprunt WHERE code_barre = '934701281931582' ;
Après exécution de cet ordre, la recherche dans la table emprunt ne donne plus de résultats :
SELECT COUNT(*) AS total
FROM emprunt
WHERE code_barre = '934701281631582';
Attention, au même titre qu’une requête SELECT sans clause WHERE sélectionne toutes les lignes, un ordre DELETE sans clause WHERE efface toutes les lignes de la table. Il ne faut pas confondre « DELETE FROM » et « DROP TABLE + ». La première opération vide une table de son contenu, mais ne supprime pas la table.
Il est donc possible d’y ajouter de nouveau des données au moyen de l'instruction INSERT.
La seconde opération détruit la table (et ses données). La table ne peut donc plus être référencée.
Comme nous l'avons dit précédemment, les contraintes sont vérifiées à chaque mise à jour.
Essayons de supprimer le livre Hacker's delight de la
table livre, sachant que l’'ISBN de ce dernier est '978-0201914658'.
DELETE FROM livre WHERE isbn = '978-0201914658' ;
Ici, le SGBD nous indique que supprimer ce livre (et donc supprimer sa clé primaire de la table livre) violerait la contrainte de clé étrangère dans la table auteur_de.
Comme pour la destruction d’une table, il faut donc
supprimer en premier les lignes dont les attributs sont déclarés comme clés étrangères avant de supprimer celles contenant les clés primaires correspondantes.
Du point de vue de leur exécution, les ordres de modification de table sont soit entièrement exécutés, soit entièrement annulés.
Considérons la requête suivante :
DELETE FROM usager WHERE cp = '75001' OR cp = '75002' ;
qui efface de la table usager toutes les personnes dont le code postal est 75001 ou 75002. Si aucune de ces personnes n'apparaît dans la table emprunt, alors les suppressions peuvent être effectuées sans erreur.
Supposons maintenant que certaines de ces personnes ont emprunté un livre. Même si la SGDB rencontre en premier des personnes sans emprunt et les supprime, il lèvera une erreur dès qu’il rencontrera un usager référencé dans la table emprunt.
Dans ce cas, foutes les modifications déjà faites seront annulées et la table se trouvera dans l'état qu’elle avait avant la tentative d’exécution.
Les exécutions sont donc de type « tout où rien ».
Mettre le résultat ici (code et figure).
Le second type de modification est la mise à jour. Elle consiste à remplacer certains attributs d'un ensemble de lignes par de nouvelles valeurs.
La syntaxe est la suivante :
UPDATE t SET a1 = e1, ..., SET an = en WHERE c
Cette dernière signifie « sélectionne dans la table t toutes les lignes vérifiant la condition c et pour chacune de ces lignes, remplace la valeur courante de
l'attribut a par la valeur de l'expression e ».
Par exemple, si l'utilisateur Sébastien Petit souhaite mettre à jour son adresse email, on écrit ceci :
UPDATE utilisateur SET email = Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser. '
WHERE code_barre = '934701281931582';
Les expressions de mise à jour peuvent mentionner des noms d’attributs.
Ces derniers sont alors remplacés par la valeur courante (avant mise à jour) de ces attributs.
Supposons par exemple que la médiathèque soit fermée au mois d'avril. On souhaite que tous les emprunts dont la date de rendu était en avril soient prolongés de 30 jours.
UPDATE emprunt SET retour = retour + 30
WHERE retour >= '2020-04-01';
Dans la mise à jour précédente, la clause « SET retour = retour + 30 » est similaire à la modification d’une variable dans un langage de programmation comme Python, c’est-à-dire prendre la valeur courante de retour, y ajouter 30 et écrire la nouvelle valeur dans retour.
","title":"Mise à jour"},{"edit":"Mettre le résultat ici (code et figure).
Toute modification (création de table, suppression de table, mise à jour, suppression de ligne, insertion de ligne) qui ne viole pas de contrainte est définitive.
En cas de suppression ou de mise à jour, les anciennes données sont perdues. Il faut donc être particulièrement vigilant lors de la conception d’un programme effectuant des mises à jour dans une base de données.
Les bonnes pratiques recommandent l'utilisation de plusieurs « copies » de la base de données.
Une copie de test, utilisée pour le développement et une copie de « production » utilisée pour faire fonctionner le logiciel, une fois que ce dernier à été testé rigoureusement. La base de production doit par ailleurs être sauvegardée fréquemment pour éviter les risques liés à de mauvaises manipulations ou à des défaillances logicielles ou matérielles.
Comme nous l'avons vu, l’ordre « SELECT ... FROM » renvoie une table comme résultat. Il est possible de nommer cette dernière grâce au mot-clé INTO :
SELECT * INTO usager_paris FROM usager WHERE cp LIKE '75%';
Cet ordre crée une nouvelle table nommée usager_paris contenant le résultat de la requête.
Cette dernière a le même schéma que la table usager
car toutes les colonnes ont été copiées. En revanche, elle ne contiendra que les lignes pour lesquelles le code postal commence par 75.
La table usager_paris est ensuite utilisable comme n'importe quelle autre table. La clause WHERE permet de choisir les lignes à conserver. Par exemple,
SELECT * INTO usager2 FROM usager;
crée une copie conforme de usager alors que
SELECT * INTO usager3 FROM usager WHERE 1 = 0;
crée une table vide ayant le même schéma que usager (car la condition « 1 = 0») est toujours fausse.
Attention cependant, l'opération SELECT ... INTO ne copie pas les contraintes. Ainsi, dans la table usager3 ci-dessus, la colonne code_barre, bien qu’elle existe, n'est pas déclarée en tant que clé primaire. L'opération
SELECT INTO servira donc plutôt à sauvegarder un résultat temporaire de requête plutôt que créer une véritable copie.
Créer une copie conforme d’une table peut se faire en utilisant deux variations sur des opérations que nous connaissons bien.
La première est l'opération CREATE, utilsée comme ceci :
CREATE TABLE usager3 (LIKE usager INCLUDING ALL);
Cette syntaxe indique de créer la table usager3 comme une table de même schéma que usager, en incluant les contraintes (clés primaires, étrangères, CHECK, NOT NULL, ...).
L’inconvénient est que la table usager3 est vide. On peut cependant la remplir en utilisant une variation de l'order INSERT :
INSERT INTO usager3 (SELECT * FROM usager);
Ici, on dit d’insérer dans usager3 toutes les lignes renvoyées par la requête mise entre parenthèses. Cette dernière peut être arbitraire, mais doit renvoyer des lignes du même schéma que celles attendues pour la table usager3, en particulier on aurait pu utiliser une clause WHERE.
Mettre le résultat ici (code et figure).
L'opération « SELECT ... INTO » permet de sauver le résultat d’une requête sous un certain nom de table. Il est donc possible d'effectuer sur ce résultat une nouvelle requête.
Cependant, cette opération va occuper de l’espace de stockage. Il ne faudra donc pas oublier de supprimer la table ainsi créée. Il est possible de créer une table de manière temporaire et d'exécuter une requête sur cette table en imbriquant la première requête dans la clause « FROM » de la seconde ou dans une clause « JOIN ... ON »:
SELECT * FROM (SELECT * FROM livre
WHERE annee >= 1990) AS tmp
WHERE tmp.annee <= 2000;
La requête ci-dessus calcule d’abord une table intermédiaire nommée tmp qui liste les livres publiés après 1990. Suite à quoi, la table tmp est refiltrée pour
ne garder que les livres pour lesquels l’année est inférieure à 2000.
Attention, il ne s’agit ici que d’une explication de « haut niveau ». En pratique, n'importe quel SGBD moderne évaluera cette deux requêtes imbriquées comme la requête équivalente :
SELECT * FROM livre
WHERE annee >= 1990
AND annee <= 2000;
Une autre manière d’imbriquer les requêtes consiste à utiliser une sous-requête dans la clause WHERE. En effet, le langage SQL identifie les valeurs scalaires et les tables à une seule « case » telles que celles renvoyées par les fonctions d’agrégation.
Par exemple, si on souhaite afficher les titres des livres dont l’année est la plus ancienne dans la base, on pourra écrire :
SELECT titre FROM livre
WHERE annee = (SELECT MIN(annee)
FROM livre);
Ici, la sous-requête calcule l’année minimum de la table livre (1933 dans notre base), puis affiche tous les titres de livres dont l’année vaut 1933.
Attention, la sous-requête ne doit pas nécessairement comporter une fonction d’agrégation. Il suffit qu'elle renvoie une table contenant une seule valeur
.
Ainsi, si nous voulons afficher les titres des livres publiés la même année que Moby Dick (sans connaître cette année), nous pouvons écrire :
SELECT titre FROM livre
WHERE annee =
(SELECT annee
FROM livre
WHERE titre = ’Moby Dick’);
Mais attention, si la sous-requête renvoie plusieurs résultats, le SGBD renverra une erreur :
# SELECT titre FROM livre WHERE annee =
(SELECT annee FROM livre WHERE titre LIKE ?{Astérix#’);
ERROR: more than one row returned by a subquery
used as an expression
Un opérateur utilisant la puissance des requêtes imbriquées est l'opérateur IN. L'expression e IN (g) renvoie vrai si et seulement si la valeur résultant de l'évaluation de e est l’une des lignes renvoyées par la requête g. Ainsi, pour exprimer la requête « afficher les titres des livres qui ont été publiés la même année qu'un livre dont le titre contient Astérix », on pourra écrire :
SELECT titre FROM livres
WHERE annee IN
(SELECT annee FROM livres
WHERE titre LIKE '%Astérix/');
Mettre le résultat ici (code et figure).
. Les requêtes de groupe sont explicitement hors
programme. Leur compréhension peut cependant aider lors de la création d'exercices, en particulier pour déterminer si la requête demandée est réalisable dans le fragment de SQL du programme de terminale.
Ces requêtes s'expriment au moyen de l'opérateur GROUP BY (éventuellement. accompagné de l'opérateur HAVING). Intuitivement, ces requêtes
permettent, de répondre à la question « donner le f de x pour chaque g distinct de la table t » où f est une fonction d’agrégation, x un attribut de la table et g un autre attribut appelé clé de groupe.
Cette requête s’écrira alors
SELECT g, f(x) FROM t GROUP BY g
Par exemple, si on souhaite connaître le nombre (f) de Livres (t) publiés pour chaque année (g) de la base, on écrira :
SELECT annee, COUNT(*) FROM livre GROUP BY annee;
Mettre le résultat ici (code et figure).
Avant de commencer les exercices, vous devez réaliser les étapes suivantes pour mettre à jour votre base sur :
http://217.182.207.90/phpmyadmin/
Etape 1 : Exporter votre base de données.
Etape 2 : Effacer toutes les tables de la base.
Etape 3 : Importer la base mediathèque.
A partir de cette base donner et tester le code SQL de chacune des requêtes ci-dessous.
Les mots en police fixe donnent une indication sur les attributs et les tables à utiliser dans la requête.
1. Tous les titres de livre.
2. Tous les noms d'usager.
3. Tous les noms d'usager en retirant les doublons.
4. Les titres des livres publiés avant 1980.
5. Les titres des livres dont le titre contient la lettre « À ».
6. Les isbn des livres à rendre pour le 01/01/2020.
7. Les noms d'auteurs triés par ordre alphabétique.
8. Les noms d'usagers vivant dans le 12ème ou 13ème arrondissement de Paris (codes postaux 75012 et 75013).
9. Les noms et adresses des usagers n’habitant pas dans une rue. (la chaîne « Rue » ne doit pas apparaître dans l’adresse).
10. Les années et titres des livres publiés lors d’une année bissextile.
On rappelle que ce sont les années divisibles par 4, mais pas celles divisibles par 100 sauf si elles sont divisibles par 400.
","title":"Exercice : requêtes simples, sans jointure ni imbrication"},{"edit":"Mettre le résultat ici (code et figure).
***** 9 et 10 pb sol
En utilisant cette base, donner et tester le code SQL
de chacune des requêtes ci-dessous.
1. Le titre des livres empruntés.
2. Le titre des livres empruntés à rendre avant le 31/03/2020.
3. Le nom et prenom de l’auteur du livre 1984”
4. Le nom et le prenom des usagers ayant emprunté des livres, sans doublons (ie. si un usager à emprunté plusieurs livres, il ne doit apparaître qu'une fois dans le résultat).
5. Même requête que précédemment, avec les noms triés par ordre alphabétique.
6. Les titre des livres publiés strictement avant Dune”.
7. Les noms et prenoms des auteurs des livres trouvés à la question précédente,
8. Comme la question précédente, en retirant les doublons.
9. Le nombre de résultats trouvés à la question précédente.
Mettre le résultat ici (code et figure).
1.
SELECT titre FROM livre;
"},{"solution":"2.SELECT nom FROM usager;
3.
SELECT DISTINCT nom FROM usager;
"},{"solution":"4.
SELECT titre FROM livre WHERE annee <= 1980;
SELECT titre FROM livre WHERE annee <= 1980;
5.
SELECT titre FROM livre WHERE titre LIKE ’#A4’;
6.
SELECT isbn FROM emprunt WHERE retour = '2020-01-01';
SELECT isbn FROM emprunt WHERE retour = '2020-01-01';
7.
SELECT nom FROM auteur ORDER BY nom ASC;
SELECT nom FROM auteur ORDER BY nom ASC;
8.
SELECT nom FROM usager WHERE cp = '75012' OR cp = '75013';
SELECT nom FROM usager WHERE cp = '75012' OR cp = '75013';
9.
SELECT nom, adresse FROM usager
SELECT nom, adresse FROM usager
WHERE NOT (adresse LIKE ’%Rue%');
10.
SELECT annee, titre FROM livre WHERE annee % 4 = 0
AND (annee % 100 <> 0 OR annee % 400 = 0);
Dans ce dernier cas, on fera attention à l’utilisation des parenthèses, l’opérateur AND étant prioritaire sur le OR (comme en Python).
A l'aide de la base de données de la médiathèque, formuler simplement en francais les requêtes SQL suivantes.
1. SELECT * FROM livre WHERE titre LIKE '%Robot%';
2. SELECT nom, prenom FROM usager WHERE ville = 'Guingamp’;
3. SELECT u.nom, u.prenom
FROM usager AS u
JOIN emprunt AS e ON u.code_barre = e.code_barre
WHERE retour < '2020-04-02';
4. SELECT l.titre
FROM livre AS l
WHERE l.isbn IN (SELECT isbn FROM livres
WHERE annee > 1990);
5. Réécrire la requête 4 de façon à utiliser une seule clause SELECT.
Mettre le résultat ici (code et figure).
1. Renvoie tous les livres dont le titre contient « Robot ».
2. Renvoie le nom et le prénom des usagers habitant Guingamp.
3. Renvoie le nom et prénom des usagers devant rendre un livre avant le 2 avril 2020.
4. Renvoie les titres des livres publiés après 1990.
Cette dernière requête peut s’écrire :
SELECT l.titre
FROM livre AS l
WHERE l.annee > 1990;
En utilisant la base de données de la médiathèque, calculer tous les auteurs ayant collaboré sur un ouvrage et les renvoyer sous la forme (n1,p1,n2, p2, t) où les ni sont les noms des auteurs, pi leur prénoms et t le titre du livre sur lequel ils ont collaboré.
Si trois auteurs ont collaboré sur le même livre, on souhaite avoir trois lignes de résultats (auteur1/auteur2, auteur2/auteur3 et auteur1/auteur3) et non pas
les trois sur la même ligne.
Pour ne pas afficher deux fois le même couple, on demande en plus que n1 < n2.
Mettre le résultat ici (code et figure).
SELECT a1.nom, a1.prenom, a2.nom, a2.prenom, l.titre
FROM livre AS l
JOIN auteur_de AS ad1 ON l.isbn = ad1.isbn
JOIN auteur_de AS ad2 ON l.isbn = ad2.isbn
JOIN auteur AS a1 ON ad1.a_id = a1.a_id
JOIN auteur AS a2 ON ad2.a_id = a2.a_id
WHERE a1.nom < a2.nom;
Le cœur de cette requête est une jointure entre deux copies de la table auteur_de (nommée ad1 et ad2), sur le même isbn, celui que l’on utilise pour la jointure avec la table livre. La jointure entre ad1 et ad2 sur le
même isbn place sur la même ligne deux a_id de deux auteurs qui ont le même isbn. Il suffit alors de joindre avec les tables contenant les données.
On considère les trois tables décrites à la figure ci-dessous. Pour chacune des requêtes SQL ci-dessous, caleuler son résultat (à la main).
1. SELECT * FROM x WHERE b > 3;
2. SELECT DISTINCT e FROM z
WHERE e > 10 AND e < 50;
3. SELECT * FROM y WHERE c % 2 = 0 ORDER BY d ASC;
4. SELECT x.a , x.b FROM x
JOIN z ON z.a = x.a
WHERE z.e < 9;
5. SELECT DISTINCT x.b , y.d FROM x
JOIN z ON z.a = x.a
CREATE TABLE x (a INT PRIMARY KEY, b INT, CHECK (b >= 0));
CREATE TABLE y (c INT PRIMARY KEY, d INT, CHECK (d <= 30));
CREATE TABLE z (a INT REFERENCES X(a),
c INT REFERENCES Y(c), e INT, UNIQUE (a,c));
| x: | y: | z: | ||||||
| a | b | c | d | a | c | e | ||
| 1 | 1 | 9 | 9 | 1 | 11 | 30 | ||
| 2 | 2 | 10 | 10 | 2 | 14 | 9 | ||
| 2 | 2 | 11 | 9 | 5 | 15 | 1 | ||
| 4 | 2 | 12 | 20 | 7 | 17 | 3 | ||
| 5 | 1 | 13 | 30 | 1 | 10 | 50 | ||
| 6 | 9 | 14 | 9 | 2 | 9 | 8 | ||
| 7 | 1 | 15 | 1 | 2 | 15 | 15 | ||
| 16 | 10 | 3 | 17 | 19 | ||||
| 17 | 10 | 4 | 16 | 12 | ||||
| 5 | 10 | 20 | ||||||
| 2 | 11 | 30 | ||||||
| 7 | 14 | 9 | ||||||
| 7 | 9 | 12 |
Mettre le résultat ici (code et figure).
| a | b |
| 6 | 9 |
| e |
| 12 15 19 20 30 |
3.
| c | d |
| 14 10 16 12 | 9 10 10 20 |
4.
| a | b |
| 5 7 2 | 1 1 2 |
5.
| b | d |
| 1 1 2 1 2 2 | 1 10 10 9 1 9 |
On considère les trois tables ci-dessous ;
| x: | y: | z: | ||||||
| a | b | c | d | a | c | e | ||
| 1 | 1 | 9 | 9 | 1 | 11 | 30 | ||
| 2 | 2 | 10 | 10 | 2 | 14 | 9 | ||
| 2 | 2 | 11 | 9 | 5 | 15 | 1 | ||
| 4 | 2 | 12 | 20 | 7 | 17 | 3 | ||
| 5 | 1 | 13 | 30 | 1 | 10 | 50 | ||
| 6 | 9 | 14 | 9 | 2 | 9 | 8 | ||
| 7 | 1 | 15 | 1 | 2 | 15 | 15 | ||
| 16 | 10 | 3 | 17 | 19 | ||||
| 17 | 10 | 4 | 16 | 12 | ||||
| 5 | 10 | 20 | ||||||
| 2 | 11 | 30 | ||||||
| 7 | 14 | 9 | ||||||
| 7 | 9 | 12 |
CREATE TABLE x (a INT PRIMARY KEY, b INT, CHECK (b >= 0));
CREATE TABLE y (c INT PRIMARY KEY, d INT, CHECK (d <= 30));
CREATE TABLE z (a INT REFERENCES X(a),
c INT REFERENCES Y(c), e INT, UNIQUE (a,c));
Pour chaeune des modifications ci-dessous, indiquer si elle réussit ou si elle échoue. Si elle réussit, indiquer comment la table est modifiée. Si elle échoue, expliqner
pourquoi.
Les questions sont indépendantes les unes des autres.
1, UPDATE x SET b = b +a;
2. UPDATE x SET b = b - 2;
3. INSERT INTO z VALUES (1, 17, 1);
4. INSERT INTO z VALUES (1, 18, 1};
5. INSERT INTO z VALUES (1, 10, 1);
6. DELETE FROM y WHERE c >= 12 AND c <= 13;
7. DELETE FROM y WHERE c >= 12 AND c <= 14;
8. INSERT INTO y VALUES (40, 20);
9. INSERT INTO y VALUES (20, 40);
10. DELETE FROM z
WHERE a % 2 = 0 OR c % 2 = 0 0R e % 2 = 0;
Mettre le résultat ici (code et figure).
1. Succès. La table x devient :
| a | b |
| 1 2 3 4 5 6 7 | 2 4 5 6 6 15 8 |
2. Échec. La ligne (1,1) deviendrait (1,-1) après mise à jour, ce qui viole la contrainte « CHECK (b >= 0) ».
3. Succès. La ligne (1,17,1) est ajoutée à la table z.
4. Échec. La valeur 18 n’est pas une clé primaire dans la table y.
5. Échec. Une ligne (1,10,50) est déjà présente dans la table.
6. Succès. La table y devient :
| c | d |
| 9 10 11 14 15 16 17 | 9 10 9 9 1 10 10 |
7. Échec. La table z contient des entrées avec 11 ou 14. Les clés primaires correspondantes dans y ne peuvent pas être supprimées.
8. Succès. La ligne (40,20) est ajoutée à la table y.
9. Échec. La valeur 40 viole la contrainte « CHECK a <= 30 »
10. Succès. La table z devient :
| a | c | e |
| 5 7 3 | 15 17 17 | 1 3 19 |