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



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

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) The employees who are currently assigned to projects

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
proj_assign=employees %>% semi_join(projects,by="emp_id")
proj_assign
  emp_id emp_name gender dept_id
1      1    Alexa      M       1
2      2  Bharath      M       1

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

proj_assign=employees %>% left_join(projects,by="emp_id")
proj_assign
  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      M       1       1 Project A
2      2  Bharath      M       1       2 Project B
3      2  Bharath      M       1       3 Project C
4      3 Chandran      M       2      NA      <NA>
5      4    Divya      F       2      NA      <NA>
6      5   Elango      M       3      NA      <NA>

3) All projects and the employees assigned to them, including projects that have no assigned employee

proj=employees %>% left_join(projects,by="emp_id")
proj
  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      M       1       1 Project A
2      2  Bharath      M       1       2 Project B
3      2  Bharath      M       1       3 Project C
4      3 Chandran      M       2      NA      <NA>
5      4    Divya      F       2      NA      <NA>
6      5   Elango      M       3      NA      <NA>
#Q4->complete list of employees and projects, including those without assignments on either side

proj=employees %>% left_join(projects,by="emp_id")
proj
  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      M       1       1 Project A
2      2  Bharath      M       1       2 Project B
3      2  Bharath      M       1       3 Project C
4      3 Chandran      M       2      NA      <NA>
5      4    Divya      F       2      NA      <NA>
6      5   Elango      M       3      NA      <NA>

5) Employees are assigned to at least one project? List them without repeating project details

df=merge(x=employees,y=projects,by="emp_id",all=FALSE)
df
  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      M       1       1 Project A
2      2  Bharath      M       1       2 Project B
3      2  Bharath      M       1       3 Project C

6) Employees are not assigned to any projects? List those employees

not_assigned=employees %>% anti_join(projects,by="emp_id")
not_assigned
  emp_id emp_name gender dept_id
1      3 Chandran      M       2
2      4    Divya      F       2
3      5   Elango      M       3