uels sont les livres empruntés aujourdhui ?

select NL
From emprunter
where datemp= current_date;
select extrat(year from now)

(a) Quels sont les livres actuellement empruntés ?

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
WHERE emprunter.dateRet IS NULL;
(b) Quels sont les livres empruntés par Jeannete Lecoeur ? Vérifier dans la réponse
qu'il n'y a pas d'homonymes.

sql
Copy code
SELECT DISTINCT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
INNER JOIN adherents ON emprunter.NA = adherents.NA
WHERE adherents.nom = 'Lecoeur' AND adherents.prenom = 'Jeannete';
(c) Quels sont tous les livres empruntés le mois dernier ?

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
WHERE MONTH(emprunter.datEmp) = MONTH(CURRENT_DATE()) - 1
AND YEAR(emprunter.datEmp) = YEAR(CURRENT_DATE());
(d) Tous les adhérents qui ont emprunté un livre de Fedor Dostoievski.

sql
Copy code
SELECT DISTINCT adherents.nom, adherents.prenom
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
INNER JOIN livres ON emprunter.NL = livres.NL
INNER JOIN oeuvres ON livres.NO = oeuvres.NO
WHERE oeuvres.auteur = 'Fedor Dostoievski';
(e) Quels sont le ou les auteurs du titre « Voyage au bout de la nuit » ?

sql
Copy code
SELECT auteur
FROM oeuvres
WHERE titre = 'Voyage au bout de la nuit';
(f) Quels sont les éditeurs du titre « Narcisse et Goldmund » ?

sql
Copy code
SELECT editeur
FROM livres
WHERE NO = (SELECT NO FROM oeuvres WHERE titre = 'Narcisse et Goldmund');
(g) Quels sont les adhérents actuellement en retard ?

sql
Copy code
SELECT DISTINCT adherents.nom, adherents.prenom
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
WHERE emprunter.dateRet IS NULL AND emprunter.dateRet > emprunter.dureeMax;
(h) Quels sont les livres actuellement en retard ?

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
WHERE emprunter.dateRet IS NULL AND emprunter.dateRet > emprunter.dureeMax;
Cela couvre les premières questions. Souhaitez-vous que je continue avec les
autres?

User
Oui
ChatGPT
D'accord, continuons avec les requêtes suivantes :

(i) Quels sont les adhérents en retard avec le nombre de livres en retard et la
moyenne du nombre de jours de retard ?

sql
Copy code
SELECT adherents.nom, adherents.prenom, COUNT(*) AS 'Nombre de livres en retard',
AVG(DATEDIFF(CURRENT_DATE(), emprunter.dureeMax)) AS 'Moyenne de jours de retard'
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
WHERE emprunter.dateRet IS NULL AND emprunter.dateRet > emprunter.dureeMax
GROUP BY adherents.nom, adherents.prenom;
(j) Nombre de livres empruntés par auteur.

sql
Copy code
SELECT oeuvres.auteur, COUNT(*) AS 'Nombre de livres empruntés'
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
GROUP BY oeuvres.auteur;
(k) Nombre de livres empruntés par éditeur.

sql
Copy code
SELECT livres.editeur, COUNT(*) AS 'Nombre de livres empruntés'
FROM livres
INNER JOIN emprunter ON livres.NL = emprunter.NL
GROUP BY livres.editeur;
(l) Nombre de livres empruntés par auteur et par éditeur.

sql
Copy code
SELECT oeuvres.auteur, livres.editeur, COUNT(*) AS 'Nombre de livres empruntés'
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
GROUP BY oeuvres.auteur, livres.editeur;
(m) Quelle est la durée moyenne des emprunts rendus ?

sql
Copy code
SELECT AVG(DATEDIFF(emprunter.dateRet, emprunter.datEmp)) AS 'Durée moyenne des
emprunts rendus'
FROM emprunter
WHERE emprunter.dateRet IS NOT NULL;
Cela complète les requêtes pour les questions restantes. Si vous avez besoin de
plus d'explications ou d'autres requêtes, n'hésitez pas à demander!

User
Continue
ChatGPT
Passons maintenant à la mise à jour de la base de données :

La bibliothèque - Mise à jour

(a) La bibliothèque vient d'acquérir un nouveau exemplaire de : « Au cœur des
ténèbres » de Joseph Conrad, chez Gallimard. Faire la mise à jour de la BD.

