R and SQL (Part 1)

Running the queries in SQL

There are least two ways you can perform SQL queries on this sample database:

1. Type queries straight into the browser using the TryIt tool provided at mysqltutorial.org; or

2. Load the sample database into MySQL or another database management system. Instructions on how to install MySQL can be found here. Instructions on how to load in the sample database can be found here (just substitute the SQL file referred to the video with the one being used in this exercise). The SQL file for the ‘classicmodel’ database can be downloaded from mysqltutorial.org.

Running the code in R

We will be looking at equivalent R code using the dplyr package. This includes the use of the pipe operator, %>%, which allows you to chain code together in a series of logical steps. We can load the dplyr package as part of the tidyverse package:

library(tidyverse)

When running the code in R, we will create a data frame that corresponds to each table in the relational database. These tables can be downloaded as individual .csv files here. Load the data into R using the following code:

# Enter the file path to where the files are saved on your system
filepath <- 'tables/'     

# Read in the tables
customers    <- read_csv(paste0(filepath,'customers.csv'), na = "NULL")
employees    <- read_csv(paste0(filepath,'employees.csv'), na = "NULL")
offices      <- read_csv(paste0(filepath,'offices.csv'), na = "NULL")
orderdetails <- read_csv(paste0(filepath,'orderdetails.csv'), na = "NULL")
orders       <- read_csv(paste0(filepath,'orders.csv'), na = "NULL")
payments     <- read_csv(paste0(filepath,'payments.csv'), na = "NULL")
productlines <- read_csv(paste0(filepath,'productlines.csv'), na = "NULL")
products     <- read_csv(paste0(filepath,'products.csv'),  
               # prevent the model scale (e.g. 1:10) from being read as a date:
               col_types = cols (productScale = col_character()), na = "NULL")

Now we’re ready to start running SQL queries and the equivalent code in R!
(Top)

1.1 Select all columns

The most simple select statement is one that returns all the information from a table.

With SQL:

To get data from a table in SQL we use the SELECT statement, to list the columns we are interested in, and FROM, to specify the table. If we want all records from a table, we use SELECT *. For example, to see all the records on the employees table we would use:

SELECT * 
FROM employees

With R:

To get the same results in R, we simply enter the name of table, as follows:

employees

This gives us the names and details of the 23 employees in the database:

(Top)

1.2 Select specific columns

What if you only want a subset of columns from a given table, such as employees’ first and last names?

With SQL:

In SQL you just list the column of interest in the SELECT clause as follows:

SELECT 
  firstName, lastName 
FROM 
  employees

With R:

The equivalent R code uses the select() function:

employees %>% 
  select(firstName, lastName)

Note for those learning R, it might be helpful to read the %>% operator as “and then”.
(Top)

1.3 Select distinct records

Say we want a list of distinct countries in which we have customers, i.e. without returning the same country name twice.

With SQL:

In SQL we would use SELECT DISTINCT clause, as follows:

SELECT 
  DISTINCT country 
FROM customers

With R:

In R, we get the same result using the distinct() function:

customers %>% 
   distinct(country) 

1.4 Select distinct records with multiple columns

A similar approach can be used when you want to see distinct combinations of two or more variables / columns.

For instance, say you want a list of all US cities in which the company has customers. We can’t simply look at distinct city names, as there can be more than one city with the same name: for example, in our database we have a ‘Glendale’ in California and a ‘Glendale’ in Connecticut.

To address this we specify both city and state in our query, as follows (I’m jumping forward here and also adding a WHERE clause, to restrict our results to cities in the USA):

With SQL:

SELECT 
  DISTINCT city,state 
FROM 
  customers
WHERE 
  country = 'USA' 

With R:

The equivalent R code uses filter() instead of SQL’s WHERE, as will be discussed later:

customers %>% 
  filter(country == 'USA') %>%  # ignore this for now!
  distinct(city, state) 

Both approaches return 24 rows containing distinct combinations of cities and states.

1.5 Create new columns

What if we want to create a new column, using the information contained in existing columns?

For example: The orderdetails table contains information on which products were included in each order (productCode), the quantity of each product (quantityOrdered) and the price per unit (priceEach). We could use this information to calculate the value of each product that was ordered.

With SQL:

In SQL, within the SELECT clause, we can calculate our new column and as assign it a name (‘alias’) using the AS keyword, as follows:

SELECT 
  orderNumber, 
  productCode,
  quantityOrdered * priceEach AS value
FROM orderdetails

