Access Practicals 2 : Employee Management Database

Create a new database. Name it as "EmpMgtDB"

Create 3 tables in the specified order and the structure;

Department Table:

 

Employee Status Table:

 

Employee Table

Make the following relationships among the above tables.

Each Employee should be in one Department. (DepartmentID in Department to DepartmentID in Employee)

Each Employee should have one Employee Status. (EmployeeStatusID in Status to StatusID in Employee)

 

Insert give data to the following tables;

Data for Department Table

 

Data for EmployeeStatus Table

Data for Employee Table

 

After creating above tables and insert data run the below queries;

• Write a query to filter and get all data of male employees. Save the query as “Male Employees”

• Write a query to filter and get all data of female employees. Save the query as “Female Employees”

• Write a query to filter and get all data of employees who get a salary greater than 60000.Save the query as “High Salary Employees”

• Write a query to filter and get all data of male employees who get a salary less than 50000. Save the query as “Male Low Salary Employees”

• Write a query to filter and get all data of male employees who are in Sales department. Save the query as “Male Employees in Sales”

• Write a query to filter and get all data of female employees who are in IT or Marketing department. Save the query as “Female Employees in Marketing or IT”

• Write a query to filter and get all data of employees who are either retired or blacklisted employees. Save the query as “Employees not Present”

• Write a query to filter and get all data of employees who is active, in IT or sales and getting a salary greater than 50000. Save the query as “Valuable Employees” 

  Save your work.