SQL Queries Using Aggregate Functions – I

SQL is a standard language for storing, manipulating and retrieving data in databases.
In this series of posts, we will discuss the SQL queries from very Basic to Advanced.
It is recommended to view the post in the following order:
1. SQL Basic Queries Example – I
2. SQL Basic Queries Example – II
3. SQL Queries Using Aggregate Functions – I
4. SQL Queries Using Aggregate Functions – II
5. SQL Intermediate Queries Example – I
6. SQL Intermediate Queries Example – II
7. SQL Advanced Queries Example – I
8. SQL Advanced Queries Example – II

In this post, we will look at the SQL queries using Aggregate Functions.

Create the tables described below:

Table Name: employee

+--------+----------+--------+-----------+
 | emp_id | emp_name | salary | dept_name |
 +--------+----------+--------+-----------+
 |    101 | Amit     |  25000 | IT        |
 |    102 | Sunil    |  20000 | Sales     |
 |    103 | Rakesh   |  18000 | MKTG      |
 |    104 | Ajay     |  16000 | IT        |
 |    105 | Suhail   |  20000 | Sales     |
 |    106 | Arif     |  18000 | HR        |
 |    107 | Suresh   |  24000 | Sales     |
 |    108 | Vijay    |  22000 | MKTG      |
 +--------+----------+--------+-----------+

Command:

create table employee(emp_id int primary key, 
emp_name varchar(20), 
salary float, 
dept_name varchar(20));

insert into employee values(101, 'Amit', 25000, 'IT');
insert into employee values(102, 'Sunil', 20000, 'Sales');
insert into employee values(103, 'Rakesh', 18000, 'MKTG');
insert into employee values(104, 'Ajay', 16000, 'IT');
insert into employee values(105, 'Suhail', 20000, 'Sales');
insert into employee values(106, 'Arif', 18000, 'HR');
insert into employee values(107, 'Suresh', 24000, 'Sales');
insert into employee values(108, 'Vijay', 22000, 'MKTG');

---

Exercise on Aggregate Functions

a) Display the total sum required to pay the salary of all employees.

Command:
select sum(salary) from employee;


Output:
+-------------+
| sum(salary) |
+-------------+
|      163000 |
+-------------+

b) Display the average salary, minimum salary and maximum salary of the
company.

 Command:
 select avg(salary), max(salary), min(salary) from employee;


 Output:
 +-------------+-------------+-------------+
 | avg(salary) | max(salary) | min(salary) |
 +-------------+-------------+-------------+
 |       20375 |       25000 |       16000 |
 +-------------+-------------+-------------+

c) Display the sum of the salary department-wise.

Command:
 select sum(salary),dept_name from employee group by dept_name;


Output:
 +-------------+-----------+
 | sum(salary) | dept_name |
 +-------------+-----------+
 |       18000 | HR        |
 |       41000 | IT        |
 |       40000 | MKTG      |
 |       64000 | Sales     |
 +-------------+-----------+

d) Display the maximum salary department-wise.

Command:
 select dept_name, max(salary) from employee group by dept_name;


Output:
 +-----------+-------------+
 | dept_name | max(salary) |
 +-----------+-------------+
 | HR        |       18000 |
 | IT        |       25000 |
 | MKTG      |       22000 |
 | Sales     |       24000 |
 +-----------+-------------+

e) Display the details of the employee who earns the maximum salary.

Command:
 select * from employee where salary = (select max(salary) from employee );


Output:
 +--------+----------+--------+-----------+
 | emp_id | emp_name | salary | dept_name |
 +--------+----------+--------+-----------+
 |    101 | Amit     |  25000 | IT        |
 +--------+----------+--------+-----------+

f) Display details of every employee having a maximum salary in his department.

Command:
 select e.* from employee e, (select max(salary) as m,dept_name from employee group by dept_name) as maxsal where e.dept_name = maxsal.dept_name and salary = maxsal.m group by e.dept_name;


Output:
 +--------+----------+--------+-----------+
 | emp_id | emp_name | salary | dept_name |
 +--------+----------+--------+-----------+
 |    106 | Arif     |  18000 | HR        |
 |    101 | Amit     |  25000 | IT        |
 |    108 | Vijay    |  22000 | MKTG      |
 |    107 | Suresh   |  24000 | Sales     |
 +--------+----------+--------+-----------+

g) Display the details of the employee who earns more salary than the average salary of his department.

Command:
select e.* from employee e, (select *,avg(salary) as avg from employee group by dept_name) as e2 where e.salary > e2.avg and e.dept_name = e2.dept_name group by e.dept_name;


Output:
 +--------+----------+--------+-----------+
 | emp_id | emp_name | salary | dept_name |
 +--------+----------+--------+-----------+
 |    101 | Amit     |  25000 | IT        |
 |    108 | Vijay    |  22000 | MKTG      |
 |    107 | Suresh   |  24000 | Sales     |
 +--------+----------+--------+-----------+

h) Display the total number of employees.

Command:
 select count(emp_id) from employee;


Output:
 +---------------+
 | count(emp_id) |
 +---------------+
 |             8 |
 +---------------+