w3resource

PL/SQL Cursor Exercises: Displays employee name and number of jobs he or she done at past. Displays employee name and job count by job

PL/SQL Cursor: Exercise-30 with Solution

Write a block in PL/SQL to displays employee name and number of jobs he or she done at past.

Displays employee name and job count by job.

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: jobs
job_id				varchar(25)
job_title			varchar(50)
min_salary			integer
max_salary			integer
Table: job_history
employee_id			integer
start_date			date
end_date			date
job_id				varchar(25)
department_id			integer

PL/SQL Code:

DECLARE
    CURSOR emp_job_cur IS
      SELECT job_id,
             job_title
      FROM   jobs
      WHERE  job_id IN (SELECT job_id
                        FROM   employees)
      ORDER  BY 2;
    CURSOR employees_cur (
      emp_jobid VARCHAR) IS
      SELECT first_name,
             Count(start_date) no_of_jobs
      FROM   employees e
             left outer join job_history jh USING (employee_id)
      WHERE  e.job_id = emp_jobid
      GROUP  BY first_name
      ORDER  BY 1;
BEGIN
    FOR emp_job_rec  IN emp_job_cur LOOP
		dbms_output.Put_line('---------------------------------------');	
        dbms_output.Put_line('Job Title: '||emp_job_rec.job_title);
		dbms_output.Put_line('---------------------------------------');

        FOR employee_list IN employees_cur( emp_job_rec.job_id) LOOP
            dbms_output.Put_line('    '
                                 || Rpad(employee_list.first_name, 20)
                                 || employee_list.no_of_jobs);
        END LOOP;
    END LOOP;
END; 
/ 

Sample Output:

SQL> /
---------------------------------------
Job Title: Accountant
---------------------------------------
Daniel              0
Ismael              0
John                0
Jose Manuel         0
Luis                0
---------------------------------------
Job Title: Accounting Manager
---------------------------------------
Shelley             0
---------------------------------------
Job Title: Administration Assistant
---------------------------------------
Jennifer            2
---------------------------------------
Job Title: Administration Vice President
---------------------------------------
Lex                 1
Neena               2
...

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Displays employee name and number of jobs he or she done at past. Displays employee name and job count by job

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history.
Next: Write a block in PL/SQL to display the name of department and their head.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-30.php