Display the employee detail with department name of given Fname if he is accountant

Question

input from tblemp(empid number primary key, fname varchar2(20), lname varchar2(20), jdate date, salary number, mid number, post varchar2(20), did varchar2(10) references tbldept1 (did)) ;

tbldept1(did varchar2(10) primary key,dname varchar2(10));

Write a pl/sql block of code to Display the employee detail with department name of given Fname if he is accountant.

i am try it for two method but still i am get error .

first method in this code by using type function:

declare
    type temp is record (
    veid tblemp1.empid%type,
    vfname tblemp1.fname%type,
    vlname tblemp1.lname%type,
    vsalary tblemp1.salary%type,
    vmid tblemp1.mid%type,
    vjdate tblemp1.jdate%type,
    vpost tblemp1.post%type,
    vdid tblemp1.did%type,
    vdname tbldept.dname%type);
    remp temp;
    remp1 temp;

begin
    select empid,fname,lname,salary,mid,jdate,post,d.did,dname into remp from tblemp1 e,tbldept d where e.did=d.did and empid=&empid;

    select empid,fname,lname,salary,mid,jdate,post,d.did,dname into remp1 from tblemp1 e,tbldept d where e.did=d.did and e.post='Accountant';

        dbms_output.put_line('empid = '||remp.empid);
        dbms_output.put_line('fname = '||remp.fname);
        dbms_output.put_line('lname = '||remp.lname);
        dbms_output.put_line('jdate = '||remp.jdate);
        dbms_output.put_line('mid = '||remp.mid);
        dbms_output.put_line('post = '||remp.post);
        dbms_output.put_line(' did = '||remp.did);
        dbms_output.put_line(' salary = '||remp.salary);
        dbms_output.put_line(' dname = '||remp1.dname);

end;
/

second method is by using row type function using

declare
    remp tblemp1%rowtype;
    remp1 tbldept%rowtype;  

begin
    select * into remp from tblemp1 where empid=&empid and post='Accountant';

    select * into remp1 from tbldept ;

        dbms_output.put_line('empid = '||remp.empid);
        dbms_output.put_line('fname = '||remp.fname);
        dbms_output.put_line('lname = '||remp.lname);
        dbms_output.put_line('jdate = '||remp.jdate);
        dbms_output.put_line('mid = '||remp.mid);
        dbms_output.put_line('post = '||remp.post);
        dbms_output.put_line(' did = '||remp.did);
        dbms_output.put_line(' salary = '||remp.salary);
        dbms_output.put_line(' dname = '||remp1.dname);

end;
/

i am try it with two method . im try it till morning but untill i am not getting output.

my prectical exam pass come so plaese help me.

score 0 · Answer 1 · answered Mar 22 '22 at 06:21

I wasn't sure if your table names were correct (your code didn't match your question text), but if you fix them, then your code looks reasonable to me

SQL> create table tbldept1(did varchar2(10) primary key,dname varchar2(10));

Table created.

SQL>
SQL> create table tblemp(empid number primary key, fname varchar2(20), lname varchar2(20),
  2  jdate date, salary number, mid number, post varchar2(20), did varchar2(10) references tbldept1 (did)) ;

Table created.

SQL>
SQL> insert into tbldept1 values (10,'Accounts');

1 row created.

SQL> insert into tblemp values (1000,'Jane','Doe',sysdate,1000,1001,'Accountant',10);

1 row created.

SQL>
SQL>
SQL> set serverout on
SQL> declare
  2      remp tblemp%rowtype;
  3      remp1 tbldept1%rowtype;
  4
  5  begin
  6      select * into remp from tblemp where empid=&empid and post='Accountant';
  7
  8      select * into remp1 from tbldept1 ;
  9
 10          dbms_output.put_line('empid = '||remp.empid);
 11          dbms_output.put_line('fname = '||remp.fname);
 12          dbms_output.put_line('lname = '||remp.lname);
 13          dbms_output.put_line('jdate = '||remp.jdate);
 14          dbms_output.put_line('mid = '||remp.mid);
 15          dbms_output.put_line('post = '||remp.post);
 16          dbms_output.put_line(' did = '||remp.did);
 17          dbms_output.put_line(' salary = '||remp.salary);
 18          dbms_output.put_line(' dname = '||remp1.dname);
 19
 20  end;
 21  /
Enter value for empid: 1000
old   6:     select * into remp from tblemp where empid=&empid and post='Accountant';
new   6:     select * into remp from tblemp where empid=1000 and post='Accountant';
empid = 1000
fname = Jane
lname = Doe
jdate = 22-MAR-22
mid = 1001
post = Accountant
did = 10
salary = 1000
dname = Accounts

PL/SQL procedure successfully completed.

i am doing same but sql saw error:ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6 — gazal gauswami, Mar 23 '22 at 14:06
that means you don't have data in the tables (or at least data that matches your criteria) — Connor McDonald, Mar 23 '22 at 14:47

Display the employee detail with department name of given Fname if he is accountant

1 Answers1