This lab focuses on more advanced uses of SQL:

Directions for all labs (read before starting)

  1. Please work together with your assigned partner. Make sure you both fully understand something before moving on.
  2. Record your answers to lab questions separately from the lab’s examples. You and your partner should only turn in responses to lab questions, nothing more and nothing less.
  3. Ask for help, clarification, or even just a check-in if anything seems unclear.

The “Lab” section is something you will work on with a partner using paired programming, a framework defined as follows:

Preamble

Packages

library(DBI)
library(odbc)
library(RMySQL)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.1     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(dbplyr)
## 
## Attaching package: 'dbplyr'
## 
## The following objects are masked from 'package:dplyr':
## 
##     ident, sql

Commands from Last week

A basic SQL query looks like “SELECT [attribute] FROM [table] WHERE [some filter];”

There are a number of useful commands, and the cheat sheet at https://www.geeksforgeeks.org/sql-cheat-sheet/# can be very useful.

  • SQL Queries:
    • SELECT: retrieve data
    • *: Wildcard, everything
      • Example: SELECT * FROM employees;
      • This returns all columns and all values from the employees table
    • DISTINCT: unique values from a column
      • Example: SELECT DISTINCT department FROM employees;
      • This returns the distinct departments in the employees table
    • WHERE: filter on conditions
    • LIMIT: limit return size
    • CASE: conditional logic
  • Filters:
    • WHERE: filter rows on conditions
    • LIKE: Match a pattern in a column
    • IN: match any value in a list
    • Between: Values in a specified range
    • IS NULL: null values
    • ORDER BY: Sort
    • AND/OR/NOT can be used in Where
  • Aggregation:
    • GROUP BY: group rows with same values
    • COUNT: count the values in a column
    • SUM: sum the values of a column
    • AVG: average the values
    • MIN: find minimum value
    • MAX: find max value
    • HAVING: filter on conditions
  • Sorting:
    • ORDER BY: sorts the output by the column(s) of interest.
    • default is ascending, use DESC to descend.

Connecting to a database

The format is [database name]<-dbConnect([SQL Interpreter],[connection])

Student (Read only, Campus only) Credentials

db_user <- 'studentUser'
db_password <- 'STAread!'
db_name <- 'Test'
db_table <- 'your_data_table'
db_host <- 'aiken.cs.grinnell.edu' # for local access
db_port <- 3306
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                   dbname = db_name, host = db_host, port = db_port)
#dbDisconnect(mydb)

You should always disconnect when you are done.

Subqueries

