Skip to main content

Setting up for exercise​

Refer to the details here and get the setup ready before testing the samples

Aggregate functions​

Find the total number of employees in the company.

Calculate the average salary of all employees.

Get the highest salary in the company.

Find the lowest salary in the company.

Count the number of employees in each department.

Find the total salary expense for each department.

Get the average salary for employees in the IT department.

Find the number of employees who are mentors (employees with at least one skill in the MENTOR table).

Display the total number of employees, total salary, and average salary in a single query.

Get the employees who have a salary above the company-wide average salary.

SQL Joins​

Retrieve the employee names and their corresponding mentoring skills.

List all employees, including those who don’t mentor anyone, showing their names and mentor skills (if any).

Find employees who are mentoring more than one skill.

Find employees who share the same department, showing both employee names in the result.

Retrieve each mentor’s name along with the total number of skills they mentor in.

Db2 Views​

Create a view V_EMP_MENTOR to display all mentors along with employee name, department, and skill.

Create a view V_EMP_DIRECTORY to display only EMP_ID, NAME, and DEPARTMENT.

Create a view V_MENTOR_SKILL_COUNT that shows each skill and how many mentors have that skill.

Create a view V_DEPT_SALARY_STATS showing DEPARTMENT, TOTAL_EMPLOYEES, AVG_SALARY, and MAX_SALARY.

Create a view V_EMP_MENTOR_FLAG that includes EMP_ID, NAME, and a column IS_MENTOR as 'YES' or 'NO'.

Create a view V_IT_MENTORS showing mentors from the IT department only, with mentor ID, name, and skill.

Create a view V_EMP_SALARY_BAND showing EMP_ID, NAME, and a derived column SALARY_BAND:

  • 'HIGH' for salary > 90000
  • 'MID' for salary between 60000 and 90000
  • 'LOW' for salary < 60000

Create a view V_DEPT_MENTOR_COUNT showing number of mentors in each department.

Create a view V_EMP_WITHOUT_MENTORS to list EMP_ID and NAME of employees who are not mentors.

Create a view V_EMP_MENTOR_SUMMARY to show all employees, and if they are mentors, include their SKILLS, else show '-' as the skill.