sql
Copy code
INSERT INTO oeuvres (titre, auteur) VALUES ('Au cœur des ténèbres', 'Joseph
Conrad');
INSERT INTO livres (editeur, NO) VALUES ('Gallimard', LAST_INSERT_ID());
(b) Écrire les commandes qui permettent de retirer tout ce que vous avez ajouté.

sql
Copy code
DELETE FROM livres WHERE NO = LAST_INSERT_ID();
DELETE FROM oeuvres WHERE NO = LAST_INSERT_ID();
(c) La bibliothèque vient d'acquérir un nouveau livre de Hermann Hesse intitulé «
Siddhartha » chez Folio. Faire la mise à jour de la BD.

sql
Copy code
INSERT INTO oeuvres (titre, auteur) VALUES ('Siddhartha', 'Hermann Hesse');
INSERT INTO livres (editeur, NO) VALUES ('Folio', LAST_INSERT_ID());
(d) Écrire les commandes qui permettent de retirer tout ce que vous avez ajouté
(supprimer une œuvre et les livres correspondants).

sql
Copy code
DELETE FROM livres WHERE NO = LAST_INSERT_ID();
DELETE FROM oeuvres WHERE NO = LAST_INSERT_ID();
(e) Un nouvel adhérent vient s'inscrire : Olivier DUPOND, 76, quai de la Loire,
75019 Paris, téléphone : 0102030405

sql
Copy code
INSERT INTO adherents (nom, prenom, adr, tel) VALUES ('DUPOND', 'Olivier', '76,
quai de la Loire, 75019 Paris', '0102030405');
(f) Martine CROZIER vient emprunter « Au cœur des ténèbres » que vous venez
d'ajouter à la question 2 ET « Le rouge et le noir » chez Hachette, livre n°23.
Faire les mises à jour de la BD pour les deux emprunts.

sql
Copy code
-- Ajout de Martine CROZIER
INSERT INTO adherents (nom, prenom) VALUES ('CROZIER', 'Martine');

-- Emprunt de "Au cœur des ténèbres"

INSERT INTO emprunter (NL, datEmp, dureeMax, NA) VALUES ((SELECT NL FROM livres
WHERE NO = (SELECT NO FROM oeuvres WHERE titre = 'Au cœur des ténèbres')),
CURDATE(), 14, (SELECT NA FROM adherents WHERE nom = 'CROZIER' AND prenom =
'Martine'));

-- Emprunt de "Le rouge et le noir"

INSERT INTO emprunter (NL, datEmp, dureeMax, NA) VALUES (23, CURDATE(), 14, (SELECT
NA FROM adherents WHERE nom = 'CROZIER' AND prenom = 'Martine'));
(g) M. Cyril FREDERIC ramène les livres qu'il a empruntés. Faire la mise à jour de
la BD.

sql
Copy code
DELETE FROM emprunter WHERE NA = (SELECT NA FROM adherents WHERE nom = 'FREDERIC'
AND prenom = 'Cyril');
(h) M. Cyril FREDERIC essaie d'emprunter le livre n°23. Que constatez-vous ?

sql
Copy code
SELECT * FROM emprunter WHERE NA = (SELECT NA FROM adherents WHERE nom = 'FREDERIC'
AND prenom = 'Cyril') AND NL = 23;
Vous devriez obtenir aucun résultat, car le livre n°23 est déjà emprunté par
Martine CROZIER.

(i) M. Cyril FREDERIC essaie d'emprunter le livre n°29. Que constatez-vous ?

sql
Copy code
SELECT * FROM livres WHERE NL = 29;
Vous devriez obtenir aucun résultat, car le livre n°29 n'existe pas dans la base de
données.

Cela couvre les mises à jour de la base de données. Si vous avez d'autres questions
ou besoin de clarifications, n'hésitez pas à demander!

User
Continue
ChatGPT
Bien sûr, continuons avec les requêtes de la section 3 sur les requêtes avec
thématiques :

La bibliothèque - Requêtes avec Thématiques

(a) Quels sont les livres "jeunesse" actuellement empruntés ?

sql
Copy code
SELECT DISTINCT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN thematique ON oeuvres.NO = thematique.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
INNER JOIN categories ON thematique.NC = categories.NC
WHERE categories.categorie = 'jeunesse';
(b) Afficher les catégories des livres actuellement empruntés.

sql
Copy code
SELECT DISTINCT categories.categorie
FROM categories
INNER JOIN thematique ON categories.NC = thematique.NC
INNER JOIN oeuvres ON thematique.NO = oeuvres.NO
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL;
(c) Afficher la liste des catégories par œuvre.

sql
Copy code
SELECT oeuvres.titre, GROUP_CONCAT(categories.categorie SEPARATOR ', ') AS
'Catégories'
FROM oeuvres
INNER JOIN thematique ON oeuvres.NO = thematique.NO
INNER JOIN categories ON thematique.NC = categories.NC
GROUP BY oeuvres.titre;
(d) Quels sont les adhérents ayant emprunté des livres "jeunesse" ?

sql
Copy code
SELECT DISTINCT adherents.nom, adherents.prenom
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
INNER JOIN livres ON emprunter.NL = livres.NL
INNER JOIN thematique ON livres.NO = thematique.NO
INNER JOIN categories ON thematique.NC = categories.NC
WHERE categories.categorie = 'jeunesse';
(e) Combien de titres "jeunesse" ont été empruntés par adhérents ? Commencer par
lister les emprunts "jeunesse". Attention à compter les titres et pas les emprunts.

sql
Copy code
SELECT adherents.nom, adherents.prenom, COUNT(DISTINCT oeuvres.titre) AS 'Nombre de
titres empruntés'
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
INNER JOIN livres ON emprunter.NL = livres.NL
INNER JOIN thematique ON livres.NO = thematique.NO
INNER JOIN categories ON thematique.NC = categories.NC
INNER JOIN oeuvres ON thematique.NO = oeuvres.NO
WHERE categories.categorie = 'jeunesse'
GROUP BY adherents.nom, adherents.prenom;
(f) Dans la requête précédente, afficher en plus la liste des titres empruntés.

sql
Copy code
SELECT adherents.nom, adherents.prenom, COUNT(DISTINCT oeuvres.titre) AS 'Nombre de
titres empruntés', GROUP_CONCAT(DISTINCT oeuvres.titre SEPARATOR ', ') AS 'Titres
empruntés'
FROM adherents
INNER JOIN emprunter ON adherents.NA = emprunter.NA
INNER JOIN livres ON emprunter.NL = livres.NL
INNER JOIN thematique ON livres.NO = thematique.NO
INNER JOIN categories ON thematique.NC = categories.NC
INNER JOIN oeuvres ON thematique.NO = oeuvres.NO
WHERE categories.categorie = 'jeunesse'
GROUP BY adherents.nom, adherents.prenom;
(g) Afficher le nombre d'emprunts par catégorie.

sql
Copy code
SELECT categories.categorie, COUNT(*) AS 'Nombre d\'emprunts'
FROM categories
INNER JOIN thematique ON categories.NC = thematique.NC
INNER JOIN oeuvres ON thematique.NO = oeuvres.NO
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
GROUP BY categories.categorie;
(h) Afficher la durée moyenne des emprunts par catégorie.

sql
Copy code
SELECT categories.categorie, AVG(DATEDIFF(emprunter.dateRet, emprunter.datEmp)) AS
'Durée moyenne des emprunts'
FROM categories
INNER JOIN thematique ON categories.NC = thematique.NC
INNER JOIN oeuvres ON thematique.NO = oeuvres.NO
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
GROUP BY categories.categorie;
Cela conclut les requêtes pour la section 3 sur les requêtes avec thématiques. Si
vous avez d'autres questions ou besoin de plus de détails, n'hésitez pas à
demander!

User
Continue
ChatGPT
Bien sûr, passons maintenant aux requêtes de la section 4 sur les jointures
externes et/ou requêtes imbriquées :

La bibliothèque - Jointures externes et/ou requêtes imbriquées

(a) Quels sont les adhérents qui n'ont jamais emprunté de livres ?

sql
Copy code
SELECT adherents.nom, adherents.prenom
FROM adherents
LEFT JOIN emprunter ON adherents.NA = emprunter.NA
WHERE emprunter.NA IS NULL;
(b) Quels sont les livres qui n'ont jamais été empruntés ?

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
LEFT JOIN livres ON oeuvres.NO = livres.NO
LEFT JOIN emprunter ON livres.NL = emprunter.NL
WHERE emprunter.NL IS NULL;
(c) Combien d'exemplaires du titre "Narcisse et Goldmund" sont disponibles ?

sql
Copy code
SELECT COUNT(*) AS 'Nombre d\'exemplaires disponibles'
FROM livres
LEFT JOIN emprunter ON livres.NL = emprunter.NL
WHERE livres.NO = (SELECT NO FROM oeuvres WHERE titre = 'Narcisse et Goldmund') AND
emprunter.NL IS NULL;
(d) On commencera par afficher tous les "Narcisse". Puis tous les "Narcisse"
actuellement empruntés. Ensuite, les "Narcisse" disponibles avec une requête
imbriquée. Enfin, les "Narcisse" disponibles avec une jointure externe.

Voici comment réaliser cela :

i. Afficher tous les "Narcisse" :

sql
Copy code
SELECT *
FROM oeuvres
WHERE titre LIKE '%Narcisse%';
ii. Afficher tous les "Narcisse" actuellement empruntés :

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL
WHERE oeuvres.titre LIKE '%Narcisse%';
iii. Afficher les "Narcisse" disponibles avec une requête imbriquée :

sql
Copy code
SELECT *
FROM oeuvres
WHERE titre LIKE '%Narcisse%'
AND NO NOT IN (SELECT livres.NO FROM livres INNER JOIN emprunter ON livres.NL =
emprunter.NL);
iv. Afficher les "Narcisse" disponibles avec une jointure externe :

sql
Copy code
SELECT oeuvres.titre
FROM oeuvres
LEFT JOIN livres ON oeuvres.NO = livres.NO
LEFT JOIN emprunter ON livres.NL = emprunter.NL
WHERE oeuvres.titre LIKE '%Narcisse%' AND emprunter.NL IS NULL;
Cela couvre les requêtes pour la section 4. Si vous avez d'autres questions ou
besoin de plus d'explications, n'hésitez pas à demander!

User
Continue
ChatGPT
Bien sûr, poursuivons avec les requêtes de la section 5 sur les requêtes avancées :

La bibliothèque - Requêtes avancées

(a) Pour le titre « Narcisse et Goldmund », afficher dans une même requête :

le nombre d'exemplaires total,

le nombre d'exemplaires disponibles,
et le nombre d'exemplaires actuellement empruntés.
Voici la requête correspondante :

sql
Copy code
SELECT
COUNT(*) AS 'Nombre total d\'exemplaires',
(SELECT COUNT(*) FROM livres WHERE NO = (SELECT NO FROM oeuvres WHERE titre =
'Narcisse et Goldmund') AND NL NOT IN (SELECT NL FROM emprunter)) AS 'Nombre
d\'exemplaires disponibles',
(SELECT COUNT(*) FROM livres WHERE NO = (SELECT NO FROM oeuvres WHERE titre =
'Narcisse et Goldmund') AND NL IN (SELECT NL FROM emprunter)) AS 'Nombre
d\'exemplaires empruntés'
FROM livres
WHERE NO = (SELECT NO FROM oeuvres WHERE titre = 'Narcisse et Goldmund');
(b) Quelle est la moyenne du nombre de livres empruntés par adhérent ?

sql
Copy code
SELECT AVG(nb_emprunts) AS 'Moyenne du nombre de livres empruntés par adhérent'
FROM (
SELECT COUNT(*) AS nb_emprunts
FROM emprunter
GROUP BY NA
) AS subquery;
(c) Refaire la question 2 pour tous les titres actuellement sortis au moins une
fois.

Pour cela, nous devons d'abord obtenir la liste de tous les titres qui ont été
empruntés au moins une fois, puis nous pouvons réutiliser les requêtes de la
question 2 pour chacun de ces titres. Voici comment faire cela en plusieurs
étapes :

i. Obtenir la liste de tous les titres empruntés au moins une fois :

sql
Copy code
SELECT DISTINCT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL;
Ensuite, pour chaque titre obtenu dans cette requête, vous pouvez répéter les
étapes de la question 2 (a) à (m).

Si vous avez besoin d'aide pour une partie spécifique de cette requête, n'hésitez
pas à demander !

User
Continue
ChatGPT
Pour continuer avec la section 5(c), nous allons réutiliser les requêtes de la
question 2 pour chacun des titres obtenus dans la requête qui récupère la liste de
tous les titres empruntés au moins une fois. Voici comment le faire en plusieurs
étapes :

i. Obtenir la liste de tous les titres empruntés au moins une fois :

sql
Copy code
SELECT DISTINCT oeuvres.titre
FROM oeuvres
INNER JOIN livres ON oeuvres.NO = livres.NO
INNER JOIN emprunter ON livres.NL = emprunter.NL;
ii. Pour chaque titre obtenu dans cette requête, répéter les étapes de la question
2 (a) à (m).

Cela peut être réalisé en utilisant une boucle dans un langage de programmation ou
en exécutant manuellement les requêtes pour chaque titre. Par exemple, pour un
titre spécifique (disons "Le rouge et le noir"), vous pouvez exécuter les requêtes
de cette manière :

sql
Copy code
-- Titre : Le rouge et le noir
-- Requêtes de la question 2 (a) à (m) pour ce titre
Vous pouvez répéter cette démarche pour chaque titre obtenu dans la première
requête.

Si vous avez besoin d'aide pour exécuter ces requêtes dans votre environnement
spécifique ou si vous avez d'autres questions, n'hésitez pas à demander !