This lab focuses on the basics of SQL:

Reading tables
Joining Tables

Directions for all labs (read before starting)

Please work together with your assigned partner. Make sure you both fully understand something before moving on.
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.
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:

One partner is the driver, who physically writes code and operates the computer
One partner is the navigator, who reviews the actions of the driver and provides feedback and guidance
Partners are encouraged to switch roles throughout the “Lab” section.

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

Basics of SQL

Relational Databases (Review of week 5)

So far we’ve only dealt with data that could be stored in a single spreadsheet; however, many real world applications require more flexibility.

Data are often stored in databases, or organized collections of structured information (data). Most databases are a collection of tables of data like those we’ve been working with so far in class. For this class, and most applications in general, we will work with Relational Databases.

The essential component of a Relational Database is its relational structure. A Relational Database consists of a number of tables. Each Table consists of columns of attributes and unique rows of information. Tables may have a “Primary Key” which is a unique identifier. They may also have “Foreign Keys” which are keys that can be used to match to other tables. It is best practice that the first column be the Primary Key if it exists.

The combination of a Primary and Foreign Key is a relation and where the name comes from. The tables can be related to each other to store, access, and modify data more efficiently.

For more information on databases, check out: https://www.oracle.com/database/what-is-database

Structured Query Language

https://dept.stat.lsa.umich.edu/~jerrick/courses/stat701/notes/sql.html

When working with databases, we normally use Structured Query Language or SQL. There are a number of different implementations, but they all share the standard commands.

Conventions

By convention all SQL syntax is written in UPPER CASE and variables are written in lower case. However it is truly case insensitive, unlike R which is case sensitive.

Using SQL

Most of the time SQL is used to read data from a database. This week, we will not worry about how to write, alter, or delete databases and tables. We will however do so next week in order to make the project feasible. However, I will still include the commands in the summary today.

Basic Commands

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.

General SQL notes

In my experience, more than 90% of joins that you will do are left joins.
As a data scientist, unless you are maintaining the database, you will rarely, if ever, use SQL by itself. You will normally use it as part of a workflow in other languages (e.g. R, Python).

For these (and other reasons) I will be teaching you how to utilize SQL through R packages.

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.

Most common functions

When using SQL in R using the DBI package, there are two commands that you will use most often:

dbExecute: to change the table
dbGetQuery: to read from the table
(dbListTables): figure out what tables exist in the database

In both cases the format is generally dbFunction([database],“[SQL command]”)

Examples

If we want to see what Tables are in the database:

dbListTables(mydb)

##  [1] "course"           "course_offering"  "customers"        "office_hours"    
##  [5] "orders"           "professor_info"   "syllabus_info"    "week9courses"    
##  [9] "week9departments" "week9profcourses" "week9professors"

Caveat: This is not necessarily the best designed database since there are many practice tables in here for you all to work with.

Toy example of customers and orders:

Read everything from the tables

dbGetQuery(mydb, "SELECT * FROM customers;")

##   id    name
## 1  4   Tukey
## 2  8 Wickham
## 3 15   Mason
## 4 16  Jordan
## 5 23   Patil
## 6 42     Cox

dbGetQuery(mydb, "SELECT * FROM orders;")

##   order id   date
## 1     1  4 Jan-01
## 2     2  8 Feb-01
## 3     3 42 Apr-15
## 4     4 50 Apr-17
## 5    11  4 Apr-18
## 6    12  8 Feb-11
## 7    13 42 Jul-15
## 8    14 50 Jun-17

dbGetQuery(mydb, "SELECT date, id FROM orders;")

##     date id
## 1 Jan-01  4
## 2 Feb-01  8
## 3 Apr-15 42
## 4 Apr-17 50
## 5 Apr-18  4
## 6 Feb-11  8
## 7 Jul-15 42
## 8 Jun-17 50

dbGetQuery(mydb, "SELECT date, id as CustomerID FROM orders WHERE id<10;")

##     date CustomerID
## 1 Jan-01          4
## 2 Feb-01          8
## 3 Apr-18          4
## 4 Feb-11          8

Joins

The LEFT JOIN function is used to perform a left outer join. Note that SQL expects you to specify which keys you are comparing. This allows you to use different keys at times.

While we will mostly use left joins, SQL does have the other joins as well.

