P05 Soluciones a los ejercicios

Soluciones

P05.001- Número de colegiado, apellidos, nombre y teléfono de los trabajadores de las ciudades "Simpson","Burgos" u "Ourense"

select colegiado,apellidos,nombre,telefono
from trabajador 
where ciudad in ('Simpson','Burgos','Ourense');

Solución alternativa:

select colegiado,apellidos,nombre,telefono
from trabajador 
where ciudad = 'Simpson' or ciudad = 'Burgos' or ciudad = 'Ourense';

P05.002- Número de colegiado, apellidos, nombre y teléfono de los trabajadores de las ciudades "Simpson","Burgos" u "Ourense" que son médicos.

select colegiado,apellidos,nombre,telefono
from trabajador 
where ciudad in ('Simpson','Burgos','Ourense')
  and colegiado in (select colegiado from medico);
Solución alternativa:
select t.colegiado,apellidos,nombre,telefono
from trabajador t, medico m
where (ciudad = 'Simpson' or ciudad = 'Burgos' or ciudad = 'Ourense')
  and t.colegiado = m.colegiado
Al utilizar una subconsulta, el filtro consiste en dar por buenos los números de colegiado que se pueden encontrar en la tabla MEDICO. Es una forma eficiente de hacer este tipo de consultas en las que una de las tablas solo se usa para filtrar, no para mostrar resultados de sus columnas.

Fíjate en los paréntesis de los filtros de fila de la solución alternativa.

P05.003- Número de colegiado, apellidos, nombre y teléfono de los trabajadores de las ciudades "Simpson","Burgos" u "Ourense" que son médicos y no se llaman ni Nora, ni AINHOA ni ALBA.

select colegiado,apellidos,nombre,telefono
from trabajador 
where ciudad in ('Simpson','Burgos','Ourense')
  and colegiado in (select colegiado from medico)
  and nombre NOT in ('Nora','AINHOA','ALBA');

P05.004- Obtener en una única columna el nombre completo de los trabajadores, con el formato "APELLIDOS, NOMBRE", de aquellos que son de la ciudad de "Elx/Elche". Busca la función necesaria en el manual de MySQL. La columna ha de estar etiquetada como "NomCompleto".

select concat(apellidos,", ",nombre) NomCompleto
from trabajador 
where ciudad = 'Elx/Elche'

P05.005- Mostrar apellidos, nombre y provincia de los médicos, y si la ciudad es Alicante, mostrarla como Alacant; las otras ciudades, tal y como estén en la BD.

select apellidos,nombre,if(ciudad='Alicante','Alacant',ciudad)
from trabajador
where colegiado in (select colegiado from medico)

P05.006- Crear un código con las 3 primeras letras de los apellidos, las 3 primeras del nombre y los 3 últimos dígitos del teléfono de los médicos. La columna se etiquetará "código".

select concat(left(apellidos,3),left(nombre,3),right(telefono,3)) código
from trabajador
where colegiado in (select colegiado from medico)

P05.007- SIP y fecha de nacimiento de los pacientes, ordenado por fecha de nacimiento descendente.

select sip,fecha_nacimiento from paciente order by fecha_nacimiento desc;

P05.008- SIP y día del mes de la fecha de nacimiento de los pacientes, ordenado por fecha de nacimiento descendente.

select sip,day(fecha_nacimiento) día
from paciente 
order by fecha_nacimiento desc;
DAYOFMONTH() obtiene el mismo resultado.

P05.009- SIP, mes y día del mes de la fecha de nacimiento de los pacientes, ordenado por fecha de nacimiento descendente.

select sip,month(fecha_nacimiento) mes, day(fecha_nacimiento) día
from paciente 
order by fecha_nacimiento desc;

P05.010- SIP, año, mes y día del mes de la fecha de nacimiento de los pacientes, ordenado por fecha de nacimiento descendente.

select sip,
       year(fecha_nacimiento) año,
       month(fecha_nacimiento) mes, 
       day(fecha_nacimiento) día
from paciente 
order by fecha_nacimiento desc;

P05.011- SIP y fecha de nacimiento de los pacientes que nacieron en 2013, ordenado por fecha de nacimiento descendente.

