Lab9: SQL Basics

This lab focuses on combining multiple data sources using packages in the tidyverse suite.

Directions (Please read before starting)

Please work together with your assigned partner. Make sure you both fully understand each concept before you move on.
Please record your answers and any related code for all embedded lab questions. I encourage you to try out the embedded examples, but you shouldn’t turn them in.
Please ask for help, clarification, or even just a check-in if anything seems unclear.

\(~\)

Preamble

Packages and Datasets

This lab will primarily use the dplyr and DBI packages.

# load the following packages
library(DBI)
library(odbc)
library(RSQLite)
library(tidyverse)
library(dbplyr)

The lab’s examples will use multiple databases, the one used in the early examples is below.

orders <- read.csv("https://raw.githubusercontent.com/ds4stats/r-tutorials/master/merging/data/orders.csv", as.is = TRUE)
customers <- read.csv("https://raw.githubusercontent.com/ds4stats/r-tutorials/master/merging/data/customers.csv", as.is = TRUE)

Description: A contrived example of a customer database that is small enough to visually inspect.

\(~\)

Databases

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.

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

Example toy database from Tuesday:

print(customers)

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

print(orders)

##   order id   date
## 1     1  4 Jan-01
## 2     2  8 Feb-01
## 3     3 42 Apr-15
## 4     4 50 Apr-17

A primary key is a variable (or set of variables) that uniquely define an observation in it’s own table
- “id” is the primary key in customers as each customer is uniquely identified by the id
- “order” is the primary key in orders as each order is uniquely identified by the order number
A foreign key is a variable (or group of variables) in another table that can be linked to a primary key.
- “id” is a foreign key in the orders data file, as it can be linked to “id” in the customers data file

A primary key and a foreign key combine to form a relation. Relations are used to link information in one data file to another.

\(~\)

Structured Query Language (SQL)

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. In this class we will not worry about how to write, alter, and delete databases and tables. However, I will still include the commands for creating the tables here so that we can see the schema of the tables.

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.

Basic commands

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.

\(~\)

Today we will be working with local, temporary SQL databases in memory. We will learn how to connect to external databases later in the course (if there is time).

We will also be working with SQL commands within the RStudio IDE for ease of access.

To create today’s toy course schedule database in R, run the following command:

#This line creates a temporary local database in memory.
#We will worry about how to connect to external databases later in the course
Lab9DB <- dbConnect(RSQLite::SQLite(), ":memory:")

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

Today we are using the database Lab9DB, SQLite and connecting to memory rather than an external database.

Creating the Tables

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

In both cases the format is generally dbFunction([database],“[SQL command]”). Our toy database today will consist of 4 tables.

#Create the "courses" table
dbExecute(Lab9DB, "CREATE TABLE `courses` (
  `cid` varchar(6) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `did` varchar(3) NOT NULL
)")

## [1] 0

#Create the "departments" table
dbExecute(Lab9DB, "CREATE TABLE `departments` (
  `did` varchar(3) NOT NULL,
  `depname` varchar(50) NOT NULL
)")

## [1] 0

#Create the "profcourses" table
dbExecute(Lab9DB, "CREATE TABLE `profcourses` (
  `pid` int(11) NOT NULL,
  `cid` varchar(6) NOT NULL
)")

## [1] 0

#Create the "professors" table
dbExecute(Lab9DB, "CREATE TABLE `professors` (
  `pid` int(11) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `did` varchar(3) NOT NULL
)")

## [1] 0

At this point we should have a database consisting of 4 empty tables.

dbListTables(Lab9DB)

## [1] "courses"     "departments" "profcourses" "professors"

Filling the tables

In order for these tables to be useful, we need to fill them using the SQL “INSERT INTO” command

