HR Data Base MySQL Aggregate Functions and Group by exercises

1. Write a query to list the number of jobs available in the employees table.

- select count(distinct job_id) from employees;

 

2. Write a query to get the total salaries payable to employees. 

- select sum(salary) from employees;

 

3. Write a query to get the minimum salary from employees table. 

- select min(salary) from employees;

 

4. Write a query to get the maximum salary of an employee working as a Programmer. 

-select max(salary) from employees;

 

5. Write a query to get the average salary and number of employees working the department 90. 

- select avg(salary) from employees;

 

6. Write a query to get the highest, lowest, sum, and average salary of all employees. 

- select max(salary) "MAXIMUM", min(salary) "MINIMUM", sum(salary) "TOTAL SUM", avg(salary) "AVERAGE" 

  from employees;

 

7. Write a query to get the number of employees with the same job. 

- select job_id, count(*) from employees group by job_id;

 

8. Write a query to get the difference between the highest and lowest salaries. 

- select max(salary) - min(salary) from employees;

 

9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager. 

- select manager_id, min(salary) 

  from employees 

  where manager_id is not null 

  group by manager_id 

  order by min(salary) desc;

 

10. Write a query to get the department ID and the total salary payable in each department. 

- select department_id ,sum(salary) "total salary payable" from employees group by department_id;

 

11. Write a query to get the average salary for each job ID excluding programmer.

- select job_id, avg(salary) "avg salary" from employees where job_id not in ("IT_PROG") group by job_id;

 

12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only. 

- select job_id, sum(salary) "total salary", max(salary) "maximum", min(salary) "minimum", avg(salary) "average" 

  from employees 

  where department_id = 90 

  group by job_id;

 

**department_id가 90인 부서는 총 2개가 있다. group by를 사용하지 않으면 두개를 하나로 계산해서 결과가 나온다.

 

13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.

- select job_id ,max(salary) from employees group by job_id having max(salary)>=4000;

 

14. Write a query to get the average salary for all departments employing more than 10 employees.

- select department_id,count(department_id), avg(salary) 

  from employees 

  group by department_id

  having count(department_id) > 10;