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