Salta el contingut

DAM - DAW - MP 0484 Bases de Dades

Pràctica 9: - Base de dades hospital. Consultes Complexes - Multiples Taules i Subconsultes

  1. Selecciona el codi, el nom i l’especialitat de tots els metges que treballen a l’hospital la Paz.

    +-------------+------------+--------------+
| Codi Doctor | Nom Doctor | Especialitat |
+-------------+------------+--------------+
|         386 | Cabeza D.  | Psiquiatria  |
|         398 | Best D.    | Urologia     |
|         453 | Galo D.    | Pediatria    |
+-------------+------------+--------------+
    Possible solució
    Amb subconsulta
    SELECT doctor_codi "Codi Doctor", doctor_nom "Nom Doctor", doctor_especialitat "Especialitat"
FROM doctor
WHERE doctor_hospital_codi IN (SELECT hospital_codi
                            FROM hospital
                            WHERE hospital_nom = 'La Paz');
    Amb JOIN.
    SELECT doctor_codi "Codi Doctor", doctor_nom "Nom Doctor", doctor_especialitat "Especialitat"
FROM doctor JOIN hospital ON (doctor_hospital_codi = hospital_codi)
WHERE hospital_nom = 'La Paz';
    No cal el #sql JOIN ja que no ens cal mostrar cap dada de hospital.

  2. Obté el nom i el número total de doctors que té cada hospital.

    +--------------+--------------+
| Nom hospital | Num. Doctors |
+--------------+--------------+
| Provincial   |            1 |
| General      |            2 |
| La Paz       |            3 |
| San Carlos   |            2 |
+--------------+--------------+
    Possible solució 
    SELECT hospital_nom "Nom hospital", COUNT(*) "Num. Doctors"
FROM hospital JOIN doctor ON (doctor_hospital_codi = hospital_codi)
GROUP BY hospital_codi, hospital_nom ;

  3. Obté el número, nom i funció de tots els empleats de la plantilla que treballen a la sala de recuperació de l’hospital San Carlos.

    +--------------+----------+-----------+
| Num. empleat | Nom      | Funcio    |
+--------------+----------+-----------+
|         8526 | Frank H. | Infermera |
+--------------+----------+-----------+
    Possible solució
    Cal fer dues subconsultes anidades per trobar la Sala de Recuperació de l'hospital San Carlos
    SELECT plantilla_empleat_num "Num. empleat", plantilla_nom "Nom ", plantilla_funcio "Funcio"
FROM plantilla
WHERE (plantilla_hospital_codi, plantilla_sala_codi) IN
    (SELECT sala_hospital_codi, sala_codi
    FROM sala
    WHERE sala_nom = 'Recuperacio' AND 
            sala_hospital_codi IN (SELECT hospital_codi
                                    FROM hospital
                                    WHERE hospital_nom = 'San Carlos'));

  4. Obté el nom de l’hospital, el número d’empleats i el salari mig que tenen els empleats de la sala Recuperacio dels diferents hospitals.

    +------------+--------------+---------------+
| Hospital   | Treballadors | Sou mig       |
+------------+--------------+---------------+
| La Paz     |            2 | 192250.000000 |
| San Carlos |            1 | 252200.000000 |
+------------+--------------+---------------+
    Possible solució
    Amb JOIN i Subconsulta
    SELECT hospital_nom "Hospital", COUNT(*) "Treballadors", AVG(plantilla_salari) "Sou mig"
FROM plantilla
    JOIN hospital ON (plantilla_hospital_codi = hospital_codi)
WHERE (plantilla_hospital_codi, plantilla_sala_codi) IN 
      (SELECT sala_hospital_codi, sala_codi FROM sala WHERE sala_nom = 'Recuperacio')
GROUP BY hospital_codi, hospital_nom;
    Només amb JOIN
    SELECT hospital_nom "Hospital", COUNT(*) "Treballadors", AVG(plantilla_salari) "Sou mig"
FROM plantilla
    JOIN sala ON (plantilla_hospital_codi = sala_hospital_codi AND 
                plantilla_sala_codi = sala_codi)
    JOIN hospital ON (sala_hospital_codi = hospital_codi)
WHERE sala_nom = 'Recuperacio'
GROUP BY hospital_codi, hospital_nom;

  5. Obté el codi i el nom dels hospitals que tenen un número total de llits superior a 80. Mostra també el número total de llits d’aquests hospitals. Troba la informació dels llits a partir del camp sala_nllits de la taula sala.

    +---------------+--------------+-------------+
