Réponse AJAX TP SQL - Etudes de cas 2010

TP SQL basé sur
les études de cas 2010.


1
Etude de cas 2010, métropole, option développeur :
Cas NOIXCOOP
(coopérative de noix).

DOSSIER 2- Commercialisation de plants de noyers.

À la demande de ses adhérents NOIXCOOP développe une nouvelle activité de culture de plants de noyers compatibles avec la charte qualité associée au label AOC. Elle cultive et commercialise des plants de noyers de différentes variétés. Pour cela elle utilise une base de données, dont voici un extrait :

TypeUsageArbre(id, libellé)
     id : clé primaire
    Limité à : (1, 'Fruitier'), (2, 'Huile'), (3, ‘Greffon’)
    Il s’agit du type d’usage principal d’une variété d’arbres

EspèceArbre(id, libellé)
     id : clé primaire
     Par exemple : (1, 'noyer commun'), (2, 'noyer cendré'), (3, 'noyer noir’)

VariétéArbre(id, idEspèce, idTypeUsage, libellé)
     id : clé primaire
     idEspèce : clé étrangère en référence à id de EspèceArbre
     idTypeUsage : clé étrangère en référence à id de TypeUsageArbre
     Par exemple : (1, 1, 1,'franquette'), (2, 1, 1, 'parisienne'), (3, 1, 1, 'mayette'), (4, 3, 3, 'soligo')

Dimension(id, min, max)
     id : clé primaire
     min, max : plage de hauteur approximative de l'arbre, en mètre : [min .. max]
     Par exemple : (1, 0.15, 0.30), (2, 0.30, 0.50), (3, 0.4, 0.6), (4, 2.5, 3.0)

PrixVenteArbre(idVariété, âge, idDimension, prix)
     idVariété, âge, idDimension : clé primaire
     idVariété : clé étrangère en référence à id de VariétéArbre
     idDimension : clé étrangère en référence à id de Dimension
     âge : valeur entière en année
     prix : prix de vente hors taxes à l'unité, en euros
     Par exemple : (1, 2, 2, 9), (1, 2, 5, 12.5)

TRAVAIL À FAIRE
2.1 En prenant en compte les exemples de données ci-dessus, expliquer l'intention de cette requête :
INSERT INTO PrixVenteArbre VALUES(1, 1, 1, 8)
Votre réponse n°1
2.2 Expliquer le résultat que permet d’obtenir la requête ci-dessous :
SELECT EspèceArbre.libellé AS espèce,
VariétéArbre.libellé AS variété
FROM VariétéArbre, TypeUsageArbre, EspèceArbre
WHERE VariétéArbre.idTypeUsage = TypeUsageArbre.id
AND VariétéArbre.idEspèce = EspèceArbre.id
ORDER BY EspèceArbre.libellé
Votre réponse n°2

Puis en proposer une version simplifiée :
Votre réponse n°3
2.3 Écrire la requête SQL permettant à un utilisateur de connaître le nombre de variétés d'arbres proposées par type d'usage (id, libellé).
Votre réponse n°4
2.4 Écrire la requête SQL permettant de connaître les variétés d'arbres (libellé) ne faisant l'objet d'aucun prix de vente.
Votre réponse n°5

Au début de cette année, la coopérative NOIXCOOP désire augmenter de 3% le prix de vente de tous ses noyers âgés d'au moins 3 ans, toute variété confondue.

TRAVAIL À FAIRE
2.5 Écrire la requête SQL effectuant cette augmentation.
Votre réponse n°6


2
Etude de cas 2010, métropole, option administrateur de réseaux :
Cas SCCP
().

DOSSIER 4 - Gestion des livraisons

Le siège du groupe SCCP centralise les données provenant de 70 dépôts (appelés également silos) qui collectent les céréales. Pour cela le groupe a mis en place une base de données répartie :

Chaque silo dispose d’un compartiment différent pour chaque type de céréale stockée. Une fois livrées au silo par les adhérents, les céréales y sont stockées jusqu’à leur vente et leur acheminement vers les acheteurs. Les adhérents livrent leurs céréales au rythme des récoltes. Un adhérent peut donc réaliser plusieurs livraisons et chaque livraison peut concerner plusieurs céréales.

TRAVAIL À FAIRE
4.1 Donner l’instruction SQL permettant de créer la table LIVRER (on considérera que les tables CEREALE, ADHERENT et LIVRAISON ont déjà été créées).
Votre réponse n°7
4.2 Expliquer pourquoi la base actuelle ne permet pas à un adhérent de livrer à plusieurs silos.
Votre réponse n°8
4.3 Expliquer pourquoi la base actuelle permet d’enregistrer ou non le fait qu’un même adhérent peut livrer plusieurs fois la même variété de céréales le même jour.
Votre réponse n°9
4.4 Répondre aux questions suivantes en utilisant le langage SQL :
A.Quelle est la quantité totale de maïs, de blé et d’orge livrée en 2009 (une seule valeur globale) ?
Votre réponse n°10

B.Quelle est la quantité totale de céréales livrée par chaque adhérent en 2009, céréale par céréale ? Le résultat sera trié par nom d’adhérent et par nom de céréale.
Votre réponse n°11

Vous êtes chargé(e) de recenser les requêtes existantes, parmi lesquelles se trouve la requête ci-dessous :

SELECT A.numero, A.nom,
FROM LIVRAISON L, LIVRER LV, ADHERENT A, CEREALE C
WHERE refAdherent = A.numero
AND numlivraison = L.numero
AND refCereale = C.code
AND C.nom =’BLE’
AND YEAR(date)=2009
GROUP BY A.numero, A.nom
HAVING SUM(quantite) >= all( SELECT SUM(quantite)
                    FROM LIVRAISON LI, LIVRER LIV, CEREALE CE
                    WHERE LIV.numlivraison = LI.numero
                    AND refCereale = CE.code
                    AND YEAR(date)= 2009
                    AND CE.nom = ’BLE’
                    GROUP BY refAdherent)

TRAVAIL À FAIRE
4.5 Énoncer en une phrase simple le résultat que permet d’obtenir la sous requête SQL :
Votre réponse n°12

Puis celui que permet d’obtenir la requête principale :
Votre réponse n°13

3

Etude de cas 2010, Nouvelle-Calédonie, option développeur :
Cas
().



4
Etude de cas 2010, Nouvelle-Calédonie, option administrateur de réseaux :
Cas
().


Sébastien PASTORE.