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 integerTable: jobs
job_id varchar(25) job_title varchar(50) min_salary integer max_salary integerTable: 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:
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?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics