DAM - DAW - MP 0484 Bases de Dades

Pràctica 12: - Base de dades hospital - Inserció, actualització i esborrat

Aquesta pràctica, com que requereix treballar amb transaccions, no pot realitzar-se des del phpmyadmin. Utilitzeu amb la comanda mysql, des de l'ordre de comandes, o el programa DBeaver.

Afegeix a la taula DEPT un nou departament, el 50, que es dirà Programació i que estarà ubicat a Girona. Fes-ho sense donar nom a les columnes a la instrucció INSERT.
Possible Solució
```
INSERT INTO DEPT
VALUES (50, 'Programació', 'Girona');
```
Afegeix un altre departament a la taula DEPT, el 60, que es dirà Distribució i que estarà ubicat a Anglès. Fes-ho donant nom a les columnes a la instrucció INSERT.
Possible Solució
```
INSERT INTO DEPT(dept_num, dept_nom, dept_loc)
VALUES (60, 'Distribució', 'Anglès');
```

Verifica quines dades tens ara a la taula DEPT.

+----------+---------------+-----------+
| dept_num | dept_nom      | dept_loc  |
+----------+---------------+-----------+
|       10 | Comptabilitat | Sevilla   |
|       20 | Investigacio  | Madrid    |
|       30 | Vendes        | Barcelona |
|       40 | Produccio     | Bilbao    |
|       50 | Programació   | Girona    |
|       60 | Distribució   | Anglès    |
+----------+---------------+-----------+

Possible solució

SELECT *
FROM DEPT;

Afegeix el departament Finances a la taula DEPT, que està ubicat a Girona. El codi del departament ha de ser el mateix del departament amb el codi més gran, però incrementant amb 1.
Possible solució
```
INSERT INTO DEPT
VALUES((SELECT MAX(D.dept_num) + 1 FROM DEPT D), 'Finances', 'Girona');
```
Fixa't en l'ús del ALIAS per la taula de la subconsulta

o bé
```
INSERT INTO DEPT
SELECT MAX(dept_num) + 1, 'Finances', 'Girona' FROM DEPT;
```
A la primera opció utilitzem subconsulta, a la segona una SELECT

Verifica quines dades tens ara a la taula DEPT.

+----------+---------------+-----------+
| dept_num | dept_nom      | dept_loc  |
+----------+---------------+-----------+
|       10 | Comptabilitat | Sevilla   |
|       20 | Investigacio  | Madrid    |
|       30 | Vendes        | Barcelona |
|       40 | Produccio     | Bilbao    |
|       50 | Programació   | Girona    |
|       60 | Distribució   | Anglès    |
|       61 | Finances      | Girona    |
+----------+---------------+-----------+

Possible solució

SELECT *
FROM DEPT;

Canvia la ubicació del departament 60, d’Anglès a Girona.
Possible solució
```
UPDATE DEPT
SET dept_loc = 'Girona'
WHERE dept_num = 60;
```

Canvia la ubicació dels departaments de Girona a Barcelona.

Possible solució

UPDATE DEPT
SET dept_loc = 'Barcelona'
WHERE dept_loc = 'Girona';

Esborra el departament de Finances.

Possible solució

DELETE FROM DEPT
WHERE dept_nom = 'Finances';

Esborra tots els departaments amb un codi major de 40.
Possible solució
```
DELETE FROM DEPT
WHERE dept_num > 40;
```

Afegeix-te com a empleat de la taula EMPL, anant en compte de no duplicar la clau primària, indicant el nom de les columnes explícitament.

Possible solució

INSERT INTO empl(empl_num, empl_nom, empl_ofici, empl_dir, empl_datalt, empl_salari, empl_comissio, empl_dept_num)
VALUES ((select max(e.empl_num) + 1 from empl e), 'Nom', 'OficiNom', NULL, current_date(), 200000, NULL, 20);

o bé

INSERT INTO empl(empl_num, empl_nom, empl_ofici, empl_dir, empl_datalt, empl_salari, empl_comissio, empl_dept_num)
SELECT MAX(e.empl_num) + 1, 'Nom', 'OficiNom', NULL, CURRENT_DATE(), 200000, NULL, 20
FROM empl e;

Afegeix un empleat nou que pertanyi a algun dels departaments on hi ha menys empleats.

