Database Analysis with R

Test R Markdown to confirm successful Sql connections and data retrieval from the skills_insights_db

con <- dbConnect(
  RMySQL::MySQL(),
  dbname = "skill_insights_db",
  host = "localhost",
  port = 3306,
  user = "root",
  password = rstudioapi::askForPassword("Enter MySQL password")
)

tables <- dbListTables(con)
print(tables)

##  [1] "applicants"      "chesstournament" "courses"         "job_postings"   
##  [5] "job_skills"      "raw_skills"      "salaries"        "salaries_2025"  
##  [9] "skills"          "surveys"

skills <- dbGetQuery(con, "SELECT * FROM Skills LIMIT 10")
head(skills)

##   SkillID               Name             Category PopularityScore
## 1       1             Python Programming Language             9.8
## 2       2                SQL    Database Language             9.5
## 3       3                  R Programming Language             8.7
## 4       4              Excel                 Tool             7.0
## 5       5   Machine Learning              Concept             9.2
## 6       6 Data Visualization                 Tool             8.5

salary_summary <- dbGetQuery(con, "
  SELECT job_title, ROUND(AVG(salary_in_usd), 0) AS avg_salary
  FROM salaries
  GROUP BY job_title
  ORDER BY avg_salary DESC
  LIMIT 10
")
salary_summary

##                          job_title avg_salary
## 1    Analytics Engineering Manager     399880
## 2           Data Science Tech Lead     375000
## 3               Applied AI ML Lead     292500
## 4         Head of Machine Learning     288701
## 5              Engineering Manager     281769
## 6               AWS Data Architect     258000
## 7                       Head of AI     257250
## 8  Machine Learning Model Engineer     255000
## 9             Cloud Data Architect     250000
## 10      ML Infrastructure Engineer     249721

applicant_gender <- dbGetQuery(con, "
  SELECT gender, COUNT(*) AS applicant_count
  FROM applicants
  GROUP BY gender
")
applicant_gender

##    gender applicant_count
## 1    Male           52884
## 2 Unknown           18032
## 3  Female            4952
## 4   Other             764

dbDisconnect(con)

## [1] TRUE

---

This script confirms that key tables (Skills, Salaries, Applicants) are populated and aligned with the ER diagram. The queries also demonstrate initial analysis through salary summaries and applicant demographics.