if (!dir.exists(“data”)) dir.create(“data”) if (!file.exists(“data/chinook.sqlite”)) { download.file( url = “https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite”, destfile = “data/chinook.sqlite”, mode = “wb” ) }

install.packages library(knitr)

library(“DBI”) library(“RSQLite”) library(“dbplyr”) library(“tidyverse”)

con <- dbConnect(RSQLite::SQLite(), “data/chinook.sqlite”) dbListTables(con)

dbListTables(con)

file.exists(“data/chinook.sqlite”) con <- dbConnect(RSQLite::SQLite(), “chinook.sqlite”)

top_artists <- dbGetQuery(con, ” SELECT Artist.Name AS ArtistName, COUNT(Album.AlbumId) AS AlbumCount FROM Artists JOIN Album ON Artist.ArtistId = Album.ArtistId GROUP BY Artist.ArtistId, Artist.Name ORDER BY AlbumCount DESC LIMIT 10; “)

ggplot(top_artists, aes(x = reorder(ArtistName, AlbumCount), y = AlbumCount)) + geom_col(fill = “darkorange”) + coord_flip() + labs( title = “Top 10 Artists in Chinook”, x = “Artists”, y = “Number of Albums” ) + theme_minimal()

Step 1: Automatically knit your latest work into an HTML report

rmarkdown::render(“Week01_SQL_to_R_Chinookdata.Rmd”, quiet = TRUE)

Step 2: Upload the HTML report directly to RPubs.com

upload_result <- rsconnect::rpubsUpload( title = “BAN 3083: Week 1 SQL to R (Chinook)”, contentFile = “Week01_SQL_to_R_Chinookdata.Rmd”, originalDoc = “Week01_SQL_to_R_Chinookdata.Rmd”