w3resource
PL/SQL Control Statement Exercises

PL/SQL Control Statement Exercises: Count the number of employees in department 50 and check whether this department have any vacancies or not. There are 45 vacancies in this department

PL/SQL Control Statement: Exercise-7 with Solution

Write a PL/SQL program to count the number of employees in department 50 and check whether this department have any vacancies or not. There are 45 vacancies in this department.

Sample Solution:

PL/SQL Code:

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

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

    IF tot_emp >= 45 THEN
      dbms_output.Put_line ('There are no vacancies in the department 50.');
    ELSE
      dbms_output.Put_line ('There are some vacancies in department 50.');
    END IF;
END; 
/

Sample Output:

The employees are in the department 50: 45
There are no vacancies in the department 50.

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: PL/SQL Control Statement Exercises: Count the number of employees in department 50 and check whether this department have any vacancies or not. There are 45 vacancies in this department

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL procedure to calculate incentive achieved according to the specific sale limit.
Next: Write a PL/SQL program to display the description against a grade.

What is the difficulty level of this exercise?