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:

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

Go to:

• PL/SQL Control Statement Exercises Home. ↩
• PL/SQL Exercises Home ↩

PREV : 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.

Improve this sample solution and post your code through Disqus