Oracle Employee Database Case Study: Group By & Having Clause

In the previous articles, we have dealt with some basic queries in the data. Increasing the compressibility, let's jump on to the queries that will be solved using GROUP BY and HAVING Clause.

Click on GROUP BY and HAVING to know more about these clauses.

QUERY 1: HOW MANY EMPLOYEES ARE THERE DEPARTMENTS WISE IN THE ORGANIZATION.

In this query we need to figure out different employees present in an organization with same department_id.

select department_id, COUNT(*) from Employee
GROUP BY Department_id ;

OUTPUT:
Group by-query1

The above output clearly shows that how many employees are present in the organization department-wise.

QUERY 2: LIST OUT THE DEPARTMENT WISE MAXIMUM SALARY, MINIMUM SALARY, AVERAGE SALARY OF THE EMPLOYEES.

Here, to solve this query we use aggregate functions ie. MAX(), MIN() and AVG() in Oracle.

Select Department_id, Max(Salary),MIN(Salary),AVG(Salary)
from Employee Group BY Department_id;

OUTPUT:
Group by-query2

QUERY 3: LIST OUT JOB WISE MAXIMUM SALARY, MINIMUM SALARY, AVERAGE SALARIES OF THE EMPLOYEES.

As done in the previous query, for this query also same procedure will be followed. Olly difference is, in the above query we applied GROUP BY on Department_id, and here we are going to apply GROUP BY on Job_id attribute.

Select Job_id, Max(Salary),MIN(Salary),AVG(Salary)
from Employee Group BY Job_id;

OUTPUT:
Group by-query3

QUERY 4: LIST OUT THE NUMBER OF EMPLOYEES JOINED IN EVERY MONTH IN ASCENDING ORDER.

Select 
Extract(month from hire_date) as month,Extract(year from hire_date) YEAR, count(*)
from employee 
group by 
Extract(month from hire_date),EXTRACT(year from hire_date) 
order by month;

OUTPUT:
Group by-query4

NOTE:

1) Extract(month from Hire_Date) is used to fetch the month of the particular Hire_Date value.
2) Extract(Year from Hire_Date) is used to fetch the year of the particular Hire_Date value.

QUERY 5: LIST OUT THE NUMBER OF EMPLOYEES FOR EACH MONTH AND YEAR, IN THE ASCENDING ORDER BASED ON THE YEAR, MONTH.

To execute this query we have two different ways:

METHOD 1:

--METHOD1
select to_char(Hire_Date,'year') AS Year,
to_char(Hire_Date,'month') AS Month,count(*)
from Employee group By 
to_char(Hire_Date,'year'),to_char(Hire_Date,'month');

OUTPUT:
Group by-query5-method1

IT can be difficult to deal with the char data while working in huge database.

So, to resolve that we can follow another method as:

METHOD 2:

--METHOD 2
Select EXTRACT( Year from Hire_date) as YEAR, 
EXTRACT( Month from Hire_date) as MONTH, count(*)
from employee  group by EXTRACT( Year from Hire_date), 
EXTRACT( Month from Hire_date) order by YEAR;

OUTPUT:
Group by-query5- method2

QUERY 6: LIST OUT THE DEPARTMENT ID HAVING MINIMUM TWO EMPLOYEES.

select Department_id, count(*) from employee
group by department_id having count(*)>2;

OUTPUT:
Group by-query6

QUERY 7: HOW MANY EMPLOYEES JOINED IN MAY MONTH.

Select to_char(Hire_Date,'mon') mon,Count(*)
from Employee group by to_char(Hire_Date,'mon')
having to_char(Hire_Date,'mon')='may';

OUTPUT:
Group by-query7

QUERY 8: HOW MANY EMPLOYEES JOINED IN JANUARY OR DECEMBER MONTH.

We need to compare data with Jan and Dec month. So, here we can make use of OR Operator in Oracle.

Select to_char(Hire_Date,'mon') month, count(*)
from Employee group by to_char(Hire_Date,'mon')
having  to_char(Hire_Date,'mon')='jan' OR  to_char(Hire_Date,'mon')='dec';

OUTPUT:
Group by-query8

QUERY 9: HOW MANY EMPLOYEES WERE JOINED IN 1985?

Select EXTRACT( Year from Hire_date) as YEAR, count(*)
from employee  group by EXTRACT( Year from Hire_date)
having EXTRACT( Year from Hire_date)=1985;

OUTPUT:
Group by-query9

QUERY 10: HOW MANY EMPLOYEES WERE JOINED EACH MONTH IN 1985.

Select EXTRACT(year from Hire_Date)YEAR,
to_char(hire_date,'mon')MONTH,count(*) as Employees
from Employee where EXTRACT(year from Hire_Date)= 1985
group by EXTRACT(year from Hire_Date),to_char(hire_date,'mon');

OUTPUT:
Group by-query10

QUERY 11: HOW MANY EMPLOYEES WERE JOINED IN MAY 1985?

Select To_char(Hire_Date,'mon')MONTH,EXTRACT(Year from Hire_Date)YEAR, 
count(*)as employees from Employee where To_char(Hire_Date,'mon')='may'AND 
EXTRACT(Year from Hire_Date)=1985 group by 
To_char(Hire_Date,'mon'),EXTRACT(Year from Hire_Date);

OUTPUT:
Group by-query11

QUERY 12: WHICH IS THE DEPARTMENT ID, HAVING GREATER THAN OR EQUAL TO 1 EMPLOYEES JOINED IN APRIL 1985?

Select department_id,to_char(hire_date,'yyyy'),count(*) from employee
where to_char(hire_date,'yyyy')='1985'and to_char(hire_date,'mon')='apr'
group by to_char(hire_date,'yyyy'),to_char(hire_date,'mon'), 
department_id having count(*)>=1;

OUTPUT:
Group by-query12