SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
ASSIGNMENT
1.write pl/sql program to accept user salary& calculation of net salary and
display it.
Da =15% HRA=41%
Basic salary<3000 pf=5%
Basic salary<=5000 pf=7%
Basic salary<=8000 pf=8%
Basic salary>=8000 pf=10%
Net salary:=basic+da+hra-pf
Ans:
Declare
Salary number(4);
Da number(4);
Hra number(4);
Pf number(4);
Net number(4);
Temp number(4);
Begin
Salary:=&salary;
Net:=0;
Page 1
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
If salary<3000 then
Pf:=5;
Elseif salary<=5000 then
Pf:=7;
Elseif salary<=8000 then
Pf:=8;
Elseif salary<=8000 then
Pf:=10;
End if;
DA:=15;
HRA:=41;
NET:=net+salary;
Temp:=(salary*da)/100;
Net:=net+temp;
Temp:=(salary*hra)/100;
Net:=net+temp;
Temp:=(salary*pf)/100;
Net:=net-temp;
Dbms_output.put_line(‘net salary=’llnet);
End ;
Page 2
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
/
OUTPUT
Enter value for basic: 25000
old 2: basic number(7):=&basic;
new 2: basic number(7):=25000;
Net salary : 36500
2.create table emp(no,name,salary)with input 5 records in table.
Ans:
Create table employee
Emp_no number(5),
Emp_name varchar(10),
Emp_salary number(5)
);
Insert into employee value(1,’rani’,10000);
Insert into employee value(2,’pari’,2000);
Insert into employee value(3,’kanu’,3000);
Insert into employee value(4,’dhara’,5000);
Insert into employee value(5,’piya’,6000);
Declare
Empno number(5);
Salary number(5);
Page 3
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Begin
Empno:=&empno;
Select emp_salary into sal from employee
Where emp_no=empno;
If sal>5000 then
Update employee
Set emp_salary=emp_salary+1000
Where emp_no=empno;
End if;
Goto print_table;
Dbms_output.put_line(‘procesdure not completed successfully’);
<<print_table>>
Dbms_output.put_line(‘procesdure completed successfully’);
End;
/
OUTPUT
select*from emp;
NO NAME SALARY
1 mahesh 10000
2 shital 4000
3 pinal 90000
4 yashvi 505000
5 jaimin 78000
Page 4
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
3.write pl/sql code block accept empno from user and display record of
particular emp details.emp(empno,name,salary)
Ans:
Create table employee
Empno number(4),
Name varchar(30),
Salary number(10)
);
Insert into employee values(1,’vina’,3000);
Insert into employee value(2,’priya’,4000);
Insert into employee value(3.’riya’,5000);
Insert into employee value(4,’raj’,6000);
Insert into employee value(5,’ravi’,2000);
Commit;
Declare
R emp% rowtype;
Emp number(10);
Begin
Dbms_output,put_line(‘enter display employee no’);
Page 5
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Emp:=&emp;
Select* into r from emp1 where r.empno=emp;
Dbms_output.put_line(r.empno||’ ‘||r.name)||’ ‘||r.salary);
End;
/
OUTPUT
Enter value for vempno: 3
old 2: vempno emp.empno%type:=&vempno;
new 2: vempno emp.empno%type:=3;
Empno : 3
Name :nutan
4.write pl/sql code block empno from user and check salary is greater then 1000
display rexcord.emp details also display message salary is not greater and emp is
not found.
Ans:
Create table emp1
Empno number(4),
Name varchar2(10),
Salary number(10)
);
Insert into employee value(1,’ravi’,2000);
Insert into employee value(3,’tina’,4000);
Page 6
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Insert into employee value(4,’riya’,2300);
Insert into employee value(5,’piyu’,5000);
Insert into employee value(2,’raj’,400);
Declare
R emp1% rowtype;
No number(3);
Begin
Dbms_output.put_line(‘enter serch emp no’);
No:=&no;
Select*into r from emp1 where empno=no;
If r.salary>=1000 then
Dbms_output.put_line(r.empno)||’ ‘||r.name)||’ ‘||r.salary);
Else
Dbms_output.put_line(‘salary is not greater then 1000’);
Dbms_output.put_line(‘record not found’);
End;
/
OUTPUT
Enter value for empno: 3
old 2: empno emp.empno%type:=&empno;
new 2: empno emp.empno%type:=3;
Empno :3
Page 7
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Name : tina
Salary :4000
5.write a pl/sql code block that will accept an employee number from user and
deduct salary by rs 5000……
Ans:
Create table emp
No number,
Name varchar2(20),
Salary number(30)
);
Insert into emp values(1,’jay’,6000);
Insert into emp values(2,’piya’,4000);
Insert into emp values(3,’raj’,5000);
Insert into emp values(4,’ravi’,3000);
Insert into emp values(5,’viral’,12000);
Commit;
Declare
Sal emp.salary%type;
Nu number(2);
Page 8
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Begin
Dbms_output.put_line(‘enter emp no’);
Nu:=ν
Select salary into sal from emp where no=nu;
Sal:=sal-5000;
If sal <2000 then
Dbms_output.put_line(‘salary less then 2000’);
Else
Update emp set salary=salary-5000 where no=nu;
Commit;
End if;
Exception
When no_data_found then
Dbms_output.put_line(‘emp no’ ||no ||’is not present’);
End;
/
OUTPUT
Enter value for empno: 4
old 2: empno emp.empno%type:=&empno;
new 2: empno emp.empno%type:=4;
Salary successfully deducted
6.write a prog which will raise customer exception when deleting record in emp
table…..
Page 9
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Ans:
Create table emp
No number(10),
Name varchar2(20),
Salary number(20)
);
Insert into emp values(1,’disha’,5000);
Insert into emp values(2,’pavan’,3000);
Insert into emp values(3,’gopi’,12000);
Insert into emp values(4,’siya’,7000);
Insert into emp values(5,’kanu’,11000);
Commit;
Declare
Cursor mycurser is select*from emp;
Del exception;
R emp%rowtype;
Begin
Open mycursor;
Page 10
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Loop
Fetch mycursor%notfound;
If r.salary>5000
Delete from emp where r.salary>5000;
Commit;
Else
Raise del;
End if;
Exception
When del then
Dbms_output.out_line(‘do not take less then 5000 salary’);
End;
7.write a custom exception which is implement while inserting record emp
table…
Ans:
Create table employee
Emp_no number(5),
Emp_name varchar2(10),
Page 11
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
Date_of_join date,
Emp_sal_number(5)
);
Declare
Empno number(5);
Empname varchar(10);
Dateofjoin date;
Empsal number(5);
Less_exception;
Pragma exception_init(less_,-20001);
Begin
Empno:=&empno;
Empname:=’&empname’;
Dateofjoin:=’&dateofjoin’;
Empsal:=&empsal;
If empsal<5000 then
Raise less_;
Else
Insert into employee values(empno,empname,dateofjoin,empsal);
Page 12
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
End if;
Exception
When less_then
Dbms_output.put_line(‘salary is less then 5000’);
End;
/
OUTPUT
Enter value for vempno: 21
old 2: vempno emp.empno%type:=&vempno;
new 2: vempno emp.empno%type:=21;
Enter value for vename: mans
old 3: vename emp.ename%type:='&vename';
new 3: vename emp.ename%type:='kajal';
Enter value for vdate: 27-dec-01
old 4: vdate date:='&vdate';
new 4: vdate date:='27-dec-01';
Enter value for vsalary: 1000
old 5: vsalary emp.sal%type:=&vsalary;
new 5: vsalary emp.sal%type:=1000;
Salary is less than 5000.
8.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)
Ans:
create or replace procedure
dist(a IN number, b IN number) as
dqty dist_item.qty%type:=0;
Page 13
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
cursor c1 is select * from dist_item where dno=a and ino=b;
begin
for rec in c1
loop
dqty:=dqty+rec.qty;
dbms_output.put_line(a||' '||b||' '||rec.qty);
end loop;
end;
/
declare
a Dist_item.dno%type:='&a';
b Dist_item.ino%type:='&b';
begin
dist(a, b);
end;
/
Enter value for a: 2
Enter value for b: 12 2 12 9
9.Write a procedure to display name of employee who are earning less then
20000 and born before 30 years. Emp(eno,ename,date-of-birth,salary)
Ans:
create or replace procedure sal_age is
Page 14
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
cursor c1 is select eno,name,(sysdate-dob)/365 "age" from emp where salary<20000
and ((sysdate- dob)/365)>30;
begin
for rec in c1 5 loop
dbms_output.put_line(rec.eno||' '||rec.name);
end loop;
end;
/
execute sal_age; E3 ankit
10.Write a procedure that is passed a student rollno and return back to student
name and phone no from the student table by calling procedure in PL/SQL
block. Student(rollno,name,age,phno,marks)
Ans:
create or replace procedure stud(no in number,nm out varchar2,ph out number) is
begin
select name,phno into nm,ph from student where no=rollno;
end;
/
declare
no student.rollno%type;
nm student.name%type;
ph student.phno%type;
begin
Page 15
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
no:=&no;
stud(no,nm,ph);
dbms_output.put_line('rollno'||' '||'name'||' '||'phno');
dbms_output.put_line(no||' '||nm||' '||ph);
end;
/
Enter value for no: 1 rollno name phno
1 royal 8354638234
11.Write a function display maximum salary of employee for the given
department no. Consider table emp(eno,name,salary,dno)
Ans:
create or replace function max_sal(dptno in varchar2)return number is
sal number(20);
begin
select max(salary)into sal from emp where dno=dptno;
return sal;
end;
/
declare
dptno varchar2(2):='&dptno';
begin
dbms_output.put_line('maximum salary is:'||max_sal(dptno));
end;
Page 16
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
/
Enter value for dptno: D2 maximum salary is:60000
12.Create a function which return name of department when employee name is
passed. Emp(empno,name,salary,deptno) Dept(deptno,name)
Ans:
create or replace function d1(ename in varchar2)
return varchar2 is
dnm varchar2(20);
begin
select d.dname into dnm from emp e,dept d where e.dno=d.deptno and
ename=e.name; return dnm;
end;
/
declare
ename varchar2(20):='&ename';
begin
dbms_output.put_line(' dept of employee:'||d1(ename));
end;
/
Enter value for ename: rutika
dept of employee:sales
Page 17
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
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.
emp(eno(P.K.),name,salary,exp)
Ans:
create or replace trigger exp before update on emp for each row
begin
if((sysdate - :old.exp)/365)<3 then
raise_application_error(-20001,'can not change salary because an experience is less
than 3 years'); end if;
end;
/
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)
Ans:
create or replace trigger sal_emp
before update of salary on emp for each row
declare
pragma autonomous_transaction;
nm varchar2(20);
begin
select d.dname into nm from dept d,emp e where e.dno=d.deptno;
if nm='sales' then
raise_application_error(-20001,'can not update salary');
end if;
Page 18
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
end;
/
OUTPUT
Update emp set salary=59000 where no:=55;
ERROR at line 1:
ORA-2001:can not update salary because sales department
15.Write a database trigger that not allowing change emp table after the
business hours (from 8:00 a.m. to 5:00 P.m.) from Monday to Saturday.
emp(eno(P.K.),name,salary).
Ans:
create or replace trigger tr1
before insert or update or delete on emp 3 begin
if (rtrim(to_char(sysdate, 'day')) = 'sunday')
then
raise_application_error(-20002, 'Cannot use on sunday');
elsif to_char(sysdate,'hh24')<9 or to_char(sysdate,'hh24')>17
then
raise_application_error(-20001,'can not do any changes because business time is
over');
end if;
end;
/
OUTPUT
Trigger created.
Page 19
SYBCA-DIV-1-SEM-3 RDBMS ROLL NO-77
SQL> UPDATE EMP SET NAME='PALAK' WHERE NO:=52;
UPDATE EMP SET NAME='PALAK' WHERE NO:52 *
ERROR at line 1:
ORA-00920: invalid relational operator
Page 20
