<jupyter_start><jupyter_text>Introduction to Databases and SQL- **Authors**: <NAME>- **Research field**: Social Psychology - Social Complexity- **Lesson topic**: Databases and SQL- **Lesson content URL**:   Plan- get a data set from R: mtcars- select from it using SQL (via sqldf)- export it as CSV file- in sqlite, create database and import CSV- use RSQLite connection to select direct from database  get a data set from R: mtcars The data contain Motor Trends car testing results.  There are 32 cars.<jupyter_code>nrow(mtcars)
ncol(mtcars)
head(mtcars)
summary(mtcars)<jupyter_output><empty_output><jupyter_text>use SQL to select cars with 6-cylinder engines<jupyter_code>library(sqldf)
# help(sqldf)
sqldf('select * from mtcars where cyl=6')<jupyter_output><empty_output><jupyter_text>There are 7 cars matching the query.<jupyter_code>nrow(sqldf('select * from mtcars where cyl=6'))<jupyter_output><empty_output><jupyter_text>We can obtain the same result directly with SQL...  but let's not get ahead of ourselves.<jupyter_code>sqldf('select count(*) from mtcars where cyl=6')<jupyter_output><empty_output><jupyter_text>write mtcars dataset to the file system<jupyter_code>df = mtcars
df$name = rownames(df) # move R's rownames into their own column
write.table(df, "mtcars.csv", quote=TRUE, row.names=FALSE, col.names=FALSE, sep=",")<jupyter_output><empty_output><jupyter_text>create sqlite database in terminal    make mtcars-initThat will run the following:    sqlite3 mtcars.sqlite < mtcars-init.sqlThat SQL file does the following:- drop a table called results- create a table called results with columns for the mtcars data- tell sqlite to load a CSV file- load the CSV file into the results table  access sqlite from R<jupyter_code>library(RSQLite)

# connect to the database
db = dbConnect(SQLite(), dbname="mtcars.sqlite")<jupyter_output><empty_output><jupyter_text>list the tables that are available<jupyter_code>dbListTables(db)<jupyter_output><empty_output><jupyter_text>Inspect columns in the results table<jupyter_code>dbListFields(db, "results")<jupyter_output><empty_output><jupyter_text>use SQL to select cars with 8-cylinder engines - directly from database<jupyter_code>dbGetQuery(conn = db, "select * from results where cyl=8")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09772129710780017, 'fraction_numerical': 0.0043821209465381246, 'mean_word_length': 6.899653979238754, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '3006', 'license': 'Apache-2.0', 'n_tokens_mistral': 768, 'n_tokens_neox': 738, 'n_words': 230, 'path': 'lessons/misc/sql-intro/r-sql.ipynb', 'repo_name': 'UAAppComp/studyGroup', 'size': '2293'}