Corrigé Examen SQL SGBD

- Consulter la page de l'examen.

- Correction.

2)  Modèle conceptuel

Afin de vous aider dans la compréhension du problème, réaliser le modèle conceptuel à partir du modèle relationnel. Complétez, ce schéma, et barrez les symboles non utilisés.

3)  Réalisation de la base de données 

3.1 Rédigez l'ensemble des requêtes permettant de créer la base de donnée (les contraintes seront nommées).

Création de la base de donnée: 

CREATE DATABASE "Echantillon"
  W ITH OW NER = postgres
       ENCODING = 'SQL_ASCII'
       TABLESPACE = pg_default;

Création des tables :

CREATE TABLE client
(
        codeclient int4 NOT NULL,
        nomclient varchar(20),
        prenomclient varchar(20),
        rueclient varchar(30),
        cpclient char(5),
        villeclient varchar(20),
        telclient char(10),
        CONSTRAINT pk_client PRIM ARY KEY (codeclient)
);

CREATE TABLE typeanalyse
(
        reftypeanalyse int4 NOT NULL,
        designationtypeanalyse char(10),
        prixtypeanalyse numeric(5,2),
        CONSTRAINT pk_typeanalyse PRIM ARY KEY (reftypeanalyse)
);

CREATE TABLE echantillon
(
        codeechantillon int4 NOT NULL,
        dateentree date,
        codeclient int4 NOT NULL,
        CONSTRAINT pk_echantillon PRIM ARY KEY (codeechantillon),
        CONSTRAINT fk_echantil_apparteni_client FOREIGN KEY (codeclient)
            REFERENCES client (codeclient)
);

CREATE TABLE realiser
(
        codeechantillon int4 NOT NULL,
        reftypeanalyse int4 NOT NULL,
        daterealisation date,
        CONSTRAINT  pk_realiser PRIM ARY KEY (reftypeanalyse, codeechantilon),
        CONSTRAINT fk_realiser_realiser2_echantil    FOREIGN KEY (codeechantillon)
           REFERENCES echantillon (codeechantillon) M ATCH SIM PLE
           ON UPDATE RESTRICT ON DELETE RESTRICT,
        CONSTRAINT     fk_realiser_realser_typeanal    FOREIGN  KEY (reftypeanalyse)
           REFERENCES typeanalyse (reftypeanalyse) M ATCH SIM PLE
           ON UPDATE RESTRICT ON DELETE RESTRICT
);

3.2     Modifiez la structure de la table Client afin d'ajouter un champs email.

ALTER TABLE CLIENT 
ADD Column Email VARCHAR(30);

4)  Requêtes de sélection

4.1     Donnez  la liste des clients référencés

SELECT * FROM  CLIENT

4.2     Indiquez les noms et prénoms des clients habitant Paris

SELECT NomClient, PrenomClient FROM  CLIENT 
WHERE VilleClient = 'Paris';

4.3     Donnez le nombre de client référencé dans chaque ville

SELECT VilleClient, Count(*) FROM  CLIENT 
GROUP BY VilleClient;

4.4     Donnez la même information par ordre décroissant du nombre de client

SELECT VilleClient, Count(*) FROM  CLIENT 
GROUP BY VilleClient ORDER BY 2 DESC;

4.5     Donnez  la liste des clients (nom, prénom) habitant dans le département de la Meuse.

SELECT NomClient, PrenomClient FROM  CLIENT 
WHERE CPClent LIKE '55%';

4.6     Donnez les noms et prénoms des clients n'ayant pas d'adresse e-mail.

SELECT NomClient, PrenomClient FROM  CLIENT 
WHERE emai IS NULL;

4.7     Donnez les noms, prénoms et adresses complètes des clients  ayant fourni des échantillons, ainsi que la date entre le 01-10-2005 et le 31-12-2005/

