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) |
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)
28
- Nombre de clients (fonction d'agrégat) |
Votre réponse n°19
|
29 - Nombre de livres (fonction d'agrégat) |
Votre réponse n°20
|
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 |
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
|
42 - Type de livre le moins représenté |
Votre réponse n°33
|
LibType |
Science-fiction |
Roman policier |