CS50-MCZ

Lab 7: Songs

Escreva consultas SQL para responder perguntas sobre um banco de dados de músicas.

Começando

Abra o VS Code.

Comece clicando dentro da janela do terminal e, em seguida, execute cd por si só. Você deve encontrar que seu "prompt" se assemelha ao abaixo.

$

Clique dentro dessa janela de terminal e execute

wget https://cdn.cs50.net/2022/fall/labs/7/songs.zip

digite Enter para baixar um arquivo ZIP chamado songs.zip em seu espaço de códigos. Tenha cuidado para não ignorar o espaço entre wget e a URL seguinte, ou qualquer outro caractere!

Agora execute

unzip songs.zip

para criar uma pasta chamada songs. Você não precisa mais do arquivo ZIP, então pode executar

rm songs.zip      

e responda com "y" seguido de Enter no prompt para remover o arquivo ZIP que você baixou.

Agora digite

cd songs

seguido de Enter para entrar (ou seja, abrir) nesse diretório. Seu prompt agora deve se parecer com o abaixo.

songs/ $    

Se tudo foi bem sucedido, você deve executar

ls    

e você deve ver 8 arquivos .sql, songs.db, e answers.txt.

Se você encontrar algum problema, siga os mesmos passos novamente e veja se consegue determinar onde errou!

Entendendo

Disponibilizado para você está um arquivo chamado songs.db, um banco de dados SQLite que armazena dados do Spotify sobre músicas e seus artistas. Este conjunto de dados contém as 100 músicas mais reproduzidas no Spotify em 2018. Em uma janela de terminal, execute sqlite3 songs.db para começar a executar consultas no banco de dados.

Primeiro, quando o sqlite3 solicitar uma consulta, digite .schema e pressione enter. Isso mostrará as declarações CREATE TABLE que foram usadas para gerar cada uma das tabelas no banco de dados. Ao examinar essas declarações, você pode identificar as colunas presentes em cada tabela.

Observe que cada artista possui um id e um nome. Observe também que cada música possui um nome, um artist_id (correspondente ao id do artista da música), bem como valores para a capacidade de dança, energia, chave, volume, fala (presença de palavras faladas em uma faixa), valência, tempo e duração da música (medida em milissegundos).

O desafio que você tem pela frente é escrever consultas SQL para responder a uma variedade de perguntas diferentes, selecionando dados de uma ou mais dessas tabelas. Depois de fazer isso, você refletirá sobre as maneiras pelas quais o Spotify pode usar os mesmos dados em sua campanha anual Spotify Wrapped para caracterizar os hábitos dos ouvintes.

Detalhes de Implementação

Para cada um dos problemas a seguir, você deve escrever uma única consulta SQL que gere os resultados especificados por cada problema. Sua resposta deve estar na forma de uma única consulta SQL, embora você possa aninhar outras consultas dentro da sua consulta. Você não deve assumir nada sobre os ids de músicas ou artistas específicos: suas consultas devem ser precisas mesmo se o id de uma música ou pessoa específica fosse diferente. Por fim, cada consulta deve retornar apenas os dados necessários para responder à pergunta: se o problema pede apenas que você apresente os nomes das músicas, por exemplo, sua consulta não deve apresentar também o tempo de cada música.

1. No arquivo 1.sql, escreva uma consulta SQL para listar os nomes de todas as músicas no banco de dados.
   • Sua consulta deve gerar uma tabela com uma única coluna para o nome de cada música.
2. No arquivo 2.sql, escreva uma consulta SQL para listar os nomes de todas as músicas em ordem crescente de tempo.
   • Sua consulta deve gerar uma tabela com uma única coluna para o nome de cada música.
3. No arquivo 3.sql, escreva uma consulta SQL para listar os nomes das 5 músicas mais longas, em ordem decrescente de duração.
   • Sua consulta deve gerar uma tabela com uma única coluna para o nome de cada música.
4. No arquivo 4.sql, escreva uma consulta SQL que liste os nomes de todas as músicas que possuem dançabilidade, energia e valência maiores que 0.75.
   • Sua consulta deve gerar uma tabela com uma única coluna para o nome de cada música.
5. No arquivo 5.sql, escreva uma consulta SQL que retorne a energia média de todas as músicas.
   • Sua consulta deve retornar uma tabela com uma única coluna e uma única linha contendo a energia média.
6. No arquivo 6.sql, escreva uma consulta SQL que liste os nomes das músicas que são de Post Malone.
   • Sua consulta deve retornar uma tabela com uma única coluna para o nome de cada música.
   • Você não deve fazer nenhuma suposição sobre qual é o artist_id de Post Malone.
7. No arquivo 7.sql, escreva uma consulta SQL que retorne a energia média das músicas que são de Drake.
   • Sua consulta deve retornar uma tabela com uma única coluna e uma única linha contendo a energia média.
   • Você não deve fazer nenhuma suposição sobre qual é o artist_id de Drake.
8. No arquivo 8.sql, escreva uma consulta SQL que liste os nomes das músicas que apresentam outros artistas.
   • As músicas que apresentam outros artistas incluirão "feat." no nome da música.
   • Sua consulta deve retornar uma tabela com uma única coluna para o nome de cada música.

Uso

Além de executar suas consultas no sqlite3, você pode testar suas consultas no terminal do VS Code executando

$ cat filename.sql | sqlite3 songs.db

onde filename.sql é o arquivo que contém sua consulta SQL.

Dicas

• Consulte este referencial de palavras-chave do SQL para obter algumas sintaxes do SQL que podem ser úteis!

Spotify Wrapped

Spotify Wrapped é um recurso que apresenta as 100 músicas mais reproduzidas pelos usuários do Spotify no ano anterior. Em 2021, o Spotify Wrapped calculou uma "Aura de Áudio" para cada usuário, uma "leitura dos [seus] dois humores mais proeminentes ditados pelas [suas] principais músicas e artistas do ano". Suponha que o Spotify determine uma aura de áudio ao analisar a energia média, valência e capacidade de dança das 100 principais músicas de uma pessoa no ano anterior. Em answers.txt, reflita sobre as seguintes perguntas:

• Se songs.db contiver as 100 principais músicas de um ouvinte de 2018, como você caracterizaria a aura de áudio dessa pessoa?
• Faça uma hipótese sobre por que a forma como você calculou essa aura pode não ser muito representativa do ouvinte. Que maneiras melhores de calcular essa aura você proporia?

Não se esqueça de enviar o arquivo answers.txt juntamente com cada um dos seus arquivos .sql!

Testando

Execute o código abaixo para avaliar a corretude do seu código usando check50.

check50 cs50/labs/2023/x/songs

Como enviar

No seu terminal, execute o seguinte para enviar seu trabalho.

submit50 cs50/labs/2023/x/songs

Agradecimentos

Conjunto de dados obtido de Kaggle.