youtube video link with explanations for these examples

https://youtu.be/GYfmN2IeY1w

# Create an  in-memory RSQLite database
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "dbmtcars", mtcars)

# Write the data to your table
dbWriteTable(con, "dbmtcars", mtcars)

# Write the data to your table and tell it to overwrite the old data
dbWriteTable(con, "dbmtcars", mtcars, overwrite=TRUE)


#Check what tables exist in your database
dbListTables(con)

# Check what fields exists in the table
dbListFields(con, "dbmtcars")
dbReadTable(con, "dbmtcars")


dbAppendTable(con,'dbmtcars', mtcars)
dbExecute(con, "Delete from dbmtcars")

cyl <- 4
gear <- 3

ds <- dbGetQuery(con, 'SELECT *  FROM dbmtcars WHERE cyl=  ?  and gear = ?', params = c(cyl, gear))
ds


# How to permanently store the database on your di

con2 <- dbConnect(RSQLite::SQLite(), "D:\\tmp\\test.db")
# Write the data to your table
RSQLite::dbWriteTable(con2, "mtcars", mtcars)
#Check what tables exist in your database
RSQLite::dbListTables(con2)
RSQLite::dbAppendTable(con2,'mtcars', mtcars)

dbListTables(con2)

# How a transaction is implemented
dbBegin(con2)
dbWriteTable(con2, "mtcars", mtcars, overwrite=TRUE)
dbAppendTable(con2,'mtcars', mtcars)
dbRollback(con2)

dbWriteTable(con2, "mtcars", mtcars, overwrite=TRUE)
dbReadTable(con2, "mtcars")
dbRemoveTable(con2,'mtcars')