DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 3 – DML

Per fer aquests exercicis cal tenir una taula anomenada DEPARTAMENTS que serà una còpia de la taula empresa.DEPARTMENTS.

CREATE OR REPLACE TABLE DEPARTAMENTS AS SELECT * FROM empresa.DEPARTMENTS;

També utilitzarem variables d’usuari; aquelles que podeu utilitzar dins d’una sessió i així podrem reutilitzar-les entre diferents blocs de codi.

SET @ara = NOW();
SELECT @ara;

Canvieu el delimitador per a treballar millor (si ho feu des de la shell)

DELIMITER //

Es recomana, per possibles errades, treballar amb una transacció

START TRANSACTION //
SAVEPOINT P1 //

i així, en qualsevol moment poder desfer el que s’ha fet

ROLLBACK TO P1 //

1. Creeu un bloc anònim per buscar el codi de departament màxim a la taula DEPARTAMENTS i emmagatzemeu-lo en una variable local al bloc de codi i en una variable d’usuari anomenada @maximDepartament, ja que la utilitzarem en els següents exercicis. Mostreu el resultat a la pantalla.

   +---------+-------------------+
   | v_maxim | @maximDepartament |
   +---------+-------------------+
   |     190 |               190 |
   +---------+-------------------+
   

   Possible solució

   BEGIN NOT ATOMIC
       DECLARE v_maxim TYPE OF departaments.department_id;
       SELECT IFNULL(MAX(department_id), 0)
           INTO v_maxim
           FROM departaments;
       SET @maximdepartament = v_maxim;
       SELECT @maximdepartament, v_maxim;
   END;
   

2. Modifiqueu el bloc anònim que heu creat a l'exercici 1 per inserir un nou departament a la taula DEPARTAMENTS.

   1. Utilitzeu una variable local al bloc anònim per proporcionar el nom del departament. Anomeneu al nou departament Educació.
   2. Pel codi de departament, utilitzeu la variable @maximDepartament i afegiu-hi 10, a la variable abans de fer l’INSERT.
   3. Deixeu els altres camps com a NULL de moment.
   4. Executeu el bloc anònim.
   Possible solució

   BEGIN NOT ATOMIC
       DECLARE v_department_name TYPE OF departaments.department_name;
       SET @maximDepartament = @maximDepartament + 10;
       SET v_department_name = 'Educació';
       INSERT INTO departaments(department_id, department_name)
           VALUES(@maximDepartament, v_department_name);
   END//
   SELECT * FROM departaments WHERE department_id = @maximDepartament //
   

3. Creeu un bloc anònim que actualitzi el location_id del nou departament que heu afegit la pràctica anterior.

   1. Utilitzeu una variable @maximDepartament per al número d'identificació del departament que heu afegit a la pràctica anterior.
   2. Utilitzeu una variable @location_id per proporcionar l'identificador d’ubicació (location_id). L’identificador de la nova ubicació serà 1700. Passeu el valor al bloc anònim mitjançant aquesta variable.
   3. Executeu el bloc anònim.
   4. Mostreu el departament que heu actualitzat.
   Possible solució

   SET @location_id = 1700 //
   BEGIN NOT ATOMIC
       UPDATE departaments SET location_id = @location_id
           WHERE department_id = @maximDepartament;
   END//
   SELECT * FROM departaments //
   

4. Creeu un bloc anònim que suprimeixi el departament que heu creat a l'exercici 2.

   1. Utilitzeu la variable @maximDepartament per proporcionar l'ID del departament.
   2. Passeu el valor al bloc anònim mitjançant una variable. Imprimiu a la pantalla el nombre de files afectades.
   3. Executeu el bloc anònim.
   4. Confirmeu que s'ha suprimit el departament.
   Possible solució

   BEGIN NOT ATOMIC
       DELETE FROM departaments
           WHERE department_id = @maximDepartament;
       SELECT CONCAT("Files eliminades -> ", ROW_COUNT());
   END//
   SELECT * FROM departaments WHERE department_id = @maximDepartament //