TP SQL - Bibliothèque.mdb

01 - Liste des noms des clients

SELECT nomclt
FROM client;

nomclt
SPERRY
MIELE
WETHERILL
WANG
BOSTIC
BON
... (16 réponses)

02 - Liste des n° de livres empruntés depuis 01-01-97

SELECT DISTINCT livre.numlivre
FROM livre, emprunt
WHERE emprunt.numlivre = livre.numlivre
AND date >= #01/01/97#;

numlivre
1
2
3
4
5
6
7
8
10
11
12
13

03 - N° clients ayant emprunté le livre n°6 avant 01-09-97

SELECT DISTINCT Emprunt.NumClt
FROM Emprunt
WHERE Emprunt.NumLivre=6 AND Emprunt.Date<#9/1/97#;

NumClt
3
5
6
7
9
11

04 - Liste des titres des livres dont le libellé est 'roman'

SELECT Titre
FROM Type, Livre
WHERE Type.NumType = Livre.NumType
AND LibType="roman";

Titre
Les herbes folles
Les granges de feux
Le grand Meaulnes

05 - Liste des titres des romans écrits par Pennac

SELECT Titre
FROM Auteur, Livre, Type
WHERE Auteur.NumAuteur=Livre.NumAuteur and Livre.NumType = Type.NumType
AND NomAuteur = "Pennac" and LibType = "Roman";

Titre
Les herbes folles
Les granges de feux

06 - Clients ayant emprunté des livres de Science-fiction

SELECT DISTINCT Client.*
FROM Client, Emprunt, Livre, Type
WHERE Client.NumClt=Emprunt.NumClt and Emprunt.NumLivre=Livre.NumLivre and Livre.NumType = Type.NumType
AND LibType="Science-Fiction";

NumClt NomClt PrénomClt AdrClt
2 MIELE Donny San Fransisco
4 WANG Eric Los Angeles
5 BOSTIC Joe New York
7 DURAND Marc San Fransisco
8 KETTLER Neal Detroit
13 ARCHER John Detroit
14 LIGHTNER Michael Los Angeles
15 PANNULLO Patrick New York
16 CONNELY Patrick San Fransisco

07 - Romans empruntés par Marc DURAND

SELECT DISTINCT Titre
FROM Client, Emprunt, Livre, Type
WHERE Client.NumClt=Emprunt.NumClt and Emprunt.NumLivre=Livre.NumLivre and Livre.NumType=Type.NumType
AND NomClt="DURAND" and PrénomClt="Marc" and LibType="Roman";

Titre
Les granges de feux

08 - Clients ayant emprunté un essai de Bill Gates

SELECT DISTINCT Client.*
FROM Client, Emprunt, Livre, Auteur, Type
WHERE Client.NumClt=Emprunt.NumClt and Emprunt.NumLivre=Livre.NumLivre and Livre.NumAuteur=Auteur.NumAuteur and Livre.NumType=Type.NumType
AND LibType="Essai" and NomAuteur="William H. Gates";

NumClt NomClt PrénomClt AdrClt
1 SPERRY Brett New York
2 MIELE Donny San Fransisco
3 WETHERILL Steve Detroit
4 WANG Eric Los Angeles
5 BOSTIC Joe New York
6 BON Raphaël New York
7 DURAND Marc San Fransisco
9 LONG Denzil New York
10 LANIER Jonathan Detroit
11 ISGREEN Adam San Fransisco
14 LIGHTNER Michael Los Angeles

09 - Auteur n'ayant jamais écrit des livres de type 2

SELECT *
FROM auteur
WHERE NumAuteur not in
(Select NumAuteur from livre where numtype = 2);

NumAuteur NomAuteur
2 William H. Gates
4 Bertrand Meyer
5 Michel Dreyfus
6 Stern
7 Alan Simpson
8 Xavier Castellani

10 - Clients n'ayant jamais emprunté de livre de l'auteur n°1

SELECT *
FROM client
WHERE numclt not in
(select numclt from emprunt, livre where emprunt.numlivre= livre.numlivre and numauteur = 1);

NumClt NomClt PrénomClt AdrClt
10 LANIER Jonathan Detroit

11 - Clients n'ayant jamais emprunté de roman policier de Stern

SELECT *
FROM client
WHERE numclt not in
(SELECT Emprunt.NumClt
FROM Client, Emprunt, Livre, Auteur, Type
WHERE Client.NumClt=Emprunt.NumClt and Emprunt.NumLivre=Livre.NumLivre and Livre.NumAuteur=Auteur.NumAuteur and Livre.NumType=Type.NumType
and LibType="Roman policier" and NomAuteur="Stern");