SELECT NomClient, PrenomClient, RueClient, VilleClient, CPClient, Dateentree  
FROM  CLIENT C, ECHANTILLON E 
WHERE C.CodeClient = E.CodeClient
AND DateEntree BETW EEN '10-01-2005' AND '12-31-2005'

4.8     Donnez le nombre d'analyse en cours (non réalisées)
SELECT COUNT(*) FROM  REALISER 
WHERE DateRealisation IS NULL;

4.9     Donnez les noms, prénoms des clients attendant le résultat  d'une analyse(analyse non réalisée).

SELECT  DISTINCT  NomClient,  PrenomClient  FROM   CLIENT  C,  ECHANTILLON  E,
REALISER  R
WHERE C.CodeClient = E. CodeClient
AND E.CodeEchantillon = R.CodeEchantillon 
AND DateRealisation IS NULL;

4.10  Donnez pour chaque client par ordre alphabétique (nom et prénom) le nombre d'échantillons déposés ainsi que les dates de dépôt du plus ancien et du plus récent.

SELECT NomClient, PrenomClient, count(*), Min(Dateentree), Max(dateentree)
 FROM  CLIENT C, ECHANTILLON E
WHERE  C.CodeClient = E. CodeClient
Group BY NomClient, PrenomClient;

4.11  Donnez la liste des analyses réalisées au mois de mars 2005.

SELECT RefTypeAnalyse, Code Echantillon FROM  REALISER
WHERE MONTH(dateRealisation) = 03 AND YEAR(DateRealisation) = 2005; 

4.12  Donnez pour chaque  échantillon, le nombre prévu d'analyse(réalisées ou non).

SELECT CodeEchantillon, count(*) FROM  REALISER
GROUP BY CodeEchantilon;

4.13  Donnez les codes et dates d‘entrée des échantillons pour lesquels aucune analyse n‘a été réalisée.

SELECT CodeEchantillon, DateEntree FROM  ECHANTILLON 
WHERE CodeEchantilon NOT IN 
(SELECT CodeEchantillon FROM  REALISER W HERE DateRealisation IS not NULL);

4.14  Donnez les codes et dates d‘entrée des échantillons pour lesquels plus de 5 analyses ont été réalisées ;

SELECT CodeEchantillon, DateEntree FROM  ECHANTILLON
WHERECode Echantillon IN 
(SELECT CodeEchantillon FROM  REALISER 
WHERE dateRealisation IS NOT NULL
GROUP BY Code Echantill
HAVING COUNT(*) >5);

4.15  Donnez les désignations des analyses dont le prix est inférieur à celui de l‘analyse possédant la référence ”A102”.

SELECT DesignationTypeANalyse FROM  TYPEANALYSE
W HERE PrixTypeAnalyse < 
(SELECT  PrixTypeAnalyse  FROM   TYPEANALYSE  W HERE  DesignationTypeAnalyse  =
'A102')

Autre solution : 

SELECT TR.DesignationTypeAnalyse FROM  TYPEANALYSE TM , TYPEANALYSE TR
WHERE TM .designationTypeAnalyse = 'A102'
AND TR.PrixTypeAnalyse < TM .PrixTypeAnalyse

5)  Requêtes de mise à jour

5.1     La base est vierge. Réalisez l'insertion d'un jeu de données dans les différentes tables. Les données seront définies par vous-même à votre convenance.

INSERT INTO CLIENT VALUES (1,'Dupont','Jean','Rue de Jean',
'75000','Paris','01020304',Null);
INSERT INTO CLIENT VALUES (2,'Durand',' erre','Rue de Pierre', '75000', 'Paris',
'01020304', 't o@ lui.fr')
INSERT INTO CLIENT VALUES (3,'Autremont','Paul','Rue de Paul' 55000','Bar le Duc',
'01020304', Null);
INSERT INTO CLIENT VALUES (4,'Reste','Jacques','Rue de Jacques', '57000', 'M etz',
'01020304', 'uneadresse@ unsie');

