Requete Imbriquee

Requête Imbriquée

Pour la suite du cours, nous allons nous entrainer sur des requêtes imbriquées.

Afin d'avoir un cas concret et un contexte favorable à ce type de requête, nous allons modéliser la base de données d'une bibliothèque.

Comme nous sommes encore au début de notre entrainement, nous prendrons une version minimale (on ne va pas se rajouter trop de travail :p) :

Quels sont les sujets d'une bibliothèque ?

- Des livres

- Des abonnés (ou adhérent, ou client).

Et nous aurons également une table qui permet de savoir quel livre à été emprunté par quel abonné et à quelle date (en gros: qui a emprunté quoi et quand ?).

Nous appelerons cette table : emprunt.

Ceci nous permettra également de réaliser des requêtes sur plusieurs tables en même temps.

Création de la Base de données : bibliotheque

Pour ce faire, vous pouvez utiliser la console mysql ou phpmyadmin :

Exercice : Création de tables

L'objectif du prochain exercice sera de créer les tables suivantes :

	Table : Abonne
Field	Type	Null	Key	Default	Extra
id_abonne	int(3)	NO	PRI		auto_increment
prenom	varchar(20)	NO

Explications : id_abonne représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Nous garderons en mémoire uniquement le prenom de chaque abonné mais nous pourrions enregistrer d'autres informations comme son nom, adresse, etc.

	Table : Livre
Field	Type	Null	Key	Default	Extra
id_livre	int(3)	NO	PRI		auto_increment
auteur	varchar(30)	NO
titre	varchar(30)	NO

