Подзапросы (30 мин.)

Смотреть код

select
*
from
address a;

select
*
from
customer c;

select
*
from
address a
where
not exists(
select
1
from
customer c
where
c.address_id = a.address_id 

);

select
*
from
customer c
where
c.address_id = 5

select
*
from
film f
where
f.rental_duration in (3, 4);

select
fc.category_id
from
film_category fc
group by
fc.category_id
having
count(*) > 70;

select
f.title,
c."name" as category_name
from
film f
join film_category fc 
on
fc.film_id = f.film_id
join category c
on
c.category_id = fc.category_id
where
c.category_id in (
select
fc.category_id
from
film_category fc
group by
fc.category_id
having
count(*) > 70
);

select
1 in (1, 2);

--true

select
1 in (2, 3);

--false

select
1 in (1, 2, null);

--true

select
1 in (2, 3, null);

--null

select
null in (1, 2, null);

--null

select

distinct p.amount
from
payment p
order by
p.amount ;

select
*
from
payment p
where
p.amount > any(
select
p2.amount
from
payment p2 
)
order by
p.amount;

select
*
from
payment p
where
p.amount >= all(
select
p2.amount
from
payment p2 
)
order by
p.amount;

select
f.title,
f.rating,
(
select
count(*)
from
film f2
where
f2.rating = f.rating 
) as film_rating_cnt,
(
select
count(*)
from
film f2
) as film_cnt,
(
select
c."name"
from
film_category fc
join category c 
on
c.category_id = fc.category_id
where
fc.film_id = f.film_id 
) as category_name
from
film f;

select
f.title
from
film f
where
10 < (
select
count(*)
from
film_actor fa
where
fa.film_id = f.film_id 
);

select
f.rating,
count(*) as cnt
from
film f
group by
f.rating;

select
f.title,
f.rating,
fr.cnt as rating_film_cnt
from
film f
join (

select
f.rating,
count(*) as cnt
from
film f
group by
f.rating 
) fr

on
f.rating = fr.rating;