Подзапросы

 

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