Answering Business Questions using SQL - helper functions inquiry

I’ve begun working on this guided project as I close in on finishing the SQL section of the Data Analyst in R path, aiming to finish the entire path before my subscription expires in about a week and a half. However, I hit a pretty disruptive roadblock so early into the project that it’s greatly hindering my ability to run test/inferring subqueries.

After importing the DBI and RSQLite libraries, I defined the run_query and show_tables helper functions as shown below - nearly identical to the solution notebook, as I had repeatedly tweaked run_query to try and ensure it ran properly, but I’m worried that I have a directory or package issue - show_tables returns an output of 0 rows and 2 columns, and whenever I try to call run_query to look up a specific table (a query as benign as “SELECT * FROM employee”, for example), I get an error that said table doesn’t exist.

run_query <- function(q){
  conn <- dbConnect(SQLite(), 'chinook.db')
  result <- dbGetQuery(conn, q)
show_tables <- function(){
  q <- "SELECT name, type
        FROM sqlite_master
        WHERE type IN ('table','view');"

the code itself could be sufficient, but if I’m not properly loading all the tools from the R~SQL interaction packages, or not successfully accessing chinook.db (I set my working directory through the session menu as the folder containing the file), then the sufficient code won’t actually return anything that it should. Are there any suggestions to remedy this issue? Without the ability to actually run a query, the process of testing partial queries and problem solving is basically inaccessible. I’ll likely be working on the start of another course in the path until the issue can be resolved.

I worked on this project recently. May be these tips could help.

  • I created a folder/directory for the project

  • I put all the files that I needed in this folder/directory. In this case, it is chinook.db and chinook-unmodified

  • I opened the R Markdown or Notebook. And I saved in the same folder/directory

  • Then I did Session > Set Working Directory > To Source File Location

  • Then I ran these library(RSQLite) and library(DBI)

These steps worked for me.

You hit the nail on the head - it was definitely a directory issue, because doing this fixed it! I’m not sure if choosing to set working directory by choosing one manually was the issue, or that they were in separate folders, or both, but show_tables is returning all the tables and their names now.

Thank you very much!

1 Like