Laghari & Laghari’s Personnel Department maintains a file of all employees of the company and each employee’s unique Employee ID. The file llemployees provides information on employees for Laghari & Laghari’s database system, including the first name, last name, and employee ID for each employee in the fields First, Last, and EmpID. The first two digits of the employee ID are the day of the month on which the employee was born, the next two digits are the month in which the employee was born, and the next four digits are the date of the year in which the employee was born. The two digits that immediately follow identify the department in which the employee works, and the last four digits represent other information about the employee. From Noah Chavez’s employee ID (14-02-1983-02-4698), we can see that Noah was born on February 14, 1983, and works in department 2.

Laghari & Laghari management wants a new file that includes separate fields for the date of the month on which the employee was born, the month in which the employee was born, the year in which the employee was born, and the department in which the employee works. They also want the First and Last fields combined into a single field called Name with the first and last names separated by a space. Finally, Laghari & Laghari management has determined that the birth years of its current employees range from 1960 to 2003, and it wants to ensure that no employee record has a birth year outside of this range.

a. Create a new set of data from the file llemployees that includes the employee’s first name, last name, and each of these four new fields with the field names Day, Month, Year, and Department. Save your file under the name llemployeeinfo_a.xlsx.

b. Using the file llemployeesinfo_a that you created in part (a), combine the First and Last fields into a single field FullName with the first and last names separated by a space. Delete the original First and Last fields and save your file under the name llemployeeinfo_b.xlsx.

c. Search the file llemployeesinfo_b that you created in part (b) for values for Year that are less than 1960 or greater than 2003 (which Laghari & Laghari has indicated is the range of birth years for its current employees). Provide a list of the names, birthdates, and departments for employees for whom the value of the field Year is outside of this range.