Possible solució

INSERT INTO empl(empl_num,
         empl_nom, empl_ofici, empl_dir, empl_datalt, empl_salari, empl_comissio, 
         empl_dept_num)
VALUES ((SELECT MAX(e.empl_num) + 1 FROM empl e),
                'Nom2', 'OficiNom2', NULL, CURRENT_DATE() - INTERVAL 2 DAY, 250000, NULL, 
                (SELECT e2.EMPL_DEPT_NUM 
                 FROM empl e2
                 GROUP BY e2.EMPL_DEPT_NUM
                 HAVING COUNT(*) <= ALL (SELECT COUNT(*) FROM EMPL e3 GROUP BY e3.EMPL_DEPT_NUM)
                 LIMIT 0, 1)
);

Fixa't com hem limitat la subconsulta pel departament, a un sol departament, amb el LIMIT 0,1. A partir del primer (0) n'agafem un (1) ja que ha de tornar una sola fila

Podriem haver inclós els departaments sense empleats amb un JOIN

Compara el resultat de

SELECT DEPT_NUM 
FROM EMPL e2 RIGHT JOIN DEPT ON (dept_num = empl_dept_num) 
GROUP BY e2.EMPL_DEPT_NUM
HAVING COUNT(empl_dept_num) <= ALL (SELECT COUNT(empl_dept_num)
                                    FROM EMPL e3 RIGHT JOIN DEPT ON (dept_num = empl_dept_num) 
                                    GROUP BY e3.EMPL_DEPT_NUM)

amb

SELECT EMPL_DEPT_NUM 
FROM EMPL
GROUP BY EMPL_DEPT_NUM
HAVING COUNT(*) <= ALL (SELECT COUNT(*) FROM EMPL GROUP BY EMPL_DEPT_NUM)

o bé

INSERT INTO empl(empl_num, empl_nom, empl_ofici, empl_dir, empl_datalt, empl_salari, empl_comissio, empl_dept_num)
SELECT MAX(e.empl_num) + 1, 'Nom', 'OficiNom', NULL, CURRENT_DATE(), 2000, NULL, 20
FROM empl e;

```

Modifica l’empleat que has afegit a l’exercici 10 perquè el seu sou sigui 150000.

Possible solució

UPDATE empl
SET empl_salari = 150000
WHERE empl_nom = 'Nom' AND empl_Ofici = 'OficiNom';

Modifica el sou de tots els empleats del departament 20 perquè s’incrementi amb un 10%.
Possible solució
```
UPDATE EMPL
SET empl_salari = empl_salari * 1.1
WHERE empl_dept_num = 20;
```
Esborra els empleats que pertanyen al departament 10.
Possible solució
```
DELETE FROM empl
WHERE empl_dept_num = 10;
```

Afegeix tots els empleats de la taula PLANTILLA, que el seu codi no estigui entre 7000 i 7999, a la taula EMPL assignant-los al departament 10. Per aquelles dades que no tinguis informació, deixa-les a NULL.

Possible solució

INSERT INTO EMPL(empl_num, empl_nom, empl_ofici, empl_salari)
SELECT plantilla_empleat_num, plantilla_nom, plantilla_funcio, plantilla_salari
FROM plantilla
WHERE plantilla_empleat_num NOT BETWEEN 7000 AND 7999;

Modifica tots els empleats que has afegit, perquè tinguin tots com a sou el mateix sou que el sou mig del departament on pertanyen.

Possible solució

UPDATE empl e
SET empl_salari = (SELECT AVG(empl_salari)
                   FROM empl e2 
                   WHERE IFNULL(empl_dept_num, 0) =  IFNULL(e.empl_dept_num, 0))
WHERE empl_ofici IN ('Infermer', 'Infermera', 'Intern');

Si vols afectar també els que has afegit abans, manualment, canvia la clàusula WHERE per ..

WHERE plantilla_empleat_num NOT BETWEEN 7000 AND 7999;

Esborra tots els empleats del departament 10 que tenen un sou per sota la mitjana.

Possible solució

DELETE FROM EMPL
WHERE empl_dept_num = 10 AND
      empl_salari < (SELECT AVG(e2.empl_salari) FROM empl e2);