| codi hospital | nom hospital | total llits |
+---------------+--------------+-------------+
|            13 | Provincial   |          88 |
|            22 | La Paz       |         162 |
+---------------+--------------+-------------+
    Possible solució 
    SELECT sala_hospital_codi "codi hospital", hospital_nom "nom hospital", SUM(sala_nllits) "total llits"
FROM sala JOIN hospital ON (sala_hospital_codi = hospital_codi)
GROUP BY sala_hospital_codi, hospital_nom
HAVING SUM(sala_nllits) > 80;

  6. Obté el nom de l’hospital, i el nom de cadascuna de les sales que el componen ordenat per nom d’hospital i nom de sala.

    +--------------+------------------+
| nom hospital | nom sala         |
+--------------+------------------+
| General      | Cardiologia      |
| General      | Cures Intensives |
| La Paz       | Maternitat       |
| La Paz       | Psiquiatric      |
| La Paz       | Recuperacio      |
| Provincial   | Cures Intensives |
| Provincial   | Psiquiatric      |
| San Carlos   | Cardiologia      |
| San Carlos   | Maternitat       |
| San Carlos   | Recuperacio      |
+--------------+------------------+
    Possible solució 
    SELECT hospital_nom "nom hospital", sala_nom "nom sala"
FROM sala JOIN hospital ON (sala_hospital_codi = hospital_codi)
ORDER BY 1, 2;

  7. Obté el nom de l’hospital, i el nom del doctor de cada hospital on el nom del doctor comenci per ‘C’.

    +-----------+----------+
| Doctor    | Hospital |
+-----------+----------+
| Cabeza D. | La Paz   |
| Cajal R.  | General  |
+-----------+----------+
    Possible solució
    Trobem si hi ha repetits i quins són
    SELECT doctor_nom "Doctor", hospital_nom "Hospital"
FROM hospital JOIN doctor ON (hospital_codi = doctor_hospital_codi)
WHERE doctor_nom LIKE 'C%';

  8. Obté el nom d’hospital, i les diferents especialitats que hi ha de doctors en cadascun dels hospitals.

    +------------+--------------+
| Hospital   | Especialitat |
+------------+--------------+
| La Paz     | Psiquiatria  |
| La Paz     | Urologia     |
| Provincial | Cardiologia  |
| La Paz     | Pediatria    |
| San Carlos | Neurologia   |
| General    | Ginecologia  |
| San Carlos | Pediatria    |
| General    | Cardiologia  |
+------------+--------------+
    Possible solució
    El primer que podem fer
    SELECT DISTINCT hospital_nom "Hospital", doctor_especialitat "Especialitat"
FROM hospital JOIN doctor ON (hospital_codi = doctor_hospital_codi);
    Per si podem tenir noms de hospital repetits - No cal DISTINCT
    SELECT hospital_nom "Hospital", doctor_especialitat "Especialitat"
FROM hospital JOIN doctor ON (hospital_codi = doctor_hospital_codi)
GROUP BY hospital_codi, hospital_nom, doctor_especialitat;

  9. Selecciona el nom dels empleats, i el número de telèfon de l’hospital dels empleats que treballen a la sala de Maternitat de l’hospital La Paz.

    +-----------------+------------------+
| Nom treballador | Telefon Hospital |
+-----------------+------------------+
| Nunez C.        | 923-5411         |
+-----------------+------------------+
    Possible solució 
    SELECT plantilla_nom "Nom treballador", hospital_telefon "Telefon Hospital"
FROM plantilla JOIN hospital on (plantilla_hospital_codi = hospital_codi)
WHERE hospital_nom = 'La Paz'
    AND plantilla_sala_codi IN (SELECT sala_codi
                                FROM sala
                                WHERE sala_hospital_codi = hospital_codi
                                        AND sala_nom = 'Maternitat');

  10. Troba el nombre d’infermeres i infermers que treballen en cadascun dels diferents hospitals. Ordena-ho de major a menor nombre

    +------------+--------+
| Hospital   | Nombre |
+------------+--------+
| La Paz     |      4 |
| Provincial |      2 |
| San Carlos |      1 |
+------------+--------+
    Possible solució 
    SELECT hospital_nom "Hospital", COUNT(*) "Nombre"
FROM hospital JOIN plantilla ON (hospital_codi = plantilla_hospital_codi)
WHERE plantilla_funcio IN ('Infermer', 'Infermera')
GROUP BY hospital_codi, hospital_nom
ORDER BY 2 DESC;

  11. Mostra la relació de nombre de llits per treballador, de cada hospital. Utilitza les taules hospital, sala i plantilla

    |hospital_nom|NLLITS / NTREBALLADORS|
+------------+----------------------+
|Provincial  |               44.0000|
|General     |               63.0000|
|La Paz      |               32.4000|
|San Carlos  |               35.0000|
    Possible solució 
    SELECT hospital_nom, NLLITS / NTREBALLADORS
FROM (SELECT sala_hospital_codi hospital_codi, SUM(sala_nllits) NLLITS
    FROM sala
    GROUP BY sala_hospital_codi) S 
    NATURAL join 
    (SELECT plantilla_hospital_codi hospital_codi, count(*) NTREBALLADORS
    FROM plantilla p 
    GROUP BY plantilla_hospital_codi) P
    NATURAL JOIN hospital;

    Hem forçat el NATURAL JOIN amb els noms de camp de les subconsultes del FROM

  12. Quins empleats tenen un sou que està per sota la mitjana?

    +----------+
| empl_nom |
+----------+
| SANCHEZ  |
| ARROYO   |
| SALA     |
| MARTIN   |
| TOVAR    |
| ALONSO   |
| JIMENO   |
| MUNOZ    |
+----------+
    Possible solució 
    SELECT empl_nom
FROM empl
WHERE empl_salari < (SELECT AVG(empl_salari) FROM EMPL);

  13. Quin és el treballador de la plantilla amb sou màxim?

    +----------+
| empl_nom |
+----------+
| REY      |
+----------+
    Possible solució 
    SELECT empl_nom
FROM empl
WHERE empl_salari >= ALL (SELECT empl_salari FROM empl);

    o bé

    SELECT empl_nom
FROM empl
WHERE empl_salari = (SELECT MAX(empl_salari) FROM empl);

  14. Contesta:

    1. A partir de la taula hospital, quin hospital té més llits?

      +----------+
| Hospital |
+----------+
| General  | 
+----------+
      Possible solució 
      SELECT hospital_nom
FROM hospital
WHERE hospital_nllits = (SELECT MAX(hospital_nllits) FROM hospital);

      i també

      SELECT hospital_nom
FROM hospital
WHERE hospital_nllits >= ALL (SELECT hospital_nllits FROM hospital);

    2. A partir de la taula sala, quin hospital té més llits?

    +--------------+
| hospital_nom |
+--------------+
| La Paz       |
+--------------+
    Possible solució 
    SELECT hospital_nom
FROM hospital
WHERE hospital_codi IN (
    SELECT sala_hospital_codi FROM sala GROUP BY sala_hospital_codi HAVING SUM(sala_nllits) >= ALL (
        SELECT SUM(sala_nllits) FROM sala GROUP BY sala_hospital_codi)
    );

  15. Quin hospital té més empleats de la plantilla amb un sou per sota la mitjana?

    +--------------+
| hospital_nom |
+--------------+
| La Paz       |
+--------------+
    Possible solució 
    SELECT hospital_nom
FROM hospital
WHERE hospital_codi IN (
    SELECT plantilla_hospital_codi
    FROM plantilla 
    WHERE plantilla_salari < (SELECT AVG(plantilla_salari) FROM plantilla)
    GROUP BY plantilla_hospital_codi
    HAVING COUNT(*) >= ALL (
        SELECT COUNT(*)
        FROM plantilla
        WHERE plantilla_salari < (SELECT AVG(plantilla_salari) FROM plantilla)
        GROUP BY plantilla_hospital_codi
        )
    );

    I per sobre? Què canviaries de la consulta?

    Possible solució

    Cal canivar els WHERE plantilla_salari < (SELECT AVG(plantilla_salari) FROM plantilla) per WHERE plantilla_salari > (SELECT AVG(plantilla_salari) FROM plantilla) però tenint en compte que a la consulta principal hem utilitzat IN en comptes de =.

    SELECT hospital_nom
