# exercícios aula back-end 21/07

## exercício 01

select distinct listed_in, count(title) as count 
from netflix
group by listed_in
order by count desc 

## exercício 02

select distinct listed_in, avg((split_part(duration, ' ', 1))::real) as media 
from netflix 
	where type = 'Movie'
group by listed_in 
order by media desc

## exercicio 03

select listed_in, release_year, count(release_year) as count 
from netflix
	where type = 'TV Show'
group by listed_in, release_year 
order by count desc

## exercício 04

select count(date_added) 
from netflix 
	where date_part('year', date_added) <> release_year 

## exercício 05

select country, type, count(release_year) as year 
from netflix 
	where country not like '%United States%'
	and type = 'TV Show'
group by country, type  
order by year desc 
limit 5 

## exercício 06

select distinct listed_in, count(listed_in) as count
from netflix 
	where rating = 'R'
group by listed_in 
order by count desc 

## exercício 07

select title, casting
from netflix 
	where casting like 'Antonio Banderas%'

## exercício 08

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

## exercício 09

select title 
from netflix 
	where title like '%Harry Potter%'
	or title like '%Transformers%'
	or title like '%Star Wars%'
	or title like '%Lord of the Rings%'

## exercício 10

select type, count(type) as quantidade 
from netflix 
	where description like '%zombie%'
	or description like '%dystopian%'
group by type 

## exercício 11

select release_year 
from netflix
	where type = 'TV Show'
	and country not like 'United States%'
	and country not like '%United Kingdom%'
order by release_year asc 
limit 5 

## exercício 12

select duration, count(duration) as count
from netflix
	where listed_in like '%Documentaries%'
	and split_part(duration, ' ', 1)::real >= 90  
	and split_part(duration, ' ', 1)::real <= 100  
	and date_part('year', date_added)::int >= 2015 
	and date_part('year', date_added)::int <= 2017 
group by duration 
order by count desc

## exercício 13

select unnest(string_to_array(country,', ')), count(title) as count
from netflix 
	where country not like '%United States%'
	and (listed_in like '%Fantasy%' or listed_in like '%Sci-fi%')
group by country  
order by count desc
offset 10
limit 5

## exercício 14

select count(title) 
from netflix 
	where country like '%Brazil%'
	and not (listed_in like '%Comed%' or listed_in like '%Action%')

select count(title) 
from netflix 
	where country like '%Brazil%'

## exercício 15

select country, count(title) as count
from netflix 
	where listed_in like '%Music & Musicals%'
	and country not like '%India%'
	and country not like '%United States%'
	and country not like '%United Kingdom%'
group by listed_in, country  
order by count desc
limit 5