#install.packages("RSQLite")Desafio 06
library(RSQLite)
library(knitr)#1Baixe 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
# Definindo o caminho da pasta onde está o disco.db
path <- "." # a pasta correta
# Junta o caminho com o nome do arquivo
fname <- file.path(path, "disco.db")
# Visualiza o resultado
fname[1] "./disco.db"#2 Utilizando o pacote RSQLite, conecte-se ao arquivo de banco de dados. Armazene a conexão na variável conn.
conn = dbConnect(SQLite(),
'disco.db') # abre uma conexão com o banco de dados SQLite
conn # exibe o objeto de conexão criado<SQLiteConnection>
Path: \\smb\ra237599\WindowsDesktop\Unicamp\Banco de dados - ME315\Desafio 05 e 06\disco.db
Extensions: TRUE#3 Liste as tabelas existentes no banco de dados.
dbListTables(conn) #lista as tabelas do banco [1] "albums" "artists" "customers" "employees"
[5] "genres" "invoice_items" "invoices" "media_types"
[9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
[13] "tracks" #4 Identifique os nomes de todas as colunas existentes na tabela customers.
dbListFields(conn, "customers") #lista as colunas de customers [1] "CustomerId" "FirstName" "LastName" "Company" "Address"
[6] "City" "State" "Country" "PostalCode" "Phone"
[11] "Fax" "Email" "SupportRepId"#5 Utilizando apenas SQLite, com o apoio do comando dbGetQuery, identifique quantos clientes estão atualmente cadastrados neste banco de dados.
nclient <- dbGetQuery(conn, # executa uma consulta SQL no banco de dados aberto em 'conn'
"SELECT COUNT(*) AS total_clientes
FROM customers;") # SQL: conta quantas linhas (clientes) existem na tabela 'customers'
# renomeia o resultado como 'total_clientes'
nclient # exibe o resultado da consulta armazenado em 'nclient' total_clientes
1 59#6 Utilizando apenas SQLite, identifique o número de países diferentes em que moram os clientes encontrados acima.
npais <- dbGetQuery(conn, "
SELECT COUNT(DISTINCT Country) AS total_paises
FROM customers;
")
# executa a consulta SQL no banco conectado em 'conn'
# a consulta conta quantos países distintos existem na coluna 'Country' da tabela 'customers'
# o resultado é armazenado no objeto 'npais'
npais total_paises
1 24# exibe o resultado da consulta armazenado em 'npais'#7 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.
nclient_pais <- dbGetQuery(conn, "
SELECT Country, COUNT(*) AS total_clientes
FROM customers
GROUP BY Country
ORDER BY total_clientes DESC;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'nclient_pais'
# a consulta seleciona a coluna 'Country' e conta quantos clientes existem em cada país (COUNT(*)), nomeando como 'total_clientes'
# os dados são extraídos da tabela 'customers'
# os resultados são agrupados por país (GROUP BY Country)
# os países são ordenados em ordem decrescente pelo número de clientes (ORDER BY total_clientes DESC)
nclient_pais Country total_clientes
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 4
6 United Kingdom 3
7 Portugal 2
8 India 2
9 Czech Republic 2
10 Sweden 1
11 Spain 1
12 Poland 1
13 Norway 1
14 Netherlands 1
15 Italy 1
16 Ireland 1
17 Hungary 1
18 Finland 1
19 Denmark 1
20 Chile 1
21 Belgium 1
22 Austria 1
23 Australia 1
24 Argentina 1# exibe o resultado da consulta armazenado em 'nclient_pais'#8 Quais são os 5 países com mais clientes registrados? Use apenas SQLite.
nclient_pais_top5 <- dbGetQuery(conn, "
SELECT Country, COUNT(*) AS total_clientes
FROM customers
GROUP BY Country
ORDER BY total_clientes DESC
LIMIT 5;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'nclient_pais_top5'
# a consulta seleciona a coluna 'Country' e conta quantos clientes existem em cada país (COUNT(*)), nomeando como 'total_clientes'
# os dados são extraídos da tabela 'customers'
# os resultados são agrupados por país (GROUP BY Country)
# os países são ordenados em ordem decrescente pelo número de clientes (ORDER BY total_clientes DESC)
# apenas os 5 primeiros países com maior número de clientes são retornados (LIMIT 5)
nclient_pais_top5 Country total_clientes
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 4# exibe o resultado da consulta armazenado em 'nclient_pais_top5'#9 Quais são os países registrados que possuem apenas 6 letras no nome?
pais_sixletters <- dbGetQuery(conn, "
SELECT DISTINCT Country
FROM customers
WHERE LENGTH(Country) = 6;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'pais_sixletters'
# a consulta seleciona apenas os países distintos (DISTINCT Country) da tabela 'customers'
# filtra apenas os países cujo nome possui exatamente 6 letras (WHERE LENGTH(Country) = 6)
pais_sixletters Country
1 Brazil
2 Canada
3 Norway
4 France
5 Poland
6 Sweden# exibe o resultado da consulta armazenado em 'pais_sixletters'#10 Quais foram as músicas compradas por clientes brasileiros?
music_brazil <- dbGetQuery(conn, "
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'
ORDER BY t.Name;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'music_brazil'
# seleciona os nomes das músicas (t.Name) de forma distinta (DISTINCT) e renomeia a coluna como 'musica'
# realiza um JOIN entre 'customers' e 'invoices' para associar cada cliente às suas faturas
# realiza um JOIN entre 'invoices' e 'invoice_items' para obter os itens de cada fatura
# realiza um JOIN entre 'invoice_items' e 'tracks' para obter os detalhes das faixas/músicas
# filtra apenas os clientes que estão no Brasil (WHERE c.Country = 'Brazil')
# ordena os resultados pelo nome da música em ordem alfabética (ORDER BY t.Name)
music_brazil musica
1 1/2 Full
2 2 Minutes To Midnight
3 A Cor Do Sol
4 A Cura
5 A Menina Dança
6 Abraham, Martin And John
7 Aces High
8 Admirável Gado Novo
9 All Along The Watchtower
10 All I Want Is You
11 Aloha
12 Amor De Muito
13 Animal
14 Any Colour You Like
15 Aos Leões
16 Babyface
17 Back off Bitch
18 Bad
19 Bad Seed
20 Banditismo Por Uma Questa
21 Be Good Johnny
22 Be Mine
23 Big Wave
24 Binky The Doormat
25 Bittersweet Me
26 Black
27 Black Light Syndrome
28 Bora-Bora
29 Bossa
30 Burden In My Hand
31 Calling Dr. Love
32 Carolina Hard-Core Ecstasy
33 Casa
34 Caso Você Queira Saber
35 Child In Time
36 Cinema Mudo
37 Cold Gin
38 Coma
39 Communication Breakdown(2)
40 Cristina Nº 2
41 Cropduster
42 Cry For Love
43 Cérebro Eletrônico
44 D'Yer Mak'er
45 Damage Inc.
46 Dance
47 Demorou!
48 Dissident
49 Don't Cry (Original)
50 Don't Look Back
51 Doutor
52 Down Under
53 Down by the Sea
54 Drifter
55 Duelists
56 Dust N' Bones
57 Electrolite
58 Eu Amo Você
59 Everything I Need
60 Experiment In Terra
61 Eye
62 Fantasia On Greensleeves
63 Fast As a Shark
64 Flower
65 Flying High Again
66 Fool In The Rain
67 Garden of Eden
68 Get Down, Make Love
69 Get Up
70 Girl From A Pawnshop
71 Go Down
72 Green Disease
73 Grito De Alerta
74 Heart Of Lothian: Wide Boy / Curtain Call
75 Hell Ain't A Bad Place To Be
76 Help Yourself
77 Home Sweet Home
78 I Belong To You
79 I Feel Good (I Got You) - Sossego
80 In Bloom
81 In The Evening
82 Interlude Zumbi
83 Into The Light
84 Intro
85 Ipiranga 2001
86 Is This Love (Live)
87 It's a Mistake
88 Jesus Christ Pose
89 Karelia Suite, Op.11: 2. Ballade (Tempo Di Menuetto)
90 Knocking At Your Back Door
91 Lay Down Sally
92 Leash
93 Leper Messiah
94 Like A Song...
95 Lindo Lago Do Amor
96 Linha Do Equador
97 Live and Let Die
98 Lixo Do Mangue
99 Losfer Words
100 Mama, I'm Coming Home
101 Maracatu De Tiro Certeiro
102 Mateus Enter
103 Maybe I'm A Leo
104 Mensagen De Amor (2000)
105 Meu Caro Amigo
106 Meu Erro
107 Midnight From The Inside Out
108 Mis Penas Lloraba Yo (Ao Vivo) Soy Gitano (Tangos)
109 Morena De Angola
110 Most High
111 Mundaréu
112 Music for the Funeral of Queen Mary: VI. "Thou Knowest, Lord, the Secrets of Our Hearts"
113 Nega Do Cabelo Duro
114 No No No
115 No Quarter
116 Nossa Gente (Avisa Là)
117 Nosso Adeus
118 Não Vou Ficar
119 O Descobridor Dos Sete Mares
120 O Leaozinho
121 O Que Me Importa
122 Oceans
123 On A Plain
124 Pais E Filhos
125 Paranoid
126 Pick Myself Up
127 Plot 180
128 Podes Crer
129 Ponto De Interrogação
130 Powerslave
131 Praise
132 Red Light
133 Redundant
134 Refavela (Live)
135 Reggae Tchan
136 Revolta Olodum
137 Right Next Door to Hell
138 Rios Pontes & Overdrives
139 Rock And Roll Is Dead
140 Rocket Queen
141 Saber Amar
142 Samba Do Lado
143 Sanctuary
144 Saudade Dos Aviões Da Panair (Conversando No Bar)
145 Scentless Apprentice
146 Selvagem
147 Sereia
148 Será
149 Será Que Vai Chover?
150 Seven Seas Of Rhye
151 Shakes and Ladders
152 Shout It Out Loud
153 Sincero Breu
154 Slither
155 Snowballed
156 So Fast, So Numb
157 Sometimes Salvation
158 Soul Singing
159 Stand Inside Your Love
160 Stir It Up (Live)
161 String Quartet No. 12 in C Minor, D. 703 "Quartettsatz": II. Andante - Allegro assai
162 Strutter
163 Suite No. 3 in D, BWV 1068: III. Gavotte I & II
164 Sweetest Thing
165 Symphony No. 2, Op. 16 - "The Four Temperaments": II. Allegro Comodo e Flemmatico
166 Take the Celestra
167 The Day I Tried To Live
168 The Great Gig In The Sky
169 The Memory Remains
170 The Unforgiven II
171 The Unwritten Law
172 Think About You
173 Title Song
174 Top Top
175 TriboTchan
176 Um Satélite Na Cabeça
177 Untitled
178 Vai Passar
179 Vai Valer
180 Vamo Batê Lata
181 Vavoom : Ted The Mechanic
182 Voce Nao Entende Nada - Cotidiano
183 Wanted Dread And Alive
184 Why Go
185 X-9 2001
186 You're My Best Friend
187 You've Been A Long Time Coming
188 Your Blue Room
189 Água de Beber# exibe o resultado da consulta armazenado em 'music_brazil'#Qual o álbum mais tocado por pais? (Pergunta 1 do desafio)
pergunta1 <- dbGetQuery(conn, "
SELECT Country, Title AS album, total_compras
FROM (
SELECT c.Country,
a.Title,
COUNT(*) AS total_compras,
RANK() OVER (PARTITION BY c.Country ORDER BY COUNT(*) DESC) AS posicao
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 a ON t.AlbumId = a.AlbumId
GROUP BY c.Country, a.Title
) sub
WHERE posicao = 1
ORDER BY total_compras DESC;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'pergunta1'
# consulta retorna, para cada país, o álbum mais comprado e o total de compras desse álbum
# subconsulta:
# - seleciona o país (c.Country) e o título do álbum (a.Title)
# - conta quantas vezes cada álbum foi comprado (COUNT(*) AS total_compras)
# - calcula a posição de cada álbum dentro do país usando RANK() para determinar o mais comprado (PARTITION BY c.Country ORDER BY COUNT(*) DESC)
# - junta as tabelas customers, invoices, invoice_items, tracks e albums para relacionar clientes às faixas e aos álbuns
# - agrupa por país e título do álbum
# na consulta externa:
# - filtra apenas os álbuns que ficaram na posição 1 em cada país (os mais comprados)
# - ordena os resultados pelo total de compras em ordem decrescente
pergunta1 Country
1 USA
2 Canada
3 Brazil
4 France
5 France
6 Ireland
7 India
8 Czech Republic
9 Finland
10 Finland
11 Germany
12 Germany
13 Spain
14 United Kingdom
15 United Kingdom
16 Argentina
17 Australia
18 Austria
19 Belgium
20 Belgium
21 Denmark
22 Denmark
23 Hungary
24 Italy
25 Netherlands
26 Norway
27 Norway
28 Poland
29 Portugal
30 Portugal
31 Sweden
32 Chile
33 Chile
34 Chile
album
1 The Office, Season 3
2 Arquivo II
3 Use Your Illusion I
4 Minha Historia
5 Chronicle, Vol. 1
6 Lost, Season 2
7 Up An' Atom
8 Prenda Minha
9 Greatest Kiss
10 Greatest Hits
11 Unplugged
12 Djavan Ao Vivo - Vol. 1
13 My Generation - The Very Best Of The Who
14 No Prayer For The Dying
15 Load
16 Acústico
17 The X Factor
18 My Generation - The Very Best Of The Who
19 Rotten Apples: Greatest Hits
20 Big Ones
21 Mezmerize
22 Chronicle, Vol. 1
23 Pop
24 Acústico
25 In Step
26 For Those About To Rock We Salute You
27 Cássia Eller - Coleção Sem Limite [Disc 2]
28 Rattle And Hum
29 Raul Seixas
30 Instant Karma: The Amnesty International Campaign to Save Darfur
31 Volume Dois
32 My Way: The Best Of Frank Sinatra [Disc 1]
33 Heroes, Season 1
34 Battlestar Galactica, Season 3
total_compras
1 14
2 9
3 7
4 7
5 7
6 7
7 6
8 5
9 5
10 5
11 5
12 5
13 5
14 5
15 5
16 4
17 4
18 4
19 4
20 4
21 4
22 4
23 4
24 4
25 4
26 4
27 4
28 4
29 4
30 4
31 4
32 3
33 3
34 3# exibe o resultado da consulta armazenado em 'pergunta1'#mostrando a tabela da pergunta 1 no html
kable(pergunta1, format = "html", table.attr = "class='table table-striped'")| Country | album | total_compras |
|---|---|---|
| USA | The Office, Season 3 | 14 |
| Canada | Arquivo II | 9 |
| Brazil | Use Your Illusion I | 7 |
| France | Minha Historia | 7 |
| France | Chronicle, Vol. 1 | 7 |
| Ireland | Lost, Season 2 | 7 |
| India | Up An' Atom | 6 |
| Czech Republic | Prenda Minha | 5 |
| Finland | Greatest Kiss | 5 |
| Finland | Greatest Hits | 5 |
| Germany | Unplugged | 5 |
| Germany | Djavan Ao Vivo - Vol. 1 | 5 |
| Spain | My Generation - The Very Best Of The Who | 5 |
| United Kingdom | No Prayer For The Dying | 5 |
| United Kingdom | Load | 5 |
| Argentina | Acústico | 4 |
| Australia | The X Factor | 4 |
| Austria | My Generation - The Very Best Of The Who | 4 |
| Belgium | Rotten Apples: Greatest Hits | 4 |
| Belgium | Big Ones | 4 |
| Denmark | Mezmerize | 4 |
| Denmark | Chronicle, Vol. 1 | 4 |
| Hungary | Pop | 4 |
| Italy | Acústico | 4 |
| Netherlands | In Step | 4 |
| Norway | For Those About To Rock We Salute You | 4 |
| Norway | Cássia Eller - Coleção Sem Limite [Disc 2] | 4 |
| Poland | Rattle And Hum | 4 |
| Portugal | Raul Seixas | 4 |
| Portugal | Instant Karma: The Amnesty International Campaign to Save Darfur | 4 |
| Sweden | Volume Dois | 4 |
| Chile | My Way: The Best Of Frank Sinatra [Disc 1] | 3 |
| Chile | Heroes, Season 1 | 3 |
| Chile | Battlestar Galactica, Season 3 | 3 |
#Qual o artista mais tocado por pais? (Pergunta 2 do desafio)
pergunta_2 <- dbGetQuery(conn, "
SELECT Country, artist, total_compras
FROM (
SELECT c.Country,
ar.Name AS artist,
COUNT(*) AS total_compras,
RANK() OVER (PARTITION BY c.Country ORDER BY COUNT(*) DESC) AS posicao
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
WHERE posicao = 1
ORDER BY total_compras DESC;
")
# executa a consulta SQL no banco conectado em 'conn' e armazena o resultado no objeto 'pergunta_2'
# consulta retorna, para cada país, o artista mais comprado e o total de compras de suas músicas
# subconsulta:
# - seleciona o país (c.Country) e o nome do artista (ar.Name) renomeando como 'artist'
# - conta quantas vezes as músicas do artista foram compradas (COUNT(*) AS total_compras)
# - calcula a posição de cada artista dentro de cada país usando RANK() para determinar o mais comprado (PARTITION BY c.Country ORDER BY COUNT(*) DESC)
# - faz JOIN das tabelas customers, invoices, invoice_items, tracks, albums e artists para relacionar clientes às faixas, álbuns e artistas
# - agrupa os resultados por país e artista
# na consulta externa:
# - filtra apenas os artistas que ficaram na posição 1 em cada país (os mais comprados)
# - ordena os resultados pelo total de compras em ordem decrescente
pergunta_2 Country artist total_compras
1 USA Iron Maiden 34
2 Australia Iron Maiden 18
3 Canada Os Paralamas Do Sucesso 16
4 Portugal Iron Maiden 16
5 Germany Iron Maiden 14
6 Brazil Pearl Jam 11
7 Brazil Os Paralamas Do Sucesso 11
8 France Creedence Clearwater Revival 11
9 Austria U2 9
10 Belgium Faith No More 9
11 Czech Republic U2 9
12 Czech Republic Iron Maiden 9
13 Norway Led Zeppelin 9
14 United Kingdom Metallica 9
15 India Iron Maiden 8
16 Ireland Lost 8
17 Denmark Creedence Clearwater Revival 7
18 Argentina Metallica 6
19 Chile Led Zeppelin 6
20 Finland Van Halen 6
21 Netherlands Red Hot Chili Peppers 6
22 Poland U2 6
23 Spain Pearl Jam 6
24 Hungary The Office 5
25 Hungary Jamiroquai 5
26 Italy The Rolling Stones 5
27 Italy Faith No More 5
28 Sweden Titãs 5# exibe o resultado da consulta armazenado em 'pergunta_2'#mostrando a tabela da pergunta 1 no html
kable(pergunta_2, format = "html", table.attr = "class='table table-striped'")| Country | artist | total_compras |
|---|---|---|
| USA | Iron Maiden | 34 |
| Australia | Iron Maiden | 18 |
| Canada | Os Paralamas Do Sucesso | 16 |
| Portugal | Iron Maiden | 16 |
| Germany | Iron Maiden | 14 |
| Brazil | Pearl Jam | 11 |
| Brazil | Os Paralamas Do Sucesso | 11 |
| France | Creedence Clearwater Revival | 11 |
| Austria | U2 | 9 |
| Belgium | Faith No More | 9 |
| Czech Republic | U2 | 9 |
| Czech Republic | Iron Maiden | 9 |
| Norway | Led Zeppelin | 9 |
| United Kingdom | Metallica | 9 |
| India | Iron Maiden | 8 |
| Ireland | Lost | 8 |
| Denmark | Creedence Clearwater Revival | 7 |
| Argentina | Metallica | 6 |
| Chile | Led Zeppelin | 6 |
| Finland | Van Halen | 6 |
| Netherlands | Red Hot Chili Peppers | 6 |
| Poland | U2 | 6 |
| Spain | Pearl Jam | 6 |
| Hungary | The Office | 5 |
| Hungary | Jamiroquai | 5 |
| Italy | The Rolling Stones | 5 |
| Italy | Faith No More | 5 |
| Sweden | Titãs | 5 |
#11 Desconecte do banco de dados.
dbDisconnect(conn)
# encerra a conexão com o banco de dados armazenada em 'conn', liberando recursos do sistema