Aller au contenu

TP - Médiathèque

nom image







I. Fonctions de groupes (agrégations)⚓︎




Fonctions de groupes


Les fonctions de groupe permettent d’obtenir des informations sur un ensemble de lignes en travaillant sur les colonnes et non pas sur les lignes comme avec la clause WHERE. 


Par exemple :  


    

  • AVG calcule la moyenne d’une colonne;  
    • 

  • SUM calcule la somme d’une colonne;  
    • 

  • MIN, MAX calculent le minimum et le maximum d’une colonne;  
    • 

  • COUNT donne le nombre de lignes d’une colonne.  
    • 







exemples


1. Quelle est la plus petite date de retour ?
















2. Avec WHERE : Quelle est la plus ancienne année d’achat des livres parus chez «Plon» ?
















3. Comptons combien de livres de l’éditeur 'Flammarion' possède cette médiathèque
















4. 👉 En fait, on ne fait que compter le nombre de ligne, donc la taille de la colonne. Le nom de la colonne n’a pas d’importance. On écrit plutôt : 



















À vous de jouer 1

Combien de livres contiennent la chaîne "Astérix" dans leur titre ?

















Astuce

Utiliser LIKE et le joker %





Solution

SELECT COUNT(titre)
FROM livre
WHERE titre LIKE "%Astérix%";



👉 Nous pouvons renommer la colonne de résultat :


Essayer ci-dessus : 


SELECT COUNT(titre) AS total_avec_Astérix
FROM livre
WHERE titre LIKE "%Astérix%";








À vous de jouer 2

1. Quelle est l'année du livre le plus ancien de cette base de donnée ? (colonne nommée : le_plus_ancien)

















Solution

SELECT MIN(annee) AS le_plus_ancien
FROM livre;





2. Quelle est l'année du livre le plus récent de cette base de donnée ? (colonne nommée : le_plus_récent)

















Solution

SELECT MAX(annee) AS le_plus_récent
FROM livre;







II. Suppression des données⚓︎




Supprimer


DELETE FROM [Table] WHERE [condition]






Exemples


Par exemple, l’usager Sébastien Petit , dont le code barre est 934701281931582 a rendu ses livres. Il faut supprimer de la table emprunt toutes les lignes pour lesquelles le code_barre vaut 934701281931582


Combien d’enregistrement avec le code_barre 934701281931582 contient la table emprunt ?
















Combien d’enregistrement avec le code_barre 934701281931582 contient maintenant la table emprunt ?




















Attention


🌵🌵 Attention : une commande DELETE sans clause WHERE efface toutes les lignes de la table…

😢Essayez en exécutant les cellules ci-dessous (seulement une fois…)
















Un petit coup d'œil : 




















Réparer


👉 On va réparer ...


Dans la vraie vie, ce n'est pas si simple ! 


Rafraichir le site




















Peut-on supprimer n’importe quelle ligne ?


Essayons de supprimer le livre "Hacker's Delight" sachant que son code ISBN est 978-0201914658
















Le SGBD nous indique que supprimer ce livre, et donc sa clé primaire, violerait la contrainte de clé étrangère. En effet, le code isbn est une clé étrangère dans la table auteur_de.






On ne peut pas supprimer n'importe quoi


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.





À vous de jouer 3

Réaliser les requêtes qui permettent de supprimer le livre ‘Hacker's Delight’ sachant que son code ISBN est 978-0201914658

















Solution

DELETE 
FROM auteur_de
WHERE isbn = '978-0201914658';

DELETE 
FROM livre 
WHERE isbn = '978-0201914658';





Vérifier : 

















Solution

SELECT *
FROM livre
WHERE isbn = '978-0201914658';








À vous de jouer 4

Supprimer de la table usager Monsieur 'BERNARD' qui a déménagé :

















Aide 1

M. BERNARD apparaît dans la table emprunt.


Il doit commencer par rendre ses livres avant d’être supprimé.





Aide 2

Ecrire les commandes qui :


    

  • déterminent combien de livres M. BERNARD doit rendre
    • 

  • déterminent le code_barre de M. BERNARD
    • 

  • suppriment les livres correspondant de la table emprunt lorsqu’il les a rendus
    • 

  • suppriment M. BERNARD de la table des usagers.
    • 






