library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
# Dataset 1: Customers
customers <- tibble(
  customer_id = c(1, 2, 3, 4, 5),
  name = c("Alice", "Bob", "Charlie", "David", "Eve"),
  city = c("New York", "Los Angeles", "Chicago", "Houston", "Phoenix")
)

# Dataset 2: Orders
orders <- tibble(
  order_id = c(101, 102, 103, 104, 105, 106),
  customer_id = c(1, 2, 3, 2, 6, 7),
  product = c("Laptop", "Phone", "Tablet", "Desktop", "Camera", "Printer"),
  amount = c(1200, 800, 300, 1500, 600, 150)
)

1. Inner Join (3 points) Perform an inner join between the customers and orders datasets.

q1 <- inner_join(customers , orders)
## Joining with `by = join_by(customer_id)`

A) How many rows are in the result?

There are 4 rows.

B) Why are some customers or orders not included in the result?

Some customers and orders are not shown because they do not have a match in both tables.

C) Display the result

head(q1)
## # A tibble: 4 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300

2. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table.

q2 <- left_join(customers,orders, by = "customer_id")

A) How many rows are in the result?

There are 6 rows.

B) Explain why this number differs from the inner join result.

This method looks at the left table and matches all right table data points to create a new table.

C) Display the result

print(q2)
## # A tibble: 6 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA

3. Right Join (3 points) Perform a right join with customers as the left table and orders as the right table.

q3 <- right_join(customers, orders, by = "customer_id")

a.How many rows are in the result?

There are 6 rows.

b.Which customer_ids in the result have NULL for customer name and city? Explain why.

Customer ids 6 and 7 have NULL for customer name and city because the customers table doesn’t contain any data for them. Since the customers table was the left table in this case customer ids 6 and 7 were shown regardless of what information they were missing.

c.Display the result

print(q3)
## # A tibble: 6 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           6 <NA>    <NA>             105 Camera     600
## 6           7 <NA>    <NA>             106 Printer    150

4. Full Join (3 points) Perform a full join between customers and orders.

q4 <- full_join(customers, orders, by = "customer_id")

a.How many rows are in the result?

There are 8 rows.

b.Identify any rows where there’s information from only one table. Explain these results.

Rows 5/6 and 7/8 both are missing data since they both only contain data from one table. The reasoning is that full_join matches every row from customer_id regardless if there’s missing data.

c.Display the result

print(q4)
## # A tibble: 8 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA
## 7           6 <NA>    <NA>             105 Camera     600
## 8           7 <NA>    <NA>             106 Printer    150

5. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table.

q5 <- semi_join(customers, orders, by = "customer_id")

a.How many rows are in the result?

There are 3 rows.

b.How does this result differ from the inner join result?

This result differs from the inner_join result because it doesn’t return rows from all tables. Semi join only returns all rows from the left table where there is a match in the right table.

c.Display the result

print(q5)
## # A tibble: 3 × 3
##   customer_id name    city       
##         <dbl> <chr>   <chr>      
## 1           1 Alice   New York   
## 2           2 Bob     Los Angeles
## 3           3 Charlie Chicago

6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table.

q6 <- anti_join(customers, orders, by = "customer_id")

a.Which customers are in the result?

David and Eve are the only customers in the result.

b.Explain what this result tells you about these customers.

This result informs me that these two customers are missing from the orders table(right table).

c.Display the result

print(q6)
## # A tibble: 2 × 3
##   customer_id name  city   
##         <dbl> <chr> <chr>  
## 1           4 David Houston
## 2           5 Eve   Phoenix

7. Practical Application (4 points) Imagine you’re analyzing customer behavior.

a.Which join would you use to find all customers, including those who haven’t placed any orders? Why?

I would use the the full_join function to find all customers because it returns all rows when there is a match in either left or right table. This means that it will also include people who haven’t placed any orders.

b.Which join would you use to find only the customers who have placed orders? Why?

I would use inner_join to find only the customers who have placed orders. Inner join returns all rows from both tables where there is a match. This means customers that are missing their name or order won’t show up. Only customers with all of the required information for a complete order will be shown.

c.Write the R code for both scenarios.

q7A <- full_join(customers, orders, by = "customer_id")
q7B <- inner_join(customers , orders, by = "customer_id")

d.Display the result

print(q7A)
## # A tibble: 8 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA
## 7           6 <NA>    <NA>             105 Camera     600
## 8           7 <NA>    <NA>             106 Printer    150
print(q7B)
## # A tibble: 4 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300