PL/SQL sample queries Part - II

26.Write a program to accept the 3 sides of the triangle and display the type of triangle
DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
PERIMETER NUMBER(7,2);
BEGIN
IF (A=B AND B=C AND C=A) THEN
DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE');
ELSIF A=B OR A=C OR C=B THEN
DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE');
ELSE
DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE');
END IF;
END;
/
27.Write a program accept the value of A,B&C display which is greater
DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
BEGIN
IF (A>B AND A>C) THEN
DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
ELSIF B>C THEN
DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
ELSE
DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
END IF;
END;
/
28.Write a program accept a string and check whether it is palindrome or not
DECLARE
S VARCHAR2(10):='&S';
L VARCHAR2(20);
TEMP VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(S)
LOOP
L:=SUBSTR(S,I,1);
TEMP:=TEMP||''||L;
END LOOP;
IF TEMP=S THEN
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS NOT PALINDROME');
END IF;
END;
/
29.Write a program aceepts the value of A,B and swap the nos and print the values
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
/
30.Write a program to accept the values of A , B and swap the numbers and print the values
without using third variable
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
/
31.Write a program to accept the side of a square and calculate the area area =a2
DECLARE
A NUMBER:=&A;
AREA NUMBER(5);
BEGIN
AREA:=A*A;
DBMS_OUTPUT.PUT_LINE('AREA OF A SQUARE IS '||''||AREA);
END;
/
32.Write a program to accept principle amount ,rate,time calculate the simple interest si=(p*t*r)/100
DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
SI NUMBER(6,2);
BEGIN
SI:=(P*R*T)/100;
DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
END;
/
33.Erite a program to aceept the principle amount,rate,time and find the compound interest
ci=p*(1+r/100)n
DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
CI NUMBER(6,2);
BEGIN
CI:=P*POWER(1+(R/100),T);
DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
END;
/
34.WAP to calculate the sum of 1!+2!+......+n!
DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
/
35.WAP to calculate the sum of 1+1/2+1/3+......+1/n
DECLARE
N NUMBER:=&N;
A NUMBER;
S NUMBER(6,2):=0;
BEGIN
FOR I IN 1..N
LOOP
A:=1/I;
S:=S+A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NO ARE '||S);
END;
/
36.WAP to calculate the sum of 1/1!+1/2!+.....+1/n!
DECLARE
N NUMBER:=&N;
S NUMBER(6,2):=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(1/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM IS '||S);
END;
/
37.WAP to calculate the sum of 1/1!+2/2!+......+n/n!
DECLARE
N NUMBER(4):=&N;
S NUMBER(6,2):=0;
F NUMBER(4):=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(I/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
/
38.Write a program to display the months between two dates of a year
DECLARE
D DATE:='&D';
D1 DATE:='&D1';
BEGIN
WHILE D < D1
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'MONTH'));
D:=ADD_MONTHS(D,1);
END LOOP;
END;
/
39.Write a program to accept the date and print the weekdays from the given date
DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY'));
D:=D+1;
END LOOP;
END;
/
40.WAP to accept the date and print the weekdays from the given date along with date format
DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY')||D);
D:=D+1;
END LOOP;
END;
/
41.Writa a program to accept a year and check whether it is leap year or not
DECLARE
Y NUMBER:=&Y;
R NUMBER;
BEGIN
IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0
THEN
DBMS_OUTPUT.PUT_LINE(Y ||' IS A LEAP YEAR');
ELSE
DBMS_OUTPUT.PUT_LINE(Y ||' IS NOT A LEAP YEAR');
END IF;
END;
/
42.Write a program to accept a year and display all sundays along with the date
DECLARE
Y NUMBER(4):=&YYYY;
A DATE;
B DATE;
I NUMBER(2):=1;
BEGIN
A:=TO_DATE('01-JAN-'||Y,'DD-MON-YYYY');
B:=LAST_DAY(ADD_MONTHS(A,11));
WHILE A <= B
LOOP
IF TO_CHAR(A,'D')=1 THEN
DBMS_OUTPUT.PUT_LINE(LPAD(I,2,'0')||'-'||UPPER(TO_CHAR(A,'DAY'))||A);
I:=I+1;
END IF;
A:=A+1;
END LOOP;
END;
/
43.WAP to accept a string and count how many vowels present in the string
DECLARE
V VARCHAR2(300):='&V';
CNT NUMBER(5):=0;
C CHAR;
BEGIN
FOR I IN 1..LENGTH(V)
LOOP
C:=SUBSTR(V,I,1);
IF C IN ('A','E','I','O','U') THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
END;
/
44.Write a program to accept a year and check whether it is leap year or not . If it is
leap year then display how many sundays present in that year
DECLARE
D DATE:='&YEAR';
Y VARCHAR2(20);
CNT NUMBER(5):=0;
V VARCHAR2(20);
BEGIN
Y:=TO_CHAR(D,'YYYY');
D:=TO_DATE('01-JAN-'||Y);
IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0 THEN
FOR I IN 1..366
LOOP
V:=TO_CHAR(D,'D');
IF V=1 THEN
CNT:=CNT+1;
END IF;
D:=D+1;
DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
END LOOP;
END;
/
45.Write a program to accept a char and check it is vowel or consonant
DECLARE
C CHAR:='&C';
BEGIN
IF C='A' OR C='E' OR C='I' OR C='O' OR C='U' THEN
DBMS_OUTPUT.PUT_LINE('VOWEL');
ELSE
DBMS_OUTPUT.PUT_LINE('CONSONANT');
END IF;
END;
/
46.WAP to accept A,B,C & D check whether it is Ramanujan number or not
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
D NUMBER:=&D;
BEGIN
IF
POWER(A,3)+POWER(B,3)=POWER(C,3)+POWER(D,3) THEN
DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'='||C||CHR(179)||'+'||D||CHR(179));
ELSE
DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'!='||C||CHR(179)||'+'||D||CHR(179));
END IF;
END;
/
47.WAP to accept the CMR & LMR & find out the total bill amount
i)0-100 units Rs.50 per unit ii)101-200n units Rs.o.25 per unit
iii)>200 units Rs.1.25 per unit
DECLARE
LMR NUMBER(5):=&LMR;
CMR NUMBER(5):=&CMR;
TOT NUMBER(5):=0;
BILL NUMBER(7,2):=0;
BEGIN
TOT:=CMR-LMR;
IF TOT <= 100 THEN
BILL:=TOT*.50;
ELSIF TOT > 100 AND TOT <= 200 THEN
BILL:=(100*.50)+((TOT-100)*.75);
ELSE
BILL:=(100*.50)+(100*.75)+(TOT-200)*1.25;
END IF;
DBMS_OUTPUT.PUT_LINE('TOTAL UNIT CONSUMED '||TOT);
DBMS_OUTPUT.PUT_LINE('TOTAL BILL AMOUNT '||BILL);
END;
/
48.WAP or accept marks of 3 subject as i/p and calculate the total marks and division of a student
i) If totmark>=60 then division is First
ii) If totmark <60 and totmark>=50 then division is second
iii) If totmark< 50 and >=35 then division is third
iv) If totmark< 35 then fail
DECLARE
M1 NUMBER(2):=&M1;
M2 NUMBER(2):=&M2;
M3 NUMBER(2):=&M3;
TOTMARK NUMBER(5,2);
AVE NUMBER(5,2):=0;
BEGIN
TOTMARK:=M1+M2+M3;
AVE:=TOTMARK/3;
IF AVE>=60 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS FIRST '||AVE);
ELSIF AVE<60 AND AVE>=50 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS SECOND '||AVE);
ELSIF AVE<50 AND AVE>=35 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS THIRD '||AVE);
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL '||AVE);
END IF;
END;
/
49.WAP to accept a number and print its multiplication table horinzontally
DECLARE
J NUMBER:=&J;
V VARCHAR2(1000);
K NUMBER(3);
BEGIN
FOR I IN 1..10
LOOP
K:=J*I;
V:=V||J||'*'||I||'='||K||' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/