Salta el contingut

DAM - DAW - MP 0484 Bases de Dades

Pràctica 8: - Base de dades hospital. Consultes d'Agrupament

  1. Obté el salari mig de tots els empleats Interns de la plantilla de l'hospital.

    +---------------+
| Sou Mig       |
+---------------+
| 259966.666667 |
+---------------+
    Possible solució 
    SELECT AVG(plantilla_salari) AS "Sou Mig"
FROM plantilla
WHERE plantilla_funcio = 'Intern';

  2. Obté el salari més alt i el salari més baix de la plantilla i la diferencia entre aquests valors.

    +-----------+-----------+------------+
| Màxim     | Mínim     | Diferència |
+-----------+-----------+------------+
| 337900.00 | 162600.00 |  175300.00 |
+-----------+-----------+------------+
    Possible solució 
    SELECT MAX(plantilla_salari) AS "Màxim",
       MIN(plantilla_salari) AS "Mínim",
       MAX(plantilla_salari) - MIN(plantilla_salari) AS "Diferència"
FROM plantilla;

  3. Obté el número de funcions diferents que realitzen els empleats de la plantilla que tenen codi d'hospital 22.

    +-------------+
| NumFuncions |
+-------------+
|           3 |
+-------------+
    Possible solució 
    SELECT COUNT(DISTINCT plantilla_funcio) AS "NumFuncions"
FROM plantilla
WHERE plantilla_hospital_codi = 22;

  4. Busca quins torns de la plantilla de l'hospital tenen més de 2 persones.

    +----------------+-------+
| plantilla_torn | Total |
+----------------+-------+
| T              |     6 |
+----------------+-------+
    Possible solució 
    SELECT plantilla_torn, COUNT(*) "Total"
FROM plantilla
GROUP BY plantilla_torn
HAVING COUNT(*) > 2;

  5. Calcula l'edat mitjana dels malalts. 

    +---------+
| Mitjana |
+---------+
| 66.2000 |
+---------+
    Possible solució 
    SELECT AVG(TIMESTAMPDIFF(YEAR, malalt_dnaixa, CURRENT_DATE)) "Mitjana"
FROM malalt;

  6. Calcula el nombre de treballadors i sou mig de la plantilla de cada sala de cada hospital.

    +-------------------------+---------------------+--------+---------------+
| plantilla_hospital_codi | plantilla_sala_codi | Quants | Mitjana Sou   |
+-------------------------+---------------------+--------+---------------+
|                      13 |                   6 |      2 | 250600.000000 |
|                      18 |                   4 |      1 | 337900.000000 |
|                      22 |                   1 |      2 | 192250.000000 |
|                      22 |                   2 |      1 | 221000.000000 |
|                      22 |                   6 |      2 | 192150.000000 |
|                      45 |                   1 |      1 | 252200.000000 |
|                      45 |                   4 |      1 | 221000.000000 |
+-------------------------+---------------------+--------+---------------+
    Possible solució 
    SELECT plantilla_hospital_codi, plantilla_sala_codi,
       COUNT(*) "Quants",
       AVG(plantilla_salari) "Mitjana Sou"
FROM plantilla
GROUP BY plantilla_hospital_codi, plantilla_sala_codi;

  7. Com pots comprovar que no hi ha malalts amb un número de la SS repetit? Troba la solució.

    Possible solució
    Trobem si hi ha repetits i quins són
    SELECT malalt_nss, COUNT(*) "Repetits"
FROM malalt
GROUP BY malalt_nss
HAVING COUNT(*) > 1;
    Mirem si hi ha repetits
    SELECT COUNT(malalt_nss), COUNT(DISTINCT malalt_nss)
FROM malalt;
    Ens diu si hi ha repetits
    SELECT COUNT(malalt_nss) - COUNT(DISTINCT malalt_nss) Repetits
FROM malalt;

  8. Selecciona el nombre d'empleats per sala que hi ha a l'hospital 22, descartant les sales amb menys de 2 empleats.

    Possible solució
    Distingint per hospital
    SELECT plantilla_hospital_codi, plantilla_sala_codi, COUNT(*) "Empleats"
FROM plantilla
WHERE plantilla_hospital_codi = 22
GROUP BY plantilla_hospital_codi, plantilla_sala_codi
HAVING COUNT(*) >= 2;
    Sense distingir per hospital
    SELECT plantilla_sala_codi, COUNT(*) "Empleats"
FROM plantilla
WHERE plantilla_hospital_codi = 22
GROUP BY plantilla_sala_codi
HAVING COUNT(*) >= 2;

  9. Obté el número total de llits, i la mitja de llits dels hospitals, a partir de la taula HOSPITAL.

    +---------------+------------------+
