PL/SQL Question With Answer

WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time


set serveroutput on
declare
HH number;
begin
HH:=TO_char(sysdate,'HH24');
if HH>6 and HH<12 then
dbms_output.put_line('GOOD MORNING');
else if HH>=12 and HH<18 then
dbms_output.put_line('GOOD AFTERNOON');
else if HH>=18 and HH<25 then
dbms_output.put_line('GOOD NIGHT');
end if;
end if;
end if;
end;

Results
GOOD MORNING 

PL/SQL procedure successfully completed.

WAP to print the value of A,B&C display which is greater?

set serveroutput on
declare
A number;
B number;
C number;
begin
A:=&A;
B:=&B;
C:=&C;
if (A>B and A>C) then
dbms_output.put_line('A is Greater '||A);
else if B>C then
dbms_output.put_line('B is Greater '||B);
else
dbms_output.put_line('C is Greater '||C);
end if;
end if;
end;

Results
old 6: A:=&A; 
new 6: A:=45; 
old 7: B:=&B; 
new 7: B:=10; 
old 8: C:=&C; 
new 8: C:=67; 
C is Greater 67 

PL/SQL procedure successfully completed.

Write a program to input the salary and working experience of employee and calculate the bonus as 10% of salary. Give 500 RS. Extra bonus to those whose working experience More than 10 years.

set serveroutput on
declare
Salary number;
Bonus number;
Year number;
begin
Salary := &Salary;
Year:= &Year;
Bonus:= Salary*.10;
if Year>10 then
Bonus:= Bonus + 500;
end if;
dbms_output.put_line('Bonus:'|| Bonus);
end;

Results
old 6: Salary := &Salary; 
new 6: Salary := 10000; 
old 7: Year := &Year; 
new 7: Year := 2012; 
Bonus:1500 

PL/SQL procedure successfully completed.


WAP to print increase the salary as per following criteria:
Salary amount Increment by
<12000 10%
<25000 12%
<45000 15%
otherwise 20%


set serveroutput on
declare
salary number;
begin
salary:=&salary;
if salary<12000 then
dbms_output.put_line('Increment by 10%'||salary*.10);
else if salary<25000 then
dbms_output.put_line('Increment by 10%'||salary*.12);
else if salary<45000 then
dbms_output.put_line('Increment by 15%'||salary*.15);
else
dbms_output.put_line('Increment by 20%'||salary*.20);
end if;
end if;
end if;
end;

Results
old 4: salary:=&salary; 
new 4: salary:=10000; 
Increment by 10%1000 
PL/SQL procedure successfully completed.

WAP to print increase the salary as per following criteria:
Salary amount Increment by
<12000 10%
<25000 12%
<45000 15%

set serveroutput on
declare
salary number;
begin
salary:=&salary;
if salary<12000 then
dbms_output.put_line('Increment by 10%'||salary*.10);
else if (salary<25000 and salary<45000) then
dbms_output.put_line('Increment by 12%'||salary*.12);
else
dbms_output.put_line('Increment by 15%'||salary*.15);
end if;
end if;
end;

Results
old 4: salary:=&salary; 
new 4: salary:=10000; 
Increment by 12%800 

PL/SQL procedure successfully completed.

WAP to print the increment  percentage on the basis of salary entered by the user.
Salary amount Increment by
<12000 10%
<25000 12%
<45000 15%

set serveroutput on
declare
salary number;
begin
salary:=&salary;
if salary<12000 then
dbms_output.put_line('increament by 10%');
else if (salary<25000 and salary<45000) then
dbms_output.put_line('increament by 12%');
else
dbms_output.put_line('increament by 15%');
end if;
end if;
end;

Results
old 4: salary:=&salary; 
new 4: salary:=10000; 
increament by 10% 

PL/SQL procedure successfully completed.

OR

Salary amount Increment by
<12000 10%
<25000 12%
<45000 15%
Others                   20%

set serveroutput on
declare
salary number;
begin
salary:=&salary;
if salary <12000 then
dbms_output.put_line('Increment by 10%');
else if salary <25000 then
dbms_output.put_line('Increment by 12%');
else if salary <45000 then
dbms_output.put_line('Increment by 15%');
else
dbms_output.put_line('Increment by 20%');
end if;
end if;
end if;

end;


Write a program to input the Basic Salary and calculate the HRA, DA and Net Salary as per:
Basic            HRA               DA
>15000            20%             10%
>12000            15%              8%
>9000              10%              6%
Others               5%               200/-

declare
Basic number;
HRA number;
DA number;
NET number;
begin
Basic := &Basic;
if Basic > 15000 then
HRA := Basic * .20;
DA := Basic * .10;
else if Basic > 12000 then
HRA := Basic * .15;
DA := Basic * .08;
else if Basic > 9000 then
HRA := Basic * .10;
DA := Basic * .06;
else
HRA := Basic * .05;
DA := Basic * 200;
end if;
NET := Basic + HRA + DA;
dbms_output.put_line('Basic: ' || Basic);
dbms_output.put_line('HRA: ' || HRA);
dbms_output.put_line('DA: ' || DA);
dbms_output.put_line('NET: ' || NET);
end if;
end if;
end;

