DAM - DAW - MP 0484 Bases de Dades

Pràctica 5: - Base de dades hospital

Obté el nom, l’ofici, la data d’alta i la data un any posterior a la data d’alta de tots els empleats del departament que són analistes i que van entrar a l’empresa a partir de l’any 1987.
Possible solució
```
SELECT EMPL_NOM, EMPL_OFICI, EMPL_DATALT,
       EMPL_DATALT + INTERVAL 1 YEAR
FROM EMPL
WHERE EMPL_OFICI = 'Analista' AND
      EMPL_DATALT >= '1987/01/01';
```
Mostra per cada hospital, el codi de l’hospital, el nom complert de l’hospital i el seu nom abreviat de tres lletres (posar al nom de la columna ABR). Ordenar la recuperació de les dades per la columna ABR.
Possible solució
```
SELECT HOSPITAL_CODI, HOSPITAL_NOM, LEFT( HOSPITAL_NOM, 3) ABR
FROM HOSPITAL
ORDER BY 3;
```

De la taula DOCTOR, assigna a Cardiologia el codi 1, a Psiquiatria el codi 2, a Pediatria el codi 3 i a qualsevol altra especialitat el codi 4. Obté el nom de cada doctor, la seva especialitat i el codi assignat.

Possible solució

Amb CASE

SELECT DOCTOR_NOM, DOCTOR_ESPECIALITAT,
       CASE DOCTOR_ESPECIALITAT
            WHEN 'Cardiologia' THEN 1
            WHEN 'Psiquiatria' THEN 2
            WHEN 'Pediatria' THEN 3
            ELSE 4
        END AS Codi
FROM DOCTOR;

Amb DECODE_ORACLE

SELECT DOCTOR_NOM, DOCTOR_ESPECIALITAT,
       DECODE_ORACLE(DOCTOR_ESPECIALITAT,
            'Cardiologia', 1,
            'Psiquiatria', 2,
            'Pediatria', 3,
            4) Codi
FROM DOCTOR;

Llista els malalts amb nom, data naixement i edat.

Possible solució

SELECT MALALT_NOM, MALALT_DNAIXA,
       TIMESTAMPDIFF(YEAR, MALALT_DNAIXA, CURRENT_DATE) Edat
FROM MALALT;

Obté el cognom i nom de tots els empleats de la plantilla que no siguin ni Infermer ni Infermera, de tal manera que la sortida per pantalla ocupi el doble de la seva longitud original omplint els espais restants amb el símbol _ per l’esquerra.
Possible solució
```
SELECT CONCAT(REPEAT('_',LENGTH(MALALT_NOM)), MALALT_NOM) NOM
FROM MALALT;
```
Llista els diferents noms (sense repeticions) dels malalts de l’hospital. Entenem com a nom el que hi ha al final del cognom, després de l'espai.
Possible solució
```
SELECT DISTINCT SUBSTRING(MALALT_NOM,
                          INSTR(MALALT_NOM, ' ') + 1) Nom
FROM MALALT;
```

Selecciona el número, nom, data alta i ofici dels empleats que tenen data d’alta l’any 1981 i treballen al departament 20.

Possible solució

SELECT EMPL_NUM, EMPL_NOM, EMPL_DATALT, EMPL_OFICI
FROM EMPL
WHERE YEAR(EMPL_DATALT) = 1981 AND
      EMPL_DEPT_NUM = 20;

Troba els noms d’empleat que no tenen assignada cap tipus de comissió. Taula EMPL.
Possible solució
```
SELECT EMPL_NOM
FROM EMPL
WHERE EMPL_COMISSIO IS NULL;
```
Trobar el nom dels empleats, i la comissió del empleats que tenen assignada una comissió menor de 66000, tenint en compte que els que no en tenen cap d’assignada, cobren 50000 i també volem que surtin. Taula EMPL.
Possible solució
```
SELECT EMPL_NOM Nom, IFNULL(EMPL_COMISSIO, 50000) Comissio
FROM EMPL
WHERE IFNULL(EMPL_COMISSIO, 50000) < 66000;
```

De tots els malalts que tenen 40 anys o més, mostra'n l’edat i el seu nom complert però mostrant en primer lloc el nom i llavors el cognom (el nom sempre està format per una lletra i un punt). Cal ordenar el resultat del malalt de més vell a més jove.

Possible solució

SELECT CONCAT_WS(' ',
                 SUBSTRING(MALALT_NOM, INSTR(MALALT_NOM, ' ') + 1),
                 LEFT(MALALT_NOM, INSTR(MALALT_NOM, ' ') - 1)) "Nom i cognoms",
                 TIMESTAMPDIFF(YEAR,MALALT_DNAIXA,CURRENT_TIMESTAMP
                ) EDAT
FROM MALALT
WHERE TIMESTAMPDIFF(YEAR,MALALT_DNAIXA,CURRENT_TIMESTAMP) >= 40
ORDER BY MALALT_DNAIXA;

Obté el dia (2 dígits), mes (2 dígits) i any (4 dígits), per separat, de la data de naixement dels malalts. El resultat ha d’estar ordenat de tal manera que l’any de naixement sigui el més actual, el mes de menor a major i el dia de major a menor.
Possible solució
```
SELECT DATE_FORMAT(MALALT_DNAIXA, '%d') DIA,
       DATE_FORMAT(MALALT_DNAIXA, '%m') MES,
       DATE_FORMAT(MALALT_DNAIXA, '%Y') ANY
FROM MALALT
ORDER BY 3 DESC, MES, DIA DESC;
```