| Llists totals | Mitjana de llits |
+---------------+------------------+
|          2746 |         686.5000 |
+---------------+------------------+
    Possible solució 
    SELECT SUM(hospital_nllits) "Llists totals", AVG(hospital_nllits) "Mitjana de llits"
FROM hospital;

  10. Calcula:

    1. la mitja de les comissions dels empleats utilitzant AVG.

      Possible solució 
      SELECT AVG(empl_comissio) Mitjana
FROM empl;

    2. la mitja de les comissions dels empleats utilitzant SUM i COUNT

      Possible solució 
      SELECT SUM(empl_comissio) / COUNT(*) Mitjana
FROM empl;

    Perquè han sortit diferents aquests valors?

    Possible solució

    A l'utilitzar COUNT(*) comptem les files i no els empleats que sí tenen comissió. Per a donar el mateix resultat hauriem d'haver comptat els camps empl_comissio que no són NULL de la següent forma:

    SELECT SUM(empl_comissio) / COUNT(*) Mitjana
FROM empl
WHERE empl_comissio IS NOT NULL;

    o millor

    SELECT SUM(empl_comissio) / COUNT(empl_comissio) Mitjana
FROM empl;

  11. Troba quants malalts de cada sexe tenim als hospitals?

    +------+-------+
| Sexe | Total |
+------+-------+
| F    |     5 |
| M    |     5 |
+------+-------+
    Possible solució 
    SELECT malalt_sexe "Sexe", COUNT(*) "Total"
FROM malalt
GROUP BY malalt_sexe;

  12. Trobar el total de llits, per tipus de sala disponibles entre tots els hospitals.

    +------------------+---------------+
| sala_nom         | Suma de Llits |
+------------------+---------------+
| Cardiologia      |           108 |
| Cures Intensives |            31 |
| Maternitat       |            36 |
| Psiquiatric      |           185 |
| Recuperacio      |            23 |
+------------------+---------------+
    Possible solució 
    SELECT sala_nom, SUM(sala_nllits) "Suma de Llits"
FROM sala
GROUP BY sala_nom;

  13. Troba en quins hospitals hi treballen més d'un doctor (i indica'n quants), tenint en compte que no volem que es mostrin ni comptin els doctors amb l'especialitat de neurologia.

    +----------+--------+
| Hospital | Quants |
+----------+--------+
|       13 |      1 |
|       18 |      2 |
|       22 |      3 |
|       45 |      1 |
+----------+--------+

    o millor...

    +------------+--------+
| Hospital   | Quants |
+------------+--------+
| Provincial |      1 |
| General    |      2 |
| La Paz     |      3 |
| San Carlos |      1 |
+------------+--------+
    Possible solució 
    SELECT doctor_hospital_codi "Hospital", COUNT(*) "Quants"
FROM doctor
WHERE doctor_especialitat <> 'Neurologia'
GROUP BY doctor_hospital_codi;

    o bé, afegint el nom de l'hospital

    SELECT hospital_nom "Hospital", COUNT(*) "Quants"
FROM doctor JOIN hospital ON (doctor_hospital_codi = hospital_codi)
WHERE doctor_especialitat <> 'Neurologia'
GROUP BY doctor_hospital_codi, hospital_nom;

  14. Obté, a partir de les sales, els codis dels hospitals que tenen més de 65 llits i indica quants en tenen. Prova d'afegir-hi el nom de l'hospital.

    +----------+--------+
| Hospital | Quants |
+----------+--------+
|       13 |     88 |
|       22 |    162 |
|       45 |     70 |
+----------+--------+

    o millor

    +------------+--------+
| Hospital   | Quants |
+------------+--------+
| Provincial |     88 |
| La Paz     |    162 |
| San Carlos |     70 |
+------------+--------+
    Possible solució 
    SELECT sala_hospital_codi "Hospital", SUM(sala_nllits) "Quants"
FROM sala
GROUP BY sala_hospital_codi
HAVING SUM(sala_nllits) > 65;

    i també

    SELECT hospital_nom "Hospital", SUM(sala_nllits) "Quants"
FROM sala JOIN hospital ON (sala_hospital_codi = hospital_codi)
GROUP BY sala_hospital_codi, hospital_nom
HAVING SUM(sala_nllits) > 65;

  15. Obté de forma ordenada per sala i torn un llistat del total de personal que hi ha en cada sala, indicant el número de sala, el tipus d'empleat, el torn i el número d'empleats.

    +-------------------------+---------------------+------------------+----------------+--------+
