# exercícios back-end aula 28/07

## exercício 0.A

select category, count(*) 
from times 
group by category 
order by count(*) desc
limit 5

## exercício 0.B

select * from (
	select category, count(*) 
	from times 
	group by category 
	order by count(*) desc
) as honras 
where count between 3 and 6 

## exercício 0.C

select *
from times 
where category in (
select category from (
	select category, count(*) 
	from times 
	group by category 
	order by count(*) desc
) as honras 
where count between 3 and 6 
order by count desc 
limit 2 
)

## exercício 1.A

select * from (
	select birth_country, count(*) 
	from nobel
	group by birth_country
	order by count desc) as dados 
where count between 10 and 50 

## exercício 1.B

select prize_share 
from (select prize_share, count(prize_share)
from nobel 
group by prize_share 
order by count asc) as dados  

## exercício 1.C

select full_name, category, year, prize_share  
from nobel 
where prize_share in (select prize_share from (
	select prize_share, count(prize_share)
	from nobel 
	group by prize_share 
	order by count asc
	limit 1
) as share)  
group by full_name, category, year, prize_share

## exercício 1.D

select category, count(*)  
from nobel 
where prize_share in (select prize_share from (
	select prize_share, count(prize_share)
	from nobel 
	group by prize_share 
	order by count asc
	limit 1
) as share)  
group by category 
order by count(*) desc 

## exercício 1.E

select category from (select category, count(*)  
from nobel 
where prize_share in (select prize_share from (
	select prize_share, count(prize_share)
	from nobel 
	group by prize_share 
	order by count asc
	limit 1
) as share)
group by category 
order by count(*) desc) as dados 
where count > 30 

## exercício 2.A

select *, count(*)
from (
select unnest(string_to_array(name, ' '))
from cervejarias) as nomes
group by nomes.unnest
order by count desc
limit 4

## exercício 2.B

select *, count(*) 
from (
	select unnest(string_to_array(name, ' '))
	from cervejas) as nomes  
 group by nomes.unnest 
order by count desc 
limit 5 

## exercício 2.C

select count(dados.unnest) 
from (select *, count(*) 
    from (
        select unnest(string_to_array(name, ' '))
        from cervejarias) as nomes  
    group by nomes.unnest 
    order by count asc ) as dados 
where count = 1

## exercício 2.D

select count(dados.unnest) 
from (select *, count(*) 
    from (
        select unnest(string_to_array(name, ' '))
        from cervejas) as nomes  
    group by nomes.unnest 
    order by count asc ) as dados 
where count = 1

## exercício 2.E

select distinct cervejarias_nomes.unnest from (select unnest(string_to_array(name, ' '))
from cervejarias) as cervejarias_nomes
where cervejarias_nomes.unnest in (
	select unnest(string_to_array(name, ' '))
	from cervejas)

## exercício 3.A

select distinct unnest(string_to_array(casting, ', '))    
from netflix

## exercício 3.B

select unnest(string_to_array(casting, ', ')), count(*)    
from netflix
group by unnest
order by count desc

## exercício 3.C

select unnest(string_to_array(casting, ', ')), count(*)    
from netflix
group by unnest
order by count desc
limit 10

## exercício 3.D

select actors.unnest from (select unnest(string_to_array(casting, ', ')), count(*)    
from netflix
group by unnest
order by count desc) as actors 
where (actors.count between 10 and 30) and (actors.count not between 15 and 20)

## exercício 3.E

select unnest(string_to_array(casting, ', ')), country, count(*)    
from netflix
	where country not like '%India%'
	and country not like '%,%'
group by unnest, country 
order by count desc

## exercício 3.F

select actors.unnest from (select distinct unnest(string_to_array(casting, ', '))    
from netflix) as actors 
where actors.unnest in (select distinct unnest(string_to_array(director, ', ') )
from netflix) 

## exercício 4.A

select title 
from netflix 
where country not like '%,%'

## exercício 4.B

select unnest(string_to_array(title, ' ')), count(*) 
from netflix 
where country not like '%,%'
group by unnest 
order by count desc 

## exercício 4.C

select * from (select unnest(string_to_array(title, ' ')), count(*) 
from netflix 
where country not like '%,%'
group by unnest 
order by count desc) as titulos
where titulos.count between 20 and 40

## exercício 4.D

select * from (select unnest(string_to_array(title, ' ')), count(*) 
from netflix 
where country not like '%,%'
group by unnest 
order by count desc) as titulos
limit 1
offset 7