toyeiei

# install tidyverse
install.packages("tidyverse")
library(tidyverse)

# step 1 -- review example dataset 'msleep' in tidyverse package
glimpse(msleep)
summary(msleep)

# step 2.1 -- remove all rows with missing values
clean_msleep <- drop_na(msleep)

toyeiei

-- SELECT clause to select specific columns
-- Select all columns from customers table
SELECT * FROM customers;

-- Select firstname, lastname and address columns from customers table
SELECT firstname, lastname, address FROM customers;

-- Use AS (alias) to change the column name
SELECT 
  firstname AS customer_firstname, 

toyeiei

# install package in R
install.packages("dplyr")
library(dplyr)

# useful functions to analyze dataframe
# review dataframe structure
glimpse(mtcars)

# print first six and last six rows
head(mtcars)

toyeiei

# dplyr five verbs
# select columns mpg, hp, wt, cyl from mtcars dataframe
select(mtcars, mpg, hp, wt, cyl)

# select column 1 to 5, 8 and 9
select(1:5, 8, 9)

# remove column 9 and 10
select(-9, -10)

toyeiei

-- select all columns from table customers
SELECT * FROM customers;

-- select column firstname, lastname, email, phone from customers
SELECT 
  firstname,
  lastname,
  email,
  phone
FROM customers;

toyeiei

-- JOIN two tables: album and artist
SELECT * 
FROM artists
JOIN albums 
  ON artists.artistid = albums.artistid;

toyeiei

-- JOIN more than two tables
SELECT
  artists.name,
  albums.title,
  tracks.name
FROM artists
JOIN albums
  ON artists.artistid = albums.artistid
JOIN tracks
  ON tracks.albumid = albums.albumid

toyeiei

-- where clause used to filter rows
-- filter only customers from USA
SELECT *
FROM customers
WHERE country = 'USA';

toyeiei

-- filter customers from USA who also live in CA state
SELECT *
FROM customers
WHERE country = 'USA' AND state = 'CA';

-- filter customers from USA or Brazil
SELECT *
FROM customers
WHERE country = 'USA' OR country = 'Brazil';

toyeiei

-- count number of rows in table customers
SELECT COUNT(*)
FROM customers;