1). Write a PL/SQL
program to increment all manager salary by 10%. After increment if total salary become greater
than 200000 then undo increment.
declare
tot
number(6);
begin
savepoint
no_update;
update
emp set emp_sal = emp_sal+(emp_sal*10)/100
where
emp_disp='manager';
select
sum(emp_sal) into tot from emp;
if tot>200000 then
rollback to savepoint no_update;
end if;
commit;
e nd;
OUTPUT:-
PL/SQL
procedure successfully completed.
SQL>
set serveroutput on
SQL>
/
PL/SQL
procedure successfully completed.
SQL>
select * from emp;
EMP_SAL
EMP_DISP TOT
----------
--------------- ----------
1210
manager 100000
2000
clerk 200000
3630 manager 200000
48400
manager 2500000
2)
Create view Emp1 (empno, deptname, NewSal) from EMP
& dept tables.
In this view Newsal=Sal - 500.
create
view emp1 as
select emp_no,emp_disp,emp_sal-500 newsal
from emp;
OUTPUT:-
View
created.
3) Create sequence Seq_Product. Using this sequence create
primary key for Product Table.
Product (ProId char (2), ProdName)
In this table ProId is a primary key & have following structure:
Char + number e.g. A1, A2, A3,….. A9, B1, B2…..
create
sequence seq_product
maxvalue 9
minvalue 1
cycle
cache 2;
OUTPUT:-
Sequence
created.
4)
Create unique index Idx_emp for (Empno, deptno).
create
unique index idx_emp on employee (emp_no,dept_no);
OUTPUT:-
SQL>
create unique index idx_emp on employee (emp_no,dept_no);
Index
created.
5)
Allow user tmp to view empno, ename column of EMP Table.
create
view emp2 as select emp_no, emp_name from emp ;
OUTPUT:-
SQL>
create view emp2 as select emp_no, emp_name from emppp;
View
created.
6)
Write a PL/SQL block to display all employees’ details.
set
serveroutput on;
declare
cursor c_emp is select *from
employee;
begin
for r in c_emp
loop
dbms_output.put_line(r.e_no||' '||r.e_name||' '|| r.e_salary||' ');
end loop;
end;
/
OUTPUT:-
SQL>
@ E:\data\6.sql
1 hardik
190000
2 pankaj
150000
3 jayesh
130000
4 nikunj
180000
5 chirag
100000
PL/SQL
procedure successfully completed.
7)
Write a PL/SQL block to find employee(s) having minimum
salary( Without using min function).
declare
cursor cur_emp is select * from emp1;
minsal
number :=999;
begin
for
m_emp in cur_emp
loop
if
m_emp.sal<minsal then
minsal:=m_emp.sal;
end
if;
end loop;
dbms_output.put_line(to_char(minsal));
end;
OUTPUT:-
PL/SQL
procedure successfully completed.
8)
Write a PL/SQL block to total salary of even records &
odd records.
declare
cursor cur_emp is select *from emp1;
even number:=0;
odd number:=0;
begin
for tmp in cur_emp
loop
if mod(cur_emp%rowcount,2)=0 then
even:=even+tmp.sal;
else
odd:=odd+tmp.sal;
end if;
end loop;
dbms_output.put_line('Total evenno of
salary is= '||to_char(even));
dbms_output.put_line('Total oddno of
salary is= '||to_char(odd));
end;
OUTPUT:-
Total
evenno of salary is= 1750
Total
oddno of salary is= 1799
PL/SQL
procedure successfully completed.
9)
consider following smarks table.
Smarks(rollno
number(3) , mark1 number(3) , mark2 number(3) , mark3 number(3) , percentage
number, result varchar2(10))
Write pl\sql block
tocalculate result,update record& display it.
Result may contain
following values:fail,pass,first,second.
Criteria for first percentage>60
Second percentage>50
pass percentage>40
fail percentage<40
Table1
create table student
(rno number(3) primary key,
name varchar2(15),
percentage number(5,2));
SQL> desc student;
Name Null? Type
----------------------------------------- --------
RNO NOT NULL NUMBER(3)
NAME
VARCHAR2(15)
PERCENTAGE NUMBER(5,2)
Insert
Table1
insert into student
values(&rno,'&name',&percentage)
SQL> select * from student;
RNO NAME PERCENTAGE
---------- --------------- ----------
1 Mahesh 86.2
2 Ram 34.3
3 Manoj 56.4
4 Aisha 46.6
5 Rani 55.6
6 ankita 73.1
7 komal 76.2
8 yash 67.3
8 rows selected.
Table2
create table result
(rno number(3) references student(rno)
on delete cascade,
name varchar2(15),
remark varchar2(12));
SQL> desc result;
Name Null? Type
-----------------------------------------
--------
RNO
NUMBER(3)
NAME
VARCHAR2(15)
REMARK
VARCHAR2(12)
Procedure1
create or replace procedure
pro_stud(mrno student.rno%type,
mname student.name%type,
mper student.percentage%type)
is
result
varchar2(5);
rmark
varchar2(12);
begin
if
(mper > 35)then
result:='pass';
else
result:='fail';
end
if;
if
(result = 'pass')then
if
(mper >60) then
rmark:='fist';
elseif
(mper >50) then
rmark:='second';
elseif
(mper >40) then
rmark:='pass';
elseif
(mper <40) then
rmark:='fail';
end
if;
insert
into result values(mrno,mname,rmark);
end
if;
end;
Procedure2
create or replace procedure
pro_result(mrno result.rno%type,
mname
result.name%type,
rmark
result.remark%type)
is
begin
if
(rmark ='very good')then
dbms_output.put_line('Roll
no :' ||mrno);
dbms_output.put_line('Name :' ||mname);
dbms_output.put_line('Remark :' ||rmark);
end
if;
end;
pl/sql
code
declare
srno
student.rno%type;
sname
student.name%type;
sper
student.percentage%type;
rrno
result.rno%type;
rname
result.name%type;
rmark
result.remark%type;
cursor
stud is select * from student;
cursor
r is select * from result;
begin
open
stud;
if
stud%isopen then
loop
fetch
stud into srno,sname,sper;
exit
when stud%notfound;
execute
pro_stud(srno,sname,sper);
end
loop;
close
stud;
else
dbms_output.put_line('cursor
is not opened');
end
if;
open
r;
if
r%isopen then
loop
fetch
r into rrno,rname,rmark;
exit
when r%notfound;
execute
pro_result(rrno,rname,rmark);
end
loop;
close
r;
else
dbms_output.put_line('cursor
is not opened');
end
if;
end;
10)Write
a pl/sql block to give increment to each salary in following way:
a. Upto 5000 ( 15%
b. 5001 to 10000 ( 10%
c. above 10000 ( 5%
declare
cursor cur_emp is select *from emp1;
salx number:=0;
begin
for tmp in cur_emp
loop
if(tmp.sal<5000) then
update emp1 set sal=tmp.sal+tmp.sal*0.15 where sal<5000;
end if;
if tmp.sal>5000 and
tmp.sal<10000 then
update emp1 set sal=tmp.sal+tmp.sal*0.10 where sal>5000
and sal<10000;
else
update emp1 set sal=tmp.sal+tmp.sal*0.5 where sal>10000;
end if;
end loop;
end;
OUTPUT:-
PL/SQL procedure successfully
completed.
SQL> SELECT * FROM EMP1;
EMPNO EMPNAME DEPTNAME DEPTN0 SAL
---------- ---------- ---------- ---------- ----------
1 Mahesh SALES 101 1149
2 Akshay PURCHASE 102
1149
3 Payal SALES 700 1149
4 Amit
PURCHASE 501 1149
10)
write a PL/SQL
block to find minimum & maximum salary department-wise (Without using min
& max function)
declare
cursor cur_dept is select * from
dept1;
begin
declare
cursor
cur_emp(dno1 number) is select empno,sal from emp1 where dno1=empno;
minsal number;
maxsal number;
begin
for d_var in cur_dept
loop
minsal:=999999;
maxsal:=0;
dbms_output.put_line(d_var.deptname);
for e_var in cur_emp(d_var.deptno)
loop
if e_var.sal<minsal then
minsal:=e_var.sal;
end if;
if e_var.sal>maxsal then
maxsal:=e_var.sal;
end if;
end loop;
dbms_output.put_line(minsal);
dbms_output.put_line(maxsal);
end loop;
end;
end;
OUTPUT:-
SALES
1149
1149
SALES
1149
1149
PURCHASE
500
500
PL/SQL procedure successfully
completed.
11)Write
a PL/SQL block to transfer all employees
who are working in Write account department into Sales & Personal dept.
according to following designation.
If
Manager ( Sales
Otherwise ( Personal
declare
tmp
number;
tmp1 number;
tmp2 number;
begin
select deptno into tmp from empl where
deptname='account';
select deptno into tmp1 from
empl where deptname='sales';
select deptno into tmp2 from empl where
deptname='personal';
update empl
set deptno=tmp1
where deptno=tmp and
desig='manager';
update empl
set deptno=tmp2
where deptno=tmp and desig
<> 'manager';
end;
SQL> select * from empl;
DEPTNO DEPTNAME DESIG
---------- ---------- ----------
1 sales manager
2 personal manager
3 account manager
OUTPUT:-
PL/SQL procedure successfully
completed.
SQL> SELECT * FROM EMPL;
DEPTNO DEPTNAME
DESIG.
----------
---------- ----------
1 sales manager
2 personal manager
1 account manager
12)Write a trigger to Audit every Data Manipulation activity on Emp table.
Edit
Information Store in Audit_Emp Table. This table contain Audit
Activity,
Before Change Row values, Time of Change and Name of User who perform that
activity.
create table audit_emp
(activity varchar2(10),
eno number(5),
Ename varchar2(25),
SAL number(10,2),
deptno number(4),
desig varchar2(15),
username varchar2(25),
time_of_change number(8));
create or replace trigger trig_aud
after update or delete or insert
on emp1
for each row
declare
d date;
begin
select sysdate into d from dual;
if updating then
insert into audit_emp
values('update',:old.eno,:old.ename,:old.sal,:old.deptno,:old.desig,d,user);
elsif deleting then
insert into audit_emp
values('delete',:old.eno,:old.ename,:old.sal,:old.deptno,:old.desig,d,user);
elsif inserting then
insert into audit_emp
values('insert',:new.eno,:new.ename,:new.sal,:new.deptno,:new.desig,d,user);
end if;
end;
13)Table Prod_Mast(ProdId,ProdName,Qty),
PurchaseMast(ProdId,ProdName,Qty)
Write a trigger on PurchaseMast so that
when we enter any product info
in PurchaseMast then
(i)If Product Available in Prod_mast
then update Stock in Prod_Mast
(ii)If Product Not available in
Prod_Mast then enter that record in Prod_Mast .
create table prod_mast
(prodid number(3),
prodname varchar2(20),
qty
number(5));
create table purchasemast
( prodid number(3),
prodname varchar2(20),
qty
number(5));
create or replace trigger pur_bef_ins
after insert
on purchasemast
for each row
declare
no number;
begin
select
count(*) into no from prod_mast where prodid=:new.prodid;
if no=0 then
insert into prod_mast
values(:new.prodid,:new.prodname,:new.qty);
else
update prod_mast set
qty=qty+:new.qty where prodid=:new.prodid;
end if;
end;
14)Write a Audit trigger on Emp Table so that User Jack can
not change any Value and Any other user can change value from Monday to Friday Only.
create or replace trigger aud_emp
before insert or delete or update
on emp
for each row
declare
dd varchar2(20);
myexe exception;
begin
select to_char(sysdate,'day') into dd from
dual;
if user='jack' then
raise myexe;
elsif dd='Saturday' or dd='Sunday' then
raise myexe;
end if;
exception
when myexe then
dbms_output.put_line('The User can not change');
end;
OUTPUT:-
Trigger
created.
15)Table:MemTree(MemId,MemName,ParentId,MembershipDate)
Write a procedure MemAdd(ParentId,
FirstMem,SecondMem) to insert two
Member in Tree. MemAdd procedure should be
capable of concurrent- access.
create table memtree
(memid number(4),
memname varchar2(20),
parentid number(3),
membershipdate date);
create
or replace procedure add_tree(p in number,
f in varchar2,l in varchar2)
is
d date;
t number;
begin
lock table memtree in exclusive mode nowait;
select sysdate into d from dual;
select memid into t from memtree where
memid=p;
select max(memid) into t from memtree;
insert into memtree values(t+1,f ,p,d);
insert into memtree values(t+2,l,p,d);
exception
when no_data_found then
dbms_output.put_line('parent not exist');
end;
16)Write a function Number
MemCount(ParentId) to count all members(sub member)
under that Member.
// fun memcount (parent number)
create or replace function
memcount(p number)
return number
AS
d number;
begin
select memid into d from memtree where memid=p;
select count(memid) into d from memtree group by parentid having
(parentid=p);
return d;
exception
when NO_DATA_FOUND then
dbms_output.put_line('Parent id does
not exist');
end;
// calling
declare
i number:= 10;
begin
i:=memcount(i);
dbms_output.put_line(i);
end;
17)Write a procedure DeptMember to Display all Department
Name & Total Numbe of Employee of that department.
Procedure should use function number
TotEmp(DeptNo) to count total employee in given Department.
create or replace procedure deptmember
as
cursor cur_dept is select * from dept;
n number;
begin
for r_dept in cur_dept
loop
n:=countemp(r_dept.deptno);
dbms_output.put_line(r_dept.dname||'
'||n);
end loop;
end;
create or replace function countemp(dno
number)
return number
as
n number;
begin
select count(*) into n from emp where
deptno=dno;
return n;
end;
18)write a pl/sql code that determine the four highest commission getting employees
from the commission table.use rawcount cursor attributes for the same.display
the name and commission of these employee in proper format.(table commission
emp-no,emp-name,job,comm.)
Table
create table commission
(empno number(3)primary key,
empname varchar2(20),
job varchar2(15),
comm number(6));
SQL> desc commission;
Name Null? Type
-----------------------------------------
-------- ---------------
EMPNO NOT NULL NUMBER(3)
EMPNAME
VARCHAR2(20)
JOB VARCHAR2(15)
COMM
NUMBER(6)
Insert
table
insert into commission
values(&empno,'&empname','&job',&comm);
SQL> select * from commission;
EMPNO EMPNAME JOB COMM
---------- --------------------
--------------- ----------
1 Amit manager 6200
2 Nimesh manager 6500
3 Nilam peon 2000
4 hitesh clerk 3400
5 Ali
manager 643
6 Jitu clerk 998
7 kiran
peon 345
8 Payal clerk 4500
8 rows selected.
declare
mname
commission.empname%type;
mcomm
commission.comm%type;
cursor tmp
is
select empname,comm from commission order by comm desc;
begin
open
tmp;
dbms_output.put_line('NAME ' ||
' ' || ' COMMISSION');
loop
fetch tmp into mname,mcomm;
exit when (tmp%rowcount-1)=4 or
tmp%notfound;
dbms_output.put_line(mname || ' ' || mcomm);
end
loop;
close
tmp;
end;
SQL> @
e:\print\oracle\p5\program.sql
16 /
NAME COMMISSION
Nimesh 6500
Amit
6200
Payal
4500
hitesh
3400
PL/SQL procedure successfully
completed.
19)write a database trigger that not following chage to emp
table after the business hours(from 8: a.m. to :p.m.) from Monday to Saturday
,there is no restrictiuon on viewing data from a table.
Table
create table emp
(eno number(5)primary key,
ename varchar2(15),
salary number(6),
dept_no number(3),
city varchar2(15));
SQL> desc emp;
Name Null? Type
----------------------------------------- --------
ENO NOT NULL NUMBER(5)
ENAME
VARCHAR2(15)
SALARY
NUMBER(6)
DEPT_NO
NUMBER(3)
CITY
VARCHAR2(15)
Trigger
create or replace trigger trig_on_emp
before insert or delete or update on
emp
for each row
declare
not_on_sun
excepiton;
not_on_working_hours
exception;
begin
if(to_char(sysdate,'dy')='sun')then
raise
not_on_sun;
end
if;
if(to_char(sysdate,'hh24')
< '8' or to_char(sysdate,'hh24') > '17')then
raise
not_on_working_hours;
end
if;
exception
when not_on_sun then
DBMS_OUTPUT.PUT_LINE('Data Manipulaiton
Operation Is Not Allowed On Sunday');
exception
when not_on_working_hours then
DBMS_OUTPUT.PUT_LINE('Data Manipulaiton
Operation Is Allowed Only During Working Hours');
end;
20) consider the following sybca
with several records.
Table:sybca(rollno
number(3) primary key,name varchar2(20),tp
number(2) [tp total present during the 4th
sem])
write a pl/sql code to delete records from
table if tp <60 rearrange
all roll numbers in increasing order.
Table
create table sybca
(rno number(3)primary key,
name varchar2(15),
TP number(2));
SQL> desc sybca
Name Null? Type
-----------------------------------------
-------- -----------------
RNO NOT NULL
NUMBER(3)
NAME
VARCHAR2(15)
TP
NUMBER(2)
Insert
Table
insert into sybca
values(&rno,'&name',&TP);
SQL> select * from sybca;
RNO NAME TP
---------- --------------- ----------
1 Anamika 86.2
2 Sonalika 34.3
3 parul 56.4
4 Bhumi 46.6
5 amit 55.6
6 sonal 73.1
7 asmita 76.2
8 jayshi 65
8 rows selected.
Pl/sql
code
declare
mrno
sybca.rno%type;
mname
sybca.name%type;
mTP
sybca.TP%type;
cursor
cur is
select
* from sybca;
begin
open
cur;
if
cur%isopen then
loop
fetch
cur into mrno,mname,mTP;
exit
when cur%notfound;
if
Mtp<60 then
delete
from sybca where mrno=rno;
else
dbms_output.put_line('Roll no:'||mrno);
dbms_output.put_line('Name :'||mname);
dbms_output.put_line('Total
present :'||mTP);
end
if;
end
loop;
else
dbms_output.put_line('cursor
is not opened');
end
if;
close
cur;
end;
OUTPUT:
SQL> @
e:\Prayag\1.sql
27 /
Roll no :1
Name :Anamika
Total present
:86s
Roll no :2
Name :Sonalika
Total present
:65
Roll no :5
Name :amit
Total present
:65
Roll no :6
Name :Bhumi
Total present
:67
Roll no :7
Name :sonal
Total present
:64
PL/SQL
procedure successfully completed.
Comment Here