Devoir N°1 en PL/SQL sur les requêtes SQL et les triggers

Considérez les relations suivantes :

•  Etudiant(etdid:string, nometd:string, tele: string, vip:boolean)
•  Categorie(catid:integer, libelle:string)
•  Livre(lid:integer, titre:string, auteur:string, edition:string, exemplaires:integer, #cat:integer)
•  Emprunter(#etdid:integer, #lid:integer, date_emp:date, nb_jrs:integer, etat:boolean)

1. Avant de commencer à manipuler la base de données, il vous est demandé de créer toutes les tables avec leurs contraintes.  Voir la réponse 

   CREATE TABLE Categorie(
       catid int,
       libelle varchar2(20)
   );
   ALTER TABLE Categorie ADD CONSTRAINT pk_categorie PRIMARY KEY(catid);
   
   CREATE TABLE Etudiant(
       etdid int,
       nometd varchar2(20),
       tele varchar2(12),
       vip boolean
   );
   ALTER TABLE Etudiant ADD CONSTRAINT pk_etudiant PRIMARY KEY(etdid);
   
   CREATE TABLE Livre(
       lid int,
       titre varchar2(20),
       auteur varchar2(20),
       edition varchar2(20),
       exemplaires int,
       cat int
   );
   
   ALTER TABLE Livre ADD CONSTRAINT pk_livre PRIMARY KEY(lid);
   ALTER TABLE Livre ADD CONSTRAINT fk_livre FOREIGN KEY (cat) REFERENCES Categorie(catid);
   
   CREATE TABLE Emprunter(
       etdid int,
       lid int,
       date_emp date,
       nb_jrs int,
       etat boolean
   );
   ALTER TABLE Emprunter ADD CONSTRAINT pk_emprunter PRIMARY KEY(etdid,lid);
   ALTER TABLE Emprunter ADD CONSTRAINT fk_emprunter_livre FOREIGN KEY (lid) REFERENCES Livre(lid);
   ALTER TABLE Emprunter ADD CONSTRAINT fk_emprunter_etudiant FOREIGN KEY (etdid) REFERENCES Etudiant(etdid);
   
                                               

Écrivez les requêtes suivantes en SQL :

2. Afficher tous les étudiants dont le nom commence par 'al'.  Voir la réponse 

   SELECT * FROM Etudiant WHERE nometd LIKE 'al%';
                                               

3. Afficher tous les livres de la catégorie 'informatique'.  Voir la réponse 

   SELECT lv.* FROM Livre lv INNER JOIN Categorie cat ON cat.catid=lv.cat  AND cat.libelle='Informatique';
                                               

4. Afficher le nombre de livres de l'auteur 'Mostafa'.  Voir la réponse 

   SELECT count(*) AS 'Nombre de livres' FROM Livre WHERE auteur='Mostafa';
                                               

5. Afficher tous les auteurs et leur nombre de livres.  Voir la réponse 

   SELECT auteur, count(*) AS 'Nombre de livres' FROM livres GROUP BY auteur;
                                               

6. Afficher tous les livres empruntés par tous les élèves.  Voir la réponse 

   SELECT lv.lid, lv.titre FROM Livre lv INNER JOIN Emprunter emp ON lv.lid=emp.lid GROUP BY lv.lid, lv.titre HAVING count(DISTINCT etdid)=(SELECT count(*) FROM Etudiant)
                                               

7. Afficher tous les étudiants qui ont emprunté tous les livres.  Voir la réponse 

   SELECT etd.etdid,etd.nometd FROM Etudiant etd INNER JOIN Emprunter emp ON etd.etdid=emp.etdid GROUP BY etd.etdid,etd.nometd HAVING count(DISTINCT lid)=(SELECT count(*) FROM Livre);
                                               

8. Afficher tous les étudiants et le nombre de livres empruntés au cours du mois de juin.  Voir la réponse 

   SELECT etd.etdid,etd.nometd FROM Etudiant etd INNER JOIN Emprunter emp ON etd.etdid=emp.etdid WHERE EXTRACT(MONTH from emp.date_emp)=6 GROUP BY etd.etdid,etd.nometd;
                                               

Pour maintenir l'intégrité de la base de données et également journaliser certaines activités dans la base de données, vous devez implémenter les triggers suivants :

9. L'étudiante 'Sara' ne peut emprunter aucun livre dans le mois de février.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_9
   BEFORE INSERT ON Emprunter FOR EACH ROW
   WHEN EXTRACT(MONTH FROM NEW.date_emp)=2
   DECLARE
       id NUMBER;
   BEGIN
       SELECT etdid into id FROM Etudiant WHERE nometd='Sara';
       IF NEW.etdid=id THEN
           RAISE_APPLICATION_ERROR(-20005,'Sara ne peut emprunter aucun livre dans le mois 2.');
       END IF;
   END;
   /
                                               

10. Nous ne pouvons pas emprunter un livre dont tous les exemplaires sont empruntés dans la même période.  Voir la réponse 

    CREATE OR REPLACE TRIGGER quest_10
    BEFORE INSERT ON Emprunter FOR EACH ROW
    DECLARE
        nb_exp NUMBER;
        nb_emp NUMBER;
    BEGIN
        SELECT exemplaire into nb_exp FROM Livre WHERE lid=:NEW.lid;
        SELECT count(*) into nb_emp FROM Emprunter WHERE lid=:NEW.lid AND date_emp BETWEEN :NEW.date_emp AND :NEW.date_emp+:NEW.nb_jrs;
        IF nb_exp<=nb_emp THEN
            RAISE_APPLICATION_ERROR(-20005,'Nombre d exemplaires est insuffisant');
        END IF;
    END;
    /
                                                

11. Un livre ne peut pas être emprunté plus de 3 jours.  Voir la réponse 

    CREATE OR REPLACE TRIGGER quest_11
    BEFORE INSERT OR UPDATE ON Emprunter FOR EACH ROW
    WHEN NEW.nb_jrs>3
    BEGIN
        RAISE_APPLICATION_ERROR(-20005,'Un livre ne peut pas être emprunté plus de 3 jours');
    END;
    /
                                                

12. Les étudiants qui ont déjà emprunté deux livres ne peuvent plus emprunter de livre, jusqu'à ce qu'ils retournent les livres empruntés (Sauf les étudiants VIP).  Voir la réponse 

    CREATE OR REPLACE TRIGGER quest_12
    BEFORE INSERT ON Emprunter FOR EACH ROW
    DECLARE
        nb_emp NUMBER;
        etdcat BOOLEAN;
    BEGIN
        SELECT vip INTO etdcat FROM Etudiant WHERE etdid=:NEW.etdid;
        if not vip THEN 
            SELECT count(*) into nb_emp FROM Emprunter WHERE etdid=:NEW.etdid AND etat=0;
            IF nb_emp>=2 THEN
                RAISE_APPLICATION_ERROR(-20005,'Les étudiants qui ont déjà emprunté deux livres ne peuvent plus emprunter de livre');
            END IF;
        END IF;
    END;
    /
                                                

13. Les livres à moins de deux exemplaires ne peuvent pas être empruntés plus de deux jours.  Voir la réponse 

    CREATE OR REPLACE TRIGGER quest_13
    BEFORE INSERT OR UPDATE ON Emprunter FOR EACH ROW
    WHEN NEW.nb_jrs>2
    DECLARE
        nb_exp NUMBER;
    BEGIN
        SELECT exemplaire into nb_exp FROM Livre WHERE lid=:NEW.lid;
        IF nb_exp<=2 THEN
            RAISE_APPLICATION_ERROR(-20005,'Les livres à moins de deux exemplaires ne peuvent pas être empruntés plus de deux jours');
        END IF;
    END;
    /