Exercice Sur les Requêtes SQL avec Correction

Exercice 1

Sur la base de données exemple du cours (CLIENT-COMMANDE-PRODUIT-FOURNISSEUR), formuler avec le langage SQL  les requêtes suivantes.

1)   Désignation et prix unitaire de tous les produits.
2)  Désignation des produits de prix inférieur à 100 F.
3)   Nom des clients qui ont commandé le produit n° 1.
4)  Nom des clients qui ont commandé au moins un produit de prix supérieur à 500 F.
5)  Nom des clients qui n’ont pas commandé le produit n° 1.
6)  Numéro des clients qui ont commandé tous les produits.
7)  Numéro des clients qui ont commandé tous les produits commandés par le client n° 2.


Exercice 2

Soit le schéma relationnel de la base FABRICATION.

CLIENT (NOC, NOM, ADRESSE)
SERVICE (NOS, INTITULE, LOCALISATION)
PIECE (NOP, DESIGNATION, COULEUR, POIDS)    clés primaires
COMMANDE (NOP, NOS, NOC, QUANTITE)    clés étrangères

Formuler en SQL*Plus les commandes de création de la structure de cette base, puis exprimer les requêtes suivantes.

1)  Donner pour chaque service le poids de la pièce commandée de couleur bleue la plus pesante.
2)  Donner le poids moyen des pièces commandées pour chacun des services “Promotion”.
3)  Donner les pièces de couleur bleue qui sont commandées par plus de trois services différents.
4)   Donner le maximum parmi les totaux des quantités des pièces commandées par les différents services.

Correction Exercice 1


1) SELECT Desi, PrixUni
    FROM Client ;

2) SELECT Desi
    FROM Client
    WHERE PrixUni < 100 ;

3) SELECT DISTINCT Nom
    FROM Client C1, Commande C2
    WHERE C1.NumCli = C2.NumCli
    AND NumProd = 1 ;

4) SELECT DISTINCT Nom
    FROM Client C1, Commande C2, Produit P
    WHERE C1.NumCli = C2.NumCli
    AND C2.NumProd = P.NumProd
    AND PrixUni > 500 ;

5) SELECT NumCli
    FROM Client C1
    WHERE NOT EXISTS (
    SELECT *
    FROM Commande C2
    WHERE C2.NumCLi = C1.NumCli
    AND NumProd = 1) ;

6)    SELECT NumCLi
FROM Client C1
WHERE NOT EXISTS ( SELECT *
FROM Produit P WHERE NOT EXISTS (
SELECT *
FROM Commande C2
WHERE C2.NumCli = C1.NumCli
AND C2.NumProd = P.NumProd)) ;

7) SELECT Nom
    FROM Client C0
    WHERE NOT EXISTS (
       SELECT *
       FROM Commande C1
       WHERE NumCli = 2
       AND NOT EXISTS (
          SELECT *
          FROM Commande C2
          WHERE C2.NumCli = C0.NumCli
          AND C2.NumProd = C1.NumProd)) ;

Correction Exercice 2


CREATE TABLE CLIENT (NOC NUMBER(3),
                                             NOM VARCHAR(40),
                                             ADRESSE VARCHAR(100),
                                              CONSTRAINT PRICLI PRIMARY KEY (NOC));

CREATE TABLE SERVICE (NOS NUMBER(3),
                                               INTITULE VARCHAR(30),
                                               LOCALISATION VARCHAR(100),
                                               CONSTRAINT PRISER PRIMARY KEY (NOS));

CREATE TABLE PIECE (NOP NUMBER(3),
                                          DESIGNATION VARCHAR(30),
                                          COULEUR VARCHAR(20),
                                          POIDS NUMBER(5,2),
                                          CONSTRAINT PRIPIE PRIMARY KEY (NOP));

CREATE TABLE COMMANDE (NOP NUMBER(3),
                                                      NOS NUMBER(3), NOC NUMBER(3),
                                                      QUANTITE NUMBER(3),
                                                     CONSTRAINT PRICOM PRIMARY KEY (NOP, NOS, NOC),   
                                                     CONSTRAINT ETRPIE FOREIGN KEY (NOP)
                                                     REFERENCES PIECE(NOP),
                                                     CONSTRAINT ETRSER FOREIGN KEY (NOS)
                                                     REFERENCES SERVICE(NOS)
                                                    CONSTRAINT ETRCLI FOREIGN KEY (NOC)
                                                    REFERENCES CLIENT(NOC));

1)  SELECT INTITULE, MAX(POIDS)
      FROM SERVICE S, COMMANDE C, PRODUIT P
      WHERE S.NOS=C.NOS
      AND C.NOP=P.NOP
      AND COULEUR=’bleu’ GROUP BY INTITULE ;

2)  SELECT AVG(POIDS)
     FROM SERVICE S, COMMANDE C, PRODUIT P
     WHERE S.NOS=C.NOS
     AND C.NOP=P.NOP
     AND INTITULE=’Promotion’
     GROUP BY S.NOS ;

3)  SELECT P.NOP
     FROM PRODUIT P
    WHERE COULEUR=’bleu’
    AND 3 <
    (SELECT COUNT(DISTINCT NOS)
    FROM COMMANDE C
    WHERE C.NOP=P.NOP) ;

4)  SELECT MAX(SUM(QUANTITE))
     FROM COMMANDE
     GROUP BY NOS ;