Aller au contenu
Bases de données

UGA - MIASHS - S7 - BDD - Pierre Blarre

TD4 - SQL DML 2

Icône Présentation
✕
1 / 1

Exercice 1

Soit le schéma relationnel suivant :

PRODUIT (NPRO, LIBELLE, PRIX_U, POIDS_U)
COMPOSITION (COMPOSE, COMPOSANT, QTE)
Remarques
  • Un produit est identifié par son numéro NPRO, son libellé LIBELLE, son prix unitaire PRIX_U et son poids unitaire POIDS_U.
  • La relation COMPOSITION décrit la composition des produits : Chaque ligne de la relation COMPOSITION indique que le produit identifié par COMPOSE est composé du produit identifié par COMPOSANT en quantité QTE.
  • Seuls les produits composés ont une entrée dans la relation COMPOSITION.
  • Un produit peut être composé de plusieurs produits, et un produit peut être le composant de plusieurs produits.
  • La relation COMPOSITION est une relation réflexive : un produit peut être composé de lui-même.
  • La relation COMPOSITION est une relation symétrique : si le produit A est composé du produit B, alors le produit B est le composant du produit A.
  • Seuls les composants finaux ont un prix et un poids unitaires.
Données
PRODUIT
NPROLIBELLEPRIX_UPOIDS_U
p1 A-200
p2 A-056
p3 B-661
p4 B-122
p5 B-326
p6 D-822 3.5 0.7
p7 D-507 8 0.25
p8 G-993 5 1.15
p9 F-016
p10 J-500
p11 J-544 0.5 0.9
p12 L-009 1.7 2.3
COMPOSITION
COMPOSECOMPOSANTQTE
p1 p2 2
p1 p3 1
p1 p4 2
p2 p7 8
p2 p8 2
p3 p8 5
p4 p8 4
p4 p9 5
p4 p10 5
p5 p4 2
p5 p6 7
p9 p11 2
p10 p11 4
p10 p12 3

Exprimer en SQL les requêtes suivantes :

  1. les matières premières (produits qui n’ont pas de composants) ;
  2. les produits finis (qui n’entrent dans la composition d’aucun autre) ;
  3. les produits semi-finis (tous les autres) ;
  4. le prix et poids unitaires d’un produit fini ou semi-fini dont tous les composants ont un poids et un prix unitaires.

Exercice 2

Soit le schéma relationnel suivant :

Personnes (numero, nom, prenom, responsable*)
Données
Personnes
numeronomresponsable
p1 Mercier
p2 Durant
p3 Noirons p1
p4 Dupont p1
p5 Verger p4
p6 Dupond p4
p7 Dermiez p6
p8 Anciers p2

Quels sont les personnes qui ont le même responsable que p4 ?

Exercice 3

En reprendant le schéma relationnel de la base de données fil rouge :

Clients (numero, nom, prenom, rue, codePostal, ville, telephone, categorie, compte)
Commandes (numero, numeroClient*, dateCommande, statut)
Produits (numero, nom, prix, stock)
Details (numeroCommande*, numeroProduit*, quantite)
Données de la base
Clients
numeronomprenomruecodePostalvilletelephonecategoriecompte
1 Lebreton Éléonore 6 avenue Françoise Olivier 38240 Meylan 0776059929 A1 10000
2 Vasseur Georges 54, avenue Jean 38000 Grenoble +33 1 44 68 49 74 B2 1903
3 Da Costa Léon 18, boulevard Brigitte Faivre 75000 Paris 0568740044 C3 -1500
4 Rey Michel 49, avenue de Adam 38220 Vizille B1 3251
5 Weiss Dominique 98, boulevard Remy 59000 Lille B2 0
6 Berlioz Diane 27 avenue de la Chartreuse 75000 Paris 123094
7 Lefevre Jean 12, rue de la République 69000 Lyon C1 -1000
9 Barda Jérôme 128 rue de la Paix 75000 Paris C1 -2000
8 Jacques Chaoui 1240 rue des Ateliers 75001 Paris C1 -1750
Produits
numeronomprixstock
1 T-shirt 25 30
2 Pantalon 60 50
3 Chaussures 120 100
4 Casquette 15 10
5 Sac à dos 80 5
Commandes
numeronumeroClientdateCommandestatut
1 1 2024-02-01 livrée
2 1 2024-03-15 livrée
3 3 2024-04-28 livrée
4 4 2024-06-24 annulée
5 5 2024-06-30 en cours
6 5 2024-07-12 en cours
7 4 2024-08-19 annulée
Details
numeroCommandenumeroProduitquantite
1 1 3
1 2 1
1 3 1
2 4 1
2 5 1
3 1 4
3 2 1
3 3 2
3 4 1
3 5 1
4 1 7
4 2 1
5 1 2
5 2 1
5 3 1
6 1 10
7 1 1

