Terceiro projeto de Back End fornecido pela instituição Trybe, onde utilizamos queries para manipular informações, junção de tabelas no banco de dados e Normalização de tabelas.

Aprendi nesse projeto a utilizar o MySql ralizando queries e manipulando informações e unindo tabelas, além de Normalização de tabelas.

Back-End: MySql.

Instalação com npm

  git clone [email protected]:GuilhermeFMP/Trybe-MySQL-One-For-All.git
  cd Trybe-MySQL-One-For-All
  npm install

• @guilherme.kuro

Arquivos como .trybe e trybe.yml

Trybe

Você recebeu uma planilha não normalizada semelhante ao que poderia ser uma versão simplificada do banco de dados do Spotify. O trabalho consistirá de duas partes:

1. Normalizar essa planilha, criar as tabelas no seu banco de dados local e populá-lo;

2. Realizar os desafios no seu banco normalizado e populado.

A planilha acima foi normalizada? Ótimo! Agora siga com os passos abaixo:

• Crie um banco com o nome de SpotifyClone.

• Providencie as queries necessárias para criar tabelas normalizadas que atendam às regras de negócio descritas anteriormente;

• Providencie as queries necessárias para popular as tabelas do banco de dados de acordo com as dados da planilha fornecida anteriormente;

• Ajuste o 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.

  👇 As configurações devem possuir o seguinte formato

  
  

  {
    "tabela_pessoas_usuarias": "nome-da-tabela-de-pessoas-usuárias",
    "coluna_pessoas_usuarias": "nome-da-coluna-da-pessoa-usuária",
    "tabela_plano": "nome-da-tabela-do-plano",
    "coluna_plano": "nome-da-coluna-do-plano",
    "tabela_historico_de_reproducoes": "nome-da-tabela-do-histórico-de-reproduções",
    "coluna_historico_de_reproducoes": "nome-da-coluna-da-canção",
    "tabela_seguindo_artistas": "nome-da-tabela-de-seguidores-dos-artistas",
    "coluna_seguindo_artistas": "nome-da-coluna-da-artista",
    "tabela_artista": "nome-da-tabela-de-artistas",
    "coluna_artista": "nome-da-coluna-da-pessoa-artista",
    "tabela_album": "nome-da-tabela-de-álbuns",
    "coluna_album": "nome-da--coluna-do-álbum",
    "tabela_cancoes": "nome-da-tabela-de-canções",
    "coluna_cancoes": "nome-da-coluna-da-canção"
  }

  Essa configuração deve ser feita baseada no seu banco de dados após a normalização. Ou seja, se você criou uma tabela chamada users que possui a coluna name, você substituiria "coluna_pessoa_usuaria" e "tabela_pessoa_usuaria" da seguinte forma:

  {
    "tabela_pessoa_usuaria": "users",
    "coluna_pessoa_usuaria": "name",
    ...
  }

• Salve as queries criadas no arquivo desafio1.sql.

  👇 Seu código deverá ser similar ao seguinte

  
  

  DROP DATABASE IF EXISTS SpotifyClone;
  
  CREATE DATABASE SpotifyClone;
  
  CREATE TABLE SpotifyClone.tabela1(
      coluna1 tipo restricoes,
      coluna2 tipo restricoes,
      colunaN tipo restricoes,
  ) engine = InnoDB;
  
  CREATE TABLE SpotifyClone.tabela2(
      coluna1 tipo restricoes,
      coluna2 tipo restricoes,
      colunaN tipo restricoes,
  ) engine = InnoDB;
  
  INSERT INTO SpotifyClone.tabela1 (coluna1, coluna2)
  VALUES
    ('exemplo de dados 1', 'exemplo de dados A'),
    ('exemplo de dados 2', 'exemplo de dados B'),
    ('exemplo de dados 3', 'exemplo de dados C');
  
  INSERT INTO SpotifyClone.tabela2 (coluna1, coluna2)
  VALUES
    ('exemplo de dados 1', 'exemplo de dados X'),
    ('exemplo de dados 2', 'exemplo de dados Y');

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".

✨ Dica: Considerando que a tabela está normalizada, você pode usar as chaves estrangeiras para juntar as tabelas, ou usar sub-queries de forma que seja possível extrair o resultado

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

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

2. A segunda coluna deve possuir o alias "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.

Os resultados devem estar agrupados pelo nome da pessoa usuária e ordenados em ordem alfabética.

✨ Dica: Consulte o conteúdo do dia de Funções mais usadas no SQL.

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

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

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