NumClt NomClt PrénomClt AdrClt
1 SPERRY Brett New York
2 MIELE Donny San Fransisco
3 WETHERILL Steve Detroit
4 WANG Eric Los Angeles
8 KETTLER Neal Detroit
9 LONG Denzil New York
10 LANIER Jonathan Detroit
12 YEO Eric New York
13 ARCHER John Detroit
14 LIGHTNER Michael Los Angeles

12 - Clients ayant emprunté les mêmes livres que Raphaël BON

SELECT DISTINCT c2.*
FROM Client AS C1, Emprunt AS E1, Emprunt AS E2, Client AS C2
WHERE C1.NumClt = E1.NumClt and E1.NumLivre = E2.NumLivre and E2.NumClt=C2.NumClt and C2.NumClt <> C1.NumClt
AND C1.NomClt = "BON" and C1.PrénomClt = "Raphaël";

NumClt NomClt PrénomClt AdrClt
1 SPERRY Brett New York
2 MIELE Donny San Fransisco
3 WETHERILL Steve Detroit
4 WANG Eric Los Angeles
5 BOSTIC Joe New York
7 DURAND Marc San Fransisco
... ... ... ... (15 réponses)

13 - Livres du même type que "Livre d'or d'Access 97"

SELECT L2.*
FROM livre AS L1, livre AS L2
WHERE L1.NumType = L2.NumType
and L1.NumLivre <> L2.NumLivre
and L1.Titre = "Livre d'or d'Access 97";

NumLivre Titre NumAuteur NumType
8 Méthodes de programmation 4 1
9 Internet 5 1
10 Fortran IV 5 1
13 Méthode générale d'analyse des applications 8 1

14 - Emprunteurs de livres de l'auteur de "La route du futur"

SELECT DISTINCT client.*
FROM Client, Emprunt, Livre AS L1, Livre AS L2
WHERE Client.NumClt = Emprunt.NumClt and Emprunt.NumLivre = L1.NumLivre and L1.NumAuteur = L2.NumAuteur
AND L2.Titre = "La route du futur";

NumClt NomClt PrénomClt AdrClt
1 SPERRY Brett New York
2 MIELE Donny San Fransisco
3 WETHERILL Steve Detroit
4 WANG Eric Los Angeles
5 BOSTIC Joe New York
6 BON Raphaël New York
7 DURAND Marc San Fransisco
9 LONG Denzil New York
10 LANIER Jonathan Detroit
11 ISGREEN Adam San Fransisco
14 LIGHTNER Michael Los Angeles

15 - Livres empruntés en juin 96

SELECT livre.*, date
FROM emprunt, livre
WHERE emprunt.numlivre = livre.numlivre
and date Between #06/01/96# and #06/30/96#;

NumLivre Titre NumAuteur NumType date
12 Livre d'or d'Access 97 7 1 03/06/96
5 La route du futur 2 4 27/06/96
3 Les granges de feux 1 2 10/06/96

16 - Clients qui n'ont pas emprunté de livre en 95

SELECT *
FROM client
WHERE numclt not in (select numclt from emprunt where date like "??/??/95");

NumClt NomClt PrénomClt AdrClt
1 SPERRY Brett New York
7 DURAND Marc San Fransisco
12 YEO Eric New York
14 LIGHTNER Michael Los Angeles
15 PANNULLO Patrick New York

17 - Livres qui n'ont pas été empruntés en 96

SELECT *
FROM livre
WHERE numlivre not in (select numlivre from emprunt where right$(date,2)="96");

NumLivre Titre NumAuteur NumType
9 Internet 5 1
13 Méthode générale d'analyse des applications 8 1

18 - Emprunts le jour ou Neal KETTLER a emprunté "l'aviateur mod

SELECT c2.nomclt, c2.prénomclt, e2.date, l2.titre
FROM client AS c1, emprunt AS e1, livre AS l1, client AS c2, emprunt AS e2, livre AS l2
WHERE c1.numclt=e1.numclt and e1.numlivre = l1.numlivre and c2.numclt = e2.numclt and e2.numlivre = l2.numlivre
and e1.date = e2.date and l1.NumLivre<>l2.NumLivre
and c1.nomclt = "KETTLER" and c1.prénomclt = "Neal" and l1.titre = "L'aviateur moderne";

nomclt prénomclt date titre
KETTLER Neal 24/11/97 Le petit bateau
DURAND Marc 24/11/97 Les menottes noires
BON Raphaël 24/11/97 Les herbes folles

19 - Emprunts le mois ou Raphaël BON a emprunté "Fortran IV"

