P08 Soluciones a los ejercicios

Soluciones

P08.001- Identificador y área de las habitaciones con mayor tamaño

select id, idArea 
from habitacion 
where dimension = (select max(dimension) from habitacion);

P08.002- SIP, nombre y apellidos de los últimos pacientes tratados por un médico (por fecha de línea de historial)

select sip, nombre, apellidos 
from paciente p, tiene_un t, linea_historial l 
where p.sip=t.idpaciente 
  and t.idhistorial=l.idhistorial 
  and l.fecha = (select max(fecha) from linea_historial);

P08.003- Tipo de especialidad del médico de mayor edad

select tipo from especialidad e, medico m, trabajador t 
where e.id=m.idEspecialidad and m.colegiado=t.colegiado
  and t.fecha_nacimiento = (select min(t1.fecha_nacimiento) from trabajador t1, medico m1 where m1.colegiado=t1.colegiado);

P08.004- Muestra toda la información de las habitaciones del área de traumatología cuyas dimensiones son superiores a la media en ese área.-

select h.*
from habitacion h, area a
where h.idArea = a.id
  and tipo = 'traumatología'
  and dimension > 
    (select avg(dimension) 
     from habitacion h, area a 
     where h.idArea = a.id
        and a.tipo = 'traumatología');

P08.005- ¿Cuántos médicos hay asignados a cada una de las especialidades? Muestra el tipo de especialidad junto con el número de médicos.

select e.tipo, count(*) 
from medico m, especialidad e 
where m.idEspecialidad=e.id 
group by e.id, e.tipo;

P08.006- ¿Cuántas líneas tiene cada paciente en su historial? Muestra el SIP, nombre y apellidos del paciente junto con el número de líneas registradas en su historial y ordena por nombre y apellidos.

select p.sip, p.nombre, p.apellidos, count(*) 
from paciente p, tiene_un t, linea_historial l
where p.sip=t.idpaciente and t.idhistorial=l.idHistorial
group by p.sip, p.nombre, p.apellidos
order by p.apellidos, p.nombre;

P08.007- Número de habitaciones libres agrupado por la orientación

select a.orientacion, count(*) 
from habitacion h, area a
where h.idArea=a.id and not h.ocupado 
group by a.orientacion;
Solución alternativa:
select a.orientacion, count(*) 
from habitacion h, area a
where h.idArea=a.id and ocupado=0
group by a.orientacion;
ocupado es un atributo booleano por lo que puede tratarse como "NOT ocupado" o "ocupado=0"

P08.008- Cuenta la cantidad de líneas de historial que no son ni ingresos, ni diagnósticos, ni intervenciones, ni tratamientos, ni pruebas.

select count(*) 
from linea_historial l
where (l.idHistorial, l.id) not in (select idH,idL from ingreso)
  and (l.idHistorial, l.id) not in (select idH,idL from diagnostico)
  and (l.idHistorial, l.id) not in (select idH,idL from intervencion)  
  and (l.idHistorial, l.id) not in (select idH,idL from tratamiento)
  and (l.idHistorial, l.id) not in (select idH,idL from pruebas);

P08.009- Nombre de provincia y nombre de ciudad y cantidad de pacientes residentes en ella ordenado descendentemente por esa cantidad.

select p.provincia, p.ciudad, count(*) 
from paciente p
group by p.provincia, p.ciudad
order by count(*) desc;
Solución alternativa:
select p.provincia, p.ciudad, count(*) 
from paciente p
group by p.provincia, p.ciudad
order by 3 desc;
Es necesario agrupar por provincia y por ciudad por dos motivos: a) no se podría mostrar la provincia si no incluye en el group by, b) podría haber dos ciudades con el mismo nombre en diferentes provincias y por tanto deberían formar grupos diferentes.

P08.010- Muestra el tipo de área y el número de habitaciones desocupadas por cada área ordenado por dicha cantidad

select a.tipo, count(*) 
from area a, habitacion h
where a.id=h.idArea and not h.ocupado
group by a.id, a.tipo
order by count(*);

P08.011- Cantidad de pacientes por cada uno de los grupos sanguíneos mostrando dicho grupo y ordenado descendiente esa cantidad

select h.grupo_sanguineo, count(*) 
from historial h
group by h.grupo_sanguineo
order by count(*) desc;

P08.012- Cantidad de pacientes agrupados por su año de nacimiento mostrando dicho año ordenado descendiente

select year(p.fecha_nacimiento), count(*) 
from paciente p
group by year(p.fecha_nacimiento)
order by year(p.fecha_nacimiento) desc;

Solución alternativa:

select year(p.fecha_nacimiento), count(*) 
from paciente p
group by year(p.fecha_nacimiento)
order by 1 desc;

P08.013- Cantidad de pacientes alérgicos a la insulina agrupados por su año de nacimiento mostrando dicho año ordenado descendiente

select year(p.fecha_nacimiento), count(*) 
from paciente p, historial h, tiene_un t
where p.sip=t.idpaciente and t.idhistorial=h.id
  and h.alergias like '%INSULINA%'
group by year(p.fecha_nacimiento)
order by year(p.fecha_nacimiento) desc;
Solución alternativa:
select year(p.fecha_nacimiento), count(*) 
from paciente p, historial h, tiene_un t
where p.sip=t.idpaciente and t.idhistorial=h.id
  and h.alergias = 'INSULINA'
group by year(p.fecha_nacimiento)
order by year(p.fecha_nacimiento) desc;
Aunque la solución alternativa proporciona el mismo resultado en nuestra BD, sería más correcta la primera puesto que recogería a los pacientes que tuvieran más de una alergia, por ejemplo "INSULINA, PLATA"

P08.014- Obtén el espacio total en metros que ocupan las habitaciones de cada área. Muestra dicha cantidad ordenada de mayor a menor junto con el tipo de área a la que pertenece.

select sum(h.dimension), a.tipo 
from habitacion h, area a
where h.idArea=a.id
group by a.id, a.tipo
order by sum(h.dimension) desc;
Si se agrupa únicamente por a.tipo el resultado es el mismo, pero podría darse el caso de que dos áreas tengan el mismo tipo y entonces agruparía los datos de ambas. La solución no sería correcta.

P08.015- ¿Cuáles son los tipos de área de los que se dispone de menos de 100 habitaciones libres? Muestra el tipo y el número

select a.tipo, count(*) 
from habitacion h, area a
where h.idArea=a.id and not h.ocupado 
group by a.tipo
having count(*)<100;

P08.016- ¿Qué áreas tienen más de 1900 trabajadores asignadas? Muestra el tipo y el número de trabajadores.

select count(t.nif), a.tipo from area a, trabajador t
where a.id=t.idArea
group by a.id, a.tipo
having count(t.nif)>1900;

Solución alternativa:

select count(*), a.tipo from area a, trabajador t
where a.id=t.idArea
group by a.id, a.tipo
having count(*)>1900;

P08.017- Muestra cuántos trabajadores había asignados a cada tipo de turno (mañana, tarde y noche) el día 5 de mayo de 2014.

select count(t.nif), tu.tipo from asignado a, turno tu
where a.idTurno=tu.id
  and '2014-05-05' between tu.fechainicio and tu.fechafin
group by tu.tipo;

Solución alternativa:

select count(t.nif), tu.tipo from asignado a, turno tu
where a.idTurno=tu.id
  and tu.fechainicio<='2014-05-05' 
  and tu.fechafin>='2014-05-05' 
group by tu.tipo;

P08.018- Muestra los tipos de turno (mañana, tarde y noche) que tuvieron menos de 1800 trabajadores asignados durante el día 5 de mayo de 2014.

select tu.tipo from asignado a, turno tu
where a.idTurno=tu.id
  and '2014-05-05' between tu.fechainicio and tu.fechafin
group by tu.tipo
having count(t.nif)<1800;

P08.019- ¿Cuáles son los nombres más frecuentes entre los pacientes? Ordena los nombres de los pacientes por la frecuencia de su uso de mayor a menor (número de pacientes que lo usan).

select nombre, count(*) 
from paciente
group by nombre
order by count(*) desc;
Solución alternativa:
select nombre 
from paciente
group by nombre
having count(*) >= ALL (
  select count(*) from paciente
  group by nombre);
La solución alternativa devuelve exactamente el primero de la lista, es decir, el nombre más frecuente. No obstante, es una construcción que no se han practicado en clase y no se pedirá en esta asignatura

P08.020- Muestra el nombre, apellidos, especialidad y número de líneas de historial que ha firmado cada uno de los médicos

select t.nombre, t.apellidos, e.tipo, count(*)
from trabajador t, medico m, especialidad e, linea_historial l
where t.colegiado=m.colegiado 
  and m.idEspecialidad=e.id
  and l.idMedico =t.colegiado
group by t.colegiado, t.nombre, t.apellidos, e.tipo;

P08.021- Muestra el nombre, apellidos, y especialidad de los médicos que han firmado más de una línea de historial

select t.nombre, t.apellidos, e.tipo
from trabajador t, medico m, especialidad e, linea_historial l
where t.colegiado=m.colegiado 
  and m.idEspecialidad=e.id
  and l.idMedico =t.colegiado
group by t.colegiado, t.nombre, t.apellidos, e.tipo
having count(*)>1;

P08.022- Muestra el nombre, apellidos, y especialidad de los médicos que han firmado al menos una línea de historial

select distinct t.nombre, t.apellidos, e.tipo
from trabajador t, medico m, especialidad e, linea_historial l
where t.colegiado=m.colegiado 
  and m.idEspecialidad=e.id
  and l.idMedico =t.colegiado;
Solución alternativa:
select t.nombre, t.apellidos, e.tipo
from trabajador t, medico m, especialidad e, linea_historial l
where t.colegiado=m.colegiado 
  and m.idEspecialidad=e.id
  and l.idMedico =t.colegiado
group by t.colegiado t.nombre, t.apellidos, e.tipo;
La solución alternativa devuelve el mismo resultado y puede ir en consonancia con las anteriores propuestas.
No obstante, se puede comprobar que no es necesario agrupar para contar valores >=1 ya que si aparece en el resultado de la concatenación es porque el médico ha firmado al menos una línea de historial.
Igualmente, no tendría sentido añadir un count(*)>=1, aunque devolvería el mismo resultado.

P08.023- Un estudio clínico necesita conocer qué días del año producen el mayor número de nacimientos. Obtén día y mes del año (en letra), así como número de pacientes que cumplen años ese día, para aquellos días que sumen 70 o más cumpleaños de pacientes. Ordena la salida cronológicamente

select day(p.fecha_nacimiento), monthname(p.fecha_nacimiento), count(*) from paciente p
group by monthname(p.fecha_nacimiento), day(p.fecha_nacimiento)
having count(*)>=70
order by month(p.fecha_nacimiento), day(p.fecha_nacimiento);
Para la ordenación no sirve la función monthname puesto que daría una ordenación alfabética y no cronológica del mes.