M3106C BDA - TP1

/* Première étape */
/* Question 1 */
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'PROF';

/* Question 2 */
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION' OR OBJECT_TYPE = 'PROCEDURE';

/* Question 3 */
SELECT *
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R' OR CONSTRAINT_TYPE = 'P';

/* Question 4 */
SELECT DISTINCT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
JOIN USER_TAB_COLUMNS
ON USER_CONSTRAINTS.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME
WHERE DATA_TYPE = 'NUMBER';

/* Question 5 */
SELECT DISTINCT CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONSTRAINTS
JOIN USER_TAB_COLUMNS
ON USER_CONSTRAINTS.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME
WHERE DATA_TYPE = 'NUMBER';

/* Question 6 */
SELECT DISTINCT CONSTRAINT_NAME, COLUMN_NAME, DATA_TYPE
FROM USER_CONSTRAINTS
JOIN USER_TAB_COLUMNS
ON USER_CONSTRAINTS.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME
WHERE DATA_TYPE = 'NUMBER';

/* Question 7 */
SELECT *
FROM USER_CONS_COLUMNS;

/* Question 8 */
SELECT DISTINCT C1.TABLE_NAME, C1.COLUMN_NAME, C2.TABLE_NAME, C2.COLUMN_NAME
FROM USER_TAB_COLUMNS C1
JOIN USER_TAB_COLUMNS C2
ON C1.COLUMN_NAME = C2.COLUMN_NAME
WHERE C1.TABLE_NAME <> C2.TABLE_NAME;

/* Question 9 */
SELECT DISTINCT C1.TABLE_NAME, C1.COLUMN_NAME, C2.TABLE_NAME, C2.COLUMN_NAME
FROM USER_TAB_COLUMNS C1
JOIN USER_TAB_COLUMNS C2
ON C1.COLUMN_NAME = C2.COLUMN_NAME
WHERE C1.TABLE_NAME != C2.TABLE_NAME;

/* Deuxième étape */
/* Question 10 */
CREATE
VIEW PROF_INFO2
AS
SELECT DISTINCT PROF.NUM_PROF, PROF.NOM_PROF, PROF.PRENOM_PROF
FROM PROF
JOIN ENSEIGNT
ON PROF.NUM_PROF = ENSEIGNT.NUM_PROF
JOIN ETUDIANT
ON ENSEIGNT.NUM_ET = ETUDIANT.NUM_ET
WHERE ETUDIANT.ANNEE = '2';

/* Question 11 */
CREATE
VIEW DIS
AS
SELECT DISCIPLINE
FROM MODULE
WITH CHECK OPTION;

/* Question 12 */
CREATE
VIEW RESP
AS
SELECT DISTINCT MODULE.CODE, PROF.NUM_PROF
FROM ENSEIGNT
JOIN MODULE
ON ENSEIGNT.CODE = MODULE.CODE
JOIN PROF
ON MODULE.RESP = PROF.NUM_PROF
WITH CHECK OPTION;

/* Question 13 */
CREATE
VIEW ENSEIGNTVIEW
AS
SELECT ENSEIGNT.CODE, ENSEIGNT.NUM_ET, ENSEIGNT.NUM_PROF
FROM ENSEIGNT
WHERE ENSEIGNT.CODE IN (SELECT DISTINCT CODE
                        FROM MODULE)
AND ENSEIGNT.NUM_ET IN (SELECT DISTINCT NUM_ET
                        FROM ETUDIANT)
AND ENSEIGNT.NUM_PROF IN (SELECT DISTINCT NUM_PROF
                          FROM PROF)
WITH CHECK OPTION;

/* Question 14 */
CREATE
VIEW MODULEVIEW
AS
SELECT CODE, LIBELLE
FROM MODULE
WHERE CODE NOT IN (SELECT DISTINCT CODE
                  FROM ENSEIGNT)
AND CODE NOT IN (SELECT DISTINCT CODE
                 FROM NOTATION)
AND CODE NOT IN (SELECT DISTINCT CODEPERE
                 FROM MODULE)
AND CODE NOT IN (SELECT DISTINCT MAT_SPEC
                 FROM PROF)
WITH CHECK OPTION;

/* Troisième étape */
/* Question 15 */
GRANT SELECT
ON ETUDIANT
TO PUBLIC;

/* Question 16 */
SELECT *
FROM ET2044.ETUDIANT
WHERE VILLE_ET = 'MARSEILLE';

INSERT INTO ET2044.ETUDIANT
VALUES('5762','CARDINALE','CESAR','13012','MARSEILLE','2','2');
COMMIT;

/* Question 17 */
GRANT ALL
ON PROF
TO ET2044;

/* Quatrième étape */
/* Questin 18 */
DECLARE
    CURSOR LIBMAT
    IS
    SELECT CODE, LIBELLE
    FROM MODULE;

    PASDEMAT EXCEPTION;

BEGIN
    OPEN LIBMAT;
    IF (LIBMAT%NOTFOUND) THEN RAISE PASDEMAT;
    END IF;
    CLOSE LIBMAT;

    FOR I IN LIBMAT
    LOOP
        DBMS_OUTPUT.PUT_LINE(I.CODE || I.LIBELLE);
    END LOOP;

EXCEPTION
    WHEN PASDEMAT
    THEN RAISE_APPLICATION_ERROR(-20090,'PAS DE MATIERE');
END;

/* Question 19 */
CREATE TABLE GROUPE( NUMERO NUMBER(3,0), EFFECTIF NUMBER(3,0), CONSTRAINT CP_GROUPE PRIMARY KEY (NUMERO));

DECLARE
    NB_GROUPE NUMBER(1,0);
    EFFECTIF GROUPE.EFFECTIF%TYPE;
    AUCUN_ETUDIANT EXCEPTION;
BEGIN
  COMMIT;
  SELECT COUNT(DISTINCT GROUPE) INTO NB_GROUPE FROM ETUDIANT WHERE ANNEE = 2;
  IF NB_GROUPE = 0 THEN RAISE AUCUN_ETUDIANT;
  END IF;
  FOR i IN 1 .. NB_GROUPE
  LOOP
    SELECT COUNT(*) INTO EFFECTIF FROM ETUDIANT WHERE GROUPE = i AND ANNEE = 2;
    INSERT INTO GROUPE VALUES (i, EFFECTIF);
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN AUCUN_ETUDIANT THEN DBMS_OUTPUT.PUT_LINE('Il n''y a pas d''étudiant');
END;