O resultado deve estar ordenado em ordem alfabética.

✨ Dica: Consulte o conteúdo do dia de Funções mais usadas no SQL.

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.

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

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. Crie 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 pessoas usuárias.

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

✨ Dica: O ROUND pode ser útil aqui, mas não vai criar casas decimais extras, se precisar disto busque uma função que vai fazer essa formatação.

Mostre uma relação de todos os álbuns produzidos por cada pessoa artista, ordenada pela quantidade de seguidores que ela possui, de acordo com os detalhes a seguir. Para tal, crie 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 "pessoas_seguidoras".

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

Mostre uma relação dos álbuns produzidos por uma pessoa artista específica, neste caso "Elis Regina". Para isto crie 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".

Os resultados devem ser ordenados pelo nome do álbum em ordem alfabética.

Crie 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 da usuária "Barbara Liskov" e a consulta deve retornar a seguinte coluna:

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

Você receberá uma planilha não normalizada que complementa o banco de dados do SpotifyClone, que é uma tabela de canções favoritas de cada pessoa usuária. O trabalho consistirá de duas partes:

1. Normalizar essa planilha, criar a tabela no seu banco de dados local e populá-lo;

2. Realizar os próximos desafios no seu banco atualizado com a tabela de canções favoritas.

💡 Dica: Se precisar reveja as dicas do desafio 1, elas podem ser muito úteis! 😉

• Providencie as queries necessárias para criar a tabela normalizada que atenda às regras de negócio descritas anteriormente;

• Providencie as queries necessárias para popular a tabela no banco de dados de acordo com as dados da planilha fornecida anteriormente;

• Ajuste o arquivo de configurações desafio10.json, que mapeará a nova tabela para a avaliação automatizada deste desafio.

  👇 As configurações devem possuir o seguinte formato

  
  

  {
    "tabela_cancoes_favoritas": "nome-da-tabela-de-canções-favoritas",
    "coluna_cancoes_favoritas": "nome-da-coluna-de-canções-favoritas", 
  }

  Essa configuração deve ser feita baseada na sua nova tabela após a normalização.

• Salve as queries criadas no arquivo desafio10.sql

  👇 Seu código deverá ser similar ao seguinte

  
  

  CREATE TABLE SpotifyClone.tabela1(
      coluna1 tipo restricoes,
      coluna2 tipo restricoes,
      colunaN tipo restricoes,
  ) engine = InnoDB;
  
  INSERT INTO SpotifyClone.tabela1 (coluna1, coluna2)
  VALUES
    ('exemplo de dados 1', 'exemplo de dados A'),
    ('exemplo de dados 2', 'exemplo de dados B'),
    ('exemplo de dados 3', 'exemplo de dados C');

Crie uma QUERY que exibe o top 3 de álbuns com as músicas que mais foram favoritadas.

O resultado deve possuir duas colunas:

1. album: O nome do álbum

2. favoritadas: Quantas vezes as músicas do álbum foram favoritadas

Seu resultado deve estar ordenado em ordem decrescente, baseando-se no número de favoritadas. Em caso de empate, ordene os resultados pelo nome do álbum em ordem alfabética. Queremos apenas o top 3 de álbuns com mais músicas favoritadas.

Crie uma QUERY que exibe um ranking de artistas baseado na quantidade de favoritadas em suas músicas.

O resultado deve possuir duas colunas:

1. artista: O nome da pessoa artista

2. ranking: Uma classificação definida pela quantidade de favoritadas as canções da pessoa artista receberam, como segue:

   ranking	quantidade de favoritadas em canções
   A	5 ou mais
   B	3 - 4
   C	1 - 2
   -	0

Seu resultado deve estar ordenado em ordem decrescente, baseando-se no número de favoritadas. Em caso de empate, ordene os resultados pelo nome da pessoa artista em ordem alfabética.

Crie uma QUERY que exibe uma relação da quantidade total de pessoas usuárias e favoritadas por faixa etária.

O resultado deve possuir três colunas:

1. faixa_etaria: A faixa etária das pessoas usuárias, sendo elas:

   • Até 30 anos
   • Entre 31 e 60 anos
   • Maior de 60 anos

2. total_pessoas_usuarias: O total de pessoas usuárias na respectiva faixa etária

3. total_favoritadas: O total de favoritadas realizadas pelas pessoas usuárias da respectiva faixa etária

Seu resultado deve estar ordenado de acordo com a sequência das faixas etárias descritas acima.