PostgreSQL Aggregate Functions and Group by- Exercises, Practice, Solution

Last update on August 19 2022 21:50:56 (UTC/GMT +8 hours)

Aggregate Functions and Group by [14 exercises with solution]

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

Go to editor

Click me to see the solution of Exercise No.- 1. Click me again to collapse.

SELECT COUNT(DISTINCT job_id) 
FROM employees;

Sample table : employees

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

Go to editor

Click me to see the solution of Exercise No.- 2. Click me again to collapse.

SELECT SUM(salary) 
FROM employees;

Sample table : employees

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

Go to editor

SELECT MIN(salary) 
FROM employees;

Sample table : employees

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

Go to editor

SELECT MAX(salary) 
FROM employees 
WHERE job_id = 'IT_PROG';

Sample table : employees

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

Go to editor

SELECT AVG(salary),count(*) 
FROM employees 
WHERE department_id = 90;

Sample table : employees

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

Go to editor

SELECT ROUND(MAX(salary),0) "Maximum",
ROUND(MIN(salary),0) "Minimum",
ROUND(SUM(salary),0) "Sum",
ROUND(AVG(salary),0) "Average"
FROM employees;

Sample table : employees

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

Go to editor

SELECT job_id, COUNT(*) 
FROM employees 
GROUP BY job_id;

Sample table : employees

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

Go to editor

SELECT MAX(salary) - MIN(salary) DIFFERENCE
FROM employees;

Sample table : employees

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

Go to editor

SELECT manager_id, MIN(salary) 
FROM employees 
WHERE manager_id IS NOT NULL 
GROUP BY manager_id 
ORDER BY MIN(salary) DESC;

Sample table : employees

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

Go to editor

SELECT department_id, SUM(salary) 
FROM employees 
GROUP BY department_id;

Sample table : employees

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

Go to editor

SELECT job_id, AVG(salary) 
FROM employees 
WHERE job_id <> 'IT_PROG' 
GROUP BY job_id;

Sample table : employees

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

Go to editor

SELECT job_id, SUM(salary), AVG(salary), MAX(salary), MIN(salary) 
FROM employees 
WHERE department_id = '90' 
GROUP BY job_id;

Sample table : employees

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.

Go to editor

SELECT job_id, MAX(salary)  
FROM employees 
GROUP BY job_id 
HAVING MAX(salary) >=4000;

Sample table : employees

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

Go to editor

SELECT department_id, AVG(salary), COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 10;

Sample table : employees

... More

Structure of 'hr' database :

Practice Online

Go To Top