PL/SQL Control Statement Exercises: Count the number of employees in a specific department and check whether this department have any vacancies or not. If any vacancies, how many vacancies are in that department

PL/SQL Control Statement: Exercise-9 with Solution

Write a PL/SQL program to count the number of employees in a specific department and check whether this department have any vacancies or not. If any vacancies, how many vacancies are in that department.

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

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

SET SERVEROUTPUT ON
DECLARE
    tot_emp NUMBER;
	get_dep_id NUMBER;
	
BEGIN
    get_dep_id := '&new_dep_id';
    SELECT Count(*)
    INTO   tot_emp
    FROM   employees e
           join departments d
             ON e.department_id = d.department_id
    WHERE  e.department_id = get_dep_id;

    dbms_output.Put_line ('The employees are in the department '||get_dep_id||' is: '
                          ||To_char(tot_emp));

    IF tot_emp >= 45 THEN
      dbms_output.Put_line ('There are no vacancies in the department '||get_dep_id);
    ELSE
      dbms_output.Put_line ('There are '||to_char(45-tot_emp)||' vacancies in department '|| get_dep_id );
    END IF;
END; 
/

Sample Output:

Enter value for new_dep_id: 20
old   6:     get_dep_id := '&new_dep_id';
new   6:     get_dep_id := '20';
The employees are in the department 20 is: 2
There are 43 vacancies in department 20

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL program to display the description against a grade.
Next: Write a PL/SQL program to display the description against a grade using CASE statement.