# Left join with limit
dbGetQuery(mydb, "SELECT * FROM orders as a
LEFT JOIN customers as b ON a.id = b.id
LIMIT 5;")

##   order id   date id    name
## 1     1  4 Jan-01  4   Tukey
## 2    11  4 Apr-18  4   Tukey
## 3     2  8 Feb-01  8 Wickham
## 4    12  8 Feb-11  8 Wickham
## 5     3 42 Apr-15 42     Cox

# Left join without limit
dbGetQuery(mydb, "SELECT * FROM orders as a
LEFT JOIN customers as b ON a.id = b.id;")

##   order id   date id    name
## 1     1  4 Jan-01  4   Tukey
## 2    11  4 Apr-18  4   Tukey
## 3     2  8 Feb-01  8 Wickham
## 4    12  8 Feb-11  8 Wickham
## 5     3 42 Apr-15 42     Cox
## 6    13 42 Jul-15 42     Cox
## 7     4 50 Apr-17 NA    <NA>
## 8    14 50 Jun-17 NA    <NA>

# Right join without limit
dbGetQuery(mydb, "SELECT * FROM orders as a
RIGHT JOIN customers as b ON a.id = b.id;")

##   order id   date id    name
## 1     1  4 Jan-01  4   Tukey
## 2     2  8 Feb-01  8 Wickham
## 3     3 42 Apr-15 42     Cox
## 4    11  4 Apr-18  4   Tukey
## 5    12  8 Feb-11  8 Wickham
## 6    13 42 Jul-15 42     Cox
## 7    NA NA   <NA> 15   Mason
## 8    NA NA   <NA> 16  Jordan
## 9    NA NA   <NA> 23   Patil

# Left join without limit
dbGetQuery(mydb, "SELECT * FROM customers as a
LEFT JOIN orders as b ON a.id = b.id;")

##   id    name order id   date
## 1  4   Tukey     1  4 Jan-01
## 2  8 Wickham     2  8 Feb-01
## 3 42     Cox     3 42 Apr-15
## 4  4   Tukey    11  4 Apr-18
## 5  8 Wickham    12  8 Feb-11
## 6 42     Cox    13 42 Jul-15
## 7 15   Mason    NA NA   <NA>
## 8 16  Jordan    NA NA   <NA>
## 9 23   Patil    NA NA   <NA>

# Inner join without limit
dbGetQuery(mydb, "SELECT * FROM customers as a
INNER JOIN orders as b ON a.id = b.id;")

##   id    name order id   date
## 1  4   Tukey     1  4 Jan-01
## 2  8 Wickham     2  8 Feb-01
## 3 42     Cox     3 42 Apr-15
## 4  4   Tukey    11  4 Apr-18
## 5  8 Wickham    12  8 Feb-11
## 6 42     Cox    13 42 Jul-15

# Cross Join: all possible combinations
dbGetQuery(mydb, "SELECT * FROM customers as a
CROSS JOIN orders as b ON a.id = b.id;")

##   id    name order id   date
## 1  4   Tukey     1  4 Jan-01
## 2  8 Wickham     2  8 Feb-01
## 3 42     Cox     3 42 Apr-15
## 4  4   Tukey    11  4 Apr-18
## 5  8 Wickham    12  8 Feb-11
## 6 42     Cox    13 42 Jul-15

#original join
dbGetQuery(mydb, "SELECT * FROM orders as a
LEFT JOIN customers as b ON a.id = b.id
LIMIT 5;")

##   order id   date id    name
## 1     1  4 Jan-01  4   Tukey
## 2    11  4 Apr-18  4   Tukey
## 3     2  8 Feb-01  8 Wickham
## 4    12  8 Feb-11  8 Wickham
## 5     3 42 Apr-15 42     Cox

#better join
dbGetQuery(mydb, "SELECT a.*, b.name FROM orders as a
LEFT JOIN customers as b ON a.id = b.id
ORDER BY name DESC
LIMIT 5;")

##   order id   date    name
## 1     2  8 Feb-01 Wickham
## 2    12  8 Feb-11 Wickham
## 3    11  4 Apr-18   Tukey
## 4     1  4 Jan-01   Tukey
## 5     3 42 Apr-15     Cox

Lab

At this point you will be working on the lab with your partner(s). We will be using the “week9…” tables today.

Basics

Question 1 Make sure that you have connected to the database correctly by running the following code and comparing the output

dbListTables(mydb)

##  [1] "course"           "course_offering"  "customers"        "office_hours"    
##  [5] "orders"           "professor_info"   "syllabus_info"    "week9courses"    
##  [9] "week9departments" "week9profcourses" "week9professors"

Question 2* Write queries to return the first 3 rows of the 4 tables that start week9. Describe the tables and what you think these represent.

\(~\)

You can also save the results of the query to an R dataframe. For example:

exampleQuery<-dbGetQuery(mydb, "SELECT a.*, b.name FROM orders as a
LEFT JOIN customers as b ON a.id = b.id
ORDER BY name DESC
LIMIT 5;")
print(exampleQuery)

##   order id   date    name
## 1     2  8 Feb-01 Wickham
## 2    12  8 Feb-11 Wickham
## 3    11  4 Apr-18   Tukey
## 4     1  4 Jan-01   Tukey
## 5     3 42 Apr-15     Cox

Question 3 Write queries to pull the week9 tables and store them as R dataframes with the same name.

Joins

Question 4

Part A: Write a left join in SQL to add the department name to the data in the professors table and return the first 5 values.
Part B: Do the same thing using R (dplyr) commands with the tables saved in Q3.
Part C: Are the resulting tables the same? If so, why? If not, what differences are there?

Question 5 Using the tables imported into R determine the number of faculty in each department by department ID

Part A: Create a table containing only professor ids and department ids. I have included what the first 5 lines should look like.

##   pid did
## 1  42 MAT
## 2  41 ECN
## 3  40 ECN
## 4  39 CHM
## 5  38 ECN

Part B: Create a table containing only the number of faculty in each department by department id.Your answer should match the table below

##   did COUNT(*)
## 1 CHM       11
## 2 CSC        8
## 3 ECN        9
## 4 MAT        9
## 5 STA        5

Question 6 Write a SQL query to determine the number of faculty in each department by department name. Use the corresponding parts of Question 3 to make sure your query works.

Part A: Write a SQL query to get a table containing only professor ids and department ids.
Part B: Write a SQL query to get a table containing only professor ids and the name of the department they are in.
Part C: Write a SQL query to determine the number of faculty in each department by department id.
Part D: Put it all together to write a SQL query to determine the number of faculty in each department by department name. The first line of the table should say “Chemistry 11”

R vs SQL

Question 7 Using R create a table consisting of only pid, cname, and depname.

Part A: First create a table by left joining professors and profcourses
Part B: create a table by left joining the table from Part A and courses. At this point you should be able to match pid and cname.
Part C: Create a table by left joining the table from Part B and departments using the FOREIGN KEY from professors. Keep only the three columns we care about. Sort the Dataset by depname (ascending), then pid (descending), then cname (ascending).
Part D: Create a table by left joining the table from Part B and departments using the FOREIGN KEY from courses. Keep only the three columns we care about. Sort the Dataset by depname (ascending), then pid (descending), then cname (ascending).
Part E: Are these tables the same? If so prove it. If not, filter the data to the pid corresponding to Professor Rebelsky (3). Which courses are different? Which table is “Correct”? Justify your answer.

Question 8 Repeat part 7 using SQL. Do you get the same results in each part?

Question 9 Using SQL return the table consisting of professors with id < 15

Question 10 Using SQL return the table consisting of departments (did, depname) that start with the letter C

Question 11 Using your answer to Q10, and in a single query, how many professors are in those departments?

Bonus Question! (not graded)

Question 12 Putting it all together!

In a single query, recreate the following table.

##             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

Using your answer to part A expand the query to match the following table

##             Name ProfessorDepartment                    CourseName
## 1        Xinchan           Economics        Macroeconomic Analysis
## 2        Xinchan           Economics             Money and Banking
## 3          Xiang           Economics     Introduction to Economics
## 4          Xiang           Economics  ST: Time Series Econometrics
## 5        William          Statistics        Analysis of Algorithms
## 6        William          Statistics  Introduction to Data Science
## 7            Thu           Economics        Macroeconomic Analysis
## 8            Thu           Economics  ST: Time Series Econometrics
## 9        Stephen           Chemistry       General Chemistry w/lab
## 10       Stephen           Chemistry     Organic Chemistry I w/lab
## 11        Samuel    Computer Science  OO Prob Slvg, Data Struc/Alg
## 12         Renee         Mathematics               Math Laboratory
## 13      Rajendra           Chemistry    Physical Chemistry I w/lab
## 14      Rajendra           Chemistry       General Chemistry w/lab
## 15       Pratima         Mathematics        Differential Equations
## 16 Peter-Michael    Computer Science           Discrete Structures
## 17 Peter-Michael    Computer Science           Discrete Structures
## 18 Peter-Michael    Computer Science Functional Prob Solving w/lab
## 19        Nicole    Computer Science Imperative Prob Solving w/lab
## 20        Nicole    Computer Science    Auto, Frm Lng, Cmp Cmplxty
## 21        Nathan          Statistics            Applied Statistics
## 22         Molly           Chemistry    Analytical Chemistry w/Lab
## 23         Molly           Chemistry   Instrumental Analysis w/lab
## 24      Meredith           Economics                  Econometrics
## 25      Meredith           Economics  ST: Time Series Econometrics
##    CourseDepartment
## 1         Economics
## 2         Economics
## 3         Economics
## 4         Economics
## 5  Computer Science
## 6        Statistics
## 7         Economics
## 8         Economics
## 9         Chemistry
## 10        Chemistry
## 11 Computer Science
## 12      Mathematics
## 13        Chemistry
## 14        Chemistry
## 15      Mathematics
## 16      Mathematics
## 17 Computer Science
## 18 Computer Science
## 19 Computer Science
## 20 Computer Science
## 21       Statistics
## 22        Chemistry
## 23        Chemistry
## 24        Economics
## 25        Economics

If time only (not graded)

Investigate the other tables in the database. What do you think we will use them for?
Practice with other commands from SQL, which ones seem most complicated?

dbDisconnect(mydb)

## [1] TRUE

Week 9: Introduction to SQL

William Rebelsky

2025-04-01

Directions for all labs (read before starting)

Preamble

Packages

Basics of SQL

Relational Databases (Review of week 5)

Structured Query Language

Conventions

Using SQL

Basic Commands

General SQL notes

Connecting to a database

Most common functions

Examples

Toy example of customers and orders:

Joins

Lab

Basics

Joins

R vs SQL

Bonus Question! (not graded)

If time only (not graded)