Employee count by country

PL/SQL While Loop: Exercise-17 with Solution

Write a PL/SQL program to display the number of employees in each country. Return country name and number of employees

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

country_id		varchar(5)	
country_name		varchar(25)

location_id			integer
street_address			varchar(50)
postal_code			varchar(13)
city				varchar(25)
state_province			varchar(25)
country_id			varchar(2)

PL/SQL Code:

DECLARE
v_country_namecountry.country_name%TYPE;
v_total_employees NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Country Name    Total Employees');
  DBMS_OUTPUT.PUT_LINE('-----------------------------');
  DECLARE
    CURSOR c_countries IS
      SELECT country_name
      FROM country;
r_countryc_countries%ROWTYPE;
  BEGIN
    OPEN c_countries;
    FETCH c_countries INTO r_country;
    WHILE c_countries%FOUND LOOP
v_country_name := r_country.country_name;
v_total_employees := 0; -- Reset total for each country
      DECLARE
        CURSOR c_employees IS
          SELECT COUNT(*) AS emp_count
          FROM employees emp
          JOIN departments dept ON emp.department_id = dept.department_id
          JOIN locations loc ON dept.location_id = loc.location_id
          JOIN country ctry ON loc.country_id = ctry.country_id
          WHERE ctry.country_name = v_country_name;
r_employeec_employees%ROWTYPE;
      BEGIN
        OPEN c_employees;
        FETCH c_employees INTO r_employee;
        CLOSE c_employees;
v_total_employees := r_employee.emp_count;
      END;
      DBMS_OUTPUT.PUT_LINE(v_country_name || '           ' || v_total_employees);
      FETCH c_countries INTO r_country;
    END LOOP;
    CLOSE c_countries;
  END;
END;
/

Sample Output:

Country Name    Total Employees
-----------------------------
Argentina           0
Australia           0
Belgium           0
Brazil           0
Canada           2
Switzerland           0
China           0
Germany           1
Denmark           0
Egypt           0
France           0
Israel           0
India           0
Italy           0
Japan           0
Kuwait           0
Malaysia           0
Mexico           0
Nigeria           0
Netherlands           0
Singapore           0
United Kingdom           35
United States of America           68
Zambia           0
Zimbabwe           0

Explanation:

The said code in Oracle's PL/SQL that retrieves the employees for each country and calculates the total number of employees for each country.

The variables v_country_name that stores the current country name, and v_total_employees that stores the total employee count for each country are declared.

A cursor c_countries associated with the 'country' table record type is declared to select the country names from the 'country' table.

Inside the outer loop, the the first row is fetched from the c_countries cursor into the r_country record and the loop continues as long as there are rows found in the cursor.

The v_country_name variable is assigned the value of the current country name from the r_country record. The v_total_employees variable is then reset to 0 for each country.

Another cursor c_employees is declared that counts the employees for the current country. This cursor joins the 'employees,' 'departments,' 'locations,' and 'country' tables based on their relationships and filters the rows for the current country using the v_country_name variable.

The c_employees cursor is associated with the c_employees record type, which contains a single field emp_count to store the count of employees.

From the c_employees cursor the employee count is fetched into the r_employee record. The v_total_employees variable is updated with the value of r_employee.emp_count, representing the total number of employees for the current country.

The DBMS_OUTPUT.PUT_LINE displays the current country name and its corresponding total employee count.

The c_countries cursor is fetched again to move to the next country in the loop and the loop continues until all countries have been processed.

Flowchart:

Previous: PL/SQL program to display total number of employees hired each year.
Next:PL/SQL Program to Display Average Salary for Each Department.