| plantilla_hospital_codi | plantilla_sala_codi | plantilla_funcio | plantilla_torn | Quants |
+-------------------------+---------------------+------------------+----------------+--------+
|                      13 |                   6 | Infermer         | T              |      1 |
|                      13 |                   6 | Infermera        | T              |      1 |
|                      18 |                   4 | Intern           | T              |      1 |
|                      22 |                   1 | Infermera        | N              |      1 |
|                      22 |                   1 | Infermera        | T              |      1 |
|                      22 |                   2 | Intern           | M              |      1 |
|                      22 |                   6 | Infermer         | M              |      1 |
|                      22 |                   6 | Infermera        | T              |      1 |
|                      45 |                   1 | Infermera        | T              |      1 |
|                      45 |                   4 | Intern           | N              |      1 |
+-------------------------+---------------------+------------------+----------------+--------+
    Possible solució 
    SELECT plantilla_hospital_codi, plantilla_sala_codi,
       plantilla_funcio, plantilla_torn, COUNT(*) "Quants"
FROM plantilla
GROUP BY plantilla_hospital_codi, plantilla_sala_codi,
         plantilla_funcio, plantilla_torn;

  16. Troba la data de naixement dels pacients més jove i del més vell, per homes i dones.

    +-------------+------------+------------+
| malalt_sexe | Més Jove   | Més gran   |
+-------------+------------+------------+
| F           | 1980-07-10 | 1940-06-18 |
| M           | 1980-12-26 | 1942-01-01 |
+-------------+------------+------------+
    Possible solució 
    SELECT malalt_sexe, MAX(malalt_dnaixa) "Més Jove", MIN(malalt_dnaixa) "Més gran"
FROM malalt
GROUP BY malalt_sexe;

  17. Obté els codis d'hospital on hi hagi com a mínim dues persones treballant al torn de tarda.

    +----------+--------+
| Hospital | Quants |
+----------+--------+
|       13 |      2 |
|       22 |      2 |
+----------+--------+
    Possible solució 
    SELECT plantilla_hospital_codi "Hospital", COUNT(*) "Quants"
FROM plantilla
WHERE plantilla_torn = 'T'
GROUP BY plantilla_hospital_codi
HAVING COUNT(*) >= 2;

  18. Obté els codis d'hospital on hi hagi com a mínim dues persones treballant a la sala núm. 6 i al torn de tarda.

    +----------+
| Hospital |
+----------+
|       13 |
+----------+
    Possible solució 
    SELECT plantilla_hospital_codi Hospital
FROM plantilla
WHERE plantilla_sala_codi = 6 AND plantilla_torn = 'T'
GROUP BY plantilla_hospital_codi, plantilla_sala_codi
HAVING COUNT(*) >= 2;

  19. Obté el nombre de llits totals de les sales dels hospitals, mostrant el codi d'hospital i el total de llits. Ordena el resultat de tal manera que l'hospital amb més llits sigui el primer de la llista.

    +--------------------+--------------+
| sala_hospital_codi | Llits Totals |
+--------------------+--------------+
|                 22 |          162 |
|                 13 |           88 |
|                 45 |           70 |
|                 18 |           63 |
+--------------------+--------------+
    Possible solució 
    SELECT sala_hospital_codi, SUM(sala_nllits) "Llits Totals"
FROM sala
GROUP BY sala_hospital_codi
ORDER BY SUM(sala_nllits) DESC;

  20. Obté el salari mig (amb euros i dos decimals) de cada departament, però, només tenint en compte el salari dels empleats que van entrar a treballar a l'empresa l'any 1981. Ordena el resultat de menys a més sou.

    +---------------+-----------+
| empl_dept_num | Sou mig   |
+---------------+-----------+
|            30 | 206916.67 |
|            20 | 388375.00 |
|            10 | 484250.00 |
+---------------+-----------+
    Possible solució 
    SELECT empl_dept_num, ROUND(AVG(empl_salari), 2) "Sou mig"
FROM empl
WHERE YEAR(empl_datalt) = 1981
GROUP BY empl_dept_num
ORDER BY 2;

  21. Mostra els codis d'hospital on hi treballen un mínim de dues Infermeres en el torn de tarda.

    +----------+----------+
| Hospital | COUNT(*) |
+----------+----------+
|       22 |        2 |
+----------+----------+
    Possible solució 
    SELECT plantilla_hospital_codi "Hospital", COUNT(*)
FROM plantilla
WHERE plantilla_funcio = 'Infermera' AND plantilla_torn = 'T'
GROUP BY plantilla_hospital_codi
HAVING COUNT(*) >= 2;