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:
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?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework