# desafio back-end 04/08
## grupo 01 - contagem, agrupamentos e comparações
### exercício 01
``` sql
select house, count(*)
from harry_potter hp
where blood_status = 'Half-blood' or blood_status = 'Muggle-born'
group by house
order by count desc;
```
### exercício 02
``` sql
select house, count(*)
from harry_potter hp
where blood_status = 'Pure-blood'
group by house
order by count asc
limit 1;
```
### exercício 03
``` sql
select count(*)
from harry_potter hp
where name like '%Weasley%';
```
### exercício 04
``` sql
select count(*)
from harry_potter hp
where name like '%Weasley%' and hair_colour <> 'Red';
```
### exercício 05
``` sql
select patronus, count(*)
from harry_potter hp
where patronus <> 'Unknown' and patronus <> 'Non-corporeal' and patronus <> 'None'
group by patronus
order by count desc
limit 3;
```
### exercício 06
``` sql
select split_part(name, ' ', 2) as sobrenome, name, species
from harry_potter hp
where (name like '%Slytherin%' or name like '%Gryffindor%' or name like '%Ravenclaw%' or name like '%Hufflepuff%')
and species <> 'Ghost';
```
### exercício 07
``` sql
select name
from harry_potter hp
where loyalty like '%Lord Voldemort%' and house <> 'Slytherin';
```
## grupo 02 - lógica
### exercício 01
``` sql
select name, loyalty, house
from harry_potter hp
where death like '%2 May%'
and (loyalty like '%Dumbledore''s Army%' or loyalty like '%lbus Dumbledore%'
or loyalty like '%Order of Phoenix%' or loyalty like '%Lord Voldemort%' or loyalty like '%Death Eaters%')
or name = 'Tom Marvolo Riddle';
```
### exercício 02
``` sql
select name
from harry_potter hp
where gender = 'Male' and (loyalty like '%Albus Dumbledore%' or loyalty like '%Order of the Phoenix%' or loyalty like '%Dumbledore''s Army%')
and birth like '%July%' and (birth like '%31%' or birth like '%29%' or birth like '%30%');
```
### exercício 03
``` sql
select distinct job, count(*)
from harry_potter hp
group by job
order by count desc;
```
### exercício 04
``` sql
select name
from harry_potter hp
where name not in (select name
from harry_potter hp
where loyalty like '%Lord Voldemort%' and house <> 'Slytherin')
and birth like '%September%'
and death is null
and loyalty is not null
and patronus <> 'Non-corporeal' and patronus <> 'Unknown'
and house is not null
and gender = 'Female';
```
## grupo 03 - subqueries e join
## exercício 01.a
``` sql
select unnest(string_to_array(name, ' '))
from harry_potter hp
```
## exercício 01.b
``` sql
select distinct unnest(string_to_array(name, ' ')), count(*)
from harry_potter hp
group by unnest
```
## exercício 01.c
``` sql
select distinct unnest(string_to_array(name, ' ')), count(*)
from harry_potter hp
group by unnest
order by count desc
```
## exercício 01.d
``` sql
select *
from
(select distinct unnest(string_to_array(name, ' ')) as nomes, count(*) as quantidade
from harry_potter hp
group by nomes
order by quantidade desc) as dados
where dados.quantidade >= 4
```
## exercício 01.e
``` sql
select name, unnest, house
from (select unnest(string_to_array(name, ' ') ), name, house
from harry_potter hp) as primeira
inner join
(select *
from
(select distinct unnest(string_to_array(name, ' ')) as nomes, count(*) as quantidade
from harry_potter hp
group by nomes
order by quantidade desc) as dados
where dados.quantidade >= 4) as familias
on primeira.unnest = familias.nomes
```
## exercício 01.f
``` sql
select name, unnest, house
from (select unnest(string_to_array(name, ' ') ), name, house
from harry_potter hp
where house is not null
) as primeira
inner join
(select *
from
(select distinct unnest(string_to_array(name, ' ')) as nomes, count(*) as quantidade
from harry_potter hp
group by nomes
order by quantidade desc) as dados
where dados.quantidade >= 4) as familias
on primeira.unnest = familias.nomes
```
## exercício 01.g
``` sql
select house, count(*)
from (select name, unnest, house
from (select unnest(string_to_array(name, ' ') ), name, house
from harry_potter hp
where house is not null
) as primeira
inner join
(select *
from
(select distinct unnest(string_to_array(name, ' ')) as nomes, count(*) as quantidade
from harry_potter hp
group by nomes
order by quantidade desc) as dados
where dados.quantidade >= 4) as familias
on primeira.unnest = familias.nomes) as dados
group by house
order by count
```
## exercício 02.a
``` sql
select *
from (select unnest(string_to_array(skills, ' | ')) as habilidades
from harry_potter hp) as tudo
where tudo.habilidades = 'Keeper' or tudo.habilidades = 'Beater' or tudo.habilidades = 'Seeker' or tudo.habilidades = 'Chaser'
```
## exercício 02.b
``` sql
select *, count(*)
from (select unnest(string_to_array(skills, ' | ')) as habilidades
from harry_potter hp) as tudo
where tudo.habilidades = 'Keeper' or tudo.habilidades = 'Beater' or tudo.habilidades = 'Seeker' or tudo.habilidades = 'Chaser'
group by tudo.habilidades
```
## exercício 02.c
``` sql
select *, count(*)
from (select unnest(string_to_array(skills, ' | ')) as habilidades
from harry_potter hp) as tudo
where tudo.habilidades = 'Keeper' or tudo.habilidades = 'Beater' or tudo.habilidades = 'Seeker' or tudo.habilidades = 'Chaser'
group by tudo.habilidades
order by count asc
limit 1
```
## exercício 02.d
``` sql
select name, unnest
from (select unnest(string_to_array(skills, ' | ') ), name
from harry_potter hp) as primeira
inner join
(select goleiro.habilidades
from (select *, count(*)
from (select unnest(string_to_array(skills, ' | ')) as habilidades
from harry_potter hp) as tudo
where tudo.habilidades = 'Keeper' or tudo.habilidades = 'Beater' or tudo.habilidades = 'Seeker' or tudo.habilidades = 'Chaser'
group by tudo.habilidades
order by count asc
limit 1) as goleiro) as segunda
on primeira.unnest = segunda.habilidades;
```
## exercício 03
``` sql
select name, patronus
from harry_potter hp
inner join
(select *
from (select patronus as animais, count(*)
from harry_potter hp
where patronus <> 'Unknown' and patronus <> 'None' and patronus <> 'Non-corporeal'
group by patronus
order by count desc) as pats
where pats.count > 1) as pat2
on hp.patronus = pat2.animais
order by patronus asc
```
## exercício 04.a
``` sql
select unnest(string_to_array(loyalty, ' | '))
from harry_potter hp
```
## exercício 04.b
``` sql
select unnest(string_to_array(loyalty, ' | ')), count(*)
from harry_potter hp
group by unnest
order by count desc
```
## exercício 04.c
``` sql
select *
from (select unnest(string_to_array(loyalty, ' | ')), count(*)
from harry_potter hp
group by unnest
order by count desc) as loyalties
where loyalties.count = 4 and loyalties.unnest <> 'Minister of Magic'
```
## exercício 04.d
``` sql
select name
from (select distinct unnest(string_to_array(loyalty, ' | ')), name
from harry_potter) as primeira
where primeira.unnest in (select lealdades
from (select unnest(string_to_array(loyalty, ' | ')) as lealdades, count(*)
from harry_potter hp
group by lealdades
order by count desc) as loyalties
where loyalties.count = 4 and loyalties.lealdades <> 'Minister of Magic')
```
Sign in with Wallet
Connect another wallet