Results
old 7: Basic := &Basic; 
new 7: Basic := 120000; 

PL/SQL procedure successfully completed.

Write a program to print the square & cube of a number?

set serveroutput on
DECLARE
x number;
square number;
cube number;
Begin
x:=&x;
square:=x*x;
cube:=X*X*x;
dbms_output.put_line('square='||square||'çube='||cube);
end;

Result
old 6: x:=&x; 
new 6: x:=10; 
square=100çube=1000 
PL/SQL procedure successfully completed.

Write a program to calculate the Average & Percentage of a student of a
five course?

set serveroutput on
DECLARE
S1 number;
S2 number;
S3 number;
S4 number;
S5 number;
Average number;
percentage number;
total number;
begin
S1:=&S1;
S2:=&S2;
S3:=&S3;
S4:=&S4;
S5:=&S5;
total:=S1+S2+S3+S4+S5;
Average:=total/5;
percentage:=total/500*100;
dbms_output.put_line('Average='||Average||'percentage='||percentage);
end;

Result
old 11: S1:=&S1; 
new 11: S1:=10; 
old 12: S2:=&S2; 
new 12: S2:=20; 
old 13: S3:=&S3; 
new 13: S3:=30; 
old 14: S4:=&S4; 
new 14: S4:=30; 
old 15: S5:=&S5; 
new 15: S5:=40; 
Average=26percentage=26 
PL/SQL procedure successfully completed.

WAP to print the Average Marks of 5 Students?

set serveroutput on
DECLARE
S1 number;
S2 number;
S3 number;
S4 number;
S5 number;
Average number;
total number;
begin
S1:=&S1;
S2:=&S2;
S3:=&S3;
S4:=&S4;
S5:=&S5;
total:=S1+S2+S3+S4+S5;
Average:=total/5;
dbms_output.put_line('Average='||Average);
end;

write a program print the Grade on the basis of percentage of marks ?

set serveroutput on
DECLARE
S1 number;
S2 number;
S3 number;
S4 number;
S5 number;
Average number;
percentage number;
total number;
begin
S1:=&S1;
S2:=&S2;
S3:=&S3;
S4:=&S4;
S5:=&S5;
total:=S1+S2+S3+S4+S5;
Average:=total/5;
percentage:=total/500*100;
dbms_output.put_line('Average='||Average||'percentage='||percentage);
if percentage>=90 then
dbms_output.put_line('Grade is A+');
else if(percentage>=70 and percentage<90) then
dbms_output.put_line('Grade is A');
else
dbms_output.put_line('Grade is B');
end if;
end if;
end;

Result
old 11: S1:=&S1; 
new 11: S1:=10; 
old 12: S2:=&S2; 
new 12: S2:=20; 
old 13: S3:=&S3; 
new 13: S3:=30; 
old 14: S4:=&S4; 
new 14: S4:=40; 
old 15: S5:=&S5; 
new 15: S5:=60; 
Average=32percentage=32 
Grade is B 
PL/SQL procedure successfully completed.

Write to print the Greater value?
DECLARE
x number;
y number;
begin
x:=&x;
y:=&y;
if x>y then
dbms_output.put_line('x is Greater');
else
dbms_output.put_line('y is Greater');
end if;
end;

Result
old 5: x:=&x; 
new 5: x:=10; 
old 6: y:=&y; 
new 6: y:=20; 
y is Greater 
PL/SQL procedure successfully completed.

WAP to print the number is Even/Odd?

set serveroutput on
DECLARE
x1 number;
begin
x1:=&x1;
if(x1 mod 2 = 0) then
dbms_output.put_line('Number is Even');
Else
dbms_output.put_line('Number is Odd');
End if;
end;

WAP to print the number is Positive/ Negative?

set serveroutput on
DECLARE
x1 number;
begin
x1:=&x1;
if x1>0 then
dbms_output.put_line('x1 is Positiver');
else
dbms_output.put_line('x1 is Negative');
end if;
end;

Result
old 4: x1:=&x1; 
new 4: x1:=-3; 
x1 is Negative 
PL/SQL procedure successfully completed.

Result
old 4: x1:=&x1; 
new 4: x1:=9; 
Number is Odd 
PL/SQL procedure successfully completed.


WAP to print the year is Leap/Not Leap Year?
set serveroutput on
DECLARE
year number;
begin
year:=&year;
if(year mod 4=0) then
dbms_output.put_line('year is Leap');
else
dbms_output.put_line('year is Not Leap');
End if;
End;

Result
old 4: year:=&year; 
new 4: year:=2016; 
year is Leap 
PL/SQL procedure successfully completed.