Repositório do projeto One For All

Sumário

• Repositório do projeto One For All
• Sumário
• Habilidades
  • O que foi desenvolvido
  • Desenvolvimento
  • Problema a ser resolvido
  • Normalização das tabelas para a 3ª Forma Normal
• Projeto:
  • Desafio 1
  • Desafio 2
  • Desafio 3
  • Desafio 4
  • Desafio 5
  • Desafio 6
  • Desafio 7
  • Desafio 8
  • Desafio 9
  • Desafio 10
  • Desafio 11

---

Habilidades

• Modelar um banco de dados

• Identificar entidades, atributos e relacionamentos

• Aplicar a 1ª, 2ª e 3ª Forma Normal

• Criar tabelas

---

O que foi desenvolvido

Hoje, no projeto One For All, você usará uma tabela para revisar e consolidar todos os principais conceitos vistos até o momento.

Utilizou-se como base do projeto uma tabela não normalizada, a ser normalizada e populada, para executar queries sobre ela com o intuito de encontrar as informações solicitadas.

Desenvolvimento

Normalização de uma tabela na terceira forma normal e acesse as informações desta tabela através de queries utilizando SQL.

---

Problema a ser resolvido

Tabela não normalizada semelhante ao que poderia ser uma versão simplificada do banco de dados do Spotify. O trabalho de duas partes:

1. Normalização da tabela, criar o schema no seu banco de dados local e populá-lo;

2. Realizar os desafios no banco normalizado e populado.

Dados da tabela abaixo são fictícios e foram gerados aleatoriamente.

---

Normalização das tabelas para a 3ª Forma Normal

Banco de dados com as seguintes regras de negócio e ser capaz de recuperar:

• Informações sobre quais planos estão disponíveis e seus detalhes;

  • Cada pessoa usuária pode possuir apenas um plano.

• Informações sobre todas as pessoas artistas;

  • Uma pessoa artista pode ter vários álbuns;
  • Uma pessoa artista pode ser seguida por várias pessoas usuárias.

• Informações sobre todos os álbuns de cada artista;

  • Para fins deste projeto, considere que cada álbum possui apenas uma pessoa artista como principal;
  • Cada álbum possui várias canções.

• Informações sobre todas as canções de cada álbum;

  • Para fins deste projeto, considere que cada canção está contida em apenas um álbum.

• Informações sobre todas as pessoas usuárias, seus planos, seu histórico de reprodução e pessoas artistas seguidas.

  • Uma pessoa usuária pode possuir apenas um plano;
  • Cada música do histórico de reprodução pode aparecer uma única vez por pessoa (para simplificar, considere que o objetivo do histórico é saber quais canções já foram reproduzidas e não quantas vezes foram reproduzidas);
  • Uma pessoa usuária pode seguir várias pessoas artistas, mas cada pessoa artista pode ser seguida apenas uma vez por pessoa usuária.

---

Projeto:

⚠️**AVISO IMPORTANTE:**⚠️ Você deve escrever sua query manualmente, com o gerador automático de queries do Workbench o avaliador não funcionará corretamente!

Desafio 1

• Criar um banco com o nome de SpotifyClone.

• Providenciar as queries necessárias para criar tabelas normalizadas que atendam aos requisitos descritos na seção anterior;

• Providenciar as queries necessárias para popular as tabelas de acordo com os dados listados na seção anterior;

• Criar um arquivo de configurações desafio1.json, que mapeará em qual tabela e coluna se encontram as informações necessárias para a avaliação automatizada deste desafio.

---

Desafio 2

Crie uma QUERY que exiba três colunas:

1. A primeira coluna deve exibir a quantidade total de canções. Dê a essa coluna o alias "cancoes".

2. A segunda coluna deve exibir a quantidade total de artistas e deverá ter o alias "artistas".

3. A terceira coluna deve exibir a quantidade de álbuns e deverá ter o alias "albuns".

---

Desafio 3

Crie uma QUERY que deverá ter apenas três colunas:

1. A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.

2. A segunda coluna deve possuir o alias "qtde_musicas_ouvidas" e exibir a quantidade de músicas ouvida pela pessoa com base no seu histórico de reprodução.

3. A terceira coluna deve possuir o alias "total_minutos" e exibir a soma dos minutos ouvidos pela pessoa usuária com base no seu histórico de reprodução.

Resultados agrupados pelo nome da pessoa usuária e ordenados em ordem alfabética.

---

Desafio 4

Crie uma QUERY que deve mostrar as pessoas usuárias que estavam ativas no ano de 2021 se baseando na data mais recente no histórico de reprodução.