FROM hospital
WHERE hospital_codi IN (
    SELECT plantilla_hospital_codi
    FROM plantilla 
    WHERE plantilla_salari > (SELECT AVG(plantilla_salari) FROM plantilla)
    GROUP BY plantilla_hospital_codi
    HAVING COUNT(*) >= ALL (
        SELECT COUNT(*)
        FROM plantilla
        WHERE plantilla_salari > (SELECT AVG(plantilla_salari) FROM plantilla)
        GROUP BY plantilla_hospital_codi
        )
    );

    Donant 3 hospitals...

    +--------------+
| hospital_nom |
+--------------+
| Provincial   |
| General      |
| San Carlos   |
+--------------+

  16. Mostra el codi de l’hospital on hi treballen més metges.

    +---------------+
| Codi Hospital |
+---------------+
|            22 |
+---------------+
    Possible solució 
    SELECT doctor_hospital_codi "Codi Hospital"
FROM doctor
GROUP BY doctor_hospital_codi 
HAVING COUNT(*) >= ALL (
        SELECT COUNT(*)
        FROM doctor
        GROUP BY doctor_hospital_codi
    );

  17. Mostra el codi i nom de l’hospital on hi treballen més metges.

    +------+--------------+
| Codi | Nom hospital |
+------+--------------+
|   22 | La Paz       |
+------+--------------+
    Possible solució 
    SELECT hospital_codi "Codi", hospital_nom "Nom hospital"
FROM hospital
WHERE hospital_codi IN (
    SELECT doctor_hospital_codi
    FROM doctor
    GROUP BY doctor_hospital_codi 
    HAVING COUNT(*) >= ALL (
            SELECT COUNT(*)
            FROM doctor
            GROUP BY doctor_hospital_codi
        )
    );

  18. Llistar les sales que pertanyen a l’hospital on hi treballen més metges.

    +-----------+-------------+-------------+
| sala_codi | sala_nom    | sala_nllits |
+-----------+-------------+-------------+
|         1 | Recuperacio |          10 |
|         2 | Maternitat  |          34 |
|         6 | Psiquiatric |         118 |
+-----------+-------------+-------------+
    Possible solució 
    SELECT sala_codi, sala_nom, sala_nllits
FROM sala
WHERE sala_hospital_codi IN (
    SELECT doctor_hospital_codi
    FROM doctor
    GROUP BY doctor_hospital_codi 
    HAVING COUNT(*) >= ALL (
            SELECT COUNT(*)
            FROM doctor
            GROUP BY doctor_hospital_codi
        )
    );

  19. Mostra els treballadors de cada sala, amb dades de la sala, que pertanyi a l’hospital amb més metges.

    +-----------+-------------+-------------+---------------+------------------+------------------+
| sala_codi | sala_nom    | sala_nllits | plantilla_nom | plantilla_funcio | plantilla_salari |
+-----------+-------------+-------------+---------------+------------------+------------------+
|         6 | Psiquiatric |         118 | Higueras D.   | Infermera        |        200500.00 |
|         1 | Recuperacio |          10 | Rivera G.     | Infermera        |        162600.00 |
|         1 | Recuperacio |          10 | Carlos R.     | Infermera        |        221900.00 |
|         6 | Psiquiatric |         118 | Bocina G.     | Infermer         |        183800.00 |
|         2 | Maternitat  |          34 | Nunez C.      | Intern           |        221000.00 |
+-----------+-------------+-------------+---------------+------------------+------------------+
    Possible solució 
    SELECT sala_codi, sala_nom, sala_nllits, plantilla_nom, plantilla_funcio, plantilla_salari
FROM plantilla JOIN sala ON (plantilla_hospital_codi = sala_hospital_codi AND
                            plantilla_sala_codi = sala_codi)
WHERE plantilla_hospital_codi IN (
    SELECT doctor_hospital_codi
    FROM doctor
    GROUP BY doctor_hospital_codi 
    HAVING COUNT(*) >= ALL (
            SELECT COUNT(*)
            FROM doctor
            GROUP BY doctor_hospital_codi
        )
    );

  20. Mostra tots els hospitals que tenen sales de Cardiologia.

    +------------+
| Hospital   |
+------------+
| General    |
| San Carlos |
+------------+
    Possible solució 
    SELECT hospital_nom "Hospital"
FROM hospital
WHERE hospital_codi IN (SELECT sala_hospital_codi
                        FROM sala
                        WHERE sala_nom = 'Cardiologia');