PL/SQL Cursor Exercises: Display the first name, job title and start date of employees

PL/SQL Cursor: Exercise-42 with Solution

Write a block in PL/SQL to display the first name, job title and start date 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

job_id				varchar(25)
job_title			varchar(50)
min_salary			integer
max_salary			integer

PL/SQL Code:

declare
  cursor employees_cur is
   select employee_id, first_name, last_name, job_title , hire_date
   from employees natural join jobs;
   
   emp_st_date  date;
begin
dbms_output.put_line( rpad('Employee Name',30) || rpad('Job Title',35)|| 'Starting Date');
dbms_output.put_line('-----------------------------------------------------------------------------------------');
   for employee_rec in employees_cur
   loop
       -- find out most recent end_date in job_history
       select max(end_date) + 1 into emp_st_date
       from job_history
       where employee_id = employee_rec.employee_id;
       if emp_st_date is null then
             emp_st_date :=  employee_rec.hire_date;      
       end if;
       dbms_output.put_line(rpad((employee_rec.first_name||' '||employee_rec.first_name),30) || rpad(employee_rec.job_title,35)
           || to_char(emp_st_date,'dd-mon-yyyy'));
   end loop;
end; 

Sample Output:

SQL> /
Employee Name                 Job Title                          Starting Date
-------------------------------------------------------------------------------
William William               Public Accountant                  07-jun-2002
Shelley Shelley               Accounting Manager                 07-jun-2002
Jennifer Jennifer             Administration Assistant           01-jan-2007
Steven Steven                 President                          17-jun-2003
Lex Lex                       Administration Vice President      25-jul-2006
Neena Neena                   Administration Vice President      16-mar-2005
John John                     Accountant                         28-sep-2005
Daniel Daniel                 Accountant                         16-aug-2002
Luis Luis                     Accountant                         07-dec-2007
Ismael Ismael                 Accountant                         30-sep-2005
Jose Manuel Jose Manuel       Accountant                         07-mar-2006
Nancy Nancy                   Finance Manager                    17-aug-2002
Susan Susan                   Human Resources Representative     07-jun-2002
... 

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the last name, first name and overpaid amount by adding formal parameters and specify a default values for the added parameters.
Next: Write a block in PL/SQL to print the department name, head of the department, city and number of employees are wroking in that department.