INSERT INTO TYPEANALYSE VALUES (1,'A200',8.10);
INSERT INTO TYPEANALYSE VALUES (2,'A102',10.00);
INSERT INTO TYPEANALYSE VALUES (3,'A300',12.20);
INSERT INTO TYPEANALYSE VALUES (4,'A400',5);
INSERT INTO TYPEANALYSE VALUES (5,'A500',4.20);

INSERT INTO ECHANTILLON VALUES (1,'05-10-2005',1);
INSERT INTO ECHANTILLON VALUES (2,'12-10-2005',2);
INSERT INTO ECHANTILLON VALUES (3,'10-20-2005',3);
INSERT INTO ECHANTILLON VALUES (4,'02-02-2006',4);
INSERT INTO ECHANTILLON VALUES (5,'12-30-2005',2);
INSERT INTO ECHANTILLON VALUES (6,'10-10-2005',1);
INSERT INTO ECHANTILLON VALUES (7,'05-10-2005',4);
INSERT INTO ECHANTILLON VALUES (8,'05-07-1999',3);
INSERT INTO ECHANTILLON VALUES (9,'02-28-2005',1);

INSERT INTO REALISER VALUES (1,1,'10-12-2005');
INSERT INTO REALISER VALUES (1,2,NULL);
INSERT INTO REALISER VALUES (2,1,'08-10-2006');
INSERT INTO REALISER VALUES (2,2,NULL);
INSERT INTO REALISER VALUES (3,1,'06-15-2005');
INSERT INTO REALISER VALUES (3,2,NULL);
INSERT INTO REALISER VALUES (2,4,'05-20-1999');
INSERT INTO REALISER VALUES (1,4,NULL);
INSERT INTO REALISER VALUES (4,1,'02-28-2005');
INSERT INTO REALISER VALUES (1,3,NULL);
INSERT INTO REALISER VALUES (3,3,'12-30-2005');
INSERT INTO REALISER VALUES (4,4,NULL);
INSERT INTO REALISER VALUES (2,3,'01-29-2006');
INSERT INTO REALISER VALUES (3,4,NULL);

5.2     Augmentez de 10% tous les prix des analyses.

UPDATE TYPEANALYSE
SET PrixTypeAnalyse = PrixTypeAnalyse*1.1;

5.3     Il a été défini un prix plancher de 8 Euros pour toutes les analyses. Mettez à jour la table ANALYSE.

UPDATE TYPEANALYSE
SET PrixTypeAnalyse = 8
W HERE PrixTypeAnalyse <8;

5.4     Aujourd'hui, toutes les analyses en cours ont été réalisées. Mettez à jour la base.

UPDATE REALISER 
SET DateRealisation = '02-06-2006'
W HERE DateRealisation IS NULL;


5.5     Le client Dupont vient de fournir son adresse e-mail (Dupont@btsig.fr). Mettre à jour la table correspondante.

UPDATE CLIENT 
SET email = 'Dupont@ btsig.fr'
W HERE NomClent = 'Dupont';


5.6     Suite à un bug informatique, des entrées ont été réalisées le 01 février 2007 au lieu du 1er février 2006. Mettez à jour la base.

UPDATE ECHANTILLON 
SET DateEntree = '02-01-2006'
W HERE DateEntree = '02-01-2007'

UPDATE REALISER 
SET Date Realisation  = '02-01-2006'
W HERE DateRealisation = '02-01-2007'

5.7     Afin de préparer la nouvelle campagne, de nouvelles analyses ont été définies.
Ces nouvelles analyses sont disponibles dans une table ANALYSE2006 dont la structure (champs, types de donnée) est identique à TYPEANALYSE. Mettez à jour la table TYPEANALYSE à partir de la table ANALYSE2006.

INSERT INTO TYPEANALYSE
SELECT * FROM  ANALYSE2006;