Exercice Access : gestion d'oeuvres



En tant que chargé de TP de MS Access il m'a été demandé de créer un examen concernant la rédaction des requêtes.
Je le mets ici à la diposition des étudiants voulant s'entraîner, ou des enseignants en panne d'imagination.
A noter que la base mériterait d'être complétée.

ce qui est fourni

Dans le cas d'un examen, il est préférable de donner en avance (une semaine, par exemple) la présentation et le MCD, afin que les étudiants soient déjà familiarisés avec la structure de la base, et ne perde pas ainsi de temps au moment des questions.

présentation :
La base sur laquelle vous allez travailler a pour but de référencer toutes les oeuvres d'art du monde (!) en les reliant à leurs créateurs (un seul artiste par oeuvre), le musée où on peut les trouver, et là (ou les) techniques utilisées (certaines oeuvres, comme la Miss Chicago de Mirò, appartiennent autant à la sculpture qu'à la peinture).
Ne soyez pas surpris par le petit nombre d'entrées, les tables sont en train d'être remplies au moment où vous travaillez dessus.

dessin du MCD

FICHIER : la base



questions

Il s'agit de rédiger des requêtes permettant de répondre aux questions suivantes :

  1. Combien la base référence-t-elle de peintures ?
  2. Dans quel(s) musée(s) peut-on trouver du Picasso ?
  3. Quel est le pays le plus représenté par ses artistes ?
  4. Pour chaque musée, combien comporte-t-il d'oeuvres au mètre carré ? (les superficies sont ici données en mètres carrés)
  5. Composez un talbeau représentant le nombre d'oeuvres de chaque artiste par pays.
  6. Dites, pour chaque artiste, s'il est represénté dans son propre pays (ie: si un musée de sa nationalité héberge une de ses oeuvres).

FICHIER : la base corrigée

  1. 15
    erreur classique : penser que toutes les oeuvres sont des peintures, et répondre 17
    SQL : SELECT Count(Type.CodeOeuvre) AS CompteDeCodeOeuvre FROM Type GROUP BY Type.CodeArt HAVING (((Type.CodeArt)="P"));
  2. Musée Picasso Barcelone et Musée Picasso Màlaga
    SQL : SELECT Musée.NomMusee FROM (Musée INNER JOIN Oeuvre ON Musée.CodeMusee = Oeuvre.CodeMusee) INNER JOIN (Artiste INNER JOIN Créateur ON Artiste.CodeArtiste = Créateur.CodeArtiste) ON Oeuvre.CodeOeuvre = Créateur.CodeOeuvre WHERE (((Artiste.NomArtiste)="Pablo Picasso"));
  3. Espagne
    On passe ici par deux sous-requêtes :
    - nombre d'artistes par pays (SQL : SELECT Pays.NomPays, Count(Artiste.CodePays) AS CompteDeCodePays FROM Pays INNER JOIN Artiste ON Pays.CodePays=Artiste.CodePays GROUP BY Pays.NomPays;)
    - max d'artistes par pays (SQL : SELECT Max([3a - nombre d'artistes par pays].CompteDeCodePays) AS MaxDeCompteDeCodePays FROM [3a - nombre d'artistes par pays];)
    puis : SELECT [3a - nombre d'artistes par pays].NomPays FROM [3a - nombre d'artistes par pays] INNER JOIN [3b - max d'artistes par pays] ON [3a - nombre d'artistes par pays].CompteDeCodePays = [3b - max d'artistes par pays].MaxDeCompteDeCodePays;
  4. Brooklyn Museum : 0,00025 / collection Faringdon - Oxon : 0,003 / Delaware Art Museum : 0,002 / ... / Musée Picasso Màlaga : 1,119E-05
    Les nombres sont ridicules en raison du peu d'oeuvres entrées (pour des superficies réalistes)
    On passe par une sous-requête :
    nombre d'oeuvres par musée (SELECT Count(Oeuvre.CodeOeuvre) AS CompteDeCodeOeuvre, Musée.CodeMusee FROM Musée INNER JOIN Oeuvre ON Musée.CodeMusee=Oeuvre.CodeMusee GROUP BY Musée.CodeMusee;)
    puis un champ densité: [CompteDeCodeOeuvre]/Musée!Superficie
    SQL : SELECT Musée.NomMusee, [CompteDeCodeOeuvre]/Musée!Superficie AS densité FROM Musée INNER JOIN [4a - nombre d'oeuvres par musée] ON Musée.CodeMusee = [4a - nombre d'oeuvres par musée].CodeMusee GROUP BY Musée.NomMusee, [CompteDeCodeOeuvre]/Musée!Superficie;
  5. On demande ici une analyse croisée !
    SQL : TRANSFORM Count(Oeuvre.CodeOeuvre) AS CompteDeCodeOeuvre SELECT Artiste.NomArtiste FROM Pays INNER JOIN (Oeuvre INNER JOIN (Artiste INNER JOIN Créateur ON Artiste.CodeArtiste = Créateur.CodeArtiste) ON Oeuvre.CodeOeuvre = Créateur.CodeOeuvre) ON Pays.CodePays = Artiste.CodePays GROUP BY Artiste.NomArtiste PIVOT Pays.NomPays;
  6. NomArtiste / représenté dans son propre pays, Barnabé Muges / oui, Howard Pyle / oui, ... , William Blake / non
    Il faut faire ici un Vrai/Faux : représenté dans son propre pays: VraiFaux(Pays!NomPays=Pays_1!NomPays;"oui";"non")
    SQL : SELECT Artiste.NomArtiste, IIf(Pays!NomPays=Pays_1!NomPays,"oui","non") AS [représenté dans son propre pays] FROM Pays AS Pays_1 INNER JOIN ((Musée INNER JOIN Oeuvre ON Musée.CodeMusee = Oeuvre.CodeMusee) INNER JOIN ((Pays INNER JOIN Artiste ON Pays.CodePays = Artiste.CodePays) INNER JOIN Créateur ON Artiste.CodeArtiste = Créateur.CodeArtiste) ON Oeuvre.CodeOeuvre = Créateur.CodeOeuvre) ON Pays_1.CodePays = Musée.CodePays GROUP BY Artiste.NomArtiste, IIf(Pays!NomPays=Pays_1!NomPays,"oui","non") ORDER BY Artiste.NomArtiste;