# **Exercício Back-End: Aula 07 e 08 (22/07/2020)**

###### tags: `SQL` `Backend` `Cubos Academy` `Aula7e8`

## Classe

**1 QUESTÃO**

SELECT listed_in, 
       Count(*) 
FROM   netflix 
GROUP  BY listed_in 
ORDER  BY Count(*) DESC 

**2 QUESTÃO**

select listed_in, AVG(split_part(duration, ' ', 1)::INT)
from netflix 
group by listed_in 
order by AVG(split_part(duration, ' ', 1)::INT) desc
limit 5
;

**3 QUESTÃO**

    select release_year, listed_in, count(*)
from netflix 
where type <> 'Movie'
group by release_year, listed_in
order by count(*) desc
limit 5;

**4 QUESTÃO**

    SELECT count(*) 
FROM   netflix 
WHERE  date_part('year', date_added) <> release_year ;

**5 QUESTÃO**

    select 
	(string_to_array(country, ' ')) as pais,
	"type",
	count(*)
from netflix 
where "type" = 'TV Show'
and country <> 'United States'
group by pais , "type" 
order by count(*) desc
limit 5;

**6 QUESTÃO**

    select 
	listed_in,
	rating,
	count(*)
from netflix
where rating = 'R'
group by listed_in, rating  
order by count(*) desc
limit 5;

**7 QUESTÃO**

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

**8 QUESTÃO**

    
select 
	unnest(string_to_array(casting, ', '))  as actors
from netflix 
limit 5;

**9 QUESTÃO**

    select 
	show_id,
	"type",
	title 
from netflix 
where (title like '%Transformers%' or title like '%Star Wars%' or title like '%Harry Potter%'or title like '%Lord of the Rings%')

**10 QUESTÃO**

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

**11 QUESTÃO**

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

**12 QUESTÃO**

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

**13 QUESTÃO**

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

**14 QUESTÃO**

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


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

**15 QUESTÃO**

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

## Casa

**1 QUESTÃO**

select distinct 
	allegiances
from personagens_mortes
order by allegiances asc 
limit 5;

**2 QUESTÃO**

select  
	count(*)
from personagens_mortes
where nobility = false ;

**3 QUESTÃO**

select  
	gender,
	count(*)
from personagens_mortes
group by gender   

**4 QUESTÃO**

select  
	allegiances, 
	count(*)
from personagens_mortes
where allegiances <> 'None'
group by allegiances 
order by count(*) desc

**5 QUESTÃO**

select  
	battle_type, 
	count(*)
from batalhas
where attacker_size < defender_size
and attacker_outcome = 'win'
group by battle_type
order by count(*) desc;

**6 QUESTÃO**

select 
	attacker_outcome,
	count(*)
from batalhas
where attacker_king like '%Stark%' or attacker_commander like '%Stark%'
group by attacker_outcome ;

Resultado: 
Starks Venceram 8 e perderam 2

**7 QUESTÃO**

select 
	attacker_king,
	count(*)
from batalhas
where defender_king like '%Stark%' 
group by attacker_king 
order by count desc;