Employee-Project Analysis

Data Setup

We begin by defining the data for employees, departments, and projects.

# Create the data frames
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
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)
)

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. Employees Assigned to Projects

This query returns the employees who are currently assigned to at least one project.

# Find employees who are assigned to at least one project
assigned_employees <- projects %>%
  filter(!is.na(emp_id)) %>%
  inner_join(employees, by = "emp_id") %>%
  select(emp_name, proj_name)

assigned_employees
emp_name proj_name
Alexa Project A
Barath Project B
Barath Project C

2. All Employees and Their Assigned Projects (Including Unassigned)

This query returns all employees, including those not assigned to any project.

# All employees and their assigned projects (including unassigned)
all_employees_projects <- employees %>%
  left_join(projects, by = "emp_id") %>%
  select(emp_name, proj_name)

# View the result
all_employees_projects
emp_name proj_name
Alexa Project A
Barath Project B
Barath Project C
Chandran NA
Divya NA
Elango NA

3. All Projects and Their Assigned Employees (Including Unassigned Projects)

This query returns all projects, including those without assigned employees.

# All projects and their assigned employees (including unassigned projects)
all_projects_employees <- projects %>%
  left_join(employees, by = "emp_id") %>%
  select(proj_name, emp_name)

all_projects_employees
proj_name emp_name
Project A Alexa
Project B Barath
Project C Barath
Project D NA

4. Complete List of Employees and Projects (Including Unassigned)

Here, we return the complete list of employees and projects, including both those without assignments.

# Complete list of employees and projects (including unassigned on either side)
complete_list <- employees %>%
  full_join(projects, by = "emp_id") %>%
  select(emp_name, proj_name)

complete_list
emp_name proj_name
Alexa Project A
Barath Project B
Barath Project C
Chandran NA
Divya NA
Elango NA
NA Project D

5. Employees Assigned to At Least One Project (No Repetition)

This query returns the employees assigned to at least one project, with no repeated project details.

# Employees assigned to at least one project (no repetition)
unique_assigned_employees <- projects %>%
  filter(!is.na(emp_id)) %>%
  inner_join(employees, by = "emp_id") %>%
  distinct(emp_name, .keep_all = TRUE) %>%
  select(emp_name)

unique_assigned_employees
emp_name
Alexa
Barath

6. Employees Not Assigned to Any Project

This query lists the employees who are not assigned to any project.

# Employees who are not assigned to any project
unassigned_employees <- employees %>%
  filter(!emp_id %in% projects$emp_id) %>%
  select(emp_name)

unassigned_employees
emp_name
Chandran
Divya
Elango

Summary of Queries

-I used inner_join to fetch employees who are assigned to projects.

-I used left_join and full_join to handle cases where there may be missing assignments for either employees or projects.

-distinct helped to ensure that employees assigned to multiple projects are not repeated in the list.