PL/SQL Cursor Exercises: Display the number of employees by month. Print number of employees by month

PL/SQL Cursor: Exercise-38 with Solution

Write a PL/SQL block to display the number of employees by month. Print number of employees by month.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
    st_month NUMBER(2) := 1;
    no_o_emp NUMBER(3);
BEGIN
    dbms_output.Put_line(Rpad('Month No', 20)
                         ||Rpad('Month Name', 20)
                         || 'Number of Employees');
dbms_output.Put_line('-------------------------------------------------------------');

FOR month IN 1 .. 12 LOOP
    SELECT Count(*)
    INTO   no_o_emp
    FROM   employees
    WHERE  To_char(hire_date, 'mm') = month;

    dbms_output.Put_line(Rpad(To_char(month, '00'), 20)
                         ||Rpad(To_char(To_date(month, 'MM'), 'MONTH'), 20)
                         || To_char(no_o_emp, '999'));
END LOOP;
END; 
/

Sample Output:

SQL> /
Month No            Month Name          Number of Employees
-----------------------------------------------------------
01                 JANUARY               14
02                 FEBRUARY              13
03                 MARCH                 17
04                 APRIL                  7
05                 MAY                    6
06                 JUNE                  11
07                 JULY                   7
08                 AUGUST                 9
09                 SEPTEMBER              5
10                 OCTOBER                6
11                 NOVEMBER               5
12                 DECEMBER               7

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.
Next: Write a PL/SQL block to display the last name of manager, and their departments for a particular city, using parameters with a default value in explicit cursor.