select sip,fecha_nacimiento
from paciente 
where year(fecha_nacimiento) = 2013
order by fecha_nacimiento desc;

P05.012- SIP y fecha de nacimiento, con formato "dd/mm/aaaa", de los pacientes que nacieron en 2013, ordenado por fecha de nacimiento descendente.

select sip,date_format(fecha_nacimiento,'%d/%m/%Y') nacido
from paciente 
where year(fecha_nacimiento) = 2013
order by fecha_nacimiento desc;

P05.013- SIP y fecha de nacimiento, con formato "dd de mm de aaaa", de los pacientes que nacieron en 2013, ordenado por fecha de nacimiento descendente.

select sip,date_format(fecha_nacimiento,'%d de %m de %Y') nacido
from paciente 
where year(fecha_nacimiento) = 2013
order by fecha_nacimiento desc;

P05.014- SIP y fecha de nacimiento, con formato "dd de mes de aaaa", con el mes como nombre (en inglés), de los pacientes que nacieron en 2013, ordenado por fecha de nacimiento descendente.

select sip,date_format(fecha_nacimiento,'%d de %M de %Y') nacido
from paciente 
where year(fecha_nacimiento) = 2013
order by fecha_nacimiento desc;

P05.015- SIP, fecha de nacimiento y días que han pasado desde su nacimiento hasta el 1 de septiembre de 2016 de los pacientes que nacieron en 2013, ordenado por fecha de nacimiento descendente.

select sip,fecha_nacimiento,datediff('2016-09-01',fecha_nacimiento) días
from paciente 
where year(fecha_nacimiento) = 2013 
order by fecha_nacimiento desc;

P05.016- SIP, fecha de nacimiento y días que han pasado desde su nacimiento hasta el 1 de septiembre de 2016 de los pacientes que nacieron en 2013, ordenado por esa cantidad de días descendente.

select sip,fecha_nacimiento,datediff('2016-09-01',fecha_nacimiento) días
from paciente 
where year(fecha_nacimiento) = 2013 
order by datediff('2016-09-01',fecha_nacimiento) desc;

P05.017- SIP y fecha de nacimiento de los pacientes cuando han pasado entre 900 y 1000 días desde esa fecha hasta el 1 de septiembre de 2016.

select sip,fecha_nacimiento
from paciente 
where datediff('2016-09-01',fecha_nacimiento) between 900 and 1000;

P05.018- SIP y semana del año de la fecha de nacimiento de los pacientes cuando han pasado entre 900 y 1000 días desde esa fecha hasta el 1 de septiembre de 2016, ordenado por semana.

select sip,weekofyear(fecha_nacimiento) semana
from paciente 
where datediff('2016-09-01',fecha_nacimiento) between 900 and 1000
order by 2;
Order by permite utilizar el orden de las columnas. Si no, order by weekofyear(fecha_nacimiento).

P05.019- SIP de los pacientes y nombre en español del día de la semana de su nacimiento, para los nacidos en marzo de 2014.

select sip,
case dayofweek(fecha_nacimiento)
when 2 then 'Lunes'
when 3 then 'Miércoles'
when 4 then 'Miércoles'
when 5 then 'Jueves'
when 6 then 'Viernes'
when 7 then 'Sábado'
when 1 then 'Domingo' end diasemana
from paciente 
where year(fecha_nacimiento) = 2014 and month(fecha_nacimiento) = 3;
Dayofweek devuelve el número de día de la semana comenzando por 1=Sunday.

CASE valor WHEN valoresperado THEN resultado es una instrucción de control de flujo que compara un valor con una lista de valores esperados, y devuelve el resultado definido para cada uno de esos valores esperados.

P05.020- SIP, nombre, apellidos, día de nacimiento, mes de nacimiento y años que cumplen, los pacientes que celebran su cumpleaños este mes.

SELECT p.nombre, p.apellidos, DAY(p.fecha_nacimiento) día, MONTHNAME(p.fecha_nacimiento) mes, YEAR(NOW())-YEAR(p.fecha_nacimiento) años from paciente p WHERE month(p.fecha_nacimiento)=MONTH(NOW());