Screen Link:
My Code:
library(DBI)
library(dplyr)
library(RSQLite)
library(ggplot2)
db <-'chinook.db'
run_query <- function(q){
conn <- dbConnect(SQLite(),db)
result <- dbGetQuery(conn,q)
#dbDisconnect(conn)
return(result)
}
show_tables <- function(){
q = "SELECT
name,type
FROM sqlite_master
WHERE type IN ('table','view')"
return(run_query(q))
}
show_tables()
albums_to_purchase ='
WITH usa_track AS
(
SELECT * FROM invoice i
INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id
WHERE i.billing_country="USA"
)
SELECT
g.name genre,
COUNT(ut.invoice_line_id) tracks_sold,
CAST(COUNT(ut.invoice_line_id) AS FLOAT)/(
SELECT COUNT(*) FROM usa_track)
percentage_sold
FROM usa_track ut
INNER JOIN track t ON t.track_id=ut.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
GROUP BY genre
ORDER BY tracks_sold DESC
LIMIT 10;
'
run_query(albums_to_purchase)
What I expected to happen:
I expected the query to run and return every genre with the number of tracks sold in the USA.
What actually happened:
I receive an error message about how invoice doesn’t exist.
Error: no such table: invoice
In addition: Warning message:
Error: no such table: invoice
I at first tried the answer given in the solution key and received the same error message for invoice_line.