Explications : id_livre représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Chaque livre possède un titre et a été écrit par un auteur (nous aurions pu également enregistrer d'autres informations).

	Table : Emprunt
Field	Type	Null	Key	Default	Extra
id_emprunt	int(3)	NO	PRI		auto_increment
id_livre	int(3)	YES
id_abonne	int(3)	YES
date_sortie	date	NO
date_rendu	date	YES

Explications : id_emprunt représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Dans la table emprunt, id_livre sera un champ clé étrangère en référence au champ id_livre de la table livre.
De la même manière, le champ id_abonne de la table emprunt sera une clé étrangère au champ id_abonne de la table abonne.

Pour effectuer la création, libre à vous d'utiliser PhpMyAdmin ou la Console Mysql.

Correction : Création de tables

Voici le code SQL permettant de créer vos 3 tables :

CREATE DATABASE IF NOT EXISTS  bibliotheque ;

USE bibliotheque ;

CREATE TABLE abonne (
  id_abonne INT(3) NOT NULL AUTO_INCREMENT,
  prenom VARCHAR(20) NOT NULL,
  PRIMARY KEY (id_abonne)
) ENGINE=InnoDB ;

CREATE TABLE emprunt (
  id_emprunt INT(3) NOT NULL AUTO_INCREMENT,
  id_livre INT(3) DEFAULT NULL,
  id_abonne INT(3) DEFAULT NULL,
  date_sortie DATE NOT NULL,
  date_rendu DATE DEFAULT NULL,
  PRIMARY KEY  (id_emprunt)
) ENGINE=InnoDB ;

CREATE TABLE livre (
  id_livre INT(3) NOT NULL AUTO_INCREMENT,
  auteur VARCHAR(30) NOT NULL,
  titre VARCHAR(30) NOT NULL,
  PRIMARY KEY (id_livre)
) ENGINE=InnoDB ;

Nous pourrions ajouter les clés étrangères comme ceci :

ALTER TABLE emprunt ADD FOREIGN KEY ( id_livre ) REFERENCES bibliotheque.livre (id_livre);

ALTER TABLE emprunt ADD FOREIGN KEY ( id_abonne ) REFERENCES bibliotheque.abonne (id_abonne);

---

Exercice : Insertion du contenu

Maintenant que nous avons nos 3 tables, il faut penser à insérer du contenu à l'intérieur.

Voici les données attendues :

abonne
id_abonne	prenom
1	Guillaume
2	Benoit
3	Chloe
4	Laura

livre
id_livre	auteur	titre
100	GUY DE MAUPASSANT	Une vie
101	GUY DE MAUPASSANT	Bel-Ami
102	HONORE DE BALZAC	Le père Goriot
103	ALPHONSE DAUDET	Le Petit chose
104	ALEXANDRE DUMAS	La Reine Margot
105	ALEXANDRE DUMAS	Les Trois Mousquetaires

id_emprunt	id_livre	id_abonne	date_sortie	date_rendu
1	100	1	2014-12-17	2014-12-18
2	101	2	2014-12-18	2014-12-20
3	100	3	2014-12-19	2014-12-22
4	103	4	2014-12-19	2014-12-22
5	104	1	2014-12-19	2014-12-28
6	105	2	2015-03-20	2015-03-26
7	105	3	2015-06-13	NULL
8	100	2	2015-06-15	NULL

* Seule particularité : le champ date_rendu de la table emprunt comportera la valeur NULL si le livre se trouve hors de la bibliothèque.

Pour effectuer les insertions, libre à vous d'utiliser PhpMyAdmin ou la Console Mysql.

Correction : Insertion de contenu

Voici le code SQL permettant de créer vos enregistrements :

INSERT INTO abonne (id_abonne, prenom) VALUES
(1, 'Guillaume'),
(2, 'Benoit'),
(3, 'Chloe'),
(4, 'Laura');

INSERT INTO livre (id_livre, auteur, titre) VALUES
(100, 'GUY DE MAUPASSANT', 'Une vie'),
(101, 'GUY DE MAUPASSANT', 'Bel-Ami '),
(102, 'HONORE DE BALZAC', 'Le père Goriot'),
(103, 'ALPHONSE DAUDET', 'Le Petit chose'),
(104, 'ALEXANDRE DUMAS', 'La Reine Margot'),
(105, 'ALEXANDRE DUMAS', 'Les Trois Mousquetaires');

INSERT INTO emprunt (id_emprunt, id_livre, id_abonne, date_sortie, date_rendu) VALUES
(1, 100, 1, '2014-12-17', '2014-12-18'),
(2, 101, 2, '2014-12-18', '2014-12-20'),
(3, 100, 3, '2014-12-19', '2014-12-22'),
(4, 103, 4, '2014-12-19', '2014-12-22'),
(5, 104, 1, '2014-12-19', '2014-12-28'),
(6, 105, 2, '2015-03-20', '2015-03-26'),
(7, 105, 3, '2015-06-13', NULL),
(8, 100, 2, '2015-06-15', NULL);

---

---

Requête Imbriquée sur plusieurs tables

Avant d'effectuer des requêtes sur plusieurs tables, vous pouvez prendre connaissance de vos nouvelles données en effectuant les requêtes suivantes :

	SELECT * FROM abonne ; 
	SELECT * FROM livre ; 
	SELECT * FROM emprunt ; 
	

Pour une première requête, nous essaierons d'afficher les id des livres encore dans la nature n'ayant pas été rendus à la bibliothèque

La logique voudrait que l'on inscrive la requête suivante :

SELECT id_livre FROM emprunt WHERE date_rendu = NULL;

Cas particulier la valeur NULL se teste avec le mot clé IS, voici donc la bonne requête :

SELECT id_livre FROM emprunt WHERE date_rendu IS NULL;

Pour la prochaine requête, essayons de trouver les titres des livres dans la nature n'ayant pas été rendus à la bibliothèque

Si nous souhaitons savoir si un livre a été rendu ou non, nous aurons besoin de la table emprunt (avec le champ date_rendu).

Si nous souhaitons connaitre le titre d'un livre, nous aurons besoin de la table livre (avec le champ titre).

Nous allons donc avoir besoin d'exécuter une requête imbriquée sur 2 tables différentes.

Demande : Quels sont les titres des livres n'ayant pas été rendus à la bibliothèque ?

Requête :

SELECT titre FROM livre WHERE id_livre	IN
		(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL);

Explications : La requête entre parenthèse s'exécute en premier et nous sort les id de livre 105 et 100.

Ensuite, nous demandons les titres des livres dans la table emprunt correspondant aux id des livres 105 et 100.

Nous utilisons le mot clé IN et non pas le signe = (égal) car plusieurs résultats seront renvoyés par la sous requête.

Dans le cadre de cet exemple, pour assurer la jonction et la correspondance des données, nous utilisons le champ id_livre, le champ annoncé en condition WHERE doit être le même que le champ SELECT juste à après.


Résultat
:



Dans notre cas, nous avons utilisé le champ id_livre pour joindre et faire correspondre les informations de la table livre et de la table emprunt.

---

Demande : Nous aimerions connaitre le n° (id) de(s) livre(s) que Chloé a emprunté à la bibliothèque

Indices : Chloé se trouve dans la table abonné, les informations (id) sur les livres qu'elle a empruntés se trouvent dans la table emprunt.

Il est donc nécessaire de faire la passerelle entre ces deux tables.

Comment faire ? un champ se répète à la fois dans la table abonné et dans la table emprunt, il s'agit du champ id_abonne. C'est grâce à ce champ que nous pourrons faire la passerelle entre les deux tables.

N'oubliez pas qu'un champ doit absolument être commun aux 2 tables pour que la jonction et la correspondance des données puissent se faire dans le cadre d'une requête imbriquée.

Requête :

SELECT titre FROM livre WHERE id_livre	IN
		(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL);

Explications : La requête entre parenthèse s'exécute en premier et nous sort de la table emprunt les id de livre 105 et 100 (ayant une date de rendu à null et donc hors de la bibliothèque).
Ensuite, nous demandons les titres des livres (dans la table livre) correspondant aux id des livres 105 et 100.

Résultat
:

---

Demande : Afficher les prénoms des abonnés ayant emprunté un livre le 19/12/2014

Indices :
- La date de sortie d'un livre se trouve dans la table emprunt.
- Les prénoms des abonnés se trouve dans la table abonné.
- Le champ en commun entre ces deux tables permettant de faire la passerelle est le champ id_abonne.

Requête :

SELECT prenom FROM abonne WHERE id_abonne IN
		( SELECT id_abonne FROM emprunt WHERE date_sortie='2014-12-19' ) ;

Explications : La requête entre parenthèse s'exécute en premier et nous sort de la table emprunt les id des abonnés ayant emprunté un livre le 19/12/2014.

Ces id sont 1, 3 et 4. Lorsqu'une sous requête est suceptible de sortir plusieurs résultats, il faut prévoir le mot clé IN acceptant plusieurs valeurs, et non pas le signe égal = n'acceptant qu'une seule valeur.

Ensuite, nous demandons les prénoms des abonnés correspondant aux n° id 1, 3 et 4.

Résultat
:

---

Demande : Afficher la liste des abonnés ayant déjà emprunté un livre d'Alphonse DAUDET

Indices :
- La liste des abonnés se trouvent dans la table abonné.
- Les n° id des livres écrits par Alphonse Daudet se trouve dans la table livre.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.

Requête :

SELECT prenom FROM abonne WHERE id_abonne IN 
		(SELECT id_abonne FROM emprunt WHERE id_livre IN
			(SELECT id_livre FROM livre WHERE auteur='ALPHONSE DAUDET') );

Explications : La dernière requête entre parenthèse s'exécute en premier et nous sort de la table livre les id des livres écrits par Alphonse Daudet.
SELECT id_livre FROM livre WHERE auteur='ALPHONSE DAUDET';
Le résultat de cette requête est :

id_livre
103

Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id d'abonnés des personnes ayant emprunté ces id de livres (le 103).
SELECT id_abonne FROM emprunt WHERE id_livre IN(103);
Le résultat de cette requête est :

id_abonne
4

Pour terminer, dans la requête du dessus, nous demandons à afficher les prénoms des abonnés répondant à ces id d'abonnés (le 4).
SELECT prenom FROM abonne WHERE id_abonne IN(4);
Le résultat de cette requête est :

prenom
Laura

Résultat
: C'est donc le résultat final.

---

Demande : Afficher le(s) titre de(s) livre(s) que Chloé a emprunté à la bibliothèque.

Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.

Requête :

SELECT titre FROM livre WHERE id_livre IN
		(SELECT id_livre FROM emprunt WHERE id_abonne =
			(SELECT id_abonne FROM abonne WHERE prenom='chloe') );

Explications : La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :

id_abonne
3

Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été emprunté par l'abonné n°3
SELECT id_livre FROM emprunt WHERE id_abonne = 3;
Le résultat de cette requête est :

id_livre
100
105

Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres correspondants aux n° de livre 100 et 105..
SELECT titre FROM livre WHERE id_livre IN(100,105);
Le résultat de cette requête est :

titre
Une vie
Les Trois Mousquetaires

Résultat
: C'est donc le résultat final.

---

Demande : Afficher le(s) titre de(s) livre(s) que Chloé n'a pas encore emprunté à la bibliothèque.

Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relié la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.

Requête :

SELECT titre FROM livre WHERE id_livre NOT IN
		(SELECT id_livre FROM emprunt WHERE id_abonne =
			(SELECT id_abonne FROM abonne WHERE prenom='chloe') );

Explications : La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :

id_abonne
3

Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été empruntés par l'abonné n°3
SELECT id_livre FROM emprunt WHERE id_abonne = 3;
Le résultat de cette requête est :

id_livre
100
105

Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres qui NE SONT PAS les n° de livres récupérés soit le 100 et 105.
En effet, nous ne voulons pas connaitre les n° de livres que Chloé à emprunté mais tous les autres !
Cette requête permet d'abord de lister les n° de livres que chloé à emprunté pour ensuite les exclures (NOT IN) au moment de la demande des titres de livres.
SELECT titre FROM livre WHERE id_livre NOT IN(100,105);

Le résultat de cette requête est :

titre
Bel-Ami
Le père Goriot
Le Petit chose
La Reine Margot

Résultat
: C'est donc le résultat final.

---

Demande : Afficher le(s) titre de(s) livre(s) que Chloé n'a pas encore rendu(s) à la bibliothèque.

Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.

Requête :

SELECT titre FROM livre WHERE id_livre IN
		(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL AND id_abonne =
			(SELECT id_abonne FROM abonne WHERE prenom='chloe') );

Explications : La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :

id_abonne
3

Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été empruntés par l'abonné n°3 et n'ayant pas été rendus (date rendu is null)
SELECT id_livre FROM emprunt WHERE date_rendu IS NULL AND id_abonne = 3;
Le résultat de cette requête est :

id_livre
105

Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres qui correspondent au n° (id) de livres récupérés (dans notre exemple, le 105)
SELECT titre FROM livre WHERE id_livre IN(105);
Dans le cadre de cette requête, même si un seul résultat aurait pu être compris par le signe égal (=), il était plus prudent de prévoir le mot clé IN dans la mesure où le nombre de résultats renvoyés par la requête du milieu peut changer à tout moment.

Le résultat de cette requête est :

titre
Les Trois Mousquetaires

Résultat
: C'est donc le résultat final.

---

Demande : Combien de livre Guillaume a emprunté à la bibliotheque ?

Indices :
- Nous ne souhaitons pas savoir quels sont les livres empruntés par Guillaume mais combien il en a emprunté.
- Nous devrons prendre en compte la table abonné (pour connaitre le n° d'abonné de Guillaume)
- Nous devrons également prendre en compte la table emprunt puisque à l'intérieur tous les emprunts y sont repertoriés.
- Le champ en commun entre les deux tables est id_abonne. Il servira donc de jonction dans le cadre de notre requête imbriquée pour assurer la correspondance des données.

Requête :

SELECT COUNT(*) AS 'nombre de livre' FROM emprunt WHERE id_abonne =
		(SELECT id_abonne FROM abonne WHERE prenom='guillaume');

Explications : La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Guillaume.
SELECT id_abonne FROM abonne WHERE prenom = 'guillaume';
Le résultat de cette requête est :

id_abonne
1

Ensuite, la première requête nous permet de compter (via le mot clé COUNT) tous les emprunts réalisés par cet abonné (le n°1).
SELECT COUNT(*) AS 'nombre de livre' FROM emprunt WHERE id_abonne = 1;
le mot clé AS permet de donner un alias pour renommer l'entête du résultat (cela reste facultatif mais toujours conseillé car plus lisible).
Le résultat de cette requête est :

nombre de livre
2

Résultat
: C'est donc le résultat final.

---

---