RSQLite | R interface for SQLite | Database library

Toy table: (code to create it in R is given below)

I have a local SQLite database with two tables. I want to make a query from one table filtering rows by ids from another table in the same database using dbplyr.

As an example, here is what I would do without a database involved:

I want to fetch data from a local SQLite database in R by using the any() function in combination with group_by to filter groups where at least one row is equal to a certain condition. It would probably help to finally learn SQL, however, until now I managed to do all my queries using dbplyr and I hope there is a dplyr solution for this problem as well.

I would like to filter a SQL database based whether a regular expression appears within any column. I would like to specify the regex as a variable; however it is read as a literal string. I am having trouble getting the regex in as a variable. Thank you for your help!

Resources I've consulted:

• https://www.tidyverse.org/blog/2018/01/dbplyr-1-2/
• https://github.com/tidyverse/dbplyr/issues/168

Note: I had trouble making a reprex using the mtcars dataset, following https://www.tidyverse.org/blog/2018/01/dbplyr-1-2/. I get the error: "Error: str_detect() is not available in this SQL variant". I cannot share a reprex using my actual SQL database. As such, below is a pseudo-reprex.

I am trying to create a graph where I am can view total flights by year, filtered by origin and its destination. Picture1: What I envision VS Picture2: The result.

Problem 1: Graph unable to appear on shinyApp.

Problem 2: When the graph appears, the graph does not change even when I select a different Origin from the dropdown box

I am quite new to shiny, any help would be greatly appreciated! The data I am using is from Harvard database- data expo 2009, Airline on time data.

I'm working on converting a simple Python script to R to act as a template for connecting up to SQLite files. The data is on a NFS mount, and we've run into a few snags in setting up the original Python template (namely, IO errors), but we were able to work around them by connecting with read-only mode and setting the VFS to unix-none, e.g.:

I am very new to SQL (using it in R currently with RSQLite and DBI packages)

I am trying to create a column that is the absolute mean deviation, aka:

(i) - AVG(i,g)

Where i is the individual occurence and the AVG component is the average for the group. What I am having troubles with is making sure the AVG component is the only part that gets grouped. When I do GROUP BY, it groups everything and doesn't give me the right number.

Here is the sample data:

Basically I would want the individual GRADE for a student in a class to compare with the average of that student (e.g. the individual english grade - the total average for a student)

Iha ve tried the following:

I am facing performance issues when using bind variables with DBI package. My original use case is with Postgres database, but for reproducibility, below I use in-memory SQLite that has exactly the same issue - when I select data from some table by id (in Postgres the column is indexed) the parametrized version runs multiple times longer on selecting number of rows than SQL with IDs pasted in IN statement:

I am trying to scrape some pitchf/x data and store it in an SQLite database. However, I am receiving the following error when I run the following code: