---
# System prepended metadata

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

---

# exercícios back-end aula 28/07 

## exercício 0.A

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

## exercício 0.B

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

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

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

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

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

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

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

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

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

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

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

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

## exercício 3.B

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


## exercício 3.C

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

## exercício 3.D

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

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

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

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

## exercício 4.B

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

## exercício 4.C

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

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

