Import necessary library

dplyr is an R package that provides a set of functions for manipulating data frames in a user-friendly way.

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
suppressWarnings(suppressMessages(library(dplyr)))

Creating employee dataframe:

Creating employee dataframe which contains emp_id,emp_name,gender,dept_id as variables.

employees <- data.frame(
  emp_id = c(1, 2, 3, 4, 5),
  emp_name = c("Alexa", "Barath", "Chandran", "Divya", "Elango"),
  gender = c("F", "M", "M",  "F", "M"),
  dept_id = c(1, 1, 2, 2, 3)
)

Creating departments dataframe:

Creating departments dataframe which contains dept_id,dept_nam as variables.

departments <- data.frame(
  dept_id = c(1, 2, 3, 4),
  dept_name = c("HR", "Finance", "IT", "Marketing")
)

Creating projects dataframe:

Creating projects dataframe which contains proj_id,proj_name,emp_id as variables.

projects <- data.frame(
  proj_id = c(1, 2, 3, 4),
  proj_name = c("Project A", "Project B", "Project C", "Project D"),
  emp_id = c(1, 2, 2, NA)
)

1.Employees who are currently assigned to projects

A semi join returns the rows of the employees table where it can find a match in the projects table without adding the columns from projects table.

em_pr<-semi_join(employees,projects,by="emp_id")
em_pr

2.Employees and assigned projects,including those who are not assigned to any project

The left_join combines two tables by retaining all rows from the Employees table(1st table) and adding matching rows from the Projects tables(2nd table), filling unmatched entries with NA.

em_pr<-left_join(employees,projects,by="emp_id")
em_pr

3.All projects and employees,including those who are not assigned to any project

The right_join combines two tables by retaining all rows from the employees table(2nd table)and adding matching rows from the projects table(1st table),filling unmatched entries with NA.

em_pr<-right_join(projects,employees,by="emp_id")
em_pr

4.The complete list of employees and projects, including those without assignments on either side

The left_join combines two tables by retaining all rows from the Employees table(1st table) and adding matching rows from the Projects table(2nd table), filling unmatched entries with NA.

em_pr<-left_join(employees,projects,by ="emp_id")
em_pr

5.Employees assigned to atleast one project,without repeating project details.

A semi join returns the rows of the employees table where it can find a match in the projects table without adding the columns from projects table.

em_pr<-semi_join(employees,projects,by="emp_id")
em_pr

6.List of employees those who are not assigned to any projects

The anti-join returns the rows from the employees table that have no matching keys in the projects table.

em_pr<-anti_join(employees,projects,by="emp_id")
em_pr