dbGetQuery(mydb," SELECT  tab1.Name, tab1.ProfessorDepartment,
                          tab2.CourseName,tab2.CourseDepartment
                  FROM
                      (SELECT a.fname as Name, b.depname as ProfessorDepartment, c.cid
                      FROM week9professors as a 
                      LEFT JOIN week9departments as b ON a.did=b.did
                      LEFT JOIN week9profcourses as c ON a.pid=c.pid)
                    AS tab1
                  LEFT JOIN
                      (Select d.cname as CourseName, e.depname as CourseDepartment, d.cid 
                      FROM week9courses as d
                      LEFT JOIN week9departments as e ON d.did=e.did)
                    AS tab2
                  ON tab1.cid=tab2.cid;")
##             Name ProfessorDepartment                    CourseName
## 1      Elizabeth           Chemistry       General Chemistry w/lab
## 2         Maisha           Chemistry       General Chemistry w/lab
## 3       Rajendra           Chemistry       General Chemistry w/lab
## 4        Stephen           Chemistry       General Chemistry w/lab
## 5           Mark           Chemistry    Analytical Chemistry w/Lab
## 6          Molly           Chemistry    Analytical Chemistry w/Lab
## 7         Andrew           Chemistry     Organic Chemistry I w/lab
## 8      Elizabeth           Chemistry     Organic Chemistry I w/lab
## 9          Erick           Chemistry     Organic Chemistry I w/lab
## 10         James           Chemistry     Organic Chemistry I w/lab
## 11       Stephen           Chemistry     Organic Chemistry I w/lab
## 12         Molly           Chemistry   Instrumental Analysis w/lab
## 13        Corasi           Chemistry    Physical Chemistry I w/lab
## 14        Elaine           Chemistry    Physical Chemistry I w/lab
## 15      Rajendra           Chemistry    Physical Chemistry I w/lab
## 16        Andrew           Chemistry      ST: Adv NMR Spectroscopy
## 17          Leah    Computer Science Functional Prob Solving w/lab
## 18 Peter-Michael    Computer Science Functional Prob Solving w/lab
## 19         Jerod    Computer Science Imperative Prob Solving w/lab
## 20        Nicole    Computer Science Imperative Prob Solving w/lab
## 21        Samuel    Computer Science  OO Prob Slvg, Data Struc/Alg
## 22 Peter-Michael    Computer Science           Discrete Structures
## 23       Charlie    Computer Science    Oper Sys/Paral Algor w/lab
## 24         Jerod    Computer Science               Computer Vision
## 25          Eric    Computer Science        Analysis of Algorithms
## 26       William          Statistics        Analysis of Algorithms
## 27      Fernanda    Computer Science   Software Design & Dev w/Lab
## 28        Nicole    Computer Science    Auto, Frm Lng, Cmp Cmplxty
## 29          Leah    Computer Science  ST: Algorithms, Ethics & Soc
## 30      Abhinaba           Economics     Introduction to Economics
## 31       Bradley           Economics     Introduction to Economics
## 32         Xiang           Economics     Introduction to Economics
## 33       Xinchan           Economics             Money and Banking
## 34         Keith           Economics  Resource & Environ Economics
## 35         Keith           Economics        Microeconomic Analysis
## 36           Thu           Economics        Macroeconomic Analysis
## 37       Xinchan           Economics        Macroeconomic Analysis
## 38      Meredith           Economics                  Econometrics
## 39      Abhinaba           Economics      ST: Behavioral Economics
## 40        Andrea           Economics   Seminar in Health Economics
## 41       Bradley           Economics    Seminar in Law & Economics
## 42         Logan           Economics      Seminar in Econ of Crime
## 43      Meredith           Economics  ST: Time Series Econometrics
## 44           Thu           Economics  ST: Time Series Econometrics
## 45         Xiang           Economics  ST: Time Series Econometrics
## 46         Renee         Mathematics               Math Laboratory
## 47         Royce         Mathematics                    Calculus I
## 48           Joe         Mathematics                    Calculus I
## 49          Marc         Mathematics                    Calculus I
## 50   Christopher         Mathematics                   Calculus II
## 51       Christy         Mathematics                   Calculus II
## 52   Christopher         Mathematics    ST: Intro to Math Practice
## 53 Peter-Michael    Computer Science           Discrete Structures
## 54       Debdeep         Mathematics                Linear Algebra
## 55         Jenny         Mathematics                Linear Algebra
## 56         Royce         Mathematics      Elementary Number Theory
## 57         Jenny         Mathematics      Elementary Number Theory
## 58       Pratima         Mathematics        Differential Equations
## 59       Debdeep         Mathematics            Numerical Analysis
## 60          Marc         Mathematics       Foundations of Analysis
## 61           Joe         Mathematics              Fourier Analysis
## 62       Christy         Mathematics Foundatns of Abstract Algebra
## 63      Jonathan          Statistics    Probability & Statistics I
## 64        Collin          Statistics            Applied Statistics
## 65        Nathan          Statistics            Applied Statistics
## 66       William          Statistics  Introduction to Data Science
## 67          Jeff          Statistics          Statistical Modeling
## 68      Jonathan          Statistics    Probability & Statistics I
##    CourseDepartment
## 1         Chemistry
## 2         Chemistry
## 3         Chemistry
## 4         Chemistry
## 5         Chemistry
## 6         Chemistry
## 7         Chemistry
## 8         Chemistry
## 9         Chemistry
## 10        Chemistry
## 11        Chemistry
## 12        Chemistry
## 13        Chemistry
## 14        Chemistry
## 15        Chemistry
## 16        Chemistry
## 17 Computer Science
## 18 Computer Science
## 19 Computer Science
## 20 Computer Science
## 21 Computer Science
## 22 Computer Science
## 23 Computer Science
## 24 Computer Science
## 25 Computer Science
## 26 Computer Science
## 27 Computer Science
## 28 Computer Science
## 29 Computer Science
## 30        Economics
## 31        Economics
## 32        Economics
## 33        Economics
## 34        Economics
## 35        Economics
## 36        Economics
## 37        Economics
## 38        Economics
## 39        Economics
## 40        Economics
## 41        Economics
## 42        Economics
## 43        Economics
## 44        Economics
## 45        Economics
## 46      Mathematics
## 47      Mathematics
## 48      Mathematics
## 49      Mathematics
## 50      Mathematics
## 51      Mathematics
## 52      Mathematics
## 53      Mathematics
## 54      Mathematics
## 55      Mathematics
## 56      Mathematics
## 57      Mathematics
## 58      Mathematics
## 59      Mathematics
## 60      Mathematics
## 61      Mathematics
## 62      Mathematics
## 63      Mathematics
## 64       Statistics
## 65       Statistics
## 66       Statistics
## 67       Statistics
## 68       Statistics

Question 0 Explain how this query works as best you can. What information does it provide? What problems could you answer from last week using this query?

Lab part 1: Additional Operations

We will be working with only the following five tables for this section:

course_offering ,office_hours, professor_info ,syllabus_info and course.

DISTINCT, COUNT Operators

Question 1: List the name of the professor, the number of courses they have taught over the years and their contact emails from the History department at Grinnell College

##      instructor email num_courses
## 1 Albert Lacson  <NA>          60

HAVING CLAUSE:

Question 2: Using the same SQL query as above, try to list the professor details who have taught more than 20 courses in the timespan

##      instructor num_courses email
## 1 Albert Lacson          60  <NA>

WHERE CLAUSE:

Question 3: Classify the instructors and their office hour timings in terms of day and start time for the semester 2024/FA

##      instructor                                 course_id    day start_time
## 1 Adey Almohsen HIS-100-02__Essays and Writing of History Monday   12:20:00

BETWEEN CLAUSE:

Question 4: Display using SQL the name of professors who have an average number of students enrolled between 20 and 24

##      Professor Mean Max enrolled
## 1 Andrew Hsieh 20.5           28

LIKE Operator

Question 5: Select the Professors whose name starts with either ‘A’ or ‘E’ and display their titles and phone numbers

##       Professor            title        phone
## 1 Adey Almohsen Assist Professor 641-269-9430

Lab part 2: Writing Tables

In this part of the lab, we will work with the data from last week (week9 tables)

Question 6: Using your code from last week, save 4 dataframes with the week 9 tables.

Creating a temporary database in memory

After finishing question 6, run the following command:

dbDisconnect(mydb)
## [1] TRUE
mydb <- dbConnect(RSQLite::SQLite(), ":memory:")

Creating a table

## Create Table
dbExecute(mydb, "CREATE TABLE `profcourses` (
  `pid` int(11) NOT NULL,
  `cid` varchar(6) NOT NULL
)")
## [1] 0
## Fill Table
dbWriteTable(conn = mydb, name = 'profcourses', value = week9profcourses, append = TRUE, header = FALSE, row.names = FALSE)

## Check
dbGetQuery(mydb,"Select * from profcourses LIMIT 5;")
##   pid    cid
## 1  42 MAT131
## 2  42 MAT218
## 3  41 ECN111
## 4  41 ECN295
## 5  40 ECN366

This works as follows: we use dbExecute not dbGetQuery since we are executing a function on the database. We are creating a table called profcourses. It has 2 values, a pid that is an integer with at most 11 characters, and a cid which is a string of at most 6 characters. Neither of them is allowed to be NULL.

Question 7 Using similar queries, create tables for the other 3 dataframes.

Question 8 Investigating Options

  1. What happens if you try to create a table that already exists?
  2. What if append=FALSE?
  3. What if header=TRUE?
  4. what if you rerun the writetable a second time with the original values?
  5. What if you write a table to a “name” table that doesn’t exist?

Disconnecting the SQL connection

dbDisconnect(mydb)