w3resource

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:

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?



Follow us on Facebook and Twitter for latest update.