Este foi o segundo projeto o qual utilizamos o WorkBench o MySQL para criar, popular e estabelecer queries para mostrar os dados.
Primeiro tivemos que normalizar uma tabela fictícia que nos foi disponibilizada e, depois disso, estabeler a criação da mesma no WorkBench. Depois disso, criamos algumas queries para que dados específicos fossem mostrados.
-
Normalizar dados dispostos em uma tabela;
-
Criar e popular um banco de dados com o MySQL;
-
Manipular comandos SQL mais usados no dia a dia para selecionar e criar dados;
-
Fazer consultas mais refinadas, complexas e precisas ao banco de dados;
-
Manipular tabelas, fazendo inserções, alterações e exclusões.
🐳 Rodando no Docker vs Localmente
Rode os serviços
node
edb
com o comandodocker-compose up -d
.
- Lembre-se de parar o
mysql
se estiver usando localmente na porta padrão (3306
), ou adapte, caso queria fazer uso da aplicação em containers - Esses serviços irão inicializar um container chamado
one_for_all
e outro chamadoone_for_all_db
. - A partir daqui você pode rodar o container
one_for_all
via CLI ou abri-lo no VS Code.
Use o comando
docker exec -it one_for_all bash
.
- Ele te dará acesso ao terminal interativo do container criado pelo compose, que está rodando em segundo plano;
- As credencias de acesso ao banco de dados estão definidas no arquivo
docker-compose.yml
, e são acessíveis no container através das variáveis de ambienteMYSQL_USER
eMYSQL_PASSWORD
. 💡
Instale as dependências [Caso existam] com
npm install
⚠ Atenção ⚠ Caso opte por utilizar o Docker, TODOS os comandos disponíveis no package.json
(npm start, npm test, npm run dev, ...) devem ser executados DENTRO do container, ou seja, no terminal que aparece após a execução do comando docker exec
citado acima.
⚠ Atenção ⚠ O git dentro do container não vem configurado com suas credenciais. Ou faça os commits fora do container, ou configure as suas credenciais do git dentro do container.
⚠ Atenção ⚠ Não rode o comando npm audit fix! Ele atualiza várias dependências do projeto, e essa atualização gera conflitos com o avaliador.
docker-compose --version
Se não for a versão 1.29, faça os seguintes comandos para atualizar a versão:
sudo rm /usr/local/bin/docker-compose
sudo curl -L "https://github.com/docker/compose/releases/download/1.29.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
Instale as dependências [Caso existam] com
npm install
⚠ Atenção ⚠ Não rode o comando npm audit fix! Ele atualiza várias dependências do projeto, e essa atualização gera conflitos com o avaliador.
✨ Dica: Para rodar o projeto desta forma, obrigatoriamente você deve ter o node
instalado em seu computador.
✨ Dica: O avaliador espera que a versão do node
utilizada seja a 16.
-
Crie um banco com o nome de
SpotifyClone
. -
Providencie as queries necessárias para criar tabelas normalizadas que atendam aos requisitos descritos na seção anterior;
-
Providencie as queries necessárias para popular as tabelas de acordo com os dados listados na seção anterior;
-
Crie 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.
👇 As configurações devem possuir o seguinte formato
{
"coluna_usuario": "nome-da-coluna",
"tabela_que_contem_usuario": "nome-da-tabela-que-armazena-a-coluna",
"coluna_plano": "nome-da-coluna",
"tabela_que_contem_plano": "nome-da-tabela-que-armazena-a-coluna",
"coluna_historico_de_reproducoes": "nome-da-coluna",
"tabela_que_contem_historico_de_reproducoes": "nome-da-tabela-que-armazena-a-coluna",
"coluna_seguindo_artistas": "nome-da-coluna",
"tabela_que_contem_seguindo_artistas": "nome-da-tabela-que-armazena-a-coluna",
"coluna_artista": "nome-da-coluna",
"tabela_que_contem_artista": "nome-da-tabela-que-armazena-a-coluna",
"coluna_album": "nome-da-coluna",
"tabela_que_contem_album": "nome-da-tabela-que-armazena-a-coluna",
"coluna_cancoes": "nome-da-coluna",
"tabela_que_contem_cancoes": "nome-da-tabela-que-armazena-a-coluna"
}
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_usuario"
e "tabela_que_contem_usuario"
da seguinte forma:
{
"coluna_usuario": "name",
"tabela_que_contem_usuario": "users",
...
}
- 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');
☑️ O que será verificado
-
Será validado se os planos estão na tabela normalizada.
-
Será validado se o histórico de reprodução está na tabela normalizada.
-
Será validado se as informações sobre pessoas seguindo artistas estão na tabela normalizada.
-
Será validado se os álbuns estão na tabela normalizada.
-
Será validado se as canções estão na tabela normalizada.
-
Será validado se as informações sobre pessoas usuárias estão na tabela normalizada.
-
Será validado se as informações sobre artistas estão na tabela normalizada.
SpotifyClone
será dropada. Logo, se atente a salvar seu progresso nos arquivos de desafio!
Crie uma QUERY
que exiba três colunas:
-
A primeira coluna deve exibir a quantidade total de canções. Dê a essa coluna o alias "cancoes".
-
A segunda coluna deve exibir a quantidade total de artistas e deverá ter o alias "artistas".
-
A terceira coluna deve exibir a quantidade de álbuns e deverá ter o alias "albuns".
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se existe uma
QUERY
que exibe os dados corretos nas colunascancoes
,artistas
ealbuns
.
Crie uma QUERY
que deverá ter apenas três colunas:
-
A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.
-
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.
-
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.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasusuario
,qtde_musicas_ouvidas
etotal_minutos
. -
Será validado se as colunas estão ordenadas de forma correta.
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.
-
A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.
-
A segunda coluna deve ter o alias "condicao_usuario" e exibir se a pessoa usuária está ativa ou inativa.
O resultado deve estar ordenado em ordem alfabética.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasusuario
econdicao_usuario
. -
Será validado se as colunas estão ordenadas de forma correta.
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:
-
A primeira coluna deve possuir o alias "cancao" e exibir o nome da canção.
-
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.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunascancao
ereproducoes
. -
Será validado se as colunas estão ordenadas de forma correta.
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:
-
A primeira coluna deve ter o alias "faturamento_minimo" e exibir o menor valor de plano existente para uma pessoa usuária.
-
A segunda coluna deve ter o alias "faturamento_maximo" e exibir o maior valor de plano existente para uma pessoa usuária.
-
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.
-
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.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasfaturamento_minimo
,faturamento_maximo
,faturamento_medio
efaturamento_total
.
Mostre 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, crie uma QUERY
com as seguintes colunas:
-
A primeira coluna deve exibir o nome da pessoa artista, com o alias "artista".
-
A segunda coluna deve exibir o nome do álbum, com o alias "album".
-
A terceira coluna deve exibir a quantidade de pessoas seguidoras que aquela pessoa artista possui e deve possuir o alias "seguidores".
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 há artistas com o mesmo nome, ordene os resultados pelo nome do álbum alfabeticamente.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasartista
,album
eseguidores
. -
Será validado se as colunas estão ordenadas de forma correta.
Mostre uma relação dos álbuns produzidos por um artista específico, neste caso "Walter Phoenix"
.
Para isto crie uma QUERY
que o retorno deve exibir as seguintes colunas:
-
O nome da pessoa artista, com o alias "artista".
-
O nome do álbum, com o alias "album".
Os resultados devem ser ordenados pelo nome do álbum em ordem alfabética.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se a
QUERY
retorna a colunaartista
contendo o nome da pessoa artista e a colunaalbum
contendo o nome do álbum. -
Será validado se as colunas estão ordenadas de forma correta.
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 do usuário "Bill"
e a consulta deve retornar a seguinte coluna:
- O valor da quantidade, com o alias "quantidade_musicas_no_historico".
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se a
QUERY
retorna a quantidade correta de canções presentes no histórico de reprodução da pessoa especificada.
Crie 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";
-
Seus resultados devem estar agrupados pelo nome da canção e ordenados em ordem alfabética.
➕ Informações complementares
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasnome
ereproducoes
. -
Será validado se as colunas estão ordenadas de forma correta.
Crie 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:
-
O nome da música em seu estado normal com o alias nome_musica
-
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"