1. A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.

2. A segunda coluna deve ter o alias "condicao_usuario" e exibir se a pessoa usuária está ativa ou inativa.

Resultado ordenado em ordem alfabética.

---

Desafio 5

Estamos fazendo um estudo das músicas mais tocadas e precisamos saber quais são as duas músicas mais tocadas no momento. Crie uma QUERY que possua duas colunas:

1. A primeira coluna deve possuir o alias "cancao" e exibir o nome da canção.

2. A segunda coluna deve possuir o alias "reproducoes" e exibir a quantidade de pessoas que já escutaram a canção em questão.

Resultado ordenado em ordem decrescente, baseando-se no número de reproduções. Em caso de empate, ordenar os resultados pelo nome da canção em ordem alfabética. Recuperar apenas o top 2 de músicas mais tocadas.

---

Desafio 6

Tendo como base o valor dos planos e o plano que cada pessoa usuária cadastrada possui no banco, queremos algumas informações sobre o faturamento da empresa.

Criar uma QUERY que deve exibir quatro dados:

1. A primeira coluna deve ter o alias "faturamento_minimo" e exibir o menor valor de plano existente para uma pessoa usuária.

2. A segunda coluna deve ter o alias "faturamento_maximo" e exibir o maior valor de plano existente para uma pessoa usuária.

3. A terceira coluna deve ter o alias "faturamento_medio" e exibir o valor médio dos planos possuídos por pessoas usuárias até o momento.

4. Por fim, a quarta coluna deve ter o alias "faturamento_total" e exibir o valor total obtido com os planos possuídos por pessuas usuárias.

Para cada um desses dados, por se tratarem de valores monetários, deve-se arredondar o faturamento usando apenas duas casas decimais.

---

Desafio 7

Mostrar uma relação de todos os álbuns produzidos por cada pessoa artista, com a quantidade de seguidores que ela possui, de acordo com os detalhes a seguir. Para tal, criar uma QUERY com as seguintes colunas:

1. A primeira coluna deve exibir o nome da pessoa artista, com o alias "artista".

2. A segunda coluna deve exibir o nome do álbum, com o alias "album".

3. A terceira coluna deve exibir a quantidade de pessoas seguidoras que aquela pessoa artista possui e deve possuir o alias "seguidores".

Resultados ordenados de forma decrescente, baseando-se no número de pessoas seguidoras. Em caso de empate no número de pessoas, ordenar os resultados pelo nome da pessoa artista em ordem alfabética e caso há artistas com o mesmo nome, ordenar os resultados pelo nome do álbum alfabeticamente.

---

Desafio 8

Mostrar uma relação dos álbuns produzidos por um artista específico, neste caso "Walter Phoenix". Criar uma QUERY que o retorno deve exibir as seguintes colunas:

1. O nome da pessoa artista, com o alias "artista".

2. O nome do álbum, com o alias "album".

Resultados ordenados pelo nome do álbum em ordem alfabética.

---

Desafio 9

Criar uma QUERY que exibe a quantidade de músicas que estão presentes atualmente no histórico de reprodução de uma pessoa usuária específica. Para este caso queremos saber quantas músicas estão no histórico do usuário "Bill" e a consulta deve retornar a seguinte coluna:

1. O valor da quantidade, com o alias "quantidade_musicas_no_historico".

---

Desafio 10

Criar uma QUERY que exiba o nome e a quantidade de vezes que cada canção foi tocada por pessoas usuárias do plano gratuito ou pessoal, de acordo com os detalhes a seguir:

• A primeira coluna deve exibir o nome da canção, com o alias "nome";

• A segunda coluna deve exibir a quantidade de pessoas que já escutaram aquela canção, com o alias "reproducoes";

• Resultados agrupados pelo nome da canção e ordenados em ordem alfabética.

---

Desafio 11

Criar uma QUERY que altere o nome de algumas músicas e as ordene em ordem alfabética com as colunas abaixo se baseando nos seguintes critérios:

1. O nome da música em seu estado normal com o alias nome_musica

2. O nome da música atualizado com o alias novo_nome

Critérios

• Trocar a palavra "Streets" no final do nome de uma música por "Code Review"

• Trocar a palavra "Her Own" no final do nome de uma música por "Trybe"

• Trocar a palavra "Inner Fire" no final do nome de uma música por "Project"

• Trocar a palavra "Silly" no final do nome de uma música por "Nice"

• Trocar a palavra "Circus" no final do nome de uma música por "Pull Request"

---