Répondez aux questions suivantes :

  1. Calculer le montant de chaque détail de commande du client numéro 1

  2. Calculer le montant commandé des produits Pantalon

  3. Afficher le total et la moyenne des comptes des clients, ainsi que le nombre de clients, selon chacune des classifications suivantes : (a) par catégorie, (b) par ville, (c) par catégorie dans chaque ville.

  4. Combien y a-t-il de commandes spécifiant un (ou plusieurs) produit(s) Casquette ?

  5. Compléter le fragment suivant de manière à former une requête valide :

    SELECT categorie, numeroProduit, SUM(quantite*prix)
    FROM ...

  1. Calculer le montant dû par chaque client. Dans ce calcul, on ne prend en compte que le montant des commandes. Attention aux clients qui n’ont pas passé de commandes, ils devraient apparaître avec un montant nul.

  2. Calculer le montant dû par les clients de chaque ville. Dans ce calcul, on ne prend en compte que le montant des commandes. Attention aux villes dans lesquelles aucun client n’a passé de commandes.

  3. Calculer, par jour, le total des montants des commandes. Suggestion : Regroupement par date

  4. On suppose que le concepteur de la base de données n’a pas trouvé utile d’imposer un identifiant primaire sur la table Produits

    Il se pourrait donc que plusieurs lignes de la table Produits aient la même valeur pour le numéro de produit (ce qui déroge au principe d’unicité des bases de données relationnelles…)

    (a) Ecrire une requête qui recherche les valeurs de numero présentes en plus d’un exemplaire.

    (b) Ecrire une requête qui indique combien de valeurs de numero sont présentes en plus d’un exemplaire.

  5. Afficher pour chaque ville, les noms des produits qui y sont commandés.

  6. Afficher par ville, et pour chaque catégorie dans celle-ci, le total des montants des commandes.

  7. Indiquer, pour chaque ville, les catégories de clients qui n’y sont pas représentées Suggestion: Construire l’ensemble de tous les couples (ville, categorie), possibles et en retirer ceux qui existent dans la base. Attention aux valeurs null, qui ne doivent pas être prises en compte.

  8. Produire (à l’écran) une table de couples < X, Y > de clients tels que X et Y habitent dans la même ville. On évitera de renseigner < X,X >, mais aussi < Y,X > si < X,Y > est déjà repris.

    Suggestion. Auto-jointure de CLIENT. On évitera les couples inverse en imposant un ordre sur les valeurs de NOM (p.ex. X < Y ).

  9. Afficher pour chaque client, le nombre de commandes, le nombre de produits commandés et le nombre de détails. On se limite aux clients qui ont passé au moins une commande.

    Suggestion : il s’agit d’une requête basée sur des groupements multi-niveaux.

  10. Afficher, pour chaque ville et pour chaque catégorie :

    (a) le nombre de commandes passées par les clients de cette ville et de cette catégorie

    (b) le montant total de ces commandes.

  11. Calculer le nombre moyen de produits par commande. De même le nombre moyen de commandes par client, par ville ou par catégorie.

    Remarque: Il n’est pas possible de demander directement la moyenne d’une somme. Suggestion : Construction et interrogation d’une vue ou calcul de la moyenne dans le FROM.

  12. Quel est, pour chaque ville, le nombre moyen de commandes par client.

  13. Ecrire une requête SQL qui donne, pour chaque produit, le nombre de produits qui ont été commandés le 30/06/2024.

  14. Donner pour chaque ville dans laquelle se trouve au moins un client de catégorie C1 la liste des produits Pantalon qu’on y a commandés.

  15. Donner pour chaque produit la liste des villes dans lesquelles ce produit est commandé en plus de 2 unités au total pour la ville. Suggestion : Un produit est intéressant si le nombre de villes dans lesquelles il est commandé est supérieur ou égal à 2.

  16. Afficher, pour chaque ville, les produits qu’on y commande et qui sont aussi commandés dans au moins une autre ville.

  17. Calculer, pour chaque ville, le nombre de catégories distinctes.