Assignment:3
Q-1. Write a pl/sql code block that will accept an student number from
the user and calculate total,avg and class. Insert all values related student number into stud_result table
if student has no found then
display user defined message ‘student number is not found’.
Student
(no,name,mark1,mark2,mark3,total,average,class)
Stud_result
(sno,sname,smark1,smark2,smark3,stotal,saverage,class
–
Create table stud
(
no number,
name varchar(20),
m1 number,
m2 number,
m3 number
);
Create table stud_result
(
sno number,
sname varchar(20),
sm1 number,
sm2 number,
sm3 number,
stotal number,
savg number,
sclass varchar2(20)
);
Insert into stud values(1,’vishal’,80,90,99);
Insert into stud values(2,’smita’,99,80,89);
Insert into stud values(3,’nikunj’,60,70,80);
Insert into stud values(4,’akshali’,90,80,99);
Insert into stud values(5,’dhaval’,80,70,69);
Declare
Srec
stud%rowtype;
sno
stud.no%type:=&no;
stotal
stud_result.stotal%type;
sclass stud_result.sclass%type;
savg stud_result.savg%type;
Begin
Select
* into srec from stud where no=sno;
Dbms_output.put_line(‘srec_no
‘|| srec.name ||’ ’||srec.m1 ||’ ’||srec.m2||’ ’||srec.m3);
stotal:=srec.m1+srec.m2+srec.m3;
dbms_output.put_line(‘total
is : ’||stotal);
savg:=
stotal/3;
dbms_output.put_line(‘average
is : ’||savg);
if(savg>=75)
then
sclass:=’distinction’;
dbms_output.put_line(‘class
is : ’||sclass);
elsif(savg>=60)
then
sclass:=’first
class’;
dbms_output.put_line(‘class
is : ’||sclass);
elsif(savg>=50)
then
sclass:=’second
class’;
dbms_output.put_line(‘class
is : ’||sclass);
elsif(savg>=40)
then
sclass:=’pass
class’;
dbms_output.put_line(‘class
is : ’||sclass);
else
sclass:=’fail’;
dbms_output.put_line(‘class
is : ’||sclass);
end if;
insert into stud_result
values(srec.no,srec.name,srec.m1,srec.m2,srec.m3,stotal,savg,sclass);
end;
Q-2. write a pl/sql
block which display name of dept, name of employee who is earning higest
salary of the department
Emp(empno,name,salary,deptno)
Dept(deptno,name)
Create table emp
(
empno
number primary key,
name
varchar2(10),
salary
number,
deptno number references dept(deptno)
);
Create table dept
(
Deptno
number primary key,
Dname
varchar2(15)
);
Insert into emp values(1,’vishal’,90000,10);
Insert into emp values(2,’dhaval’,50000,20);
Insert into emp values(3,’smita’,90000,30);
Insert into emp values(4,’akshali’,89999,40);
Insert into emp values(5,’rinkal’,60000,50);
Insert into dept values(10,’managing’);
Insert into dept values(20,’sales’);
Insert into dept values(30,’managing’);
Insert into dept values(40,’managing’);
Insert into dept values(50,’clark’);
Declare
Cursor dcursor is select d.dname,e.name,e.salary from emp
e,dept d where d.deptno=e.deptno and e.salary in( select max(salary) from emp);
Begin
For rec
in dcursor
Loop
Dbms_output.put_line(rec.dname||’ ’||rec.name||’ ’||rec.salary);
End loop;
End;
Q-3. The Bank Manager has
Decided to mark all those accounts as inactive(I) on which there are no transactions performed in the
last 365 days. Whenever any such updatetakes place, a record for
the same is
maintained in the
INACTV_ACCT_MSTR table comprising
of the account number, the opening date and type of account write a PL/SQL block using
Explicit cursor.
Acct_mstr(acct_no,status,opndt,type)
Trans_mst(trans_no,acct_no)
INACTV_ACCT_MSTR(acct_no,opndt,type)
declare
cursor acursor is select
acc_no,status,opndt,type from Acc_mstr
where acc_no in (select accno
from Trans_mst group by accno
having
(sysdate-max(tdate))>365);
crec Acc_mstr%rowtype;
begin
open acursor;
loop
fetch acursor
into crec;
exit when
acursor%notfound;
update Acc_mstr
set status='I'
where
acc_no=crec.acc_no;
insert into
INACT_ACC_MSTR values (crec.acc_no,crec.opndt,crec.type);
end loop;
close acursor;
end;
Q-4 .The Bank Manager has Decided to
mark all those accounts as inactive(I) on which there are no transactions
performed in the last 365 days. Whenever any such update takes place, a record
for the same is maintained in the INACTV_ACCT_MSTR table comprising of the
account number, the opening date and type of account write a PL/SQL block using
for loop in cursor.
Acct_mstr(acct_no,status,opndt,type)
Trans_mst(trans_no,acct_no,dt)
INACTV_ACCT_MSTR(acct_no,opndt,type)
DECLARE
Cursor acct is select * from
acct_mstr where acct_no in(select acct_no from trans_mst group by acct_no
having max(sysdate-dt)>365);
BEGIN
For datacur in
acct
Loop
Update acct_mstr set status=’s’
where acct_no = datacur.acct_no;
Insert into inactv_acct_mstr
values(datacur.acct_no,datacur.opndt,datacur.type);
end loop
Q-5. Write a program display all
department records
department(d_no,d_name,D_location)
Create table department
(d_no number primary key,
d_name varchar2(20),
d_loc varchar2(20));
insert into department(d_no,d_name,d_loc)values
(1,'vishal','Surat');
insert into department(d_no,d_name,d_loc)values
(2,'dhaval','Bardoli');
insert into department(d_no,d_name,d_loc)values
(3,'Smita','Nagpur');
insert into department(d_no,d_name,d_loc)values
(4,'akshali','Mumbai');
insert into department(d_no,d_name,d_loc)values
(5,'rinkal','Vapi');
declare
cursor
dcursor is select *from department;
drec
dcursor%rowtype;
begin
open
dcursor;
loop
fetch
dcursor into drec;
exit
when dcursor %notfound;
dbms_output.put_line(drec.d_no
|| ' ' || drec.d_name || ' ' || drec.d_loc);
end
loop;
close
dcursor;
end;
Q-6. Write A PL/SQL to display the
following:
department(d_no,d_name,D_location)
employee(e_no,e_name,e_hiredate,job,sal,comm)
DeptNo:______ Department Name:________
EmpNo EmpName
Designation
create table employee
(
e_no number primary key,
e_name varchar(20),
e_hiredate date,
e_job varchar(20),
e_sal number,
e_comm number);
insert into employee(e_no,e_name,e_hiredate,e_job,e_sal,e_comm)values
(101,'Rakesh','10-jun-2015','manager',5000,200);
insert into
employee(e_no,e_name,e_hiredate,e_job,e_sal,e_comm)values
(102,'Mohan','5-nov-2014','manager',7000,200);
insert into employee(e_no,e_name,e_hiredate,e_job,e_sal,e_comm)values
(103,'Piyush','20-dec-2016','clerk',4000,100);
declare
cursor
dcursor is select d_no,d_name from department
where
d_no in(select d_no from employee)
order
by d_no;
cursor
ecursor is select e_no,e_name,e_job,e_sal,e_comm
from
employee;
drec
dcursor%rowtype;
erec
ecursor%rowtype;
begin
open
dcursor;
loop
fetch
dcursor into drec;
exit
when dcursor%notfound;
dbms_output.put_line(drec.d_no
|| ' ' || drec.d_name);
dbms_output.put_line('e_no,
e_name, e_designation');
open
ecursor;
loop
fetch
ecursor into erec;
exit
when ecursor%notfound;
dbms_output.put_line(erec.e_no
|| ' ' || erec.e_name || ' ' || erec.e_job || ' ' || erec.e_sal || ' ' || erec.e_comm);
end
loop;
close
ecursor;
dbms_output.put_line(' ');
dbms_output.put_line(' ');
end
loop;
close
dcursor;
end;
Q-7.Write
a program using PL/SQL that
1)Fetches
a department from the department table.
2)For
each such department, fetches employee information belonging to that department
from
the employees table.
3)Then
insert all this information into employeesDept table
create table
department
(
d_no number(3) primary key,
d_name varchar2(30),
d_locatio varchar2(30)
);
insert into
department values(1,'Account','surat');
insert into
department values(2,'Manufacturing','Ahmedabad');
insert into
department values(3,'sales','delhi');
create table
employee
(
e_no number(3) primary key,
e_name varchar2(30),
e_hiredate date,
job varchar2(30),
sal number(10),
comm number(10),
dno number(3) references
department(d_no)
);
insert into
employee values(1,'Rahul','12-jun-2015','sales man',5000,1500,1);
insert into
employee values(2,'Sunil','13-jul-2001','manager',100000,60000,2);
insert into
employee values();
create table
employeeDept
(
d_no number(3) ,
d_name varchar2(30),
d_locatio varchar2(30),
e_no number(3) primary key,
e_name varchar2(30),
e_hiredate date,
job varchar2(30),
sal number(10),
comm number(10)
);
declare
cursor dcursor is select * from department
where d_no in(select dno from
employee);
cursor ecursor(vdno
department.d_no%type) is select * from
employee where dno=vdno;
begin
for drec in dcursor
loop
for erec in
ecursor(drec.d_no)
loop
insert
into employeeDept values(drec.d_no,drec.d_name,drec.d_locatio,
erec.e_no,erec.e_name,erec.e_hiredate,erec.job,erec.sal,erec.comm);
end loop;
end loop;
end;
Q-8. write a procedure which enters records of
all the toppers in toppers table.
toppers from all
stream
wise,year wise and sem wise
student(rollno,name,stream,year,sem,percentage)
topper(rollno,name,stream,year,sem)
create table student
( rollno number(3),
name varchar2(30),
stream varchar2(30),
year number(4),
sem number(2),
per number(3));
insert into
student values(1,'Sunil','bba',2001,2,98);
insert into
student values(2,'Anil','bba',2002,2,96);
insert into
student values(3,'Rohit','bba',2001,2,85);
insert into
student values(4,'Sumit','bca',2001,2,99);
insert into
student values(5,'Amit','bca',2001,2,85);
create table
topper
( rollno number(3),
name varchar2(30),
stream varchar2(30),
year number(4),
sem number(2));
create or
replace procedure ptopper is
cursor scursor
is select stream,year,sem,max(per) as vper from student
group by
stream,year,sem ;
cursor tcursor
(vstream student.stream%type,vyear student.year%type,vsem student.sem%type, vpe
student.per%type) is
select
rollno,name from student where stream=vstream and year=vyear and sem=vsem and
per=vpe;
begin
for srec in scursor
loop
for trec in
tcursor(srec.stream,srec.year,srec.sem,srec.vper)
loop
insert
into topper values(trec.rollno,trec.name,srec.stream,srec.year,srec.sem);
end loop;
end loop;
end;
Q-9. Write a function which takes
input as stream,year and sem along with other necessary paramenters and returns
total number of students who are
fail in specified
stream,year and sem(consider less than
40% as fail) student(rollno,name,stream,year,sem,percentage)
topper(rollno,name,stream,year,sem)
create table
student
(
rollno number(3),
name varchar2(30),
stream varchar2(30),
year number(4),
sem number(2),
per number(3)
);
insert into
student values(1,'Sunil','bba',2001,2,98);
insert into
student values(2,'Anil','bba',2002,2,96);
insert into
student values(3,'Rohit','bba',2001,2,85);
insert into
student values(4,'Sumit','bca',2001,2,99);
insert into
student values(5,'Amit','bca',2001,2,85);
create table
topper
( rollno number(3),
name varchar2(30),
stream varchar2(30),
year number(4),
sem number(2));
create or
replace function dis_fail
(vstream
student2.stream%type,
vyear
student2.year%type,
vsem
student2.sem%type)
return number is
vent number;
BEGIN
select count(*)into vent from
stud
where per<40 and
stream=vstream
and year=vyear
and sem=vsem;
return vent;
EXCEPTION
when no_data_found then
vent:=null;
END;
Q-10. Write a function which display the no of
items whose weight fall between a given range for
a particual color. write a pl/sql block to
call the function
Distributor(dno,dname,phno)
Item(ino,name,color,weight)
Dis_item(dno,ino,qty)
create or replace function range(a in number,b in number,c in
varchar2) return number is
no number;
begin
select count(*)
into no from (select * from item where colour=c and weight between a and b);
return no;
end;
declare
r1 number;
r2 number;
cl varchar2(10);
n number;
begin
r1:=&r1;
r2:=&r2;
cl:='&cl';
n:=range(r1,r2,'cl');
dbms_output.put_line('no
of item :'||n);
end;
Q-11.
create procedure which
take as argument
dno,ino and return
total quantity sold
by
distributor for this particular item
distributer(dno,dname,dadress,dphno)
item(ino,iname,colour,weight)
dist_item(dno,ino,qty)
create or
replace procedure disp_qty
(vdno in
dist_item.dno%type,
vion in
dist_item.ino%type) is
vqty
dist_item.qty%type;
BEGIN
select qty into vqty
from dist_item
where dno=vdno and ino=vion;
dbms_output.put_line(vqty);
EXCEPTION
when no_data_found then
dbms_output.put_line('item not
found');
END;
/
Q-12. create a function which return name of
department when employee name is passed.
Emp(empno,name,salary,deptno)
Dept(deptno,name)
create table dept
( dno number
primary key,
name varchar2(10)
unique,
loc varchar2(10)
);
insert into dept values('&dno','&name','&loc');
create table emp
( eno varchar2(10)
primary key,
ename
varchar2(10),
dob date,
salary number,
address
varchar2(20),
city
varchar2(10),
dno number
references dept
);
insert into emp
values('&eno','&ename','&dob','&salary','&address','&city','&dno');
create or replace function dept_name(enm in varchar2) return varchar2
is
dnm varchar2(20);
begin
select d.name
into dnm from emp e,dept d where e.dno=d.dno and e.ename=enm;
return dnm;
end;
Q-13. Write
a database trigger
that will not
allow the changes
to the salary of employees
who are having an experience less
than 3 years.
Table
(emp(eno(P.K.),name,salary,exp)
create table emp
(no number primary key,
name varchar(20),
salary number,
exp number);
insert into emp values(1,'Nilesh',1000,3);
insert into emp values(2,'Vinay',5000,2);
insert into emp values(3,'Hiren',10000,5);
create or replace trigger chk_exp
before update of salary
on emp
for each row
begin
if :old.exp<3
then
raise_application
error(-20001,'cant insert record');
end if;
end;
Q-14.
Create trigger that
will not allow
update of salary
for employee who
are in sales
department
Emp(no,name,dno,salary,designamtion)
Dept(dno,name,location)
create table dept
( dno number
primary key,
name varchar2(10)
unique,
loc varchar2(10)
);
insert into dept values('&dno','&name','&loc');
create table emp
( eno varchar2(10)
primary key,
ename
varchar2(10),
dob date,
salary number,
address
varchar2(20),
city
varchar2(10),
dno number
references dept
);
insert into emp
values('&eno','&ename','&dob','&salary','&address','&city','&dno');
create or replace trigger chk_emp before update of salary on emp
for each row
declare
nm varchar2(10);
begin
select d.name
into nm from dept d,emp e where e.dno=d.dno and e.eno = :old.eno;
if nm='salse'
then
raise_application
error(-20001,'cant insert record');
end if;
end;
Q-15. create
a trigger that
automatically insert into cust_no
in table when a new
record is
inserted.
Table customer(c_no,cname,city);
create table cust
( cno varchar2(5)
primary key,
cname
varchar2(10),
city varchar2(10)
);
create sequence cust_seq increment by 1 start with 1;
create or replace trigger custpk before insert on cust
for each row
declare
pk
varchar2(10);
begin
select
'c'||to_char(cust_seq.nextval) into pk from dual;
:new.cno:=pk;
end;
Q-16. Consider
Branch Master table create
a trigger that
automatically
generated
Branch_no & other
data can be
inserted into new
PKEY_LOOKUP table.
Create table branch_mst(Branch_no,Bname)
create table branch
( bno number
primary key,
bname
varchar2(10)
);
create table pk_lookup(pk varchar2(10));
create or replace trigger pk_branch before insert on branch
for each row
declare
lpk varchar2(10);
npk varchar2(10);
begin
begin
select
pk into lpk from pk_lookup;
exception
when
no_data_found then lpk:='b1';
end;
:new.bno:=lpk;
npk:=to_number(substr(lpk,2))+1;
lpk:='b'||npk;
if lpk='b2' then
insert
into pk_lookup values(lpk);
else
update
pk_lookup set pk=lpk;
end if;
end;
Q-17. Write a database trigger which generate
automatic primary key on
table emp(eno(P.K.),name,salary) like
E001,E002.
create table emp
( eno varchar2(10) primary key,
ename varchar2(10),
dob date,
salary number,
address varchar2(20),
city varchar2(10),
dno number references dept
);
create or
replace trigger pk_emp before insert on emp
for each row
declare
max varchar2(10);
new varchar2(10);
begin
select
nvl(max(to_number(substr(eno,2))),0) into max from emp;
new:=to_char((to_number(max)+1));
:new.eno:='e'||new;
end;
Q-18. create trigger which maintain log table
and store any action performed on emp table.
Emp(empno,name,salary,deptno)
Dept(deptno,name)
Log(logno,action,deptname)
create table log
( lno number
primary key,
action
varchar2(10),
dname
varchar2(10) references dept(name)
);
create sequence log_seq start with 1 increment by 1;
create or replace trigger record after insert or update or delete on
emp
for each row
declare
oper varchar2(8);
nm varchar2(10);
begin
if updating then
oper:='update';
end if;
if inserting then
oper:='insert';
end if;
if deleting then
oper:='delete';
end if;
select d.name
into nm from dept d,emp e where e.dno=d.dno and e.eno=:old.eno;
insert into log
values(log_seq.nextval,oper,nm);
end;
Q-19. write a trigger which executes at the
time of insert in student table and checks whether the
current student is
topper or notand if the current student is
topper then delete topper record
from topper table and insert current student's
record in topper table.
student(rollno,name,stream,year,sem,percentage)
topper(rollno,name,stream,year,sem)
create table stud
( rno number
primary key,
name
varchar2(10),
stream
varchar2(10),
year
varchar2(10),
sem varchar2(10),
percentage
number);
create table topper
( rno number
references stud,
name
varchar2(10),
stream
varchar2(10),
year
varchar2(10),
sem
varchar2(10));
create or replace trigger topper_info after insert on stud
for each row
declare
p number;
begin
select s.per into
p from stud s,topper t where s.rno=t.rno;
if :old.per>p
then
delete
from topper;
insert
into topper values(:old.rno,:old.name,:old.stream,:old.year,:old.sem);
end if;
end;
Q-20.
write a daabase trigger which will no allow qunatity less than 5.
distributer(dno,dname,dadress,dphno)
item(ino,iname,colour,weight)
dist_item(dno,ino,qty)
create table distributer
( dno number primary key,
dname varchar2(20),
daddress varchar2(20),
dphno number);
create table
item
( ino number primary key,
iname varchar2(20),
colour varchar2(20),
weight number);
create table
dist_item
( dno number references distributer(dno),
ino number references item(ino),
qty number);
insert into
distributer values(1,'aaa','surat',9317398638);
insert into
distributer values(2,'bbb','navsari',9732873998);
insert into
distributer values(3,'ccc','amroli',9813287612);
insert into item
values(11,'pen','red',100);
insert into item
values(12,'pencil','pink',120);
insert into item
values(13,'table','black',700);
insert into
dist_item values(1,11,20);
insert into
dist_item values(2,12,25);
insert into
dist_item values(3,13,28);
create or
replace trigger chk_validqty before insert on dist_item
for each row
BEGIN
if :new.qty<5 then
raise_application_error(-20001,'cannot
insert data because qty<5');
end if;
END;
Comments
Post a Comment