Ejemplos CASE

-- Funciones de control de flujo
-- Tienen un comportamiento similar al if de programación
-- Sirven para devolver valores dependiendo de condiciones

-- CASE sirve para evaluar múltiples valores de un campo
-- O múltiples condiciones

-- CASE value WHEN compare_value THEN result 
-- [WHEN compare_value THEN result ...] [ELSE result] END

select case rating 
	when 'g' then 'Para todos los públicos'
	when 'r' then 'Para mayores de edad'
	when 'pg' then 'menores acompañados'
	else 'No lo se'
end clasificacion
from film;

-- CASE WHEN condition THEN result 
-- [WHEN condition THEN result ...] [ELSE result] END

select case 
	when length<90 then 'Corta'
	when length<120 then 'Media'
	else 'Larga'
end duracion
from film;

select first_name, case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end letra
from actor;

select first_name
from actor
order by  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end;

select first_name
from actor
where  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end='A';

select title, case
	when rating='R' and length>120 then 'Adultos intelectuales'
    when rating<>'R' and length>120 then 'Todos los públicos con aguante'
    else 'Todos los públicos'
    end tipo
from film;