TD5

-- Création des tables (Q2)
CREATE TABLE AVION
(
  avNum NUMBER primary key,
  avNom VARCHAR2(50),
  capacite NUMBER,
  localisation VARCHAR2(50)
);

CREATE TABLE Pilote
(
  plNum NUMBER primary key,
  plNom VARCHAR2(40),
  plPrenom VARCHAR2(40),
  ville VARCHAR2(40),
  salaire NUMBER
);

CREATE TABLE vol
(
  volNum NUMBER primary key
  avNum NUMBER foreign key reference avion.avNum,
  plNum NUMBER foreign key reference pilote.plNum,
  villeDept VARCHAR2(40),
  villeArr VARCHAR2(40),
  heureDept DATE,
  heureArr DATE

  Constraint fk_av foreign key (avNum) references avion(avNum);
  Constraint fk_pl foreign key (plNum) reference
);

-- Q1.
DECLARE
  v_nb_total NUMBER := 0;
  v_nb_jongleur NUMBER := 0;
  v_percent_jongleur NUMBER(4,2) := 0;
CURSOR c_personnel IS SELECT * FROM personnel;

BEGIN
  FOR v_personnel IN c_personnel
  LOOP
    IF v_personnel.role = 'Jongleur' THEN
      v_nb_jongleur := v_nb_jongleur + 1;
    END IF;
    v_nb_total := v_nb_total + 1;
  END LOOP;
  v_percent_jongleur := v_nb_jongleur/v_nb_total*100;
  DBMS_OUTPUT.PUT_LINE('Pourcentage de Jongleur :  ' || v_percent_jongleur || '%');

END;

-- Q2
DECLARE
  CURSOR c_record IS SELECT vol.volNum, vol.avNum, heureArr, heureDept, avnom FROM vol, avion WHERE vol.avnum = avion.avnum AND avion.avnom IN ('A300', 'A310') ;

BEGIN
  FOR v_record IN c_record
  LOOP
    IF v_record.avnom = 'A300' THEN
      UPDATE vol
      SET HEUREARR = HEUREARR * 0.9 WHERE volnum = v_record.volnum;
    ELSE
      UPDATE vol
      SET HEUREARR = HEUREARR * 0.85 WHERE volnum = v_record.volnum;
    END IF;
  END LOOP;

END;



-- Q3.1

DECLARE
  v_nom VARCHAR2(40);
BEGIN
  SELECT ename INTO v_nom FROM emp WHERE  empno = 7844;
  DBMS_OUTPUT.PUT_LINE('L e salarié qui a pour matricule 7844 s appelle ' || v_nom);
END;

-- Q3.2

DECLARE
  CURSOR c_dept IS SELECT * FROM dept WHERE DNAME = 'RESEARCH';
BEGIN
  FOR v_dept IN c_dept
  LOOP
      DBMS_OUTPUT.PUT_LINE('Departement numero : ' || v_dept.deptno);
      DBMS_OUTPUT.PUT_LINE('Departement qui a pour nom : ' || v_dept.dname);
      DBMS_OUTPUT.PUT_LINE('Departement qui se situe : ' || v_dept.loc);
  END LOOP;

END;


-- Q3.3
DECLARE
  v_nb_emp NUMBER;
CURSOR c_dept IS SELECT * FROM dept ;
BEGIN
  FOR v_dept IN c_dept
  LOOP
      SELECT COUNT(*) INTO v_nb_emp FROM emp WHERE emp.deptno = v_dept.deptno;
      DBMS_OUTPUT.PUT_LINE('Departement numero : ' || v_dept.deptno);
      DBMS_OUTPUT.PUT_LINE('Departement qui a pour nom : ' || v_dept.dname);
      DBMS_OUTPUT.PUT_LINE('Departement qui se situe : ' || v_dept.loc);
      DBMS_OUTPUT.PUT_LINE('Departement a : ' || v_nb_emp || ' employé(s)');

  END LOOP;

