CUBOS ACADEMY

Semana de Desafio - Módulo 1 - Grupos 1, 2 e 3


GRUPO 1

1 Quantos bruxos nascidos-trouxa (Muggle-born) ou mestiços (Half-blood) existem em cada casa (house)?
Ordene-os decrescentemente e limite a busca em quatro (4) registros.
Observe que não temos certeza sobre blood_status de alguns bruxos. 
Mas, nós estamos buscando aqui nessa questão pelos bruxos que são Muggle-born, Half-blood ou Muggle-born or Half-blood.

select house, count(*)
from harry_potter
where blood_status is not null
and blood_status ='Half-blood'
or blood_status = 'Muggle-born'
group by house
order by count(*) desc
limit 4
;```

2 Qual é a casa que tem a menor quantidade de sangues-puro (Pure-blood), assumindo apenas aqueles que têm-se certeza?
Remova, também, aqueles casos que a casa (House) seja nula e o (Blood Status) seja nula.

select house, count(*)
from harry_potter
where blood_status is not null
and house is not null
and blood_status ='Pure-blood'
group by house
order by count(*)
limit 1
;

3 Quantas pessoas existem na família Weasley (assuma pessoas com algum nome de Weasley)?

select count(*)
from harry_potter
where name like '%Weasley'
;

4 Existe alguém na família Weasley que não seja ruivo (Red)?

select count(*)
from harry_potter
where name like '%Weasley'
and hair_colour <> 'Red'
;

5 Todo bruxo consegue aprender o feitiço do patrono, um feitiço de proteção que toma forma de um animal.
É um feitiço complexo e nem todos os bruxos possuem um patrono. Existem pessoas que possuem o mesmo animal patrono (patronus)?
Remova os casos em que os valores sejam Unknown, Non-corporeal ou None(do inglês, desconhecido) e ordene-os pela
quantidade decrescentemente, limitando nos 3 maiores resultados.

select patronus, count(*)
from harry_potter
where (patronus <> 'Unknown'
and patronus <> 'Non-corporeal'
and patronus <> 'None')
group by patronus
order by count(*) desc
limit 3
;

6 Utilizando apenas uma consulta, responda: busque pelos quatro bruxas ou bruxos que possuem nomes
que deram origem ao nome das casas de Hogwarts (House). Remova aqueles que sejam da espécie fantasma (Ghost).
A resposta deve conter o sobrenome, o nome e a espécie.

select *
from (select split_part(name, ' ', 2) as sobrenome, name, species
from harry_potter
where species <> 'Ghost'
and birth is not null) as fundadores
where (sobrenome = 'Gryffindor'
or sobrenome = 'Ravenclaw'
or sobrenome = 'Slytherin'
or sobrenome ='Hufflepuff')
;

7 Existiam bruxos que não eram da casa (house) Sonserina (Slytherin) que eram leais ao Lord Voldemort?
Se existiram, qual são seus nomes e suas casas? Desconsidere aqueles que não estão associados a nenhuma casa.
A Resposta deve conter apenas o nome desses bruxos.

select name
from harry_potter
where house <> 'Slytherin'
and loyalty like '%Voldemort%'
;

GRUPO 2

1 A Batalha de Hogwarts foi uma intensa batalha em que vários

bruxos morreram.
Esta batalha ocorreu no dia 2 de maio (2 May) de 1998.
Busque pelas quantidade de mortos nesta batalha entre aqueles que lutavam por Albus Dumbledore
(Dumbledore's Army ou Albus Dumbledore) ou a Ordem da Fênix (Order of the Phoenix) versus aqueles que lutavam
a favor de Lord Voldemort ou eram comensais da morte (Death Eaters), inclui-se também o nome Tom Marvolo Riddle
que é o verdadeiro nome de Lord Voldemort. A resposta deve conter apenas os nomes, as casas e as lealdades.
Dica: a busca por Dumbledore's Army pode ser feita da seguinte forma Dumbledore''s Army,
ou seja com duas aspas simples ao pesquisar usando LIKE ou =.

select name, house, loyalty
from harry_potter
where death = '2 May, 1998'
and death is not null
order by house
;

2 A primeira profecia de Sibila Trelawney é uma famosa profecia que colocou em dúvida quem seria o herói que teria poder
para derrotar o grande vilão, Lord Voldemort. Provavelmente os bruxos não tinham um banco de dados tão interessante quanto
o nosso mas a profecia tinha alguns requisitos que curiosamente alguns destes podem ser validados pelo dataset, a profecia
dizia que um menino teria as seguintes características:

ser do gênero masculino.
seus pais tinham desafiado Voldemort três vezes e sobreviveram.
ele teria um poder que Voldemort era incapaz ou não queria compreender.
nascido nos últimos dias de julho de 1980, ano em que a profecia foi feita (possivelmente 29, 30 ou 31).
se mais de uma pessoa nascesse que satisfizer os requisitos, o próprio Voldemort iria escolher a quem acabaria por se referir.
Sua meta é encontrar nomes que poderiam satisfazer nossa vontade de descobrir quem poderia ser o herói.
Observação: Bom, como dizia o ditado: O inimigo do meu inimigo é meu amigo. Podemos assumir que aliados a
Albus Dumbledore, Albus ou a Order of the Phoenix (Ordem da Fênix) eram também inimigos de Voldermort. Então isso pode ser um bom indicativo para tentar identificar nosso(s) herói(s).

Com base nesses requisitos quem são os possíveis heróis? A resposta deve conter apenas o nome do bruxo.

select name
from harry_potter
where gender = 'Male'
and birth between '29 July, 1980' and '31, July, 1980'
and loyalty like '%Dumbledore%'
;

3 Busque por todos os trabalhos (job) e separe-os individualmente e por fim, ordene-os por quantidade descrecente,
além disso, filtre-os por aqueles que possuem acima de 2. Lembre-se um personagem pode ter zero ou mais trabalhos
e àqueles maiores que 1 são separados por | (barra vertical entre espaços); Cuidado: existe uma armadilha nessa pergunta:
na resposta deve existir apenas trabalhos distintos.

select *
from (select nome, count(*)
from (select unnest(string_to_array(job, ' | ')) as nome
from harry_potter) as trabalho
group by nome
order by count desc) as hp1
where count > 2
;

ou

select nome, count(*)
from (select unnest(string_to_array(job, ' | ')) as nome
from harry_potter) as trabalho
group by nome
order by count desc
limit 5
;

4 Busque por todos aqueles que entram nessas restrições:
Nasceram em Setembro September (birth);
Não faleceram (death);
Não possuem nomes iguais àqueles achados na questão 7 (utilize subquery);
Possuem lealdade associada a alguém (loyalty);
Que seu patrono não seja Unknown nem Non-corporeal;
Que possuam alguma casa (house);
Sejam do gênero feminino (gender);
A resposta deve conter os nomes das bruxas:

select name
from harry_potter
where name not in (select name
from harry_potter
where house <> 'Slytherin'
and loyalty like '%Voldemort%')
and birth like '%September%'
and death is null
and loyalty is not null
and house not like '%|%'
and gender = 'Female'
and patronus <> 'Unknown'
and patronus <> 'Non-corporeal'
;

ou

select name
from harry_potter
where birth like '%September%'
and death is null
and loyalty is not null
and house not like '%|%'
and gender = 'Female'
and patronus <> 'Unknown'
and patronus <> 'Non-corporeal'
;

GRUPO 3

1. Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas.

1.A. Busque por todos os nomes de bruxos e quebre as palavras a fim de encontrarmos cada palavra usada como nome;

Primeiros 5 registros (podem não ser os mesmos registros)

select name
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter
where gender = 'Male'
) as nome
limit 5
;

1.B. Agora, agrupe os nomes a fim de encontrar a quantidade de vezes que o certo nome é repetido.
(Lembre-se, existem pessoas com famílias, então espera-se repetição de nomes (que apareçam número maior do que 1));

select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter
) as nome
group by name
limit 5
;

1.C. Ordene-as palavras usadas como nome pela quantidade de repetições;

select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter
) as nome1
group by name
order by count desc
limit 5
;

1.D. Filtre por todos aqueles nomes que tiveram 4 ou mais repetições;

select * from (select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter
) as nome1
group by name
order by count desc
) as repeticoes
where count >= 4
;

1.E. Agora utilizando o recurso de JOIN, encontre todos os personagens que possuem nome que esteja
no grupo encontrado na questão anterior (1.D);

select tab1.name, tab1.nome, tab1.house
from (select unnest(string_to_array(name, ' ')) as nome, name, house
from harry_potter) tab1
inner join (select *
from (select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter) as nome1
group by name
) as repeticoes
where count >= 4
) tab2
on tab2.name = tab1.nome
;

1.F. Agora, filtre apenas por aqueles que estejam vinculados a casa de magia (house sejam IS NOT NULL);

select tab1.name, tab1.nome, tab1.house
from (select unnest(string_to_array(name, ' ')) as nome, name, house
from harry_potter) tab1
inner join (select *
from (select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter) as nome1
group by name
) as repeticoes
where count >= 4
) tab2
on tab2.name = tab1.nome
where house is not null
;

1.G. Agora, encontre a quantidade de pessoas por casa de magia (house).

select tab1.house, count(tab1.house)
from (select unnest(string_to_array(name, ' ')) as nome, name, house
from harry_potter) tab1
inner join (select *
from (select name, count(*)
from (select unnest(string_to_array(name, ' ')) as name
from harry_potter) as nome1
group by name
) as repeticoes
where count >= 4
) tab2
on tab2.name = tab1.nome
where tab1.house is not null
group by tab1.house
order by count
;

2. Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas.
O quadribol é um esporte bastante popular entre bruxos e existem várias posições muito parecidas com o Futebol ou Volei.
No Quadribol, existem o apanhador (Seeker), o artilheiro (Chaser), o batedor (Beater) e até mesmo o goleiro (Keeper);
A coluna de habilidades (skills) listam várias habilidades separadas por | (barra vertical entre espaços).
Responda as perguntas abaixo com uma única consulta podendo utilizar subqueries:

A. Entre todas as habilidades, quebre-as e busque por aquelas que falem sobre as posições do Quadribol citadas anteriormente no enunciado.

select *
from (select unnest(string_to_array(skills, ' | ')) as nome
from harry_potter
) as habilidade
where nome = 'Seeker'
or nome = 'Chaser'
or nome = 'Beater'
or nome = 'Keeper'
limit 5
;

B. Sabendo das informações da questão anterior (2.A), liste a quantidade de jogadores por posição;

select nome, count(*)
from (select unnest(string_to_array(skills, ' | ')) as nome
from harry_potter) as habilidade
where nome = 'Seeker'
or nome = 'Chaser'
or nome = 'Beater'
or nome = 'Keeper'
group by nome
order by nome
;

C. Sabendo das informações da questão anterior (2.B), ordene-os os registros encontrados e busque pela posição que possui MENOR quantidade de jogadores;

select nome, count(*)
from (select unnest(string_to_array(skills, ' | ')) as nome
from harry_potter) as habilidade
where nome = 'Seeker'
or nome = 'Chaser'
or nome = 'Beater'
or nome = 'Keeper'
group by nome
order by count(*)
limit 1
;

D. Sabendo das informações da questão anterior (2.C), busque por todos os jogadores que são da posição encontrada.

select jogador1.name, jogador2.nome
from (select name, unnest(string_to_array(skills, ' | ')) as nome1
from harry_potter) as jogador1
inner join (select nome, count(*)
from (select unnest(string_to_array(skills, ' | ')) as nome
from harry_potter) as habilidade
where nome = 'Seeker'
or nome = 'Chaser'
or nome = 'Beater'
or nome = 'Keeper'
group by nome
order by count(*)
limit 1) as jogador2
on jogador1.nome1 = jogador2.nome
;

3. Para aqueles bruxos possuam patrono e que esses patronos não sejam Unknown, None e Non-corporeal nem NULL,
busque pelos bruxos ou bruxas que possuem o mesmo animal. Por fim, ordene-os alfabeticamente e traga a informação de nome e o patrono.

select donos.name, animal.patronus
from (select name, patronus
from harry_potter
) as donos
inner join (select *
from (select patronus, count(*)
from harry_potter
where patronus is not null
and patronus <> 'None'
and patronus <> 'Non-corporeal'
and patronus <> 'Unknown'
group by patronus
order by count(*) desc
) as patronus
where count >= 2) as animal
on donos.patronus = animal.patronus
order by donos.patronus
;

4. Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas:

A. Busque por todas possibilidades individuais de lealdade (Loyalty). Dica: os valores estão listados separando cada valor por | (barra vertical entre espaços);

select unnest(string_to_array(loyalty, '|')) as nome
from harry_potter
limit 5
;

B. Após saber da informação (4.A), busque por estas mesmas lealdades e suas respectivas quantidades;

select nome, count(*) from (select unnest(string_to_array(loyalty, ' | ')) as nome
from harry_potter) as leal
group by nome
order by count(*) desc
;

C. Após saber da informação (4.B), busque por aquela lealdade que possui quantidade igual a 4 e que não seja igual ao ministro da magia (Minister of Magic).
select *
from (select nome, count(*) from (select unnest(string_to_array(loyalty, ' | ')) as nome
from harry_potter) as leal
group by nome
order by count(*) desc) as leal2
where nome <> 'Minister of Magic'
and count = 4
;

D. Após saber da informação (4.C), busque pelo nome das pessoas que possuem lealdade ao indíviduo ou organização encontrada na ©.

select name , hp2.nome
from(select name, unnest(string_to_array(loyalty, ' | ')) as nome
from harry_potter) as hp
inner join (select *
from (select nome, count(*) from (select unnest(string_to_array(loyalty, ' | ')) as nome
from harry_potter) as leal
group by nome
order by count(*) desc) as leal2
where nome <> 'Minister of Magic'
and count = 4
) as hp2
on hp.nome = hp2.nome
;