With R:

In R we would acheive the same results using the mutate function to create our new column:

orderdetails %>% 
  mutate(value = quantityOrdered * priceEach) %>% 
  select(orderNumber, productCode, value)

The two sets of code return 2,996 records that show the value of each product in every order. The first 20 rows are displayed in the table below:

2.1 Basic sorting

Data can be sorted using the ORDER BY clause in SQL and the arrange() function in R. For example, say you wanted to sort customer locations in the USA alphabetically, first by state and then by city:

With SQL:

SELECT 
  DISTINCT state, city 
FROM 
  customers
WHERE 
  country = 'USA'
ORDER BY 
  state, city

With R:

customers %>% 
  filter(country == 'USA') %>% 
  distinct(state, city) %>% 
  arrange(state, city)

(Top)

2.2 Sort descending

What if you wanted to list states in reverse alphabetical order, while listing cities in alphabetical order?

With SQL:

In SQL, you can use ORDER BY .... DESC. Any columns listed before the DESC clause will be listed in descending order.

SELECT 
  DISTINCT state, city 
FROM 
  customers
WHERE 
  country = 'USA'
ORDER BY 
  state DESC, city

With R:

Equivalently, in R, you can use arrange(desc()), where the parentheses include the name of the column(s) you want listed in descending order:

customers %>% 
  filter(country == 'USA') %>% 
  distinct(state, city) %>% 
  arrange(desc(state), city) 

(Top)

2.3 Limiting the number of results

To limit the number of results that are shown, you can use SQL’s LIMIT clause and R’s slice() function. These can be combined with ordered data to return the top or bottom results from a table.

For example, if you wanted to return the five customers with the highest credit limits you could use the following:

With SQL:

SELECT 
  customerName, creditLimit 
FROM 
  customers
ORDER BY 
  creditLimit DESC
LIMIT 5

With R:

customers %>% 
  select(customerName, creditLimit) %>% 
  arrange(desc(creditLimit)) %>% 
  slice(1:5)

Another option in R would be to use the top_n() function, as described here.

(Top)

3.1 Basic filtering

Filtering is used to restrict our results to records / rows that satisfy one or more criteria. We already saw some basic filtering in the above, in which we used SQL’s WHERE clause and R’s filter() to only shows cities that were within the United States.

Another example would be to find the names of each employee whose job title is ‘Sales Rep’:

With SQL:

SELECT 
  lastName, firstName, jobTitle
FROM 
  employees
WHERE 
  jobTitle = 'Sales Rep'

With R:

employees %>% 
  filter(jobTitle == 'Sales Rep') %>% 
  select(firstName,lastName,jobTitle)

Both sets of code return the names of the 17 sales reps:

There are a couple of differences to note between SQL and R:

• R uses == instead of = when filtering
• In R, it is often necessary to use filter() before select(). This would be the case if the column you are filtering on (in this case ‘jobTitle’) was not also included in the select() function below. In SQL, this order is not an issue
• In R, the filter condition is case sensitive, but in SQL it is not
  (Top)

3.2 Filtering based on inequalities

We can use the ‘not equal to’ operator != to filter records that do not match a certain criteria.

For example, to see the names of the six employees who are not sales reps:

With SQL:

SELECT 
  lastName, firstName, jobTitle
FROM 
  employees
WHERE 
  jobTitle != 'Sales Rep'

MySQL would also accept <> as the operator in this case.

With R:

employees %>% 
  filter(jobTitle != 'Sales Rep') %>% 
  select(lastName, firstName, jobTitle)

If you are dealing with numeric data, you can also filter using the operators >, >=, < and <=, which are the same in both languages.
(Top)

3.3 Filtering with AND conditions

Sometime we want to select rows where multiple criteria all hold true. This can be done using AND operator in SQL, and the & operator in R.

For example, if we wanted to see the names of customers who are located in California and who have a credit limit over $100,000:

With SQL:

SELECT 
  customername, state, creditlimit 
FROM 
  customers 
WHERE 
  state = 'CA' AND creditlimit > 100000

With R:

customers %>% 
  filter(state == 'CA' & creditLimit > 100000) %>% 
  select(customerName, state, creditLimit)

These sets of code return the three customers who are both located in California and have credit limits over $100,000:

(Top)

3.4 Filtering with OR conditions

The OR condition returns rows where any of our criteria are satisfied. In SQL we use the OR operator, while in R the | symbol is used.

For example, say we want to see a list of all customers who are located in the US or France:

With SQL:

SELECT 
  customerName, country
FROM 
  customers
WHERE 
  country = 'USA' OR country =  'FRANCE'

With R:

customers %>% 
  filter (country == 'USA' | country == 'France') %>% 
  select(customerName, country)

This gives us the 48 customers who are located in either USA or France:

(Top)

3.5 Filtering with both OR, AND conditions

The examples above can be extended to combine the OR and AND conditions.

For example, if you wanted a list of customers with a credit limit of over $100,000 who are located in either the USA or France:

With SQL:

SELECT 
  customerName, country, creditLimit
FROM 
  customers
WHERE 
  (country = 'USA' OR country =  'FRANCE') AND creditLimit > 100000

With R:

customers %>% 
  filter(
    (country == 'USA' | country == 'France') & creditLimit > 100000
    ) %>% 
  select(customerName, country, creditLimit)

The code above returns the 11 customers who are located in the USA or France, and who have credit limits over $100,000.

By default both SQL and R evaluate the AND operator before the OR operator. This is why it was necessary to put the OR condition in parentheses above, to tell SQL/R to evaluate it first.

If we hadn’t used parentheses, the code would have returned the list of customers who either (a) were located in the USA, or (b) were located in France and had a credit limit greater than $100,000. This would have returned the following 38 customers:

(Top)

3.6 The IN operator

In SQL, the IN clause is used select records where a value matches any value in a list or a subquery (we’ll look at subqueries later). The equivalent operator in R is %in%.

When used on a list, the IN clause acts just like one or more OR conditions. For example, say we want a list of customers located in either France, USA or Australia:

With SQL:

SELECT 
  customerName, country
FROM 
  customers
WHERE 
  country IN ('France', 'USA', 'Australia')

With R:

With R, we need to combine the countries into a list using c(...):

customers %>% 
  filter(country %in% c('France','USA','Australia')) %>% 
  select(customerName, country)

(Top)

3.7 The NOT IN operator

If we are interested in values are not in a list - for example, all customers who are not located in France, USA or Australia?

With SQL:

In SQL we can use the NOT IN operator:

SELECT 
  customerName, country
FROM 
  customers
WHERE 
  country NOT IN ('France','USA', 'Australia')

With R:

In R, we negate the %in% operator by placing a ! at the start of the filter function, as follows:

customers %>% 
  filter(!country %in% c('France','USA','Australia')) %>% 
  select(customerName, country)

Each approach gives us 69 customers who are located in countries other than France, USA or Australia.
(Top)

3.8 Filtering with regular expressions

You can filter your results based on whether a column contains characters that follow a specified pattern. For example, you may want to find names that start with the letter ‘T’, or phone numbers that include the area code ‘214’.

SQL offers a user-friendly way to do this using the LIKE operator, which is explained here. However, we are instead going to look at regular expressions, which are more flexible than SQL’s LIKE operator (though less user-friendly) and are consistent between SQL and R.

To use regular expressions in SQL we use the REGEXP operator, and in R we use the str_detect() (‘string detect’) function.

To see how this works, say we want to find all products with the word ‘Ford’ in them. We could use the following code:

With SQL:

SELECT 
  productName
FROM 
  products
WHERE 
  productName REGEXP 'Ford'

With R:

products %>% 
  filter(str_detect(productName,'Ford')) %>% 
  select(productName)

These sets of code return a list of 15 models with that include the word ‘Ford’:

The main difference in the code above is that SQL is not case sensitive but R is. You can specify case sensitivity as follows:

• To make SQL case sensitive, replace REGEXP with REGEXP BINARY
• To make R ignore cases, replace str_detect(productName,'Ford') in the example above with str_detect(productName, regex('ford', ignore_case=TRUE))

The beauty of SQL’s REGEXP and R’s str_detect() is that they will both work with many of the same regular expressions. You can modify both sets of code above to see the following:

• Use the ^ symbol to match the beginning of a string. For example, use '^America' to show products beginning with the words ‘America’ or ‘American’.
• Use $ to denote the end of a string. For example, use 'ter$' to return the names of all products that end in these three letters, such as ‘helicopter’ and ‘roadster’.
• Use '[...]' to match any of the strings listed within the brackets, which are separated with |. For example, use '^[A|B|C]' to match words beginning with either A, B, or C.

We won’t go into the details of regular expressions here, but a further explanation can be found at mysqltutorial.org.

Part 2 of this post, to follow, will look at joins, using grouped data, and subqueries.