---
# System prepended metadata

title: exercícios aula back-end 21/07

---

# exercícios aula back-end 21/07

## exercício 01

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

## exercício 02 

``` sql
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 

``` sql
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

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

## exercício 05 

``` sql
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

``` sql
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 

``` sql
select title, casting
from netflix 
	where casting like 'Antonio Banderas%'
```
    
## exercício 08

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


## exercício 09

``` sql
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

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

## exercício 11

``` sql
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

``` sql
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

``` sql
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

``` sql
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

``` sql
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
```