END;

-- Q3.4
DECLARE
  CURSOR c_dept IS SELECT * FROM dept;
BEGIN
  FOR v_dept IN c_dept
  LOOP
        DBMS_OUTPUT.PUT_LINE('Departement numero : ' || v_dept.deptno);
        DBMS_OUTPUT.PUT_LINE('Departement qui a pour nom : ' || v_dept.dname);
      DBMS_OUTPUT.PUT_LINE('Departement qui se situe : ' || v_dept.loc);
  END LOOP;

END;
-- Q3.5
DECLARE
  CURSOR c_emp IS SELECT ename, sal, job FROM emp, salgrade WHERE grade = 4 AND sal BETWEEN losal AND hisal;
BEGIN
  FOR v_emp IN c_emp
  LOOP
    DBMS_OUTPUT.PUT_LINE('Nom : ' || v_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Salaire : ' || v_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Job : ' || v_emp.job);

  END LOOP;
END;

-- Q3.6
DECLARE
  CURSOR c_emp IS SELECT emp.ename, emp.sal, emp.mgr, job FROM emp , salgrade WHERE grade = 4 AND sal BETWEEN losal AND hisal;
  v_nom_manager EMP.ENAME%TYPE;

BEGIN
  FOR v_emp IN c_emp
  LOOP
    SELECT ename INTO v_nom_manager FROM emp WHERE empno = v_emp.mgr;
    DBMS_OUTPUT.PUT_LINE('Nom : ' || v_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Salaire : ' || v_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Job : ' || v_emp.job);
    DBMS_OUTPUT.PUT_LINE('Manageur : ' ||v_nom_manager);
    DBMS_OUTPUT.PUT_LINE(' ');

  END LOOP;
END;

-- Values

INSERT INTO pilote VALUES (1, 'GUICHARD', 'Jonathan', 'LYON', 5000);
INSERT INTO pilote VALUES (2, 'REYNAUD', 'Baptiste', 'PARIS', 4000);
INSERT INTO pilote VALUES (3, 'CINQUINO', 'Colin', 'LYON', 3000);
INSERT INTO pilote VALUES (4, 'PLANCHON', 'Johan', 'LYON', 7000);

INSERT INTO Avion  VALUES ( 1 , 'A300' , 3.14159 , 'ChezJP');
INSERT INTO Avion  VALUES ( 2 , 'A310' , 42 , 'ChezMireille');
INSERT INTO Avion  VALUES ( 3 , 'B16' , 9 , 'Devine');
INSERT INTO Avion VALUES ( 4 , 'A300' , 3.14159 , 'Lyon');
INSERT INTO Avion VALUES ( 5 , 'B16' , 9 , 'Devine');
INSERT INTO Avion VALUES ( 6 , 'B16' , 9 , 'Devine');
INSERT INTO Avion VALUES ( 7 , 'B16' , 9 , 'Devine');
INSERT INTO Avion VALUES ( 8 , 'A310' , 42 , 'Paris');

INSERT INTO vol VALUES (1, 1, 1, 'Lyon', 'Paris', 12.00, 13.50);
INSERT INTO vol VALUES (2, 2, 2, 'Marseille', 'Reims', 10.00, 13.00);
INSERT INTO vol VALUES (3, 3, 3, 'Bordeaux', 'Paris', 8.00, 10.50);
INSERT INTO vol VALUES (4, 4, 4, 'Bordeaux', 'Paris', 8.00, 10.50);
INSERT INTO vol VALUES (5, 5, 1, 'Pompei', 'Paris', 8.00, 14.50);
INSERT INTO vol VALUES (6, 6, 2, 'IUT', 'Maison', 8, 8.2);
INSERT INTO vol VALUES (7, 7, 3, 'Recharinge', 'Perpignant', 7.00, 10.50);
INSERT INTO vol VALUES (8, 8, 4, 'Bordeaux', 'Toulouse', 7.00, .50);