Department and Employee Table Having One To Many Relationship. Consider the Following Entities and Their Relationship. Department(deptno,deptname,location) Employee(empno,empname,salary,commission,designation) Constraints: Primary Key

SQL> create table dept

  2  (deptno number primary key,

  3  deptname varchar(30),

  4  location varchar(20));


Table created.


SQL> create table emp

  2  (empno number primary key,

  3  empname varchar(20),

  4  salary number,

  5  commission number,

  6  desg varchar(20),

  7  deptno number references dept);


Table created.


 Then Insert Values into it.


SQL> select * from emp;


     EMPNO EMPNAME                  SALARY COMMISSION DESG                        DEPTNO

---------- -------------------- ---------- ---------- -------------------- ----------

       101 Raj                       20000        300 Employee                      1

       102 Ganesh                    30000        500 Manager                       1

       103 Dinesh                    25000        550 Manager                       2

       104 Safin                     22000        600 Employee                      3

       105 Chirag                    31000        250 Manager                       4

       106 Himesh                    21000        450 Employee                      5

       107 John                      21000        450 Employee                      2


7 rows selected.


SQL> select * from dept;


    DEPTNO DEPTNAME                       LOCATION

---------- ------------------------------ --------------------

         1 Computer                       Pune

         2 Sales                          Mumbai

         3 Manufacturing                  Delhi

         4 Advertisement                  Pune

         5 HR                             Chennai


a) Create or Replace a PL/SQL Procedure To Display Details of Given Employee.


SQL>  set serveroutput on;

SQL>  create or replace procedure p

  2   (eno in number,ename out varchar2,sal out number,comm out number,desgn out varchar2,dno out number)

  3   as

  4   v_ename emp.empname%type;

  5   v_sal emp.salary%type;

  6   v_comm emp.commission%type;

  7   v_desgn emp.desg%type;

  8   v_dno emp.deptno%type;

  9   begin

 10   select empname,salary,commission,desg,deptno into v_ename,v_sal,v_comm,v_desgn,v_dno

 11   from emp

 12   where empno=eno;

 13   ename:=v_ename;

 14   sal:=v_sal;

 15   comm:=v_comm;

 16   desgn:=v_desgn;

 17   dno:=v_dno;

 18   EXCEPTION

 19   when no_data_found then

 20   v_ename:='NO';

 21   end p;

 22   /


      Procedure created.


    **CALL PROGRAM**

SQL> declare

  2  no number;

  3  n varchar(20);

  4  s number;

  5  c number;

  6  d varchar(20);

  7  e number;

  8  begin

  9  no:=&no;

 10  p(no,n,s,c,d,e);

 11  if n='NO' then

 12  dbms_output.put_line('Employee Details Not Found');

 13  else

     dbms_output.put_line('Name Salary Comm Desgn Deptno');

 14  dbms_output.put_line(n ||'  '|| s ||'  '|| c ||'  '|| d ||'  '|| e);

 15  end if;

 16  end;

 17  /

Enter value for no: 4444

old   9: no:=&no;

new   9: no:=4444;

Employee Details Not Found


PL/SQL procedure successfully completed.


Enter value for no: 103

old   9: no:=&no;

new   9: no:=103;

Name Salary Comm Desgn Deptno

Dinesh  25000  550  Manager  2


PL/SQL procedure successfully completed.


b) Write a Cursor To Display Total Salary (Salary+Commission) of All Employees.


SQL> declare

  2  cursor c is

  3  select salary,commission from emp;

  4  totalsalary number;

  5  y c%rowtype;

  6  begin

  7  dbms_output.put_line('Salary Commission Total Salary');

  8  dbms_output.put_line('------------------------------');

  9  for y in c

 10  loop

 11  totalsalary:=y.salary+y.commission;

 12  dbms_output.put_line(y.salary ||'     ' || y.commission || '     ' || totalsalary);

 13  end loop;

 14  end;

 15  /

Salary Commission Total Salary

------------------------------

20000     300     20300

30000     500     30500

25000     550     25550

22000     600     22600

31000     250     31250

21000     450     21450

21000     450     21450


PL/SQL procedure successfully completed.