SELECT c2.nomclt, c2.prénomclt, e2.date, l2.titre
FROM client AS c1, emprunt AS e1, livre AS l1, client AS c2, emprunt AS e2, livre AS l2
WHERE c1.numclt = e1.numclt and e1.numlivre = l1.numlivre
and c2.numclt = e2.numclt and e2.numlivre = l2.numlivre
and month(e2.date)=month(e1.date) and year(e2.date)=year(e1.date)
and c1.nomclt = "BON" and c1.prénomclt = "Raphaël" and l1.titre = "Fortran IV";

nomclt prénomclt date titre
LONG Denzil 06/10/95 Les granges de feux
ISGREEN Adam 29/10/95 Le grand Meaulnes
ARCHER John 04/10/95 Le grand Meaulnes
BOSTIC Joe 09/10/95 Méthodes de programmation
MIELE Donny 04/10/95 Méthodes de programmation
BON Raphaël 21/10/95 Fortran IV

20 - Livre dont le titre comporte le mot 'méthode'

SELECT *
FROM livre
WHERE titre like '*méthode*';

NumLivre Titre NumAuteur NumType
8 Méthodes de programmation 4 1
13 Méthode générale d'analyse des applications 8 1

21 - Clients dont le nom commence par 'BO'

SELECT *
FROM client
WHERE nomclt like 'bo*';

NumClt NomClt PrénomClt AdrClt
5 BOSTIC Joe New York
6 BON Raphaël New York

22 - Durée entre deux emprunts consécutifs du même livre

SELECT e1.numlivre AS livre, e1.date, e2.date, e2.date - e1.date AS delai
FROM emprunt AS e1, emprunt AS e2
WHERE e1.numlivre = e2.numlivre
and e1.dateand not exists (select * from emprunt e3 where e3.numlivre = e1.numlivre and e3.date > e1.date and e3.date < e2.date);

livre e1.date e2.date delai
11 03/12/96 18/08/97 258
12 03/06/96 06/08/96 64
12 04/01/98 13/02/98 40
3 10/06/96 20/12/96 193
2 27/11/95 14/01/96 48
11 11/09/96 03/12/96 83
... ... ... ... (89 réponses)

23 - Nom & Prénom des clients triés par nom

SELECT NomClt, PrénomClt
FROM Client
ORDER BY NomClt;

NomClt PrénomClt
ARCHER John
BON Raphaël
BOSTIC Joe
CONNELY Patrick
DURAND Marc
ISGREEN Adam
... ... (16 réponses)

24 - Liste des titres et genres, triés par genre et titre

SELECT Titre, LibType
FROM Livre, Type
WHERE Livre.NumType = Type.NumType
ORDER BY LibType, Titre;

Titre LibType
L'aviateur moderne Essai
La route du futur Essai
Le travail à la vitesse de la pensée Essai
Fortran IV Informatique
Internet Informatique
Livre d'or d'Access 97 Informatique
... ... (13 réponses)

25 - Auteur et titres, triés par auteur et titres

SELECT NomAuteur, Titre
FROM Auteur, Livre
WHERE Auteur.NumAuteur = Livre.NumAuteur
ORDER BY NomAuteur, Titre;

NomAuteur Titre
Alain Fournier Le grand Meaulnes
Alan Simpson Livre d'or d'Access 97
Bertrand Meyer Méthodes de programmation
Michel Dreyfus Fortran IV
Michel Dreyfus Internet
Pennac L'aviateur moderne
... ... (13 réponses)

26 - Clients et titres empruntés, triés (client et titre)

SELECT DISTINCT PrénomClt & " " & NomClt AS Nom, Titre
FROM Client, Emprunt, Livre
WHERE Client.NumClt=Emprunt.NumClt and Emprunt.NumLivre = Livre.NumLivre
ORDER BY 1, 2 DESC;

Nom Titre
Adam ISGREEN Livre d'or d'Access 97
Adam ISGREEN Les menottes noires
Adam ISGREEN Les herbes folles
Adam ISGREEN Le travail à la vitesse de la pensée
Adam ISGREEN Le grand Meaulnes
Adam ISGREEN L'aviateur moderne
... ... (79 réponses)

27 - Nombre de clients

SELECT count(*) AS NbClients
FROM Client;

NbClients
16

28 - Nombre de livres

SELECT count(*) AS NbLivres
FROM Livre;

NbLivres
13

29 - Date de l'emprunt le plus récent

SELECT max(date) AS DateDernierEmprunt
FROM emprunt;

DateDernierEmprunt
25/11/98

30 - Nombre d'emprunts par client

SELECT PrénomClt, NomClt, count(*) AS NbEmprunts
FROM Client, Emprunt
WHERE Client.NumClt = Emprunt.NumClt
GROUP BY Client.NumClt, PrénomClt, NomClt;

PrénomClt NomClt NbEmprunts
Brett SPERRY 2
Donny MIELE 5
Steve WETHERILL 4
Eric WANG 5
Joe BOSTIC 9
Raphaël BON 9
... ... ... (16 réponses)

31 - Date de l'emprunt le plus récent, par client

SELECT PrénomClt, NomClt, Max(Date) AS DateDernierEmprunt
FROM Client, Emprunt
WHERE Client.NumClt=Emprunt.NumClt
GROUP BY Client.NumClt, NomClt, PrénomClt;

PrénomClt NomClt DateDernierEmprunt
Brett SPERRY 12/11/98
Donny MIELE 24/09/98
Steve WETHERILL 13/02/98
Eric WANG 02/06/98
Joe BOSTIC 30/06/98
Raphaël BON 28/02/98
... ... ... (16 réponses)

32 - Nombre de livres écrits par auteur

SELECT NomAuteur, Count(*) AS NbLivres
FROM Auteur, Livre
WHERE Auteur.NumAuteur=Livre.NumAuteur
GROUP BY Auteur.NumAuteur, NomAuteur;

NomAuteur NbLivres
Pennac 4
William H. Gates 2
Alain Fournier 1
Bertrand Meyer 1
Michel Dreyfus 2
Stern 1
Alan Simpson 1
Xavier Castellani 1

33 - Nombre de livre par type, trié sur le type

SELECT LibType, count(*) AS NbLivres
FROM Type, Livre
WHERE Type.NumType = Livre.NumType
GROUP BY Type.NumType, LibType
ORDER BY 1;

LibType NbLivres
Essai 3
Informatique 5
Roman 3
Roman policier 1
Science-fiction 1

34 - Nombre d'emprunts par type de livre

SELECT LibType, count(*) AS NbEmprunts
FROM Type, Livre, Emprunt
WHERE Type.NumType=Livre.NumType and Livre.NumLivre=Emprunt.NumLivre
GROUP BY Type.NumType, LibType;

LibType NbEmprunts
Informatique 36
Roman 23
Science-fiction 11
Essai 24
Roman policier 7

35 - Auteur ayant écrit un seul livre

SELECT NomAuteur
FROM Auteur, Livre
WHERE Auteur.NumAuteur = Livre.NumAuteur
GROUP BY Auteur.NumAuteur, NomAuteur
HAVING count(*) = 1;

NomAuteur
Alain Fournier
Bertrand Meyer
Stern
Alan Simpson
Xavier Castellani

35 bis - Auteurs ayant écrit un seul livre (basée sur 32)

SELECT NomAuteur
FROM [32 - Nombre de livres écrits par auteur]
WHERE NbLivres = 1;

NomAuteur
Alain Fournier
Bertrand Meyer
Stern
Alan Simpson
Xavier Castellani

36 - Clients ayant plus d'emprunts que la moyenne

SELECT PrénomClt, NomClt, count(*) AS NbEmprunts
FROM Client, Emprunt
WHERE Client.NumClt = Emprunt.NumClt
GROUP BY Client.NumClt, PrénomClt, NomClt
HAVING count(*)>(Select avg(NbEmprunts) from [30 - Nombre d'emprunts par client]);

PrénomClt NomClt NbEmprunts
Joe BOSTIC 9
Raphaël BON 9
Marc DURAND 7
Denzil LONG 8
Jonathan LANIER 8
Adam ISGREEN 11
John ARCHER 9
Patrick PANNULLO 7

37 - Auteur ayant écrit le plus de livres

SELECT NomAuteur
FROM Auteur, Livre
WHERE Auteur.NumAuteur = Livre.NumAuteur
GROUP BY Auteur.NumAuteur, NomAuteur
HAVING count(*) = (select max(nblivres) from [32 - Nombre de livres écrits par auteur]);

NomAuteur
Pennac

38 - Clients n'ayant pas emprunté de livres depuis 6 mois

SELECT PrénomClt, NomClt, max(date) AS DateDernierEmprunt
FROM Client, Emprunt
WHERE Client.NumClt = Emprunt.NumClt
GROUP BY Client.NumClt, PrénomClt, NomClt
HAVING max(date) < date() - 180;

PrénomClt NomClt DateDernierEmprunt
Brett SPERRY 12/11/98
Donny MIELE 24/09/98
Steve WETHERILL 13/02/98
Eric WANG 02/06/98
Joe BOSTIC 30/06/98
Raphaël BON 28/02/98
... ... ... (16 réponses)

39 - Type de livre le moins représenté

SELECT LibType
FROM Type, Livre
WHERE Type.NumType = Livre.NumType
GROUP BY Type.NumType, LibType
HAVING count(*) = (select min(NbLivres) from [33 - Nombre de livre par type, trié sur le type]);

LibType
Science-fiction
Roman policier