TP SQL Bibliothèque sous Access.

Réponse AJAX

Présentation du modèle logique

Vous devez télécharger la base de données au format Access 2003.

Cette base, sur laquelle vous allez travailler, correspond au modèle logique suivant :

Requêtes à rédiger en SQL
et réponses renvoyées par ces requêtes

Rappels sur... Les requêtes

Lorsque le nom d'un champ ou d'une table comporte des caractères spéciaux, vous devez l'entourer de crochets. Par exemple : SELECT [N°] FROM Client ;
Posez-vous toujours la question : "est-ce que la requête que je suis en train de taper est susceptible de renvoyer plusieurs fois la même réponse", et déduisez-en si la clause DISTINCT est nécessaire.
Les clauses SQL, les tables et les champs peuvent s'écrirent indifféremment en minuscule ou en majuscule.
Vous pouvez trouver des rappels et des explications sur la commande SELECT ici : http://sql.developpez.com/sqlaz/select/, ou utilisez le Memento SQL.

Cours SELECT 1 : Where, renommage de colonnes, tris, jointures, expressions, Like, Distinct

Liste des noms des clients

Votre réponse n°1

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

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

Votre réponse n°2

Titre

Les herbes folles

Les granges de feux

Le grand Meaulnes

Clients ayant emprunté des livres de Science-fiction

Votre réponse n°3

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

Cours SELECT 2 : Gestion des dates

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

Votre réponse n°4

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

Cours SELECT 3 : IN, NOT IN

Auteurs n'ayant jamais écrit des livres de type 2

Votre réponse n°5

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

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

Votre réponse n°6

NumClt	NomClt	PrénomClt	AdrClt
10	LANIER	Jonathan	Detroit

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

Votre réponse n°7

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

Cours SELECT 4 : fonctions de regroupement, Group By, Having

Cours SELECT 5 : auto-jointures (ou jointures réflexives)

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

Votre réponse n°8

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)

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

Votre réponse n°9

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

15 - Client qui ont empruntés des livres de l'auteur de "La route du futur" (autojointure)

Votre réponse n°10

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

16 - Livres empruntés en juin 96 (Between ou Like)

Votre réponse n°11

Le Like, vu en cours, appliqué aux dates...

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

- Livre dont le titre comporte le mot 'méthode' (like)

Votre réponse n°12

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

22 - Clients dont le nom commence par 'BO' (LIKE, facile)

Votre réponse n°13

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

23 - Durée entre deux emprunts consécutifs du même livre (très très difficile, utilise NOT EXISTS)

Votre réponse n°14

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)

Tris (clause ORDER BY).

24 - Nom & Prénom des clients triés par nom (order by)

Votre réponse n°15

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

25 - Liste des titres et genres, triés par genre et titre (order by)

Votre réponse n°16

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)

26 - Auteur et titres, triés par auteur et titres (order by)

Votre réponse n°17

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)

27 - Clients et titres empruntés, triés par client puis par titre (order by)

Votre réponse n°18

(101 réponses)

Clause GROUP BY.

28 - Nombre de clients (fonction d'agrégat)

Votre réponse n°19

NbClients

29 - Nombre de livres (fonction d'agrégat)

Votre réponse n°20

NbLivres

30 - Date de l'emprunt le plus récent (fonction d'agrégat)

Votre réponse n°21

DateDernierEmprunt

25/11/98

31 - Nombre d'emprunts par client (group by)

Votre réponse n°22

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)

32 - Date de l'emprunt le plus récent, par client (requêtes imbriquées)

Votre réponse n°23

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)

33 - Nombre de livres écrits par auteur (group by)

Votre réponse n°24

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

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

Votre réponse n°25

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

35 - Nombre d'emprunts par type de livre

Votre réponse n°26

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

Clause HAVING.

36 - Auteur ayant écrit un seul livre

Votre réponse n°27

NomAuteur

Alain Fournier

Bertrand Meyer

Stern

Alan Simpson

Xavier Castellani

37 - Clients ayant emprunté neuf livres ou plus.

Votre réponse n°28

Joe BOSTIC, Raphaël BON, Adam ISGREEN, John ARCHER.

38 - Livres ayant été emprunté une seule fois au cours de l'année 1998.

Votre réponse n°29

Le grand Meaulnes, Méthode générale d'analyse des applications

39 - Auteurs qui ont écrit dans plus d'une catégorie.

Votre réponse n°30

Pennac (essai, roman, science-fiction).

Requêtes doubles (regroupement de regroupement).

40 - Clients ayant plus d'emprunts que la moyenne

Votre réponse n°31

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

41 - Auteur ayant écrit le plus de livres

Votre réponse n°32

NomAuteur

Pennac

42 - Type de livre le moins représenté

Votre réponse n°33

LibType

Science-fiction

Roman policier