Q:

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

0

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.

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now