Q:

Write a block in PL/SQL to display the first name, job title and start date of employees

0

Write a block in PL/SQL to display the first name, job title and start date of employees.

All Answers

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

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

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