Categories
Uncategorized

PL/SQL Query

1. Create Pl/Sql block to delete the department created. print to the scree the number of rows affected.

  set serveroutput on;
declare
V_count number(3);
begin
select count(*) into v_count from employee where
deptno=(select max(deptno) from department);
delete from employee where deptno=(select max(deptno) from department);
dbms_output.put_line(‘no of rows deleted:’||V_count);
end;
/

2.  Write a PL/SQL block to award an employee with the bonus. Bonus is 15% of commission drawn by the employee. If the employee does not earn any commission then display a message that ‘employee does not earn any commission’. Otherwise add bonus to the salary of the employee. The block should accept an input for the employee number.

sql>alter table employee add comm real;

update employee set comm=2000 where empid=17;

update employee set comm=800 where empid=16;

update employee set comm=400 where empid=15;

update employee set comm=1200 where empid=14;

declare
V_empno employee.empid%type;
V_name employee.name%type;
V_sal employee.salary%type;
V_comm employee.comm%type;
begin
select empid,name,salary,comm into V_empno,V_name,V_sal,V_comm from employee where empid=&V_empno;
if V_comm is null then
dbms_output.put_line(V_name||’does not earn any commission’);
else
V_sal:=V_sal+(V_comm*0.15);
dbms_output.put_line(‘Emp no:’||V_empno);
dbms_output.put_line(‘Name:’||V_name);
dbms_output.put_line(‘Salary after commission:’||V_sal);
end if;
end;
/

3. write a pl/sql query to delete the department from the department where there is no any employee in that department.

set severout on;
declare
V_deptno employee.deptno%type:=&v_deptno;
employeeexception EXCEPTION;
pragma exception_init(employeeexception,-2292);
begin
delete from department where deptno=V_deptno;
dbms_output.put_line(‘dept’||V_deptno ||’deleted from dept table’);
commit;
exception
when employeeexception then
dbms_output.put_line(‘Cant remove dept:’|| V_deptno ||’employee exists’);
end;
/