w3resource
PL/SQL Control Statement Exercises

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:

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:

Flowchart: 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

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.

What is the difficulty level of this exercise?