Solution

SELECT COUNT(*)
FROM emprunt 
JOIN usager ON usager.code_barre = emprunt.code_barre
WHERE nom = 'BERNARD';

SELECT code_barre 
FROM usager 
WHERE nom = 'BERNARD';



Après avoir lu le résultat : 


DELETE  
FROM emprunt
WHERE code_barre = '035184062854281';   

DELETE 
FROM usager 
WHERE nom = 'BERNARD';





Vérifier en affichant le nombre d'usagers de nom 'BERNARD'

















Solution

SELECT COUNT(*)
FROM usager
WHERE nom = 'BERNARD';







III. Modifications⚓︎




Modifier


UPDATE [Table] SET [attribut = valeur] WHERE [attribut = valeur]






Exemple



















À vous de jouer 5

Le code_barre de Madame SIMON a été mal saisi. Essayer de le modifier. Que se passe-t-il ?



















Solution

UPDATE usager 
SET code_barre = '934701281931234'
WHERE nom = 'SIMON';



code_barre est la clé primaire de usager et ne peut pas être modifiée.





À vous de jouer 6

Ecrire les commandes pour trouver le prénom de l'auteur Goscinny.

















Solution

SELECT prenom 
FROM auteur 
WHERE nom = 'Goscinny';








À vous de jouer 7

Ecrire les commandes pour mettre à jour la table auteur : le prénom de Goscinny est René. Puis vérifier que la modification a bien été effectuée.

















Solution

UPDATE auteur 
SET prenom = 'René' 
WHERE nom = 'Goscinny';

SELECT prenom
FROM auteur 
WHERE nom = 'Goscinny';








À vous de jouer 8

La table auteur comporte plusieurs fois le nom Goscinny. Donner le code SQL de la requête qui affiche tous ces enregistrements

















Solution

SELECT * 
FROM auteur  
WHERE nom = 'Goscinny';








À vous de jouer 9

En utilisant la question précédente donner le code SQL de la requête qui affiche tous les titres de livres dont l’auteur est Goscinny.

















Solution

SELECT titre 
FROM livre
JOIN auteur_de  ON livre.isbn = auteur_de.isbn
WHERE a_id = 5 OR a_id = 60 OR a_id = 79;



On aurait pu ne pas utiliser les résultats de la question précédente, et réaliser une double jointure : 


sql title = ""
SELECT titre 
FROM livre
JOIN auteur_de ON livre.isbn = auteur_de.isbn
JOIN auteur ON auteur_de.a_id = auteur.a_id
WHERE auteur.nom = "Goscinny"






IV. Exercices⚓︎



Exercice 1

Donner le code SQL de la requête qui affiche tous les noms d’auteurs sans doublons classés par ordre alphabétique.

















Solution

SELECT DISTINCT nom 
FROM auteur
ORDER BY nom ASC;








Exercice 2

Donner le code SQL de la requête qui fait la mise à jour suivante : l’éditeur de "L'Idiot" est "Folio"

















Solution

UPDATE livre 
SET editeur = "Folio" 
WHERE titre = "L'Idiot";








Exercice 3

Donner le code SQL de la requête qui affiche le nom et le prénom de l’auteur du livre "1984"

















Solution

SELECT a.nom,a.prenom 
FROM auteur AS a
JOIN auteur_de AS ad ON ad.a_id = a.a_id
JOIN livre AS l ON l.isbn = ad.isbn
WHERE l.titre = '1984';








Exercice 4

Donner le code SQL de la requête qui affiche le nom et le prénom des usagers ayant emprunté des livres, sans doublons (si un usager a emprunté plusieurs livres il doit apparaître une seule fois) triés par ordre alphabétique du nom.

















Solution

SELECT DISTINCT u.nom, u.prenom 
FROM emprunt AS e
JOIN usager AS u ON e.code_barre = u.code_barre
ORDER BY u.nom ASC;








Exercice 5

Donner le code SQL de la requête qui affiche le nombre de livres parus en 2014 dans la médiathèque.

















Solution

SELECT COUNT(*) FROM livre
WHERE annee = 2014;







Crédits⚓︎


Mireille COILHAC - Lycée Saint-Aspais de Melun (77)

D'après NSI 24 leçons avec exercices corrigés – ellipses