#Fill the courses table
dbExecute(Lab9DB, "INSERT INTO `courses` (`cid`, `cname`, `did`) VALUES
('CHM129', 'General Chemistry w/lab', 'CHM'),
('CHM210', 'Analytical Chemistry w/Lab', 'CHM'),
('CHM221', 'Organic Chemistry I w/lab', 'CHM'),
('CHM358', 'Instrumental Analysis w/lab', 'CHM'),
('CHM363', 'Physical Chemistry I w/lab', 'CHM'),
('CHM395', 'ST: Adv NMR Spectroscopy', 'CHM'),
('CSC151', 'Functional Prob Solving w/lab', 'CSC'),
('CSC161', 'Imperative Prob Solving w/lab', 'CSC'),
('CSC207', 'OO Prob Slvg, Data Struc/Alg', 'CSC'),
('CSC208', 'Discrete Structures', 'CSC'),
('CSC213', 'Oper Sys/Paral Algor w/lab', 'CSC'),
('CSC262', 'Computer Vision', 'CSC'),
('CSC301', 'Analysis of Algorithms', 'CSC'),
('CSC324', 'Software Design & Dev w/Lab', 'CSC'),
('CSC341', 'Auto, Frm Lng, Cmp Cmplxty', 'CSC'),
('CSC395', 'ST: Algorithms, Ethics & Soc', 'CSC'),
('ECN111', 'Introduction to Economics', 'ECN'),
('ECN235', 'Money and Banking', 'ECN'),
('ECN240', 'Resource & Environ Economics', 'ECN'),
('ECN280', 'Microeconomic Analysis', 'ECN'),
('ECN282', 'Macroeconomic Analysis', 'ECN'),
('ECN286', 'Econometrics', 'ECN'),
('ECN295', 'ST: Behavioral Economics', 'ECN'),
('ECN366', 'Seminar in Health Economics', 'ECN'),
('ECN378', 'Seminar in Law & Economics', 'ECN'),
('ECN379', 'Seminar in Econ of Crime', 'ECN'),
('ECN395', 'ST: Time Series Econometrics', 'ECN'),
('MAT100', 'Math Laboratory', 'MAT'),
('MAT131', 'Calculus I', 'MAT'),
('MAT133', 'Calculus II', 'MAT'),
('MAT195', 'ST: Intro to Math Practice', 'MAT'),
('MAT208', 'Discrete Structures', 'MAT'),
('MAT215', 'Linear Algebra', 'MAT'),
('MAT218', 'Elementary Number Theory', 'MAT'),
('MAT220', 'Differential Equations', 'MAT'),
('MAT313', 'Numerical Analysis', 'MAT'),
('MAT316', 'Foundations of Analysis', 'MAT'),
('MAT317', 'Fourier Analysis', 'MAT'),
('MAT321', 'Foundatns of Abstract Algebra', 'MAT'),
('MAT335', 'Probability & Statistics I', 'MAT'),
('STA209', 'Applied Statistics', 'STA'),
('STA230', 'Introduction to Data Science', 'STA'),
('STA310', 'Statistical Modeling', 'STA'),
('STA335', 'Probability & Statistics I', 'STA');")

## [1] 44

#Fill the departments table
dbExecute(Lab9DB, "INSERT INTO `departments` (`did`, `depname`) VALUES
('CHM', 'Chemistry'),
('CSC', 'Computer Science'),
('ECN', 'Economics'),
('MAT', 'Mathematics'),
('STA', 'Statistics');")

## [1] 5

#Fill the professors table
dbExecute(Lab9DB, "INSERT INTO `professors` (`pid`, `fname`, `email`, `did`) VALUES
(42, 'Royce', '[Royce]', 'MAT'),
(41, 'Abhinaba', '[Abhinaba]', 'ECN'),
(40, 'Andrea', '[Andrea]', 'ECN'),
(39, 'Andrew', '[Andrew]', 'CHM'),
(38, 'Bradley', '[Bradley]', 'ECN'),
(37, 'Charlie', '[Charlie]', 'CSC'),
(36, 'Christopher', '[Christopher]', 'MAT'),
(35, 'Christy', '[Christy]', 'MAT'),
(34, 'Collin', '[Collin]', 'STA'),
(33, 'Corasi', '[Corasi]', 'CHM'),
(32, 'Debdeep', '[Debdeep]', 'MAT'),
(31, 'Elaine', '[Elaine]', 'CHM'),
(30, 'Elizabeth', '[Elizabeth]', 'CHM'),
(29, 'Eric', '[Eric]', 'CSC'),
(28, 'Erick', '[Erick]', 'CHM'),
(27, 'Fernanda', '[Fernanda]', 'CSC'),
(26, 'James', '[James]', 'CHM'),
(25, 'Jeff', '[Jeff]', 'STA'),
(24, 'Jenny', '[Jenny]', 'MAT'),
(23, 'Jerod', '[Jerod]', 'CSC'),
(22, 'Joe', '[Joe]', 'MAT'),
(21, 'Jonathan', '[Jonathan]', 'STA'),
(20, 'Keith', '[Keith]', 'ECN'),
(19, 'Leah', '[Leah]', 'CSC'),
(18, 'Logan', '[Logan]', 'ECN'),
(17, 'Maisha', '[Maisha]', 'CHM'),
(16, 'Marc', '[Marc]', 'MAT'),
(15, 'Mark', '[Mark]', 'CHM'),
(14, 'Meredith', '[Meredith]', 'ECN'),
(13, 'Molly', '[Molly]', 'CHM'),
(12, 'Nathan', '[Nathan]', 'STA'),
(11, 'Nicole', '[Nicole]', 'CSC'),
(10, 'Peter-Michael', '[Peter-Michael]', 'CSC'),
(9, 'Pratima', '[Pratima]', 'MAT'),
(8, 'Rajendra', '[Rajendra]', 'CHM'),
(7, 'Renee', '[Renee]', 'MAT'),
(6, 'Samuel', '[Samuel]', 'CSC'),
(5, 'Stephen', '[Stephen]', 'CHM'),
(4, 'Thu', '[Thu]', 'ECN'),
(3, 'William', '[William]', 'STA'),
(2, 'Xiang', '[Xiang]', 'ECN'),
(1, 'Xinchan', '[Xinchan]', 'ECN');")

## [1] 42

#Fill the profcourses table
dbExecute(Lab9DB, "INSERT INTO `profcourses` (`pid`, `cid`) VALUES
(42, 'MAT131'),
(42, 'MAT218'),
(41, 'ECN111'),
(41, 'ECN295'),
(40, 'ECN366'),
(39, 'CHM221'),
(39, 'CHM395'),
(38, 'ECN111'),
(38, 'ECN378'),
(37, 'CSC213'),
(36, 'MAT133'),
(36, 'MAT195'),
(35, 'MAT133'),
(35, 'MAT321'),
(34, 'STA209'),
(33, 'CHM363'),
(32, 'MAT215'),
(32, 'MAT313'),
(31, 'CHM363'),
(30, 'CHM129'),
(30, 'CHM221'),
(29, 'CSC301'),
(28, 'CHM221'),
(27, 'CSC324'),
(26, 'CHM221'),
(25, 'STA310'),
(24, 'MAT215'),
(24, 'MAT218'),
(23, 'CSC161'),
(23, 'CSC262'),
(22, 'MAT131'),
(22, 'MAT317'),
(21, 'MAT335'),
(21, 'STA335'),
(20, 'ECN240'),
(20, 'ECN280'),
(19, 'CSC151'),
(19, 'CSC395'),
(18, 'ECN379'),
(17, 'CHM129'),
(16, 'MAT131'),
(16, 'MAT316'),
(15, 'CHM210'),
(14, 'ECN286'),
(14, 'ECN395'),
(13, 'CHM210'),
(13, 'CHM358'),
(12, 'STA209'),
(11, 'CSC161'),
(11, 'CSC341'),
(10, 'CSC151'),
(10, 'CSC208'),
(10, 'MAT208'),
(9, 'MAT220'),
(8, 'CHM129'),
(8, 'CHM363'),
(7, 'MAT100'),
(6, 'CSC207'),
(5, 'CHM129'),
(5, 'CHM221'),
(4, 'ECN282'),
(4, 'ECN395'),
(3, 'STA230'),
(3, 'CSC301'),
(2, 'ECN111'),
(2, 'ECN395'),
(1, 'ECN235'),
(1, 'ECN282');")

## [1] 68

You can see from the output of the dbExecute command that we changed 44, 5, 42, and 68 rows respectively.

If you’ve done everything correctly, you should see 5 departments when you run the below line

dbGetQuery(Lab9DB, "SELECT * FROM 'departments'")

##   did          depname
## 1 CHM        Chemistry
## 2 CSC Computer Science
## 3 ECN        Economics
## 4 MAT      Mathematics
## 5 STA       Statistics

\(~\)

Example queries to access the Tables

Select all values from the professors table where the first name starts with W:

dbGetQuery(Lab9DB, "SELECT * FROM professors
WHERE fname LIKE 'W%';")

##   pid   fname     email did
## 1   3 William [William] STA

Select all values from the professors table where the department id is ‘STA’:

dbGetQuery(Lab9DB, "SELECT * FROM professors
WHERE did = 'STA';")

##   pid    fname      email did
## 1  34   Collin   [Collin] STA
## 2  25     Jeff     [Jeff] STA
## 3  21 Jonathan [Jonathan] STA
## 4  12   Nathan   [Nathan] STA
## 5   3  William  [William] STA

Select all columns and the first 4 rows from the professors table where the department id is ‘STA’:

dbGetQuery(Lab9DB, "SELECT * FROM professors
WHERE did = 'STA'
LIMIT 4;")

##   pid    fname      email did
## 1  34   Collin   [Collin] STA
## 2  25     Jeff     [Jeff] STA
## 3  21 Jonathan [Jonathan] STA
## 4  12   Nathan   [Nathan] STA

Select all values from the professors table where the first name starts with W and the department id is ‘STA’:

dbGetQuery(Lab9DB, "SELECT * FROM professors
WHERE fname like 'W%' and did = 'STA';")

##   pid   fname     email did
## 1   3 William [William] STA

Select distinct department ids from the professor table:

dbGetQuery(Lab9DB, "SELECT DISTINCT did FROM professors;")

##   did
## 1 MAT
## 2 ECN
## 3 CHM
## 4 CSC
## 5 STA

Select all emails and create a new variable ‘ds_dept’ based on the department id from the professors table:

dbGetQuery(Lab9DB, "SELECT 
  email,
  CASE
    WHEN did = 'STA' then 'STATS'
    WHEN did = 'CSC' then 'CompSCI'
    WHEN did = 'ECN' then 'Econ'
    ELSE 'Not'
  END AS ds_dept
FROM professors;")

##              email ds_dept
## 1          [Royce]     Not
## 2       [Abhinaba]    Econ
## 3         [Andrea]    Econ
## 4         [Andrew]     Not
## 5        [Bradley]    Econ
## 6        [Charlie] CompSCI
## 7    [Christopher]     Not
## 8        [Christy]     Not
## 9         [Collin]   STATS
## 10        [Corasi]     Not
## 11       [Debdeep]     Not
## 12        [Elaine]     Not
## 13     [Elizabeth]     Not
## 14          [Eric] CompSCI
## 15         [Erick]     Not
## 16      [Fernanda] CompSCI
## 17         [James]     Not
## 18          [Jeff]   STATS
## 19         [Jenny]     Not
## 20         [Jerod] CompSCI
## 21           [Joe]     Not
## 22      [Jonathan]   STATS
## 23         [Keith]    Econ
## 24          [Leah] CompSCI
## 25         [Logan]    Econ
## 26        [Maisha]     Not
## 27          [Marc]     Not
## 28          [Mark]     Not
## 29      [Meredith]    Econ
## 30         [Molly]     Not
## 31        [Nathan]   STATS
## 32        [Nicole] CompSCI
## 33 [Peter-Michael] CompSCI
## 34       [Pratima]     Not
## 35      [Rajendra]     Not
## 36         [Renee]     Not
## 37        [Samuel] CompSCI
## 38       [Stephen]     Not
## 39           [Thu]    Econ
## 40       [William]   STATS
## 41         [Xiang]    Econ
## 42       [Xinchan]    Econ

Count the number of rows in the professors table where the department id is ‘STA’ or ‘CSC’:

dbGetQuery(Lab9DB, "SELECT COUNT(*) FROM professors
WHERE did IN('STA','CSC');")

##   COUNT(*)
## 1       13

Count the number of rows in the professors table by department id where the department id is ‘STA’ or ‘CSC’, and rename that column to count_profs:

dbGetQuery(Lab9DB, "SELECT did,COUNT(*) as count_profs FROM professors 
WHERE did IN('STA','CSC') 
GROUP BY did;")

##   did count_profs
## 1 CSC           8
## 2 STA           5

Count the number of professors by department in the professors database and call it num_profs. In addition, specify the department with a custom case command:

dbGetQuery(Lab9DB, "SELECT 
  CASE
    WHEN did = 'STA' then 'STATS'
    WHEN did = 'CSC' then 'CompSCI'
    WHEN did = 'ECN' then 'Econ'
    ELSE 'Not'
  END AS ds_dept, COUNT(*) AS num_profs
FROM professors
GROUP BY ds_dept;")

##   ds_dept num_profs
## 1 CompSCI         8
## 2    Econ         9
## 3     Not        20
## 4   STATS         5

Question #1: Write SQL Queries to pull each of the 4 tables from the Database above and save them as dataframes with the same name in R.

Lab

At this point you will begin working with your partner. Please read through the text/examples and make sure you both understand before attempting to answer the embedded questions.

\(~\)

Mutating Joins

The goal of a mutating join is to combine variables from two different data frames, “X” and “Y”.

There are three important types of mutating joins:

Left Outer Join: Keep all observations in “X” and add information from any matching records in “Y”, filling with NA for records in “X” that do not have a match in “Y”.
Full Outer Join: Keep all observations in both “X” and “Y”, filling with NA for records in “X” without a match in “Y” and for records in “Y” without a match in “X”
Inner Join: Keep only observations with records in both “X” and “Y”, dropping any records that aren’t present in both data frames.

In most circumstances you can expect to use a left outer join, as this approach will preserve all of the records in “X” and attach additional variables from “Y”. The different mutating joins are summarized in the graphic in lab 8.

\(~\)

Left Joins

Consider the data frames “departments” and “courses”:

Notice that the variable did is the primary key in departments corresponding to the foreign key did in courses.

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.

#SQL

dbGetQuery(Lab9DB, "SELECT * FROM departments
LEFT JOIN courses ON departments.did = courses.did
LIMIT 5;")

##   did   depname    cid                       cname did
## 1 CHM Chemistry CHM129     General Chemistry w/lab CHM
## 2 CHM Chemistry CHM210  Analytical Chemistry w/Lab CHM
## 3 CHM Chemistry CHM221   Organic Chemistry I w/lab CHM
## 4 CHM Chemistry CHM358 Instrumental Analysis w/lab CHM
## 5 CHM Chemistry CHM363  Physical Chemistry I w/lab CHM

We should see the same data when we run the left_join from Tuesday in R

left_join(x = departments, y = courses, by = "did")%>%head(5)

##   did   depname    cid                       cname
## 1 CHM Chemistry CHM129     General Chemistry w/lab
## 2 CHM Chemistry CHM210  Analytical Chemistry w/Lab
## 3 CHM Chemistry CHM221   Organic Chemistry I w/lab
## 4 CHM Chemistry CHM358 Instrumental Analysis w/lab
## 5 CHM Chemistry CHM363  Physical Chemistry I w/lab

You can see that the joins are the same, however there is a duplicate did column in the SQL command. This is due to the SELECT * which tells SQL to keep all columns. Since we only want one of the columns, the below is a better join to run

dbGetQuery(Lab9DB, "SELECT departments.did, depname, cid, cname FROM departments
LEFT JOIN courses ON departments.did = courses.did
LIMIT 5;")

##   did   depname    cid                       cname
## 1 CHM Chemistry CHM129     General Chemistry w/lab
## 2 CHM Chemistry CHM210  Analytical Chemistry w/Lab
## 3 CHM Chemistry CHM221   Organic Chemistry I w/lab
## 4 CHM Chemistry CHM358 Instrumental Analysis w/lab
## 5 CHM Chemistry CHM363  Physical Chemistry I w/lab

Question #2: Write a left join in SQL to add the department name to the data in the professors table and return the first 5 values. Compare the results to doing the same thing in R. Are your resulting tables the same? If so, why. If not, why not?

\(~\)

Other Joins

As in R, SQL has all of the common Joins:

INNER JOIN: perform an inner join LEFT JOIN: perform a left join RIGHT JOIN: perform a right join, rarely used FULL OUTER JOIN: perform a full outer join

It also has two other joins which are useful at times: CROSS JOIN: retrieve all possible combinations of records SELF JOIN: join a table to itself. Normally done using a select where rather than the SELF JOIN command. For example if you had a table with the following schema: employees (employee_id: PRIMARY KEY INT, firstname: VARCHAR(50), manager_id INT)

You could figure out the name of every employee’s manager as follows:

SELECT e1.firstname, e2.firstname FROM employees e1, employees e2 WHERE e1.employee_id=e2.manager_id

Practice (required)

Question #3: 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 #4: 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 match the output below

##     depname COUNT(*)
## 1 Chemistry       11

\(~\)

Question #5: Using either R or SQL create a table consisting of only pid, cname, and depname. Use either R or SQL, not a combination of both

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 #6: Repeat Question 5 with the other system (i.e. if you used SQl in Question 5, use R in Question 6)

Lab9: SQL Basics

2024-09-26

Preamble

Packages and Datasets

Databases

Relational Databases

Example toy database from Tuesday:

Structured Query Language (SQL)

Conventions

Using SQL

Basic Commands

Creating the Tables

Filling the tables

Example queries to access the Tables

Lab

Mutating Joins

Left Joins

Other Joins

Practice (required)