MySQL Database One for All
Projeto realizado utilizando MySQL, aplicando as 1ª, 2ª e 3ª formas normais e identificando as entidades, atributos e relacionamentos.
A proposta do projeto era consolidar todos os conhecimentos de SQL criando um banco de dados a partir de uma tabela despadronizada no Excel (arquivo SpotifyClone-Non-NormalizedTable.xlsx) que simulava dados de um serviço de streaming de músicas. Também foram realizadas queries para consulta e a criação de VIEWS, TRIGGERS e UPDATES.
Lista de Requisitos
Os requisitos estão armazenados na raiz do projeto nos arquivos denominados DesafioN. Segue abaixo a lista do que foi proposto em cada um:
Desafio 1
-
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;
Desafio 2
Crie uma VIEW chamada estatisticas_musicais 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".
Desafio 3
Crie uma VIEW chamada historico_reproducao_usuarios. Essa VIEW deverá ter apenas duas colunas:
-
A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.
-
A segunda coluna deve possuir o alias "nome" e exibir o nome da canção ouvida pela pessoa com base no seu histórico de reprodução.
Os resultados devem estar ordenados por nome da pessoa usuária em ordem alfabética e em caso de empate no nome os resultados devem ser ordenados pelo nome da canção em ordem alfabética.
Desafio 4
Crie uma VIEW com o nome top_3_artistas que deve mostrar somente as três pessoas artistas mais populares no banco SpotifyClone, possuindo as seguintes colunas:
-
A primeira coluna deve possuir o alias "artista" e exibir o nome da pessoa artista.
-
A segunda coluna deve ter o alias "seguidores" e exibir a quantidade de pessoas que estão seguindo aquela pessoa artista.
Seu resultado deve estar ordenado em ordem decrescente, baseando-se na quantidade de seguidores. Em caso de empate, ordene os resultados pelo nome da pessoa artista 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 VIEW chamada top_2_hits_do_momento 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.
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. Crie uma VIEW chamada faturamento_atual 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 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
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 VIEW chamada perfil_artistas, 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.
Desafio 8
Crie uma trigger chamada trigger_usuario_delete que deve ser disparada sempre que uma pessoa usuária for excluída do banco de dados, refletindo essa exclusão em todas as tabelas que ela estiver.
Teste a funcionalidade correta de sua trigger, fazendo a exclusão da usuária "Thati".
Desafio 9
Crie uma procedure chamada albuns_do_artista que recebe como parâmetro o nome de uma pessoa artista e em 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.
Desafio 10
Crie uma function chamada de quantidade_musicas_no_historico que exibe a quantidade de músicas que estão presentes atualmente no histórico de reprodução de uma pessoa usuária. Ao receber o código identificador da pessoa, exiba a quantidade de canções em seu histórico de reprodução.
Desafio 11
Crie uma VIEW chamada cancoes_premium que exiba o nome e a quantidade de vezes que cada canção foi tocada por pessoas usuárias do plano familiar ou universitário, 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.