Условия в выражениях с помощью Case
select
a.first_name || ' ' || a.last_name as actor_name,
length(a.first_name || ' ' || a.last_name),
case
when length(a.first_name || ' ' || a.last_name) > 15
then substring(a.first_name, 1, 7) || ' ' || substring(a.last_name, 1, 7)
else a.first_name || ' ' || a.last_name
end as short_name
from
actor a;
select
case
when length(a.first_name || ' ' || a.last_name) > 15
then substring(a.first_name, 1, 7) || ' ' || substring(a.last_name, 1, 7)
else a.first_name || ' ' || a.last_name
end as short_name
from
actor a
where
substring(
case
when length(a.first_name || ' ' || a.last_name) > 15
then substring(a.first_name, 1, 7) || ' ' || substring(a.last_name, 1, 7)
else a.first_name || ' ' || a.last_name
end,
1,
2
) = 'Ca'
order by
case
when length(a.first_name || ' ' || a.last_name) > 15
then substring(a.first_name, 1, 7) || ' ' || substring(a.last_name, 1, 7)
else a.first_name || ' ' || a.last_name
end;
select
f.title,
l."name",
case
when l."name" = 'English' then 'Английский'
when l."name" = 'Italian' then 'Итальянский'
when l."name" = 'Japanese' then 'Японский'
when l."name" = 'Mandarin' then 'Китайский'
when l."name" = 'French' then 'Французский'
when l."name" = 'German' then 'Немецкий'
else 'Неизвестный язык'
end,
case
l."name"
when 'English' then 'Английский'
when 'Italian' then 'Итальянский'
when 'Japanese' then 'Японский'
when 'Mandarin' then 'Китайский'
when 'French' then 'Французский'
when 'German' then 'Немецкий'
else 'Неизвестный язык'
end
from
film f
join "language" l
on f.language_id = l.language_id;
select
f.title,
l."name",
case
when l."name" = 'English' then 'Английский'
when l."name" = 'Italian' then 'Итальянский'
when l."name" = 'Japanese' then 'Японский'
when l."name" = 'Mandarin' then 'Китайский'
when l."name" = 'French' then 'Французский'
when l."name" = 'German' then 'Немецкий'
else 'Неизвестный язык'
end,
f.rating,
f.language_id,
l.language_id
from
film f
join "language" l
on case when f.rating = 'G' then 2 else f.language_id end = l.language_id;
select
f.title,
sum(p.amount) as total_amount,
case
when sum(p.amount) >= 150 then 'Top amount'
when sum(p.amount) >= 100 then 'Middle amount'
else 'Low amount'
end as amount_rating
from
film f
join inventory i using(film_id)
join rental r using(inventory_id)
join payment p using(rental_id)
group by
f.title;
select
case
when div(f.rental_rate, 1) = 0 then 0
else 1 / div(f.rental_rate, 1)
end
from
film f