#IMPORTANTE:  a última pergunta do lab 6 (questão 11) teve que ficar abaixo das
#duas perguntas do desafio, que se encontram no final após a questão 10 do lab,
#pois a pergunta 11 pede para que seja feita a desconexão do R com o SQLite,
#então não seria possível rodar mais códigos SQL depois da resposta

library(dplyr) #para as manipulações e pq eu uso pipe

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

Baixe o arquivo disco.db e armazene na variável path o caminho completo (pasta) na qual o arquivo foi gravado. Utilize o comando file.path() para combinar a variável path com o nome do arquivo (disco.db) e obter o nome do arquivo com seu respectivo caminho. Armazene este resultado na variável fname.

#respondendo a pergunta com caminho relativo
path <- "desafios"
fname <- path %>% file.path("disco.db")
fname

## [1] "desafios/disco.db"

Utilizando o pacote RSQLite, conecte-se ao arquivo de banco de dados. Armazene a conexão na variável conn.

library(RSQLite) #carregando a biblioteca

conn <- dbConnect(SQLite(), dbname = fname) #conectando ao dataset SQLite
conn

## <SQLiteConnection>
##   Path: \\smb\ra212974\Documentos\desafios\disco.db
##   Extensions: TRUE

Liste as tabelas existentes no banco de dados.

conn %>% dbListTables() #ver quais tabelas estão dentro do dataset

##  [1] "albums"          "artists"         "customers"       "employees"      
##  [5] "genres"          "invoice_items"   "invoices"        "media_types"    
##  [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
## [13] "tracks"

Identifique os nomes de todas as colunas existentes na tabela customers.

conn %>% dbListFields("customers") #listando os nomes das colunas de customers

##  [1] "CustomerId"   "FirstName"    "LastName"     "Company"      "Address"     
##  [6] "City"         "State"        "Country"      "PostalCode"   "Phone"       
## [11] "Fax"          "Email"        "SupportRepId"

Utilizando apenas SQLite, com o apoio do comando dbGetQuery, identifique quantos clientes estão atualmente cadastrados neste banco de dados.

conn %>% dbGetQuery("SELECT COUNT(*) AS total_clientes FROM customers")

#seleciona e conta todos (*) os clientes da tabela customers, retornando uma
#coluna com o total

Utilizando apenas SQLite, identifique o número de países diferentes em que moram os clientes encontrados acima.

conn %>% dbGetQuery("SELECT COUNT (DISTINCT Country) AS total_paises FROM customers")

#seleciona e conta países distintos para retornar uma coluna com o total de
#paises diferentes em customers

Utilizando apenas SQLite, quantos clientes existem por país? A tabela resultante deve conter o nome do país e a respectiva contagem, além de ser ordenada de maneira decrescente pela referida contagem.

#criando uma tabela chamada clientes_por_pais
clientes_por_pais <- conn %>%
  dbGetQuery("SELECT Country, COUNT(*) AS total_clientes
             FROM customers
             GROUP BY Country
             ORDER BY total_clientes DESC")
clientes_por_pais

#seleciona o país, conta todos os clientes de customers existem para cada país,
#agrupa por país e coloca em ordem decrescente o total de clientes

Quais são os 5 países com mais clientes registrados? Use apenas SQLite.

top5_paises <- conn %>% 
dbGetQuery("SELECT Country, COUNT(*) AS total_clientes
    FROM customers
    GROUP BY Country
    ORDER BY total_clientes DESC
    LIMIT 5")
top5_paises

#faz a mesma coisa q o chunk anterior, mas com um comando q limita a saída para
#5 linhas

Quais são os países registrados que possuem apenas 6 letras no nome?

resposta <- conn %>% 
  dbGetQuery("SELECT DISTINCT Country
             FROM customers
             WHERE LENGTH(Country) = 6")
resposta

#seleciona países distintos de customers nos quais o comprimento é igual a 6

Quais foram as músicas compradas por clientes brasileiros?

musicas_brasil <- conn %>% 
dbGetQuery("SELECT DISTINCT t.Name AS Musica
    FROM customers c
    JOIN invoices i ON c.CustomerId = i.CustomerId
    JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
    JOIN tracks t ON ii.TrackId = t.TrackId
    WHERE c.Country = 'Brazil'")

musicas_brasil

#conecta clientes brasileiros às suas faturas, dps aos itens comprados e dps às
#músicas, retornando os nomes únicos das músicas compradas por esses clientes

Perguntas do desafio

Qual o álbum mais tocado por pais?

album_mais_tocado <- conn %>% 
  dbGetQuery("SELECT Country, Album, MAX(TotalTocados) AS MaisTocado
    FROM (
      SELECT c.Country, al.Title AS Album, COUNT(*) AS TotalTocados
      FROM customers c
      JOIN invoices i ON c.CustomerId = i.CustomerId
      JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
      JOIN tracks t ON ii.TrackId = t.TrackId
      JOIN albums al ON t.AlbumId = al.AlbumId
      GROUP BY c.Country, al.Title
    ) sub
    GROUP BY Country
    ORDER BY Country")
album_mais_tocado

#primeiro criamos uma subconsulta (subquery) que junta as tabelas necessárias:
#customers (c) - contém os clientes e seus países; invoices (i) - faturas
#associadas a cada cliente; invoice_items (ii) - itens de cada fatura
#(cada faixa comprada/tocada); tracks (t) - faixas de músicas; albums (al) -
#informações sobre os álbuns. a subconsulta conta quantas vezes cada álbum foi
#tocado por país, agrupando por país (c.Country) e título do álbum (al.Title).
#depois, na consulta principal, usamos essa subconsulta e aplicamos
#MAX(TotalTocados) para selecionar apenas o álbum mais tocado de cada país.
#o resultado final mostra três colunas e por fim, ordenamos o resultado por país
#para ficar mais organizado.

Qual o artista mais tocado por pais?

artista_mais_tocado <- conn %>% 
  dbGetQuery("SELECT Country, Artista, MAX(TotalTocados) AS MaisTocado
    FROM (
      SELECT c.Country, ar.Name AS Artista, COUNT(*) AS TotalTocados
      FROM customers c
      JOIN invoices i ON c.CustomerId = i.CustomerId
      JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
      JOIN tracks t ON ii.TrackId = t.TrackId
      JOIN albums al ON t.AlbumId = al.AlbumId
      JOIN artists ar ON al.ArtistId = ar.ArtistId
      GROUP BY c.Country, ar.Name
    ) sub
    GROUP BY Country
    ORDER BY Country;")
artista_mais_tocado

#o código faz algo análogo ao do chunk anterior, mas foi preciso incluir a
#tabela artists, q está relacionada a albums pela chave ArtistId

Desconecte do banco de dados.

#desconectando do banco de dados
dbDisconnect(conn)

Desafio 6

Maria Eduarda Villéla Silva

16 de setembro de 2025

Perguntas do desafio