Corrigé Series Exercices SQLPLUS Oracle Exercices Oracle SQL

- Consulter la page de l'exercice.
- Solution des exercices

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 ;