h9RS8NJW

11.Write a program to print the following format
2WELCOME TO PL/SQL PROGRAMMING
3BEGIN
4DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL PROGRAMMING');
5END;
6/
72.Write a program to print the numbers from 1 to 100
8DECLARE
9N NUMBER(3):=1;
10V VARCHAR2(1000);
11BEGIN
12WHILE N <=1000
13LOOP
14V:=V||''||N;
15N:=N+1;
16END LOOP;
17DBMS_OUTPUT.PUT_LINE(V);
18END;
19/
203.write a program to print the even numbers from 1 to 100
21DECLARE
22N NUMBER(3):=0;
23BEGIN
24WHILE N <=100
25LOOP
26N:=N+2;
27DBMS_OUTPUT.PUT_LINE(N);
28END LOOP;
29END;
30/
314.Write a program to print the odd numbers from 1 to 100
32DECLARE
33N NUMBER(3):=1;
34BEGIN
35WHILE N <=100
36LOOP
37N:=N+2;
38DBMS_OUTPUT.PUT_LINE(N);
39END LOOP;
40END;
41/
425.write a program for multiplication table
43DECLARE
44A NUMBER(2):=&A;
45B NUMBER(2):=1;
46C NUMBER(3);
47BEGIN
48WHILE B <=10
49LOOP
50C:=A*B;
51DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
52B:=B+1;
53END LOOP;
54END;
55/
566.write a program to find the sum of numbers from 1 to 100
57DECLARE
58N NUMBER(3):=1;
59S NUMBER(4):=0;
60BEGIN
61WHILE N <=100
62LOOP
63S:=S+N;
64N:=N+1;
65END LOOP;
66DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 IS '||S);
67END;
68/
697.Write a program to find the sum of all odd numbers from 1 to 100
70DECLARE
71N NUMBER(3):=1;
72S NUMBER(4):=0;
73BEGIN
74WHILE N <=100
75LOOP
76S:=S+N;
77N:=N+2;
78END LOOP;
79DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 ODD NUMBERS IS '||S);
80END;
81/
828.Write a program to find the sum of all even numbers from 1 to 100
83DECLARE
84N NUMBER(3):=0;
85S NUMBER(4):=0;
86BEGIN
87WHILE N <=100
88LOOP
89S:=S+N;
90N:=N+2;
91END LOOP;
92DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 EVEN NUMBERS IS '||S);
93END;
94/
959.Write a program to accept a number and find how many digits it contain
96DECLARE
97N NUMBER(5):=&N;
98CNT NUMBER:=0;
99R NUMBER(2):=0;
100BEGIN
101WHILE N !=0
102LOOP
103R:=MOD(N,10);
104CNT:=CNT+1;
105N:=TRUNC(N/10);
106END LOOP;
107DBMS_OUTPUT.PUT_LINE('NUMBER OF DIGITS OF GIVEN NUMBER IS '||CNT);
108END;
109/
11010.Write a program to accept a number and find the sum of the digits
111DECLARE
112N NUMBER(5):=&N;
113S NUMBER:=0;
114R NUMBER(2):=0;
115BEGIN
116WHILE N !=0
117LOOP
118R:=MOD(N,10);
119S:=S+R;
120N:=TRUNC(N/10);
121END LOOP;
122DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS OF GIVEN NUMBER IS '||S);
123END;
124/
12511.Write a program to accept a number and print it in reverse order
126DECLARE
127N NUMBER(5):=&N;
128REV NUMBER(5):=0;
129R NUMBER(5):=0;
130BEGIN
131WHILE N !=0
132LOOP
133R:=MOD(N,10);
134REV:=REV*10+R;
135N:=TRUNC(N/10);
136END LOOP;
137DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);
138END;
139/
14012.Write a program to accept a no and check whether it is Armstrong number or not
14113.Write a porgram to generate all the Armstrong numbers from 1 to 1000
14214.Write a program to generate all prime numbers between 1 to 100
14315.Write a program to aceept a number and check whether it is prime number or not
14416.Write a program to display the fibonacci series from 1 to 10
14517.Write a program to aceept a number and print it in binary format
14618.Write a program to accept a number and find the factorial of the number
14719.Find the factorials of numbers from 1 to 10
148DECLARE
149FACT NUMBER:=1;
150V VARCHAR2(100);
151BEGIN
152FOR I IN 1..10
153LOOP
154FOR J IN 1..I
155LOOP
156FACT:=FACT*J;
157V:=J||'*'||V;
158END LOOP;
159DBMS_OUTPUT.PUT_LINE(RTRIM(V,'*')||'='||FACT);
160FACT:=1;
161V:=NULL;
162END LOOP;
163END;
164/
16520.Write a program to aceept a number and display it in the Octal format
166DECLARE
167N NUMBER(2):=&N;
168R NUMBER(2);
169V VARCHAR2(1000);
170BEGIN
171WHILE N>0
172LOOP
173R:=MOD(N,8);
174V:=R||V;
175N:=TRUNC(N/8);
176END LOOP;
177DBMS_OUTPUT.PUT_LINE('OCTAL OF A GIVEN NUMBER IS '||V);
178END;
179/
18021.Write a program to accept a number and print the multiplication tables upto soo
181DECLARE
182N NUMBER(2):=&N;
183M NUMBER;
184BEGIN
185FOR I IN N..N+5
186LOOP
187FOR J IN 1..10
188LOOP
189M:=I*J;
190DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||M);
191END LOOP;
192DBMS_OUTPUT.PUT_LINE('*********************');
193END LOOP;
194END;
195/
19622.Write a program to accept the temp in Centigrade and convert it into Fahrenheit(c=F-32/1.8)
197DECLARE
198C NUMBER:=&C;
199F NUMBER;
200BEGIN
201F:=C*1.8+32;
202DBMS_OUTPUT.PUT_LINE('THE FARENHETT OF GIVEN OC IS '||F);
203END;
204/
20523.Write a program to calculate the area of a triangle by accepting the 3 sides
206(s=(a+b+c)/2 area=sqrt(s*(s-a)*(s-b)*(s-c)))
207DECLARE
208S NUMBER;
209A NUMBER:=&A;
210B NUMBER:=&B;
211C NUMBER:=&C;
212AREA NUMBER(7,2);
213BEGIN
214S:=(A+B+C)/2;
215AREA:=SQRT(S*(S-A)*(S-B)*(S-C));
216DBMS_OUTPUT.PUT_LINE('THE AREA OF TRIANGLE IS '||AREA);
217END;
218/
21924.Write a program to calculate the area of a circle by accepting the radius and unit of measure Area=PI*r2
220DECLARE
221R NUMBER:=&R;
222AREA NUMBER(7,2);
223BEGIN
224AREA:=(22/7)*R*R;
225DBMS_OUTPUT.PUT_LINE('THE AREA OF CIRCLE IS '||AREA);
226END;
227/
22825.Write a program to calculate the perimeter of a circle(perimeter=2*PI*r)
229DECLARE
230R NUMBER:=&R;
231PERIMETER NUMBER(7,2);
232BEGIN
233PERIMETER:=2*(22/7)*R;
234DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF CIRCLE IS '||PERIMETER);
235END;
236/
23726.Write a program to accept the 3 sides of the triangle and display the type of triangle
238DECLARE
239A NUMBER(4,2):=&A;
240B NUMBER(4,2):=&B;
241C NUMBER(4,2):=&C;
242PERIMETER NUMBER(7,2);
243BEGIN
244IF (A=B AND B=C AND C=A) THEN
245DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE');
246ELSIF A=B OR A=C OR C=B THEN
247DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE');
248ELSE
249DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE');
250END IF;
251END;
252/
25327.Write a program accept the value of A,B&C display which is greater
254DECLARE
255A NUMBER(4,2):=&A;
256B NUMBER(4,2):=&B;
257C NUMBER(4,2):=&C;
258BEGIN
259IF (A>B AND A>C) THEN
260DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
261ELSIF B>C THEN
262DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
263ELSE
264DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
265END IF;
266END;
267/
26828.Write a program accept a string and check whether it is palindrome or not
269DECLARE
270S VARCHAR2(10):='&S';
271L VARCHAR2(20);
272TEMP VARCHAR2(10);
273BEGIN
274FOR I IN REVERSE 1..LENGTH(S)
275LOOP
276L:=SUBSTR(S,I,1);
277TEMP:=TEMP||''||L;
278END LOOP;
279IF TEMP=S THEN
280DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS PALINDROME');
281ELSE
282DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS NOT PALINDROME');
283END IF;
284END;
285/
28629.Write a program aceepts the value of A,B and swap the nos and print the values
287DECLARE
288A NUMBER(2):=&A;
289B NUMBER(2):=&B;
290FLAG NUMBER(2);
291BEGIN
292FLAG:=A;
293A:=B;
294B:=FLAG;
295DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
296END;
297/
29830.Write a program to accept the values of A , B and swap the numbers and print the values
299without using third variable
300DECLARE
301A NUMBER(2):=&A;
302B NUMBER(2):=&B;
303FLAG NUMBER(2);
304BEGIN
305FLAG:=A;
306A:=B;
307B:=FLAG;
308DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
309END;
310/
31131.Write a program to accept the side of a square and calculate the area area =a2
312DECLARE
313A NUMBER:=&A;
314AREA NUMBER(5);
315BEGIN
316AREA:=A*A;
317DBMS_OUTPUT.PUT_LINE('AREA OF A SQUARE IS '||''||AREA);
318END;
319/
32032.Write a program to accept principle amount ,rate,time calculate the simple interest si=(p*t*r)/100
321DECLARE
322P NUMBER(6,2):=&P;
323R NUMBER(6,2):=&R;
324T NUMBER(6,2):=&T;
325SI NUMBER(6,2);
326BEGIN
327SI:=(P*R*T)/100;
328DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
329END;
330/
33133.Erite a program to aceept the principle amount,rate,time and find the compound interest
332ci=p*(1+r/100)n
333DECLARE
334P NUMBER(6,2):=&P;
335R NUMBER(6,2):=&R;
336T NUMBER(6,2):=&T;
337CI NUMBER(6,2);
338BEGIN
339CI:=P*POWER(1+(R/100),T);
340DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
341END;
342/
34334.WAP to calculate the sum of 1!+2!+......+n!
344DECLARE
345N NUMBER:=&N;
346S NUMBER:=0;
347F NUMBER:=1;
348BEGIN
349FOR I IN 1..N
350LOOP
351FOR J IN 1..I
352LOOP
353F:=F*J;
354END LOOP;
355S:=S+F;
356F:=1;
357END LOOP;
358DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
359END;
360/
36135.WAP to calculate the sum of 1+1/2+1/3+......+1/n
362DECLARE
363N NUMBER:=&N;
364A NUMBER;
365S NUMBER(6,2):=0;
366BEGIN
367FOR I IN 1..N
368LOOP
369A:=1/I;
370S:=S+A;
371END LOOP;
372DBMS_OUTPUT.PUT_LINE('SUM OF NO ARE '||S);
373END;
374/
37536.WAP to calculate the sum of 1/1!+1/2!+.....+1/n!
376DECLARE
377N NUMBER:=&N;
378S NUMBER(6,2):=0;
379F NUMBER:=1;
380BEGIN
381FOR I IN 1..N
382LOOP
383FOR J IN 1..I
384LOOP
385F:=F*J;
386END LOOP;
387S:=S+(1/F);
388END LOOP;
389DBMS_OUTPUT.PUT_LINE('SUM IS '||S);
390END;
391/
39237.WAP to calculate the sum of 1/1!+2/2!+......+n/n!
393DECLARE
394N NUMBER(4):=&N;
395S NUMBER(6,2):=0;
396F NUMBER(4):=1;
397BEGIN
398FOR I IN 1..N
399LOOP
400FOR J IN 1..I
401LOOP
402F:=F*J;
403END LOOP;
404S:=S+(I/F);
405END LOOP;
406DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
407END;
408/
40938.Write a program to display the months between two dates of a year
410DECLARE
411D DATE:='&D';
412D1 DATE:='&D1';
413BEGIN
414WHILE D < D1
415LOOP
416DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'MONTH'));
417D:=ADD_MONTHS(D,1);
418END LOOP;
419END;
420/
42139.Write a program to accept the date and print the weekdays from the given date
422DECLARE
423D DATE:='&D';
424WD DATE;
425BEGIN
426WD:=D+6;
427WHILE D <= WD
428LOOP
429DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY'));
430D:=D+1;
431END LOOP;
432END;
433/
43440.WAP to accept the date and print the weekdays from the given date along with date format
435DECLARE
436D DATE:='&D';
437WD DATE;
438BEGIN
439WD:=D+6;
440WHILE D <= WD
441LOOP
442DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY')||D);
443D:=D+1;
444END LOOP;
445END;
446/
44741.Writa a program to accept a year and check whether it is leap year or not
448DECLARE
449Y NUMBER:=&Y;
450R NUMBER;
451BEGIN
452IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0
453THEN
454DBMS_OUTPUT.PUT_LINE(Y ||' IS A LEAP YEAR');
455ELSE
456DBMS_OUTPUT.PUT_LINE(Y ||' IS NOT A LEAP YEAR');
457END IF;
458END;
459/
46042.Write a program to accept a year and display all sundays along with the date
461DECLARE
462Y NUMBER(4):=&YYYY;
463A DATE;
464B DATE;
465I NUMBER(2):=1;
466BEGIN
467A:=TO_DATE('01-JAN-'||Y,'DD-MON-YYYY');
468B:=LAST_DAY(ADD_MONTHS(A,11));
469WHILE A <= B
470LOOP
471IF TO_CHAR(A,'D')=1 THEN
472DBMS_OUTPUT.PUT_LINE(LPAD(I,2,'0')||'-'||UPPER(TO_CHAR(A,'DAY'))||A);
473I:=I+1;
474END IF;
475A:=A+1;
476END LOOP;
477END;
478/
47943.WAP to accept a string and count how many vowels present in the string
480DECLARE
481V VARCHAR2(300):='&V';
482CNT NUMBER(5):=0;
483C CHAR;
484BEGIN
485FOR I IN 1..LENGTH(V)
486LOOP
487C:=SUBSTR(V,I,1);
488IF C IN ('A','E','I','O','U') THEN
489CNT:=CNT+1;
490END IF;
491END LOOP;
492DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
493END;
494/
49544.Write a program to accept a year and check whether it is leap year or not . If it is
496leap year then display how many sundays present in that year
497DECLARE
498D DATE:='&YEAR';
499Y VARCHAR2(20);
500CNT NUMBER(5):=0;
501V VARCHAR2(20);
502BEGIN
503Y:=TO_CHAR(D,'YYYY');
504D:=TO_DATE('01-JAN-'||Y);
505IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0 THEN
506FOR I IN 1..366
507LOOP
508V:=TO_CHAR(D,'D');
509IF V=1 THEN
510CNT:=CNT+1;
511END IF;
512D:=D+1;
513DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
514END LOOP;
515END;
516/
51745.Write a program to accept a char and check it is vowel or consonant
518DECLARE
519C CHAR:='&C';
520BEGIN
521IF C='A' OR C='E' OR C='I' OR C='O' OR C='U' THEN
522DBMS_OUTPUT.PUT_LINE('VOWEL');
523ELSE
524DBMS_OUTPUT.PUT_LINE('CONSONANT');
525END IF;
526END;
527/
52846.WAP to accept A,B,C & D check whether it is Ramanujan number or not
529DECLARE
530A NUMBER:=&A;
531B NUMBER:=&B;
532C NUMBER:=&C;
533D NUMBER:=&D;
534BEGIN
535IF
536POWER(A,3)+POWER(B,3)=POWER(C,3)+POWER(D,3) THEN
537DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'='||C||CHR(179)||'+'||D||CHR(179));
538ELSE
539DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'!='||C||CHR(179)||'+'||D||CHR(179));
540END IF;
541END;
542/
54347.WAP to accept the CMR & LMR & find out the total bill amount
544i)0-100 units Rs.50 per unit ii)101-200n units Rs.o.25 per unit
545iii)>200 units Rs.1.25 per unit
546DECLARE
547LMR NUMBER(5):=&LMR;
548CMR NUMBER(5):=&CMR;
549TOT NUMBER(5):=0;
550BILL NUMBER(7,2):=0;
551BEGIN
552TOT:=CMR-LMR;
553IF TOT <= 100 THEN
554BILL:=TOT*.50;
555ELSIF TOT > 100 AND TOT <= 200 THEN
556BILL:=(100*.50)+((TOT-100)*.75);
557ELSE
558BILL:=(100*.50)+(100*.75)+(TOT-200)*1.25;
559END IF;
560DBMS_OUTPUT.PUT_LINE('TOTAL UNIT CONSUMED '||TOT);
561DBMS_OUTPUT.PUT_LINE('TOTAL BILL AMOUNT '||BILL);
562END;
563/
56448.WAP or accept marks of 3 subject as i/p and calculate the total marks and division of a student
565i) If totmark>=60 then division is First
566ii) If totmark <60 and totmark>=50 then division is second
567iii) If totmark< 50 and >=35 then division is third
568iv) If totmark< 35 then fail
569DECLARE
570M1 NUMBER(2):=&M1;
571M2 NUMBER(2):=&M2;
572M3 NUMBER(2):=&M3;
573TOTMARK NUMBER(5,2);
574AVE NUMBER(5,2):=0;
575BEGIN
576TOTMARK:=M1+M2+M3;
577AVE:=TOTMARK/3;
578IF AVE>=60 THEN
579DBMS_OUTPUT.PUT_LINE('THE DIVISION IS FIRST '||AVE);
580ELSIF AVE<60 AND AVE>=50 THEN
581DBMS_OUTPUT.PUT_LINE('THE DIVISION IS SECOND '||AVE);
582ELSIF AVE<50 AND AVE>=35 THEN
583DBMS_OUTPUT.PUT_LINE('THE DIVISION IS THIRD '||AVE);
584ELSE
585DBMS_OUTPUT.PUT_LINE('FAIL '||AVE);
586END IF;
587END;
588/
58949.WAP to accept a number and print its multiplication table horinzontally
590DECLARE
591J NUMBER:=&J;
592V VARCHAR2(1000);
593K NUMBER(3);
594BEGIN
595FOR I IN 1..10
596LOOP
597K:=J*I;
598V:=V||J||'*'||I||'='||K||' ';
599END LOOP;
600DBMS_OUTPUT.PUT_LINE(V);
601END;
602/
60350.WAP to accept a string and print it in reverse order
604DECLARE
605STR VARCHAR2(100):='&sTR';
606STR1 VARCHAR2(100);
607N NUMBER(5);
608L VARCHAR2(20);
609BEGIN
610N:=LENGTH(STR);
611FOR I IN 1..N
612LOOP
613L:=SUBSTR(STR,I,1);
614STR1:=L||STR1;
615END LOOP;
616DBMS_OUTPUT.PUT_LINE(STR1);
617END;
618/
61951.Write a program to accept a number and find out the sum of first and
620last digits
621DECLARE
622A NUMBER(4):=&A;
623B NUMBER(5):=0;
624C NUMBER(5):=0;
625S NUMBER(5);
626BEGIN
627IF A>9 THEN
628C:=SUBSTR(A,1,1);
629B:=SUBSTR(A,LENGTH(A),1);
630S:=B+C;
631ELSE
632S:=A;
633END IF;
634DBMS_OUTPUT.PUT_LINE('SUM OF FIRST AND LAST DIGIT IS '||S);
635END;
636/
63752.WAP to accept the basic salary and find out the ta,da,hra,lic and gs
638i)ta 20% of basic, da 10% of basic, hra 30% of basic, lic 5% of basic
639DECLARE
640BS NUMBER(6,2):=&BS;
641TA NUMBER(6,2);
642DA NUMBER(6,2);
643HRA NUMBER(6,2);
644GS NUMBER(6,2);
645LIC NUMBER(6,2);
646NS NUMBER(8,2);
647BEGIN
648TA:=BS*(20/100);
649HRA:=BS*(30/100);
650DA:=BS*(10/100);
651LIC:=BS*(5/100);
652GS:=TA+HRA+DA;
653NS:=GS-LIC;
654DBMS_OUTPUT.PUT_LINE('EMPLOYEE BS IS '||BS);
655DBMS_OUTPUT.PUT_LINE('GROSS SALARY IS '||GS);
656DBMS_OUTPUT.PUT_LINE('NET SALARY IS '||NS);
657END;
658/
65953.WAP to accept the length and breadth of a rectangle and find out the perimeter
660DECLARE
661L NUMBER(4,2):=&L;
662B NUMBER(4,2):=&B;
663A NUMBER(4,2);
664BEGIN
665A:=2*(L+B);
666DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF RECTANGLE IS '||A);
667END;
668/
66954.WAP to accept the cost price and selling price of an item and find
670the loss or profit
671DECLARE
672CP NUMBER(25,2):=&CP;
673SP NUMBER(25,2):=&SP;
674AMT NUMBER(7,2);
675BEGIN
676IF CP < SP THEN
677AMT:=SP-CP;
678DBMS_OUTPUT.PUT_LINE('PROFIT IS '||AMT);
679ELSE
680AMT:=CP-SP;
681DBMS_OUTPUT.PUT_LINE('LOSS IS '||AMT);
682END IF;
683END;
684/
68555.Writ a program to generate the following series
68653 53 53 53 53
68743 43 43 43
68833 33 33
68923 23
69013
691DECLARE
692V VARCHAR2(20);
693BEGIN
694FOR I IN REVERSE 1..5
695LOOP
696FOR J IN 1..I
697LOOP
698V:=V||I||CHR(179);
699END LOOP;
700DBMS_OUTPUT.PUT_LINE(V);
701V:=NULL;
702END LOOP;
703END;
704/
70556.WAP to accept a no in binary format and print it in decimal format
706DECLARE
707N VARCHAR2(20):=&N;
708PRO NUMBER(10,4):=0;
709L VARCHAR2(10);
710BEGIN
711FOR I IN 1..LENGTH(N)
712LOOP
713L:=SUBSTR(N,I,1);
714PRO:=PRO+L*POWER(2,LENGTH(N)-I);
715END LOOP;
716DBMS_OUTPUT.PUT_LINE('THE DECIMAL NUMBER IS '||PRO);
717END;
718/
71957.WAP to accept two nos and input and find one no is raised to another one (without using any function)
720DECLARE
721A NUMBER:=&A;
722B NUMBER:=&B;
723R NUMBER:=1;
724BEGIN
725FOR I IN 1..B
726LOOP
727R:=R*A;
728END LOOP;
729DBMS_OUTPUT.PUT_LINE('A RAISED POWER B IS '||R);
730END;
731/
73258.WAP to accept a sentence and count the no of chars in that sentence
733DECLARE
734STR VARCHAR2(100):='&STR';
735NO NUMBER(5):=0;
736I NUMBER;
737BEGIN
738I:=INSTR(STR,'.');
739DBMS_OUTPUT.PUT_LINE('NO OF CHAR IS '||I);
740END;
741/
74259.WAP to accept two strings and display the large one among those
743DECLARE
744STR1 VARCHAR2(100):='&STR1';
745STR2 VARCHAR2(100):='&STR2';
746BEGIN
747IF LENGTH(STR1) > LENGTH(STR2) THEN
748DBMS_OUTPUT.PUT_LINE(STR1 ||' IS GREATER');
749ELSIF LENGTH(STR1) < LENGTH(STR2) THEN
750DBMS_OUTPUT.PUT_LINE(STR2 ||' IS GREATER');
751ELSE
752DBMS_OUTPUT.PUT_LINE('BOTH STRINGS ARE EQUAL');
753END IF;
754END;
755/
75660.WAP to display all the nos whose sum of digits is 9 from 1 to 9999
757DECLARE
758N NUMBER;
759M NUMBER;
760S NUMBER:=0;
761BEGIN
762FOR I IN 1..999
763LOOP
764N:=I;
765WHILE N>0
766LOOP
767M:=MOD(N,10);
768S:=S+M;
769N:=TRUNC(N/10);
770END LOOP;
771IF S=9 THEN
772DBMS_OUTPUT.PUT_LINE(I||' ');
773END IF;
774S:=0;
775END LOOP;
776END;
777/
77861.WAP to accept a no and find the sum in a single digit
779DECLARE
780N NUMBER(4):=&N;
781S NUMBER(10):=0;
782BEGIN
783WHILE LENGTH(N)>1
784LOOP
785FOR I IN 1..LENGTH(N)
786LOOP
787S:=S+SUBSTR(N,I,1);
788END LOOP;
789N:=S;
790S:=0;
791END LOOP;
792DBMS_OUTPUT.PUT_LINE('THE SUM IN SINGLE DIGIT IS '||N);
793END;
794/
79562.Ente the no of days and find out the no of years and no of days and months
796DECLARE
797D NUMBER:=&D;
798Y NUMBER;
799M NUMBER;
800BEGIN
801Y:=TRUNC(D/365);
802M:=TRUNC(MOD(D,365)/30);
803D:=MOD(MOD(D,365),30);
804DBMS_OUTPUT.PUT_LINE(Y||' YEARS '||M||' MONTHS '||D||' DAYS');
805END;
806/
80763.WAP to accept the date and print all the weekdays along with the given date
808DECLARE
809D DATE:='&D';
810V VARCHAR2(20);
811BEGIN
812FOR I IN 1..7
813LOOP
814V:=TO_CHAR(D,'DAY')||D;
815DBMS_OUTPUT.PUT_LINE(V);
816D:=D+1;
817END LOOP;
818END;
819/
82064.WAP while purchasing certain items,discount of each is as follows
821i) If qty purchased > 1000 discount is 20%
822ii) If the qty and price per item are i/p then calculate the expenditure
823DECLARE
824QTY NUMBER(5):=&QTY;
825UP NUMBER(6,2):=&UP;
826DIS NUMBER(6,2):=0;
827TAMT NUMBER(10,2);
828BILL NUMBER(10,2);
829BEGIN
830BILL:=QTY*UP;
831IF BILL > 1000 THEN
832DIS:=BILL*20/1000;
833END IF;
834TAMT:=BILL-DIS;
835DBMS_OUTPUT.PUT_LINE('THE TOTAL AMOUNT IS '||TAMT);
836END;
837/
83865.Write a program to accept a string and count the no of individual chars
839DECLARE
840V VARCHAR2(100):='&V';
841V1 VARCHAR2(100);
842LB NUMBER;
843LA NUMBER;
844DIFF NUMBER;
845C CHAR;
846N NUMBER(5):=0;
847BEGIN
848V1:=V;
849WHILE LENGTH(V1)>0
850LOOP
851C:=SUBSTR(V1,1,1);
852LB:=LENGTH(V1);
853V1:=REPLACE(V1,C);
854LA:=NVL(LENGTH(V1),0);
855DIFF:=LB-LA;
856IF ASCII(C)=32 THEN
857DBMS_OUTPUT.PUT_LINE('SPACE'||' EXISTS '||DIFF||' TIMES');
858ELSE
859DBMS_OUTPUT.PUT_LINE(C||' EXISTS '||DIFF||' TIMES');
860END IF;
861N:=N+DIFF;
862END LOOP;
863DBMS_OUTPUT.PUT_LINE('TOTAL LENGTH OF THE GIVEN STRING '||V||'='||N);
864END;
865/
86666.Write a program to display all combination of 1,2,&3
867BEGIN
868FOR I IN 1..3
869LOOP
870FOR J IN 1..3
871LOOP
872FOR K IN 1..3
873LOOP
874DBMS_OUTPUT.PUT_LINE(I||J||K);
875END LOOP;
876END LOOP;
877END LOOP;
878END;
879/
88067.Write a program to find out the series 12+22+32+42+....++n2
881DECLARE
882N NUMBER:=&N;
883A NUMBER:=1;
884B NUMBER:=2;
885C NUMBER:=0;
886D NUMBER:=0;
887S NUMBER:=0;
888BEGIN
889WHILE A<=N
890LOOP
891C:=C+A*A;
892A:=A+2;
893END LOOP;
894WHILE B<=N
895LOOP
896D:=D+B*B;
897B:=B+2;
898END LOOP;
899S:=C-D;
900DBMS_OUTPUT.PUT_LINE('RESULT IS '||S);
901END;
902/
90368.Write a program to accep the time in HH & MIN format and find the total senconds
904DECLARE
905H NUMBER:=&HOUR;
906M NUMBER:=&MINUTE;
907S NUMBER(10):=0;
908BEGIN
909S:=(H*60*60)+(M*60);
910DBMS_OUTPUT.PUT_LINE(H||' HOURS '||M||' MINUTES '||'IS'||S||' SECONDS');
911END;
912/
91369.WAP to accept the distance between two cities in km and convert into mts ,cm & ft
914DECLARE
915D NUMBER:=&D;
916M NUMBER:=0;
917CM NUMBER:=0;
918FT NUMBER:=0;
919BEGIN
920M:=D*1000;
921CM:=M*100;
922FT:=ROUND(CM/12.3);
923DBMS_OUTPUT.PUT_LINE('DISTANCE IN METERS IS '||M);
924DBMS_OUTPUT.PUT_LINE('DISTANCE IN CENTIMETERS IS '||CM);
925DBMS_OUTPUT.PUT_LINE('DISTANCE IN FOOT IS '||FT);
926END;
927/
92870.Write a program to find the series x+x2/2!+x3/3!+.....+xn/n!
929DECLARE
930N NUMBER:=&N;
931X NUMBER:=&X;
932S NUMBER:=0;
933F NUMBER:=1;
934BEGIN
935FOR I IN 1..N
936LOOP
937FOR J IN 1..I
938LOOP
939F:=F*J;
940END LOOP;
941S:=ROUND(s+(POWER(X,I)/F),3);
942F:=1;
943END LOOP;
944DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS '||S);
945END;
946/
94771.Write a program to accept the population of hyderabad each year the population increases
9482% after 4y what is the population of hyd
949DECLARE
950P NUMBER:=&P;
951L NUMBER;
952BEGIN
953FOR J IN 1..4
954LOOP
955L:=P*2/100;
956P:=P+L;
957END LOOP;
958DBMS_OUTPUT.PUT_LINE('POPULATION OF HYDERABAD AFTER 4 YEARS IS '||TRUNC(P));
959END;
960/
96172.WAP to accept the 3 dates and display the most recently month among 3 dates
962DECLARE
963D1 DATE:='&D1';
964D2 DATE:='&D2';
965D3 DATE:='&D3';
966M1 NUMBER;
967M2 NUMBER;
968M3 NUMBER;
969BEGIN
970M1:=TO_CHAR(D1,'MM');
971M2:=TO_CHAR(D2,'MM');
972M3:=TO_CHAR(D3,'MM');
973IF M1>M2 AND M1>M3 THEN
974DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'MON')||' IS RECENT MONTH');
975ELSIF M2>M1 AND M2>M3 THEN
976DBMS_OUTPUT.PUT_LINE(TO_CHAR(D2,'MON')||' IS RECENT MONTH');
977ELSE
978DBMS_OUTPUT.PUT_LINE(TO_CHAR(D3,'MON')||' IS RECENT MONTH');
979END IF;
980END;
981/
98273.Accept a string and print in the following format
983O
984OR
985ORA
986ORAC
987ORACL
988ORACLE
989DECLARE
990V VARCHAR2(20):='&V';
991C VARCHAR(20);
992BEGIN
993FOR I IN 1..LENGTH(V)
994LOOP
995C:=SUBSTR(V,1,I);
996DBMS_OUTPUT.PUT_LINE(C);
997END LOOP;
998END;
999/
100074.Write a program to accept the annual income of the emp and find the income tax
1001i) If the annsal > 60000 then tax is 10% of income
1002ii) If the annsal > 100000 then tax is Rs 800+16% of income
1003iii) If the annsal > 140000 then tax is Rs 2500+25% of income
1004DECLARE
1005AI NUMBER(10,2):=&ANNUALINCOME;
1006TAX NUMBER(10,3):=0;
1007BEGIN
1008IF AI BETWEEN 36000 AND 50000 THEN
1009TAX:=AI*10/100;
1010ELSIF AI BETWEEN 50000 AND 100000 THEN
1011TAX:=800+AI*16/100;
1012ELSIF AI > 100000 THEN
1013TAX:=2500+AI*25/100;
1014END IF;
1015DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
1016DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
1017END;
1018/
101975.WAP to accept a year as i/p & find how many even number present in that year
1020DECLARE
1021Y NUMBER:=&YEAR;
1022A VARCHAR2(20);
1023CNT NUMBER(5):=0;
1024BEGIN
1025FOR I IN 1..LENGTH(Y)
1026LOOP
1027A:=SUBSTR(Y,I,1);
1028IF MOD(A,2)=0 THEN
1029CNT:=CNT+1;
1030END IF;
1031END LOOP;
1032DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
1033END;
1034/
103576.WAP to accept a year as i/p & find how many odd number present in that year
1036DECLARE
1037Y NUMBER:=&YEAR;
1038A VARCHAR2(20);
1039CNT NUMBER(5):=0;
1040BEGIN
1041FOR I IN 1..LENGTH(Y)
1042LOOP
1043A:=SUBSTR(Y,I,1);
1044IF MOD(A,2)!=0 THEN
1045CNT:=CNT+1;
1046END IF;
1047END LOOP;
1048DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
1049END;
1050/
105177.WAP to accept a number and calculate the sum of numbers in even places
1052DECLARE
1053N NUMBER:=&NUMBER;
1054A VARCHAR2(10);
1055S NUMBER:=0;
1056BEGIN
1057FOR I IN 1..LENGTH(N)
1058LOOP
1059A:=SUBSTR(N,I,1);
1060IF MOD(I,2)=0 THEN
1061S:=S+A;
1062END IF;
1063END LOOP;
1064DBMS_OUTPUT.PUT_LINE('SUM OF EVEN PLACE IS '||S);
1065END;
1066/
106778.WAP to accept the emp details and calculate the bonus based on the following conditions
1068i) If sal < 500 then bonus is 10% sal
1069ii) If sal > 3500 then bonus is 12% sal
1070iii) If sal > 1000 then bonus is 13.5% sal
1071DECLARE
1072EMPNOV NUMBER:=&EMPNOV;
1073SALV NUMBER;
1074B NUMBER(7,2);
1075BEGIN
1076SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
1077IF SALV BETWEEN 500 AND 3500 THEN
1078B:=SALV*10/100;
1079ELSIF SALV BETWEEN 3500 AND 10000 THEN
1080B:=SALV*12/100;
1081ELSIF SALV>10000 THEN
1082B:=SALV*13.5/100;
1083END IF;
1084DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
1085DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
1086DBMS_OUTPUT.PUT_LINE('BONUS '||B);
1087END;
1088/
108979.WAP to accept the empno and display ename,sal,hiredate and calculate ta,da,hra,lic,gross,exp and
1090print all emp details. ta is 30% of sal,da is 20% of sal,hra is 15% of sal,lic is 5% of sal
1091DECLARE
1092EMPNOV NUMBER:=&EMPNOV;
1093ENAMEV EMP.ENAME%TYPE;
1094SALV EMP.SAL%TYPE;
1095HIREDATEV EMP.HIREDATE%TYPE;
1096EXP NUMBER(7,2);
1097TA NUMBER(7,2);
1098DA NUMBER(7,2);
1099HRA NUMBER(7,2);
1100LIC NUMBER(7,2);
1101GROSS NUMBER(7,2);
1102S NUMBER:=0;
1103BEGIN
1104SELECT ENAME,SAL,HIREDATE INTO ENAMEV,SALV,HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
1105EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
1106TA:=SALV*30/100;
1107DA:=SALV*20/100;
1108HRA:=SALV*15/100;
1109LIC:=SALV*5/100;
1110GROSS:=SALV+TA+DA+HRA-LIC;
1111DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
1112DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
1113DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
1114DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP);
1115DBMS_OUTPUT.PUT_LINE('TA '||TA);
1116DBMS_OUTPUT.PUT_LINE('DA '||DA);
1117DBMS_OUTPUT.PUT_LINE('HRA '||HRA);
1118DBMS_OUTPUT.PUT_LINE('LIC '||LIC);
1119DBMS_OUTPUT.PUT_LINE('GROSS '||GROSS);
1120END;
1121/
112280.WAP to accept the item no ,item name,qty,unit price and calculate the bill
1123If the bill > 500 then give discount 2% of bill amount and display the details
1124DECLARE
1125INO NUMBER:=&INO;
1126INAME VARCHAR2(50):='&INAME';
1127QTY NUMBER(5):=&QTY;
1128UP NUMBER(7,2):=&UP;
1129DIS NUMBER(7,2):=0;
1130BILL NUMBER(7,2);
1131NET NUMBER(7,2);
1132BEGIN
1133BILL:=QTY*UP;
1134IF BILL>500 THEN
1135DIS:=BILL*2/100;
1136END IF;
1137NET:=BILL-DIS;
1138DBMS_OUTPUT.PUT_LINE('ITEM NO '||INO);
1139DBMS_OUTPUT.PUT_LINE('ITEM NAME '||INAME);
1140DBMS_OUTPUT.PUT_LINE('QUANTITY '||QTY);
1141DBMS_OUTPUT.PUT_LINE('UNIT PRICE '||UP);
1142DBMS_OUTPUT.PUT_LINE('BILL AMT '||BILL);
1143DBMS_OUTPUT.PUT_LINE('DISCOUNT '||DIS);
1144DBMS_OUTPUT.PUT_LINE('NET AMT '||NET);
1145END;
1146/
114781.Write a program to generate sequence of numbers horizontally from 1 to 25
1148DECLARE
1149V VARCHAR2(100);
1150BEGIN
1151FOR I IN 1..25
1152LOOP
1153V:=V||' '||I;
1154END LOOP;
1155DBMS_OUTPUT.PUT_LINE(V);
1156END;
1157/
115882.WAP to accept a empno and display empno,name,sal,exp,dname,grade and loc.
1159DECLARE
1160EMPNOV NUMBER:=&EMPNO;
1161ENAMEV EMP.ENAME%TYPE;
1162HIREDATEV DATE;
1163SALV EMP.SAL%TYPE;
1164EXP NUMBER;
1165DNAMEV DEPT.DNAME%TYPE;
1166GRADEV SALGRADE.GRADE%TYPE;
1167BEGIN
1168SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
1169WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
1170EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
1171DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
1172DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
1173DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
1174DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
1175DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
1176DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
1177END;
1178/
117983.WAP to accept a empno and display empno,based on experience calculate the bonus and store it into the bonus table
1180If exp > 5 years then bonus is 1 month salary
1181If exp between 5 and 9 years then bonus is 20% of annual salary
1182If exp more than 9 years then bonus is 1 month sal plus 25% of annual salary
1183DECLARE
1184EMPNOV NUMBER:=&EMPNO;
1185ENAMEV EMP.ENAME%TYPE;
1186HIREDATEV DATE;
1187SALV EMP.SAL%TYPE;
1188EXP NUMBER;
1189DNAMEV DEPT.DNAME%TYPE;
1190GRADEV SALGRADE.GRADE%TYPE;
1191BEGIN
1192SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
1193WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
1194EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
1195DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
1196DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
1197DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
1198DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
1199DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
1200DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
1201END;
1202/
120384.WAP to accept the empno, based upon the dname transfer the emps ie, make the changes in the emp table. Transfer the emps from Accounting dept to Research, Research dept to Operation, Opertion dept to Sales
1204and Sales to Accounting dept
1205DECLARE
1206EMPNOV NUMBER:=&EMPNO;
1207DNAMEV VARCHAR2(20);
1208DNAMEVV VARCHAR2(20);
1209BEGIN
1210SELECT DNAME INTO DNAMEV FROM EMP,DEPT WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO;
1211IF DNAMEV='ACCOUNTING' THEN
1212DNAMEVV:='RESEARCH';
1213ELSIF DNAMEV='RESEARCH' THEN
1214DNAMEVV:='SALES';
1215ELSIF DNAMEV='SALES' THEN
1216DNAMEVV:='OPERATIONS';
1217ELSIF DNAMEV='OPERATIONS' THEN
1218DNAMEVV:='ACCOUNTING';
1219END IF;
1220UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=DNAMEVV) WHERE EMPNO=EMPNOV;
1221END;
1222/
122385.WAP to accept the empno and display all the details of emp. If emp doesnot exist display the appreciate message
1224DECLARE
1225EMPNOV NUMBER:=&EMPNO;
1226EMPV EMP%ROWTYPE;
1227BEGIN
1228SELECT * INTO EMPV FROM EMP WHERE EMPNO=EMPNOV;
1229DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPV.EMPNO);
1230DBMS_OUTPUT.PUT_LINE('ENAME '||EMPV.ENAME);
1231DBMS_OUTPUT.PUT_LINE('JOB '||EMPV.JOB);
1232DBMS_OUTPUT.PUT_LINE('SALARY '||EMPV.SAL);
1233DBMS_OUTPUT.PUT_LINE('HIREDATE '||EMPV.HIREDATE);
1234DBMS_OUTPUT.PUT_LINE('DEPTNO '||EMPV.DEPTNO);
1235DBMS_OUTPUT.PUT_LINE('MGRNO '||EMPV.MGR);
1236DBMS_OUTPUT.PUT_LINE('COMMISSION '||EMPV.COMM);
1237EXCEPTION
1238WHEN NO_DATA_FOUND THEN
1239DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EXIST');
1240END;
1241/
124286.WAP to accept the empno and print all the details of emp,dept and salgrade
1243DECLARE
1244E EMP%ROWTYPE;
1245D DEPT%ROWTYPE;
1246S SALGRADE%ROWTYPE;
1247BEGIN
1248SELECT * INTO E FROM EMP WHERE EMPNO=&EMPNO;
1249SELECT * INTO D FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO;
1250SELECT * INTO S FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
1251DBMS_OUTPUT.PUT_LINE('EMPNO '||E.EMPNO);
1252DBMS_OUTPUT.PUT_LINE('DEPTNO '||D.DEPTNO);
1253DBMS_OUTPUT.PUT_LINE('DNAME '||D.DNAME);
1254DBMS_OUTPUT.PUT_LINE('LOCATION '||D.LOC);
1255DBMS_OUTPUT.PUT_LINE('GRADE '||S.GRADE);
1256DBMS_OUTPUT.PUT_LINE('HISALARY '||S.HISAL);
1257DBMS_OUTPUT.PUT_LINE('LOWSALARY '||S.LOSAL);
1258END;
1259/
126087.WAP to accept the mgrno and display the empno,ename,sal,dname and grade of all emps working under that mgr
1261DECLARE
1262MGRV NUMBER:=&MGRV;
1263CURSOR EMPCUR IS
1264SELECT EMPNO,ENAME,SAL,DEPTNO,GRADE FROM EMP,SALGRADE WHERE MGR=MGRV AND SAL BETWEEN LOSAL AND HISAL;
1265X EMPCUR%ROWTYPE;
1266BEGIN
1267OPEN EMPCUR;
1268LOOP
1269FETCH EMPCUR INTO X;
1270EXIT WHEN EMPCUR%NOTFOUND;
1271DBMS_OUTPUT.PUT_LINE('EMPNO '||X.EMPNO);
1272DBMS_OUTPUT.PUT_LINE('ENAME '||X.ENAME);
1273DBMS_OUTPUT.PUT_LINE('SALARY '||X.SAL);
1274DBMS_OUTPUT.PUT_LINE('DEPTNO '||X.DEPTNO);
1275DBMS_OUTPUT.PUT_LINE('GRADE '||X.GRADE);
1276DBMS_OUTPUT.PUT_LINE('******************');
1277END LOOP;
1278CLOSE EMPCUR;
1279END;
1280/
128188.WAP to accept the empno and display the exp with minimum 3 decimal places
1282DECLARE
1283EMPNOV NUMBER:=&EMPNOV;
1284HIREDATEV DATE;
1285EXPV NUMBER(10,5);
1286BEGIN
1287SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
1288EXPV:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
1289DBMS_OUTPUT.PUT_LINE('EXPERIENCE OF EMP'||EMPNOV||' IS '||EXPV||' YEARS ');
1290END;
1291/
129289.Write a program to print the following series
12931
12941 2
12951 2 3
12961 2 3 4
12971 2 3 4 5
1298DECLARE
1299V VARCHAR2(20);
1300BEGIN
1301FOR I IN 1..5
1302LOOP
1303FOR J IN 1..I
1304LOOP
1305V:=V||' '||J;
1306END LOOP;
1307DBMS_OUTPUT.PUT_LINE(V);
1308V:=NULL;
1309END LOOP;
1310END;
1311/
131290.Write a program to print the following series
13131
13142 1
13153 2 1
13164 3 2 1
13175 4 3 2 1
1318DECLARE
1319V VARCHAR2(20);
1320BEGIN
1321FOR I IN 1..5
1322LOOP
1323FOR J IN REVERSE 1..I
1324LOOP
1325V:=V||' '||J;
1326END LOOP;
1327DBMS_OUTPUT.PUT_LINE(V);
1328V:=NULL;
1329END LOOP;
1330END;
1331/
133291.Write a program to print the following series
13331 2 3 4 5
13341 2 3 4
13351 2 3
13361 2
13371
1338DECLARE
1339V VARCHAR2(20);
1340BEGIN
1341FOR I IN REVERSE 1..5
1342LOOP
1343FOR J IN 1..I
1344LOOP
1345V:=V||' '||J;
1346END LOOP;
1347DBMS_OUTPUT.PUT_LINE(V);
1348V:=NULL;
1349END LOOP;
1350END;
1351/
135292.Write a program to print the following series
13531 1 1 1 1
13542 2 2 2 2
13553 3 3 3 3
13564 4 4 4 4
13575 5 5 5 5
1358DECLARE
1359V VARCHAR2(20);
1360BEGIN
1361FOR I IN 1..5
1362LOOP
1363FOR J IN 1..5
1364LOOP
1365V:=V||' '||I;
1366END LOOP;
1367DBMS_OUTPUT.PUT_LINE(V);
1368V:=NULL;
1369END LOOP;
1370END;
1371/
137293.Write a program to print the following series
13731 2 3 4 5
13741 2 3 4 5
13751 2 3 4 5
13761 2 3 4 5
13771 2 3 4 5
1378DECLARE
1379V VARCHAR2(20);
1380BEGIN
1381FOR I IN 1..5
1382LOOP
1383FOR J IN 1..5
1384LOOP
1385V:=V||' '||J;
1386END LOOP;
1387DBMS_OUTPUT.PUT_LINE(V);
1388V:=NULL;
1389END LOOP;
1390END;
1391/
139294.Write a program to print the following series
13935 4 3 2 1
13945 4 3 2
13955 4 3
13965 4
13975
1398DECLARE
1399V VARCHAR2(20);
1400BEGIN
1401FOR I IN 1..5
1402LOOP
1403FOR J IN REVERSE 1..5
1404LOOP
1405IF I<=J THEN
1406V:=V||' '||J;
1407END IF;
1408END LOOP;
1409DBMS_OUTPUT.PUT_LINE(V);
1410V:=NULL;
1411END LOOP;
1412END;
1413/
141495.Write a program to print the following series
14155 5 5 5 5
14164 4 4 4
14173 3 3
14182 2
14191
1420DECLARE
1421V VARCHAR2(20);
1422BEGIN
1423FOR I IN REVERSE 1..5
1424LOOP
1425FOR J IN 1..5
1426LOOP
1427IF I>=J THEN
1428V:=V||' '||I;
1429END IF;
1430END LOOP;
1431DBMS_OUTPUT.PUT_LINE(V);
1432V:=NULL;
1433END LOOP;
1434END;
1435/
143696.Write a program to print the following series
14371
14382 2
14393 3 3
14404 4 4 4
14415 5 5 5 5
1442DECLARE
1443V VARCHAR2(20);
1444BEGIN
1445FOR I IN 1..5
1446LOOP
1447FOR J IN 1..I
1448LOOP
1449V:=V||' '||I;
1450END LOOP;
1451DBMS_OUTPUT.PUT_LINE(V);
1452V:=NULL;
1453END LOOP;
1454END;
1455/
145697.Write a program to print the following series
14571
14580 1
14591 0 1
14600 1 0 1
14611 0 1 0 1
1462DECLARE
1463A NUMBER:=1;
1464V VARCHAR2(20):=1;
1465BEGIN
1466DBMS_OUTPUT.PUT_LINE(V);
1467FOR I IN 1..4
1468LOOP
1469IF SUBSTR(V,1,1)='1' THEN
1470V:='0'||V;
1471ELSE
1472V:='1'||V;
1473END IF;
1474DBMS_OUTPUT.PUT_LINE(V);
1475END LOOP;
1476END;
1477/
147898.Write a program to print the following series
1479*
1480* *
1481* * *
1482* * * *
1483* * * * *
1484DECLARE
1485V VARCHAR2(20);
1486BEGIN
1487FOR I IN 1..5
1488LOOP
1489FOR J IN 1..I
1490LOOP
1491V:=V||' '||'*';
1492END LOOP;
1493DBMS_OUTPUT.PUT_LINE(V);
1494V:=NULL;
1495END LOOP;
1496END;
1497/
149899.Write a program to print the following series
1499*
1500* *
1501* * *
1502* * * *
1503* * * * *
1504* * * *
1505* * *
1506* *
1507*
1508DECLARE
1509V VARCHAR2(20);
1510BEGIN
1511FOR I IN 1..5
1512LOOP
1513FOR J IN 1..I
1514LOOP
1515V:=V||' '||'*';
1516END LOOP;
1517DBMS_OUTPUT.PUT_LINE(V);
1518V:=NULL;
1519END LOOP;
1520FOR I IN REVERSE 1..5
1521LOOP
1522FOR J IN 2..I
1523LOOP
1524V:=V||' '||'*';
1525END LOOP;
1526DBMS_OUTPUT.PUT_LINE(V);
1527V:=NULL;
1528END LOOP;
1529END;
1530/
1531100.Write a program to print the following series
15321 2 3 4 5
15332 3 4 5
15343 4 5
15354 5
15365
1537DECLARE
1538V VARCHAR2(20);
1539BEGIN
1540FOR I IN 1..5
1541LOOP
1542FOR J IN I..5
1543LOOP
1544V:=V||' '||J;
1545END LOOP;
1546DBMS_OUTPUT.PUT_LINE(V);
1547V:=NULL;
1548END LOOP;
1549END;
1550/
1551101.Write a program to print the following series
15525 4 3 2 1
15534 3 2 1
15543 2 1
15552 1
15561
1557DECLARE
1558V VARCHAR2(20);
1559BEGIN
1560FOR I IN REVERSE 1..5
1561LOOP
1562FOR J IN REVERSE 1..I
1563LOOP
1564V:=V||' '||J;
1565END LOOP;
1566DBMS_OUTPUT.PUT_LINE(V);
1567V:=NULL;
1568END LOOP;
1569END;
1570/
1571102.WAP to accept 2 nos and find the sum and product of the nos and print the output
1572DECLARE
1573A NUMBER:=&A;
1574B NUMBER:=&B;
1575S NUMBER;
1576M NUMBER;
1577BEGIN
1578S:=A+B;
1579M:=A*B;
1580DBMS_OUTPUT.PUT_LINE('SUM OF '||'A'||' AND '||'B'||' IS '||S);
1581DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
1582END;
1583/
1584103.WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function)
1585DECLARE
1586A NUMBER:=&A;
1587B NUMBER:=&B;
1588C NUMBER;
1589M NUMBER;
1590BEGIN
1591C:=TRUNC(A/B);
1592M:=A-C*B;
1593DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
1594END;
1595/
1596104.WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122
1597BEGIN
1598FOR I IN 1..255
1599LOOP
1600DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
1601END LOOP;
1602END;
1603/
1604105.Print the following format
1605ORACLE
1606ORACL
1607ORAC
1608ORA
1609OR
1610O
1611DECLARE
1612STR VARCHAR2(10):='&STR';
1613L VARCHAR2(10);
1614N NUMBER(15);
1615BEGIN
1616N:=LENGTH(STR);
1617WHILE N>=1
1618LOOP
1619L:=SUBSTR(STR,1,N);
1620N:=N-1;
1621DBMS_OUTPUT.PUT_LINE(L);
1622END LOOP;
1623END;
1624/
1625106.WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time
1626DECLARE
1627HH NUMBER;
1628BEGIN
1629HH:=TO_CHAR(SYSDATE,'HH24');
1630IF HH>6 AND HH<12 THEN
1631DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
1632ELSIF HH>=12 AND HH<18 THEN
1633DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
1634ELSIF HH>=18 AND HH<25 THEN
1635DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
1636END IF;
1637END;
1638/
1639107.WAP to accept two strings and concat the two strings
1640DECLARE
1641STR VARCHAR2(20):='&STR';
1642STR1 VARCHAR2(20):='&STR1';
1643V VARCHAR2(40);
1644BEGIN
1645V:=STR||''||STR1;
1646DBMS_OUTPUT.PUT_LINE(V);
1647END;
1648/
1649108.WAP to accept a string and count the no of chars,words in that string
1650DECLARE
1651STR VARCHAR2(20):='&STR';
1652NOC NUMBER(4):=0;
1653NOW NUMBER(4):=1;
1654S CHAR;
1655BEGIN
1656FOR I IN 1..LENGTH(STR)
1657LOOP
1658S:=SUBSTR(STR,I,1);
1659NOC:=NOC+1;
1660IF S=' ' THEN
1661NOW:=NOW+1;
1662END IF;
1663END LOOP;
1664DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
1665DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
1666END;
1667/
1668109.WAP to accept the octal number and print it in decimal format
1669DECLARE
1670N VARCHAR2(20):='&N';
1671A NUMBER;
1672P NUMBER:=0;
1673C CHAR;
1674BEGIN
1675A:=LENGTH(N);
1676FOR I IN 1..A
1677LOOP
1678C:=SUBSTR(N,I,1);
1679P:=P+C*POWER(8,A-I);
1680END LOOP;
1681DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
1682END;
1683/
1684110.WAP to accept the mgr and find how many emps are working under that mgr
1685DECLARE
1686MGRV EMP.MGR%TYPE:=&MGRNO;
1687N NUMBER:=0;
1688BEGIN
1689SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
1690DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
1691END;
1692/
1693111.WAP to accept the empno and update the employee row on the following
1694If sal < 2600 then sal=sal+10% of sal make the changes in the emp table
1695DECLARE
1696EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
1697SALV NUMBER(7,2):=0;
1698BEGIN
1699SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
1700IF SALV < 2600 THEN
1701SALV:=SALV+SALV*(10/100);
1702END IF;
1703UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
1704DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
1705DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
1706END;
1707/
1708112.Write the floyd's triangle
17091
17102 3
17114 5 6
17127 8 9 10
171311 12 13 14 15
171416 17 18 19 20 21
1715...............
171679..............91
1717DECLARE
1718N NUMBER:=1;
1719V VARCHAR2(100);
1720BEGIN
1721FOR I IN 1..92
1722LOOP
1723FOR J IN 1..I
1724LOOP
1725V:=V||' '||N;
1726N:=N+1;
1727EXIT WHEN N=92;
1728END LOOP;
1729DBMS_OUTPUT.PUT_LINE(V);
1730EXIT WHEN N=92;
1731V:=NULL;
1732END LOOP;
1733END;
1734/
1735113.WAP to accept the real value and print integer value only
1736DECLARE
1737N NUMBER(7,3):=&N;
1738A NUMBER(5);
1739BEGIN
1740A:=TRUNC(N);
1741DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
1742END;
1743/
1744114.WAP to calculate the sum of n odd factorials
1745DECLARE
1746N NUMBER:=&N;
1747S NUMBER:=0;
1748F NUMBER:=1;
1749BEGIN
1750FOR I IN 1..N
1751LOOP
1752IF MOD(I,2)!=0 THEN
1753FOR J IN 1..I
1754LOOP
1755F:=F*J;
1756END LOOP;
1757S:=S+F;
1758F:=1;
1759END IF;
1760END LOOP;
1761DBMS_OUTPUT.PUT_LINE('SUM '||S);
1762END;
1763/
1764115.WAP to calculate the sum of n even factorials
1765DECLARE
1766N NUMBER:=&N;
1767S NUMBER:=0;
1768F NUMBER:=1;
1769BEGIN
1770FOR I IN 1..N
1771LOOP
1772IF MOD(I,2)=0 THEN
1773FOR J IN 1..I
1774LOOP
1775F:=F*J;
1776END LOOP;
1777S:=S+F;
1778F:=1;
1779END IF;
1780END LOOP;
1781DBMS_OUTPUT.PUT_LINE('SUM '||S);
1782END;
1783/
1784116.WAP to generate the nos which are prime and odd between 1 and 100
1785DECLARE
1786N NUMBER;
1787CNT NUMBER:=0;
1788BEGIN
1789FOR I IN 1..100
1790LOOP
1791FOR J IN 1..I
1792LOOP
1793IF MOD(I,J)=0 THEN
1794CNT:=CNT+1;
1795END IF;
1796END LOOP;
1797IF CNT <= 2 THEN
1798IF MOD(I,2)!=0 THEN
1799DBMS_OUTPUT.PUT_LINE(I);
1800END IF;
1801END IF;
1802CNT:=0;
1803END LOOP;
1804END;
1805/
1806117.Write a program to generate following series
180712
180812 22
180912 22 32
181012 22 32 42
181112 22 32 42 52
1812DECLARE
1813V VARCHAR2(20);
1814BEGIN
1815FOR I IN 1..5
1816LOOP
1817FOR J IN 1..I
1818LOOP
1819V:=V||' '||J||CHR(178);
1820END LOOP;
1821DBMS_OUTPUT.PUT_LINE(V);
1822V:=NULL;
1823END LOOP;
1824END;
1825/
1826118.Find the roots of a quadratic equation
1827DECLARE
1828A NUMBER(4):=&A;
1829B NUMBER(4):=&B;
1830C NUMBER(4):=&C;
1831D NUMBER(8,2);
1832R1 NUMBER(8,2);
1833R2 NUMBER(8,2);
1834BEGIN
1835D:=POWER(B,2)-4*A*C;
1836IF D = 0 THEN
1837DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
1838ELSIF D > 0 THEN
1839R1:=(-B+SQRT(D))/2*A;
1840R2:=(-B-SQRT(D))/2*A;
1841DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
1842DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
1843ELSE
1844DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
1845END IF;
1846END;
1847/
1848119.WAP to accept the 2 diff nos, assume that first one is smaller and second one is highest value then print the all even nos in between them horizontally
1849DECLARE
1850A NUMBER:=&A;
1851B NUMBER:=&B;
1852V VARCHAR2(100);
1853BEGIN
1854FOR I IN A..B
1855LOOP
1856IF MOD(I,2)=0 THEN
1857V:=V||' '||I;
1858END IF;
1859END LOOP;
1860DBMS_OUTPUT.PUT_LINE(V);
1861END;
1862/
1863120.WAP to accept two diff nos assume that first one is smaller and second one is highest value then print the all odd nos in between them horizontally
1864DECLARE
1865A NUMBER:=&A;
1866B NUMBER:=&B;
1867V VARCHAR2(100);
1868BEGIN
1869FOR I IN A..B
1870LOOP
1871IF MOD(I,2)!=0 THEN
1872V:=V||' '||I;
1873END IF;
1874END LOOP;
1875DBMS_OUTPUT.PUT_LINE(V);
1876END;
1877/
1878121. Write a program to accept a year and display the emps belongs to that year?
1879DECLARE
1880Y NUMBER(4):=&YEAR;
1881CURSOR YEAR IS
1882SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=Y;
1883B YEAR%ROWTYPE;
1884BEGIN
1885OPEN YEAR;
1886LOOP
1887FETCH YEAR INTO B;
1888EXIT WHEN YEAR%NOTFOUND;
1889DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
1890DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
1891DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
1892DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
1893DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
1894DBMS_OUTPUT.PUT_LINE('*************************');
1895END LOOP;
1896CLOSE YEAR;
1897END;
1898/
1899122.Write a program to accept a mgr and display who are working under that mgr?
1900DECLARE
1901MGRV NUMBER(4):=&MGR;
1902CURSOR AMGR IS
1903SELECT * FROM EMP WHERE MGR=MGRV;
1904B AMGR%ROWTYPE;
1905BEGIN
1906OPEN AMGR;
1907LOOP
1908FETCH AMGR INTO B;
1909EXIT WHEN AMGR%NOTFOUND;
1910DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
1911DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
1912DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
1913DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
1914DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
1915DBMS_OUTPUT.PUT_LINE('*************************');
1916END LOOP;
1917CLOSE AMGR;
1918END;
1919/
1920123. Write a program to accept the grade and display emps belongs to that grade?
1921DECLARE
1922GRADEV SALGRADE.GRADE%TYPE:=&GRADE;
1923CURSOR A IS
1924SELECT EMP.*,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
1925B A%ROWTYPE;
1926BEGIN
1927OPEN A;
1928LOOP
1929FETCH A INTO B;
1930EXIT WHEN A%NOTFOUND;
1931DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
1932DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
1933DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
1934DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
1935DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
1936DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
1937DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
1938DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
1939DBMS_OUTPUT.PUT_LINE('*************************');
1940END LOOP;
1941CLOSE A;
1942END;
1943/
1944124. Write a program to accept a deptno and display who are working in that dept?
1945DECLARE
1946DEPTV EMP.DEPTNO%TYPE:=&DEPTNO;
1947CURSOR A IS
1948SELECT * FROM EMP WHERE DEPTNO=DEPTV;
1949B A%ROWTYPE;
1950BEGIN
1951OPEN A;
1952LOOP
1953FETCH A INTO B;
1954EXIT WHEN A%NOTFOUND;
1955DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
1956DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
1957DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
1958DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
1959DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
1960DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
1961DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
1962DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
1963DBMS_OUTPUT.PUT_LINE('*************************');
1964END LOOP;
1965CLOSE A;
1966END;
1967/
1968125. Write a program to display all the information of emp table?
1969DECLARE
1970CURSOR A IS
1971SELECT * FROM EMP;
1972B A%ROWTYPE;
1973BEGIN
1974OPEN A;
1975LOOP
1976FETCH A INTO B;
1977EXIT WHEN A%NOTFOUND;
1978DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
1979DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
1980DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
1981DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
1982DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
1983DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
1984DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
1985DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
1986DBMS_OUTPUT.PUT_LINE('*************************');
1987END LOOP;
1988CLOSE A;
1989END;
1990/
1991126. Write a program to accept the location and display empno, name, sal , date of join and also display the total salary, avg salary and no of emps?
1992DECLARE
1993LOCV DEPT.LOC%TYPE:='&LOC';
1994TOT NUMBER(10,2):=0;
1995ASAL NUMBER(10,2):=0;
1996NOEMPS NUMBER(5):=0;
1997CURSOR A IS
1998SELECT EMP.*,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=LOCV;
1999B A%ROWTYPE;
2000BEGIN
2001OPEN A;
2002LOOP
2003FETCH A INTO B;
2004NOEMPS:=NOEMPS+1;
2005TOT:=TOT+B.SAL;
2006ASAL:=TOT/NOEMPS;
2007EXIT WHEN A%NOTFOUND;
2008DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
2009DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
2010DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
2011DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
2012DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
2013DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
2014DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
2015DBMS_OUTPUT.PUT_LINE('TOT IS ' || TOT);
2016DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' || NOEMPS);
2017DBMS_OUTPUT.PUT_LINE('ASAL IS ' || ASAL);
2018DBMS_OUTPUT.PUT_LINE('*************************');
2019END LOOP;
2020CLOSE A;
2021END;
2022/
2023127. Write a program to accept a range of salary (that is lower boundary and higher boundary) and print the details of emps along with loc,grade and exp?
2024DECLARE
2025LOSALV SALGRADE.LOSAL%TYPE:=&LOSAL;
2026HISALV SALGRADE.HISAL%TYPE:=&HISAL;
2027EXP NUMBER(5,2);
2028CURSOR A IS
2029SELECT EMP.*,LOC,GRADE FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO=DEPT.DEPTNO
2030AND SAL BETWEEN LOSALV AND HISALV
2031AND SAL BETWEEN LOSAL AND HISAL;
2032B A%ROWTYPE;
2033BEGIN
2034OPEN A;
2035LOOP
2036FETCH A INTO B;
2037EXIT WHEN A%NOTFOUND;
2038EXP:=MONTHS_BETWEEN(SYSDATE,B.HIREDATE)/12;
2039DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
2040DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
2041DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
2042DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
2043DBMS_OUTPUT.PUT_LINE('EXP IS ' || EXP);
2044DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
2045DBMS_OUTPUT.PUT_LINE('*************************');
2046END LOOP;
2047CLOSE A;
2048END;
2049/
2050128. Write a program to print all the details of emps accepting the job?
2051DECLARE
2052JOBV EMP.JOB%TYPE:='&JOB';
2053CURSOR A IS
2054SELECT * FROM EMP WHERE JOB=JOBV;
2055B A%ROWTYPE;
2056BEGIN
2057OPEN A;
2058LOOP
2059FETCH A INTO B;
2060EXIT WHEN A%NOTFOUND;
2061DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
2062DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
2063DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
2064DBMS_OUTPUT.PUT_LINE('*************************');
2065END LOOP;
2066CLOSE A;
2067END;
2068/
2069129. Write a program to display the details of emps year wise?
2070DECLARE
2071CURSOR YEARS IS
2072SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') YEARS1 FROM EMP ORDER BY 1;
2073YEAR YEARS%ROWTYPE;
2074CURSOR A IS
2075SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=YEAR.YEARS1;
2076B A%ROWTYPE;
2077BEGIN
2078DBMS_OUTPUT.ENABLE(10000);
2079OPEN YEARS;
2080DBMS_OUTPUT.PUT_LINE('********************');
2081LOOP
2082FETCH YEARS INTO YEAR;
2083EXIT WHEN YEARS%NOTFOUND;
2084DBMS_OUTPUT.PUT_LINE('YEAR :' || YEAR.YEARS1);
2085DBMS_OUTPUT.PUT_LINE('**********************');
2086OPEN A;
2087LOOP
2088FETCH A INTO B;
2089EXIT WHEN A%NOTFOUND;
2090DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
2091DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
2092DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
2093DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
2094DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
2095DBMS_OUTPUT.PUT_LINE('*************************');
2096END LOOP;
2097CLOSE A;
2098END LOOP;
2099CLOSE YEARS;
2100END;
2101/
2102130. Write a program to accept empno and print all the details along with loc and grade?
2103DECLARE
2104EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
2105CURSOR A IS
2106SELECT EMP.*,GRADE,LOC FROM EMP,DEPT,SALGRADE
2107WHERE EMP.DEPTNO=DEPT.DEPTNO
2108AND SAL BETWEEN LOSAL AND HISAL AND EMPNO=EMPNOV;
2109B A%ROWTYPE;
2110BEGIN
2111OPEN A;
2112LOOP
2113FETCH A INTO B;
2114EXIT WHEN A%NOTFOUND;
2115DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
2116DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
2117DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
2118DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
2119DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
2120DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
2121DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
2122DBMS_OUTPUT.PUT_LINE('*************************');
2123END LOOP;
2124CLOSE A;
2125END;
2126/
2127131. Write a procedure to create your own print statement?
2128CREATE OR REPLACE PROCEDURE PRINT(V VARCHAR2)
2129IS
2130BEGIN
2131DBMS_OUTPUT.PUT_LINE(V);
2132END;
2133/
2134132. Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?
2135CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
2136IS
2137CURSOR A IS
2138SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
2139B A%ROWTYPE;
2140NOE NUMBER:=0;
2141TOT NUMBER:=0;
2142AVGS NUMBER(7,2):=0;
2143MAXS NUMBER(7,2):=0;
2144BEGIN
2145OPEN A;
2146LOOP
2147FETCH A INTO B;
2148EXIT WHEN A%NOTFOUND;
2149DBMS_OUTPUT.PUT_LINE('EMPNO :'||B.EMPNO);
2150DBMS_OUTPUT.PUT_LINE('ENAME :'||B.ENAME);
2151DBMS_OUTPUT.PUT_LINE('JOB :'||B.JOB);
2152DBMS_OUTPUT.PUT_LINE('SAL :'||B.SAL);
2153DBMS_OUTPUT.PUT_LINE('HIREDATE :'||B.HIREDATE);
2154DBMS_OUTPUT.PUT_LINE('COMM :'||B.COMM);
2155DBMS_OUTPUT.PUT_LINE('**********************');
2156TOT:=TOT+B.SAL;
2157NOE:=NOE+1;
2158IF B.SAL>MAXS THEN
2159MAXS:=B.SAL;
2160END IF;
2161END LOOP;
2162AVGS:=TOT/NOE;
2163DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEE :'||NOE);
2164DBMS_OUTPUT.PUT_LINE('TOTAL SALARY :'||TOT);
2165DBMS_OUTPUT.PUT_LINE('AVG SALARY :'||AVGS);
2166DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||MAXS);
2167CLOSE A;
2168END;
2169/
2170133. Write a procedure to accept two different numbers and print all odd numbers between the two given numbers?
2171CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER,B NUMBER)
2172IS
2173N NUMBER(4);
2174BEGIN
2175N:=A;
2176WHILE N<B
2177LOOP
2178IF MOD(N,2)!=0 THEN
2179DBMS_OUTPUT.PUT_LINE(N);
2180END IF;
2181N:=N+1;
2182END LOOP;
2183END;
2184/
2185134. Write a procedure to accept two different numbers and print even numbers between the two given numbers?
2186CREATE OR REPLACE PROCEDURE EVENNO(A NUMBER,B NUMBER)
2187IS
2188N NUMBER(4);
2189BEGIN
2190N:=A;
2191WHILE N<B
2192LOOP
2193IF MOD(N,2)=0 THEN
2194DBMS_OUTPUT.PUT_LINE(N);
2195END IF;
2196N:=N+1;
2197END LOOP;
2198END;
2199/
2200135. Write a procedure to accept deptno as input and print the details of emps along with grade?
2201CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNOV NUMBER)
2202IS
2203CURSOR A IS
2204SELECT EMP.*,GRADE FROM EMP,SALGRADE
2205WHERE SAL BETWEEN LOSAL AND HISAL
2206AND DEPTNO=DEPTNOV;
2207B A%ROWTYPE;
2208BEGIN
2209OPEN A;
2210LOOP
2211FETCH A INTO B;
2212EXIT WHEN A%NOTFOUND;
2213DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
2214DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
2215DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
2216DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
2217DBMS_OUTPUT.PUT_LINE('DEPTNO IS '||B.DEPTNO);
2218DBMS_OUTPUT.PUT_LINE('GRADE IS '||B.GRADE);
2219END LOOP;
2220CLOSE A;
2221END;
2222/
2223136. Write a procedure to accept a number as parameter and print its multiplication table?
2224CREATE OR REPLACE PROCEDURE MULT(A NUMBER)
2225IS
2226B NUMBER(2) DEFAULT 1;
2227C NUMBER(3);
2228BEGIN
2229WHILE B<=10
2230LOOP
2231C:=A*B;
2232DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
2233B:=B+1;
2234END LOOP;
2235END;
2236/
2237137. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different horizontal lines?
2238CREATE OR REPLACE PROCEDURE EVENODD(A NUMBER,B NUMBER)
2239IS
2240N NUMBER;
2241EV VARCHAR2(1000);
2242OD VARCHAR2(1000);
2243BEGIN
2244N:=A;
2245WHILE N<B
2246LOOP
2247IF MOD(N,2)!=0 THEN
2248OD:=OD||' '||N;
2249ELSE
2250EV:=EV||' '||N;
2251END IF;
2252N:=N+1;
2253END LOOP;
2254DBMS_OUTPUT.PUT_LINE('THE ODD NOS ARE '||OD);
2255DBMS_OUTPUT.PUT_LINE('THE EVEN NOS ARE '||EV);
2256END;
2257/
2258138. Write a procedure to accept a string and check whether it is palindrome or not?
2259CREATE OR REPLACE PROCEDURE STRPAL(STR VARCHAR2)
2260IS
2261STR1 VARCHAR2(10);
2262S VARCHAR2(10);
2263BEGIN
2264FOR I IN REVERSE 1..LENGTH(STR)
2265LOOP
2266S:=SUBSTR(STR,I,1);
2267STR1:=STR1||S;
2268END LOOP;
2269IF STR1=STR THEN
2270DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME '||STR1);
2271ELSE
2272DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME '||STR1);
2273END IF;
2274END;
2275/
2276139. Write a procedure to accept a string and print it in reverse order?
2277CREATE OR REPLACE PROCEDURE STRREV(STR VARCHAR2)
2278IS
2279STR1 VARCHAR2(10);
2280S VARCHAR2(10);
2281BEGIN
2282FOR I IN REVERSE 1..LENGTH(STR)
2283LOOP
2284S:=SUBSTR(STR,I,1);
2285STR1:=STR1||S;
2286END LOOP;
2287DBMS_OUTPUT.PUT_LINE('ORIGINAL '||STR);
2288DBMS_OUTPUT.PUT_LINE('REVERSE '||STR1);
2289END;
2290/
2291140. Write a procedure to accept the empno and print all the details of emp along with exp, grade and loc?
2292CREATE OR REPLACE PROCEDURE EMP_DET(EMPNOV NUMBER)
2293IS
2294EXP NUMBER(6,2);
2295E EMP%ROWTYPE;
2296GRADEV SALGRADE.GRADE%TYPE;
2297LOCV DEPT.LOC%TYPE;
2298BEGIN
2299SELECT EMP.* INTO E FROM EMP WHERE EMPNO=EMPNOV;
2300SELECT LOC INTO LOCV FROM DEPT WHERE DEPT.DEPTNO=E.DEPTNO;
2301SELECT GRADE INTO GRADEV FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
2302EXP:=MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12;
2303DBMS_OUTPUT.PUT_LINE('EMPNO IS '||E.EMPNO);
2304DBMS_OUTPUT.PUT_LINE('ENAME IS '||E.ENAME);
2305DBMS_OUTPUT.PUT_LINE('SAL IS '||E.SAL);
2306DBMS_OUTPUT.PUT_LINE('JOB IS '||E.JOB);
2307DBMS_OUTPUT.PUT_LINE('LOC IS '||LOCV);
2308DBMS_OUTPUT.PUT_LINE('GRADE IS '||GRADEV);
2309DBMS_OUTPUT.PUT_LINE('EXP IS '||EXP);
2310END;
2311/
2312141. Write a procedure to accept dname irrespective of case and print all the details of emps?
2313CREATE OR REPLACE PROCEDURE DETAILS(DNAMEV VARCHAR2)
2314IS
2315CURSOR A IS
2316SELECT EMP.*,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME=DNAMEV;
2317B A%ROWTYPE;
2318BEGIN
2319OPEN A;
2320LOOP
2321FETCH A INTO B;
2322EXIT WHEN A%NOTFOUND;
2323DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
2324DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
2325DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
2326DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
2327DBMS_OUTPUT.PUT_LINE('DNAME IS '||B.DNAME);
2328DBMS_OUTPUT.PUT_LINE('HIREDATE IS '||B.HIREDATE);
2329END LOOP;
2330END;
2331/
2332142. Write a procedure to accept a string and print it in reverse case?
2333CREATE OR REPLACE PROCEDURE S_R_CASE(STR VARCHAR2)
2334IS
2335S VARCHAR2(10);
2336V VARCHAR2(10);
2337N NUMBER(3);
2338BEGIN
2339FOR I IN 1..LENGTH(STR)
2340LOOP
2341S:=SUBSTR(STR,I,1);
2342N:=ASCII(S);
2343IF N BETWEEN 65 AND 90 THEN
2344V:=V||CHR(N+32);
2345ELSE
2346V:=V||CHR(N-32);
2347END IF;
2348END LOOP;
2349DBMS_OUTPUT.PUT_LINE('STRING IN REVERSE CASE IS '||V);
2350END;
2351/
2352143. Write a function to accept the empno and return exp with minimum 3 decimal?
2353CREATE OR REPLACE FUNCTION E_DETAILS(EMPNOV NUMBER) RETURN NUMBER
2354IS
2355HIREDATEV EMP.HIREDATE%TYPE;
2356EXP NUMBER(6,3);
2357BEGIN
2358SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
2359EXP:=MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12;
2360RETURN EXP;
2361END;
2362/
2363144. Write a function to accept a number and print the factorial of that number?
2364CREATE OR REPLACE FUNCTION FAC(NUM NUMBER) RETURN NUMBER
2365IS
2366FACT NUMBER(4):=1;
2367BEGIN
2368FOR I IN REVERSE 1..NUM
2369LOOP
2370FACT:=FACT*I;
2371END LOOP;
2372RETURN FACT;
2373END;
2374/
2375145. Write a function to accept a grade and return the number of emps belongs to that grade?
2376CREATE OR REPLACE FUNCTION EMPGRADE(GRADEV NUMBER) RETURN VARCHAR2
2377IS
2378N NUMBER(4);
2379BEGIN
2380SELECT COUNT(*) INTO N FROM EMP,SALGRADE
2381WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
2382RETURN 'NO OF EMPS ARE'||N;
2383END;
2384/
2385146. Write a program to accept the mgr number and return no of emp working at that mgr?
2386CREATE OR REPLACE FUNCTION N_EMPS(MGRV NUMBER) RETURN VARCHAR2
2387IS
2388N NUMBER(4);
2389BEGIN
2390SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
2391RETURN 'THE NO OF EMPS ARE WORKING UNDER THIS MGR IS '||N;
2392END;
2393/
2394147. Write a function to accept a character string and print it in reverse case?
2395CREATE OR REPLACE FUNCTION REVERSE(STR VARCHAR2) RETURN VARCHAR2
2396IS
2397STR1 VARCHAR2(20);
2398S VARCHAR2(20);
2399N NUMBER(4);
2400BEGIN
2401FOR I IN 1..LENGTH(STR)
2402LOOP
2403S:=SUBSTR(STR,I,1);
2404N:=ASCII(S);
2405IF N BETWEEN 65 AND 90 THEN
2406STR1:=STR1||CHR(N+32);
2407ELSE
2408STR1:=STR1||CHR(N-32);
2409END IF;
2410END LOOP;
2411RETURN 'THE REVERSE CASE IS '||STR1;
2412END;
2413/
2414148. Write a function to accept a string and check whether it is palindrome or not?
2415CREATE OR REPLACE FUNCTION STRPAL1(STR VARCHAR2) RETURN VARCHAR2
2416IS
2417STR1 VARCHAR2(10);
2418S VARCHAR2(10);
2419BEGIN
2420FOR I IN REVERSE 1..LENGTH(STR)
2421LOOP
2422S:=SUBSTR(STR,I,1);
2423STR1:=STR1||S;
2424END LOOP;
2425IF STR1=STR THEN
2426RETURN 'IT IS PALINDROME '||STR1;
2427ELSE
2428RETURN 'IT IS NOT PALINDROME '||STR1;
2429END IF;
2430END;
2431/
2432149. Write a function to accept the grade and return max, tot, avg salary and number of emps belongs to that grade as script without using any group functions?
2433CREATE OR REPLACE FUNCTION EMP_DETAILS_SCRIPT (GRADEV SALGRADE.GRADE%TYPE) RETURN VARCHAR2
2434IS
2435V VARCHAR2(30000);
2436CURSOR EMP_CUR IS
2437SELECT EMP.*,GRADE,DNAME FROM DEPT,EMP,SALGRADE
2438WHERE GRADE=GRADEV AND EMP.DEPTNO=DEPT.DEPTNO AND
2439SAL BETWEEN LOSAL AND HISAL;
2440EMP_CUR_V EMP_CUR%ROWTYPE;
2441MAXSAL EMP.SAL%TYPE:=0;
2442MINSAL EMP.SAL%TYPE;
2443AVGSAL NUMBER(6,2);
2444SUMSAL NUMBER(10,2):=0;
2445CNT NUMBER:=0;
2446FLAG CHAR:=0;
2447EX EXCEPTION;
2448BEGIN
2449OPEN EMP_CUR;
2450LOOP
2451FETCH EMP_CUR INTO EMP_CUR_V;
2452EXIT WHEN EMP_CUR%NOTFOUND;
2453IF MAXSAL < EMP_CUR_V.SAL THEN
2454MAXSAL:=EMP_CUR_V.SAL;
2455END IF;
2456IF FLAG=0 THEN
2457MINSAL:=EMP_CUR_V.SAL;
2458FLAG:=1;
2459ELSIF FLAG=1 AND MINSAL > EMP_CUR_V.SAL THEN
2460MINSAL:=EMP_CUR_V.SAL;
2461END IF;
2462SUMSAL:=SUMSAL+EMP_CUR_V.SAL;
2463CNT:=CNT+1;
2464ENDLOOP;
2465IF CNT=0 THEN
2466RAISE EX;
2467END IF;
2468AVGSAL:=SUMSAL/CNT;
2469V:='THE MAXIMUM SALARY OF GRADE' ||GRADEV||' IS'||MAXSAL||' MINIMUM SALARY IS'||MINSAL||
2470'AVERAGE SALARY IS'||AVGSAL||' TOTAL EMPS WORKING FOR THIS GRADE ARE'||CNT;
2471CLOSE EMP_CUR;
2472RETURN V;
2473EXCEPTION
2474WHEN EX THEN
2475RETURN 'THERE IS NO EMPLOYEE WORKING FOR THIS GRADE, CHECK AND RE-ENTER THE GRADE....';
2476END;
2477/
2478150. Create a package to store the following procedure for multiplication table,even-odd, function for factorial and function for palindrome?
2479CREATE OR REPLACE PACKAGE DATA
2480IS
2481PROCEDURE MULT(A NUMBER);
2482PROCEDURE EVEN_ODD(N NUMBER);
2483FUNCTION FACT(N NUMBER) RETURN NUMBER;
2484PRAGMA RESTRICT_REFERENCES(FACT,WNDS);
2485FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2;
2486PRAGMA RESTRICT_REFERENCES(PALEN,WNDS);
2487END;
2488/
2489CREATE OR REPLACE PACKAGE BODY DATA
2490IS
2491PROCEDURE MULT(A NUMBER)
2492IS
2493M NUMBER;
2494BEGIN
2495FOR I IN 1..10
2496LOOP
2497M:=A*I;
2498DBMS_OUTPUT.PUT_LINE(A||'*'||I||'='||M);
2499END LOOP;
2500END;
2501PROCEDURE EVEN_ODD(N NUMBER)
2502IS
2503BEGIN
2504IF MOD(N,2)=0 THEN
2505DBMS_OUTPUT.PUT_LINE(N||' IS EVEN NUMBER');
2506ELSE
2507DBMS_OUTPUT.PUT_LINE(N||' IS NOT EVEN NUMBER');
2508END IF;
2509END;
2510FUNCTION FACT(N NUMBER) RETURN NUMBER
2511IS
2512F NUMBER:=1;
2513BEGIN
2514FOR I IN 1..N
2515LOOP
2516F:=F*I;
2517END LOOP;
2518RETURN F;
2519END;
2520FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2
2521IS
2522S CHAR;
2523V VARCHAR2(50);
2524BEGIN
2525FOR I IN REVERSE 1..LENGTH(SRT)
2526LOOP
2527S:=SUBSTR(SRT,I,1);
2528V:=V||S;
2529END LOOP;
2530IF V=SRT THEN
2531RETURN 'PALINDROME';
2532ELSE
2533RETURN 'NOT PALINDROME';
2534END IF;
2535END;
2536END;
2537/
2538151.Write a database trigger halt the transaction on Sunday on EMP table
2539CREATE OR REPLACE TRIGGER SUN_TRI
2540AFTER INSERT OR UPDATE OR DELETE ON EMP
2541DECLARE
2542DY VARCHAR2(200);
2543BEGIN
2544DY:=TO_CHAR(SYSDATE,'DY');
2545IF DY='SUN' THEN
2546RAISE_APPLICATION_ERROR(-20005,'TODAY IS SUNDAY TRANSACTION NOT ALLOWED TODAY');
2547END IF;
2548END;
2549/
2550152.Write a database trigger halt the transaction of USER SCOTT on table EMP
2551CREATE OR REPLACE TRIGGER SCOTT_TRI
2552BEFORE INSERT OR UPDATE OR DELETE ON EMP
2553BEGIN
2554IF USER = 'SCOTT' THEN
2555RAISE_APPLICATION_ERROR(-20006,'TRANSACTION NOT ALLOWED FOR SCOTT');
2556END IF;
2557END;
2558/
2559153.Write a database trigger halt the transaction between the the time 6pm to 10am on table emp
2560CREATE OR REPLACE TRIGGER OVER_TIME_TRI
2561BEFORE INSERT OR DELETE OR UPDATE ON EMP
2562DECLARE
2563T NUMBER;
2564BEGIN
2565T:=TO_CHAR(SYSDATE,'HH24');
2566IF T NOT BETWEEN 10 AND 18 THEN
2567RAISE_APPLICATION_ERROR(-20007,'TIME ALREADY OVER.....TRANSACTION NOT ALLOWED NOW');
2568END IF;
2569END;
2570154.Write a database trigger to halt the transaction for the employee SALESMAN and
2571PRESIDENT
2572CREATE OR REPLACE TRIGGER SALES_PRI
2573BEFORE INSERT OR UPDATE OR DELETE ON EMP
2574FOR EACH ROW
2575WHEN (OLD.JOB IN ('SALESMAN','PRESIDENT') OR
2576NEW.JOB IN ('SALESMAN','PRESIDENT'))
2577BEGIN
2578RAISE_APPLICATION_ERROR(-20008,'TRANSACTION NOT ALLOWED FOR SALESMAN AND PRESIDENT....');
2579END;
2580/
2581155.Write a database trigger stroe the username ,type of transaction ,date of transaction and time of transaction of table emp into the table EMP_LOG
2582CREATE OR REPLACE TRIGGER TRANS_TYPE
2583AFTER INSERT OR UPDATE OR DELETE ON EMP
2584DECLARE
2585V VARCHAR2(50);
2586BEGIN
2587IF INSERTING THEN
2588V:='I';
2589ELSIF UPDATING THEN
2590V:='U';
2591ELSE
2592V:='D';
2593END IF;
2594INSERT INTO EMP_LOG VALUES (USER,V,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
2595END;
2596/
2597156.Write a database trigger store the deleted data of EMP table in EMPDEL table
2598CREATE OR REPLACE TRIGGER DEL_TRI
2599BEFORE DELETE ON EMP
2600FOR EACH ROW
2601BEGIN
2602INSERT INTO EMPDEL
2603VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,
2604:OLD.DEPTNO,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS));
2605END;
2606/
2607157.Write a database trigger display the message when the inserting hiredate is greater than system date
2608CREATE OR REPLACE TRIGGER HIREDATE_OVER
2609AFTER INSERT ON EMP
2610FOR EACH ROW
2611BEGIN
2612IF :NEW.HIREDATE > SYSDATE THEN
2613RAISE_APPLICATION_ERROR(-20009,'INVALID HIREDATE.....');
2614END IF;
2615END;
2616/
2617158.Write a database trigger halt the transaction of EMP table if the deptno is does not exist in the dept table
2618CREATE OR REPLACE TRIGGER DEPT_NO
2619BEFORE INSERT OR UPDATE OR DELETE ON EMP
2620FOR EACH ROW
2621DECLARE
2622DNO NUMBER:=0;
2623BEGIN
2624SELECT COUNT(*) INTO DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;
2625DBMS_OUTPUT.PUT_LINE(DNO);
2626IF DNO=0 THEN
2627RAISE_APPLICATION_ERROR(-20009,'DEPTNO NOT EXIST IN DEPT TABLE....');
2628END IF;
2629END;
2630/
2631159.Write a database trigger add Rs 500 if the inserting salary is less than Rs 1000
2632CREATE OR REPLACE TRIGGER SAL_ADD
2633BEFORE INSERT ON EMP
2634FOR EACH ROW
2635BEGIN
2636IF :NEW.SAL <= 1000 THEN
2637:NEW.SAL:=:NEW.SAL+500;
2638END IF;
2639END;
2640/
2641160.Write a database trigger give the appropriate message if the record exceed more than 100 on EMP table
2642CREATE OR REPLACE TRIGGER EMP_OVER_REC
2643AFTER INSERT ON EMP
2644DECLARE
2645R NUMBER;
2646BEGIN
2647SELECT COUNT(*) INTO R FROM EMP;
2648IF R>=100 THEN
2649RAISE_APPLICATION_ERROR(-20009,'100 RECORD ALLOWED IN EMP TABLE.....');
2650END IF;
2651END;
2652/
2653161.Write a program to month and year and display the Calendar of that month.
2654DECLARE
2655D NUMBER:=1;
2656M VARCHAR2(10):='&MONTH';
2657Y NUMBER:=&YEAR;
2658C CHAR(20);
2659V VARCHAR2(500);
2660N NUMBER;
2661BEGIN
2662N:=TO_CHAR(LAST_DAY(D||'-'||M||'-'||Y),'DD');
2663C:= TO_CHAR(TO_DATE(D||'-'||M||'-'||Y),'DY');
2664dbms_output.put_line('*********************************');
2665dbms_output.put_line('* '||M||'-'||Y||' *');
2666dbms_output.put_line('*SUN MON TUE WED THU FRI SAT *');
2667dbms_output.put_line('**********************************');
2668IF C='MON' THEN
2669V:=' ';
2670ELSIF C='TUE' THEN
2671V:=' ';
2672ELSIF C='WED' THEN
2673V:=' ';
2674ELSIF C='THU' THEN
2675V:=' ';
2676ELSIF C='FRI' THEN
2677V:=' ';
2678ELSIF C='SAT' THEN
2679V:=' ';
2680END IF;
2681FOR I IN 1..N
2682LOOP
2683V:=V||LPAD(I,4);
2684IF LENGTH(V)=28 THEN
2685dbms_output.put_line(LPAD(V,29,'*')||' *');
2686V:=NULL;
2687END IF;
2688END LOOP;
2689dbms_output.put_line('*'||RPAD(V,29)||'*');
2690END;
2691/
2692Posted by bagadhi sateesh at 00:15 
2693Email This
2694BlogThis!
2695Share to Twitter
2696Share to Facebook
2697Share to Pinterest
2698
2699 
2700
2701 
2702Newer Post Older Post Home
2703Subscribe to: Post Comments (Atom)
2704Sunday, 30 September 2012
2705PL/SQL PROGRAMMING
27061.Write a program to print the following format
2707WELCOME TO PL/SQL PROGRAMMING
2708BEGIN
2709DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL PROGRAMMING');
2710END;
2711/
27122.Write a program to print the numbers from 1 to 100
2713DECLARE
2714N NUMBER(3):=1;
2715V VARCHAR2(1000);
2716BEGIN
2717WHILE N <=1000
2718LOOP
2719V:=V||''||N;
2720N:=N+1;
2721END LOOP;
2722DBMS_OUTPUT.PUT_LINE(V);
2723END;
2724/
27253.write a program to print the even numbers from 1 to 100
2726DECLARE
2727N NUMBER(3):=0;
2728BEGIN
2729WHILE N <=100
2730LOOP
2731N:=N+2;
2732DBMS_OUTPUT.PUT_LINE(N);
2733END LOOP;
2734END;
2735/
27364.Write a program to print the odd numbers from 1 to 100
2737DECLARE
2738N NUMBER(3):=1;
2739BEGIN
2740WHILE N <=100
2741LOOP
2742N:=N+2;
2743DBMS_OUTPUT.PUT_LINE(N);
2744END LOOP;
2745END;
2746/
27475.write a program for multiplication table
2748DECLARE
2749A NUMBER(2):=&A;
2750B NUMBER(2):=1;
2751C NUMBER(3);
2752BEGIN
2753WHILE B <=10
2754LOOP
2755C:=A*B;
2756DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
2757B:=B+1;
2758END LOOP;
2759END;
2760/
27616.write a program to find the sum of numbers from 1 to 100
2762DECLARE
2763N NUMBER(3):=1;
2764S NUMBER(4):=0;
2765BEGIN
2766WHILE N <=100
2767LOOP
2768S:=S+N;
2769N:=N+1;
2770END LOOP;
2771DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 IS '||S);
2772END;
2773/
27747.Write a program to find the sum of all odd numbers from 1 to 100
2775DECLARE
2776N NUMBER(3):=1;
2777S NUMBER(4):=0;
2778BEGIN
2779WHILE N <=100
2780LOOP
2781S:=S+N;
2782N:=N+2;
2783END LOOP;
2784DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 ODD NUMBERS IS '||S);
2785END;
2786/
27878.Write a program to find the sum of all even numbers from 1 to 100
2788DECLARE
2789N NUMBER(3):=0;
2790S NUMBER(4):=0;
2791BEGIN
2792WHILE N <=100
2793LOOP
2794S:=S+N;
2795N:=N+2;
2796END LOOP;
2797DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 EVEN NUMBERS IS '||S);
2798END;
2799/
28009.Write a program to accept a number and find how many digits it contain
2801DECLARE
2802N NUMBER(5):=&N;
2803CNT NUMBER:=0;
2804R NUMBER(2):=0;
2805BEGIN
2806WHILE N !=0
2807LOOP
2808R:=MOD(N,10);
2809CNT:=CNT+1;
2810N:=TRUNC(N/10);
2811END LOOP;
2812DBMS_OUTPUT.PUT_LINE('NUMBER OF DIGITS OF GIVEN NUMBER IS '||CNT);
2813END;
2814/
281510.Write a program to accept a number and find the sum of the digits
2816DECLARE
2817N NUMBER(5):=&N;
2818S NUMBER:=0;
2819R NUMBER(2):=0;
2820BEGIN
2821WHILE N !=0
2822LOOP
2823R:=MOD(N,10);
2824S:=S+R;
2825N:=TRUNC(N/10);
2826END LOOP;
2827DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS OF GIVEN NUMBER IS '||S);
2828END;
2829/
283011.Write a program to accept a number and print it in reverse order
2831DECLARE
2832N NUMBER(5):=&N;
2833REV NUMBER(5):=0;
2834R NUMBER(5):=0;
2835BEGIN
2836WHILE N !=0
2837LOOP
2838R:=MOD(N,10);
2839REV:=REV*10+R;
2840N:=TRUNC(N/10);
2841END LOOP;
2842DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);
2843END;
2844/
284512.Write a program to accept a no and check whether it is Armstrong number or not
284613.Write a porgram to generate all the Armstrong numbers from 1 to 1000
284714.Write a program to generate all prime numbers between 1 to 100
284815.Write a program to aceept a number and check whether it is prime number or not
284916.Write a program to display the fibonacci series from 1 to 10
285017.Write a program to aceept a number and print it in binary format
285118.Write a program to accept a number and find the factorial of the number
285219.Find the factorials of numbers from 1 to 10
2853DECLARE
2854FACT NUMBER:=1;
2855V VARCHAR2(100);
2856BEGIN
2857FOR I IN 1..10
2858LOOP
2859FOR J IN 1..I
2860LOOP
2861FACT:=FACT*J;
2862V:=J||'*'||V;
2863END LOOP;
2864DBMS_OUTPUT.PUT_LINE(RTRIM(V,'*')||'='||FACT);
2865FACT:=1;
2866V:=NULL;
2867END LOOP;
2868END;
2869/
287020.Write a program to aceept a number and display it in the Octal format
2871DECLARE
2872N NUMBER(2):=&N;
2873R NUMBER(2);
2874V VARCHAR2(1000);
2875BEGIN
2876WHILE N>0
2877LOOP
2878R:=MOD(N,8);
2879V:=R||V;
2880N:=TRUNC(N/8);
2881END LOOP;
2882DBMS_OUTPUT.PUT_LINE('OCTAL OF A GIVEN NUMBER IS '||V);
2883END;
2884/
288521.Write a program to accept a number and print the multiplication tables upto soo
2886DECLARE
2887N NUMBER(2):=&N;
2888M NUMBER;
2889BEGIN
2890FOR I IN N..N+5
2891LOOP
2892FOR J IN 1..10
2893LOOP
2894M:=I*J;
2895DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||M);
2896END LOOP;
2897DBMS_OUTPUT.PUT_LINE('*********************');
2898END LOOP;
2899END;
2900/
290122.Write a program to accept the temp in Centigrade and convert it into Fahrenheit(c=F-32/1.8)
2902DECLARE
2903C NUMBER:=&C;
2904F NUMBER;
2905BEGIN
2906F:=C*1.8+32;
2907DBMS_OUTPUT.PUT_LINE('THE FARENHETT OF GIVEN OC IS '||F);
2908END;
2909/
291023.Write a program to calculate the area of a triangle by accepting the 3 sides
2911(s=(a+b+c)/2 area=sqrt(s*(s-a)*(s-b)*(s-c)))
2912DECLARE
2913S NUMBER;
2914A NUMBER:=&A;
2915B NUMBER:=&B;
2916C NUMBER:=&C;
2917AREA NUMBER(7,2);
2918BEGIN
2919S:=(A+B+C)/2;
2920AREA:=SQRT(S*(S-A)*(S-B)*(S-C));
2921DBMS_OUTPUT.PUT_LINE('THE AREA OF TRIANGLE IS '||AREA);
2922END;
2923/
292424.Write a program to calculate the area of a circle by accepting the radius and unit of measure Area=PI*r2
2925DECLARE
2926R NUMBER:=&R;
2927AREA NUMBER(7,2);
2928BEGIN
2929AREA:=(22/7)*R*R;
2930DBMS_OUTPUT.PUT_LINE('THE AREA OF CIRCLE IS '||AREA);
2931END;
2932/
293325.Write a program to calculate the perimeter of a circle(perimeter=2*PI*r)
2934DECLARE
2935R NUMBER:=&R;
2936PERIMETER NUMBER(7,2);
2937BEGIN
2938PERIMETER:=2*(22/7)*R;
2939DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF CIRCLE IS '||PERIMETER);
2940END;
2941/
294226.Write a program to accept the 3 sides of the triangle and display the type of triangle
2943DECLARE
2944A NUMBER(4,2):=&A;
2945B NUMBER(4,2):=&B;
2946C NUMBER(4,2):=&C;
2947PERIMETER NUMBER(7,2);
2948BEGIN
2949IF (A=B AND B=C AND C=A) THEN
2950DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE');
2951ELSIF A=B OR A=C OR C=B THEN
2952DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE');
2953ELSE
2954DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE');
2955END IF;
2956END;
2957/
295827.Write a program accept the value of A,B&C display which is greater
2959DECLARE
2960A NUMBER(4,2):=&A;
2961B NUMBER(4,2):=&B;
2962C NUMBER(4,2):=&C;
2963BEGIN
2964IF (A>B AND A>C) THEN
2965DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
2966ELSIF B>C THEN
2967DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
2968ELSE
2969DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
2970END IF;
2971END;
2972/
297328.Write a program accept a string and check whether it is palindrome or not
2974DECLARE
2975S VARCHAR2(10):='&S';
2976L VARCHAR2(20);
2977TEMP VARCHAR2(10);
2978BEGIN
2979FOR I IN REVERSE 1..LENGTH(S)
2980LOOP
2981L:=SUBSTR(S,I,1);
2982TEMP:=TEMP||''||L;
2983END LOOP;
2984IF TEMP=S THEN
2985DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS PALINDROME');
2986ELSE
2987DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS NOT PALINDROME');
2988END IF;
2989END;
2990/
299129.Write a program aceepts the value of A,B and swap the nos and print the values
2992DECLARE
2993A NUMBER(2):=&A;
2994B NUMBER(2):=&B;
2995FLAG NUMBER(2);
2996BEGIN
2997FLAG:=A;
2998A:=B;
2999B:=FLAG;
3000DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
3001END;
3002/
300330.Write a program to accept the values of A , B and swap the numbers and print the values
3004without using third variable
3005DECLARE
3006A NUMBER(2):=&A;
3007B NUMBER(2):=&B;
3008FLAG NUMBER(2);
3009BEGIN
3010FLAG:=A;
3011A:=B;
3012B:=FLAG;
3013DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
3014END;
3015/
301631.Write a program to accept the side of a square and calculate the area area =a2
3017DECLARE
3018A NUMBER:=&A;
3019AREA NUMBER(5);
3020BEGIN
3021AREA:=A*A;
3022DBMS_OUTPUT.PUT_LINE('AREA OF A SQUARE IS '||''||AREA);
3023END;
3024/
302532.Write a program to accept principle amount ,rate,time calculate the simple interest si=(p*t*r)/100
3026DECLARE
3027P NUMBER(6,2):=&P;
3028R NUMBER(6,2):=&R;
3029T NUMBER(6,2):=&T;
3030SI NUMBER(6,2);
3031BEGIN
3032SI:=(P*R*T)/100;
3033DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
3034END;
3035/
303633.Erite a program to aceept the principle amount,rate,time and find the compound interest
3037ci=p*(1+r/100)n
3038DECLARE
3039P NUMBER(6,2):=&P;
3040R NUMBER(6,2):=&R;
3041T NUMBER(6,2):=&T;
3042CI NUMBER(6,2);
3043BEGIN
3044CI:=P*POWER(1+(R/100),T);
3045DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
3046END;
3047/
304834.WAP to calculate the sum of 1!+2!+......+n!
3049DECLARE
3050N NUMBER:=&N;
3051S NUMBER:=0;
3052F NUMBER:=1;
3053BEGIN
3054FOR I IN 1..N
3055LOOP
3056FOR J IN 1..I
3057LOOP
3058F:=F*J;
3059END LOOP;
3060S:=S+F;
3061F:=1;
3062END LOOP;
3063DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
3064END;
3065/
306635.WAP to calculate the sum of 1+1/2+1/3+......+1/n
3067DECLARE
3068N NUMBER:=&N;
3069A NUMBER;
3070S NUMBER(6,2):=0;
3071BEGIN
3072FOR I IN 1..N
3073LOOP
3074A:=1/I;
3075S:=S+A;
3076END LOOP;
3077DBMS_OUTPUT.PUT_LINE('SUM OF NO ARE '||S);
3078END;
3079/
308036.WAP to calculate the sum of 1/1!+1/2!+.....+1/n!
3081DECLARE
3082N NUMBER:=&N;
3083S NUMBER(6,2):=0;
3084F NUMBER:=1;
3085BEGIN
3086FOR I IN 1..N
3087LOOP
3088FOR J IN 1..I
3089LOOP
3090F:=F*J;
3091END LOOP;
3092S:=S+(1/F);
3093END LOOP;
3094DBMS_OUTPUT.PUT_LINE('SUM IS '||S);
3095END;
3096/
309737.WAP to calculate the sum of 1/1!+2/2!+......+n/n!
3098DECLARE
3099N NUMBER(4):=&N;
3100S NUMBER(6,2):=0;
3101F NUMBER(4):=1;
3102BEGIN
3103FOR I IN 1..N
3104LOOP
3105FOR J IN 1..I
3106LOOP
3107F:=F*J;
3108END LOOP;
3109S:=S+(I/F);
3110END LOOP;
3111DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
3112END;
3113/
311438.Write a program to display the months between two dates of a year
3115DECLARE
3116D DATE:='&D';
3117D1 DATE:='&D1';
3118BEGIN
3119WHILE D < D1
3120LOOP
3121DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'MONTH'));
3122D:=ADD_MONTHS(D,1);
3123END LOOP;
3124END;
3125/
312639.Write a program to accept the date and print the weekdays from the given date
3127DECLARE
3128D DATE:='&D';
3129WD DATE;
3130BEGIN
3131WD:=D+6;
3132WHILE D <= WD
3133LOOP
3134DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY'));
3135D:=D+1;
3136END LOOP;
3137END;
3138/
313940.WAP to accept the date and print the weekdays from the given date along with date format
3140DECLARE
3141D DATE:='&D';
3142WD DATE;
3143BEGIN
3144WD:=D+6;
3145WHILE D <= WD
3146LOOP
3147DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY')||D);
3148D:=D+1;
3149END LOOP;
3150END;
3151/
315241.Writa a program to accept a year and check whether it is leap year or not
3153DECLARE
3154Y NUMBER:=&Y;
3155R NUMBER;
3156BEGIN
3157IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0
3158THEN
3159DBMS_OUTPUT.PUT_LINE(Y ||' IS A LEAP YEAR');
3160ELSE
3161DBMS_OUTPUT.PUT_LINE(Y ||' IS NOT A LEAP YEAR');
3162END IF;
3163END;
3164/
316542.Write a program to accept a year and display all sundays along with the date
3166DECLARE
3167Y NUMBER(4):=&YYYY;
3168A DATE;
3169B DATE;
3170I NUMBER(2):=1;
3171BEGIN
3172A:=TO_DATE('01-JAN-'||Y,'DD-MON-YYYY');
3173B:=LAST_DAY(ADD_MONTHS(A,11));
3174WHILE A <= B
3175LOOP
3176IF TO_CHAR(A,'D')=1 THEN
3177DBMS_OUTPUT.PUT_LINE(LPAD(I,2,'0')||'-'||UPPER(TO_CHAR(A,'DAY'))||A);
3178I:=I+1;
3179END IF;
3180A:=A+1;
3181END LOOP;
3182END;
3183/
318443.WAP to accept a string and count how many vowels present in the string
3185DECLARE
3186V VARCHAR2(300):='&V';
3187CNT NUMBER(5):=0;
3188C CHAR;
3189BEGIN
3190FOR I IN 1..LENGTH(V)
3191LOOP
3192C:=SUBSTR(V,I,1);
3193IF C IN ('A','E','I','O','U') THEN
3194CNT:=CNT+1;
3195END IF;
3196END LOOP;
3197DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
3198END;
3199/
320044.Write a program to accept a year and check whether it is leap year or not . If it is
3201leap year then display how many sundays present in that year
3202DECLARE
3203D DATE:='&YEAR';
3204Y VARCHAR2(20);
3205CNT NUMBER(5):=0;
3206V VARCHAR2(20);
3207BEGIN
3208Y:=TO_CHAR(D,'YYYY');
3209D:=TO_DATE('01-JAN-'||Y);
3210IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0 THEN
3211FOR I IN 1..366
3212LOOP
3213V:=TO_CHAR(D,'D');
3214IF V=1 THEN
3215CNT:=CNT+1;
3216END IF;
3217D:=D+1;
3218DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
3219END LOOP;
3220END;
3221/
322245.Write a program to accept a char and check it is vowel or consonant
3223DECLARE
3224C CHAR:='&C';
3225BEGIN
3226IF C='A' OR C='E' OR C='I' OR C='O' OR C='U' THEN
3227DBMS_OUTPUT.PUT_LINE('VOWEL');
3228ELSE
3229DBMS_OUTPUT.PUT_LINE('CONSONANT');
3230END IF;
3231END;
3232/
323346.WAP to accept A,B,C & D check whether it is Ramanujan number or not
3234DECLARE
3235A NUMBER:=&A;
3236B NUMBER:=&B;
3237C NUMBER:=&C;
3238D NUMBER:=&D;
3239BEGIN
3240IF
3241POWER(A,3)+POWER(B,3)=POWER(C,3)+POWER(D,3) THEN
3242DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'='||C||CHR(179)||'+'||D||CHR(179));
3243ELSE
3244DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'!='||C||CHR(179)||'+'||D||CHR(179));
3245END IF;
3246END;
3247/
324847.WAP to accept the CMR & LMR & find out the total bill amount
3249i)0-100 units Rs.50 per unit ii)101-200n units Rs.o.25 per unit
3250iii)>200 units Rs.1.25 per unit
3251DECLARE
3252LMR NUMBER(5):=&LMR;
3253CMR NUMBER(5):=&CMR;
3254TOT NUMBER(5):=0;
3255BILL NUMBER(7,2):=0;
3256BEGIN
3257TOT:=CMR-LMR;
3258IF TOT <= 100 THEN
3259BILL:=TOT*.50;
3260ELSIF TOT > 100 AND TOT <= 200 THEN
3261BILL:=(100*.50)+((TOT-100)*.75);
3262ELSE
3263BILL:=(100*.50)+(100*.75)+(TOT-200)*1.25;
3264END IF;
3265DBMS_OUTPUT.PUT_LINE('TOTAL UNIT CONSUMED '||TOT);
3266DBMS_OUTPUT.PUT_LINE('TOTAL BILL AMOUNT '||BILL);
3267END;
3268/
326948.WAP or accept marks of 3 subject as i/p and calculate the total marks and division of a student
3270i) If totmark>=60 then division is First
3271ii) If totmark <60 and totmark>=50 then division is second
3272iii) If totmark< 50 and >=35 then division is third
3273iv) If totmark< 35 then fail
3274DECLARE
3275M1 NUMBER(2):=&M1;
3276M2 NUMBER(2):=&M2;
3277M3 NUMBER(2):=&M3;
3278TOTMARK NUMBER(5,2);
3279AVE NUMBER(5,2):=0;
3280BEGIN
3281TOTMARK:=M1+M2+M3;
3282AVE:=TOTMARK/3;
3283IF AVE>=60 THEN
3284DBMS_OUTPUT.PUT_LINE('THE DIVISION IS FIRST '||AVE);
3285ELSIF AVE<60 AND AVE>=50 THEN
3286DBMS_OUTPUT.PUT_LINE('THE DIVISION IS SECOND '||AVE);
3287ELSIF AVE<50 AND AVE>=35 THEN
3288DBMS_OUTPUT.PUT_LINE('THE DIVISION IS THIRD '||AVE);
3289ELSE
3290DBMS_OUTPUT.PUT_LINE('FAIL '||AVE);
3291END IF;
3292END;
3293/
329449.WAP to accept a number and print its multiplication table horinzontally
3295DECLARE
3296J NUMBER:=&J;
3297V VARCHAR2(1000);
3298K NUMBER(3);
3299BEGIN
3300FOR I IN 1..10
3301LOOP
3302K:=J*I;
3303V:=V||J||'*'||I||'='||K||' ';
3304END LOOP;
3305DBMS_OUTPUT.PUT_LINE(V);
3306END;
3307/
330850.WAP to accept a string and print it in reverse order
3309DECLARE
3310STR VARCHAR2(100):='&sTR';
3311STR1 VARCHAR2(100);
3312N NUMBER(5);
3313L VARCHAR2(20);
3314BEGIN
3315N:=LENGTH(STR);
3316FOR I IN 1..N
3317LOOP
3318L:=SUBSTR(STR,I,1);
3319STR1:=L||STR1;
3320END LOOP;
3321DBMS_OUTPUT.PUT_LINE(STR1);
3322END;
3323/
332451.Write a program to accept a number and find out the sum of first and
3325last digits
3326DECLARE
3327A NUMBER(4):=&A;
3328B NUMBER(5):=0;
3329C NUMBER(5):=0;
3330S NUMBER(5);
3331BEGIN
3332IF A>9 THEN
3333C:=SUBSTR(A,1,1);
3334B:=SUBSTR(A,LENGTH(A),1);
3335S:=B+C;
3336ELSE
3337S:=A;
3338END IF;
3339DBMS_OUTPUT.PUT_LINE('SUM OF FIRST AND LAST DIGIT IS '||S);
3340END;
3341/
334252.WAP to accept the basic salary and find out the ta,da,hra,lic and gs
3343i)ta 20% of basic, da 10% of basic, hra 30% of basic, lic 5% of basic
3344DECLARE
3345BS NUMBER(6,2):=&BS;
3346TA NUMBER(6,2);
3347DA NUMBER(6,2);
3348HRA NUMBER(6,2);
3349GS NUMBER(6,2);
3350LIC NUMBER(6,2);
3351NS NUMBER(8,2);
3352BEGIN
3353TA:=BS*(20/100);
3354HRA:=BS*(30/100);
3355DA:=BS*(10/100);
3356LIC:=BS*(5/100);
3357GS:=TA+HRA+DA;
3358NS:=GS-LIC;
3359DBMS_OUTPUT.PUT_LINE('EMPLOYEE BS IS '||BS);
3360DBMS_OUTPUT.PUT_LINE('GROSS SALARY IS '||GS);
3361DBMS_OUTPUT.PUT_LINE('NET SALARY IS '||NS);
3362END;
3363/
336453.WAP to accept the length and breadth of a rectangle and find out the perimeter
3365DECLARE
3366L NUMBER(4,2):=&L;
3367B NUMBER(4,2):=&B;
3368A NUMBER(4,2);
3369BEGIN
3370A:=2*(L+B);
3371DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF RECTANGLE IS '||A);
3372END;
3373/
337454.WAP to accept the cost price and selling price of an item and find
3375the loss or profit
3376DECLARE
3377CP NUMBER(25,2):=&CP;
3378SP NUMBER(25,2):=&SP;
3379AMT NUMBER(7,2);
3380BEGIN
3381IF CP < SP THEN
3382AMT:=SP-CP;
3383DBMS_OUTPUT.PUT_LINE('PROFIT IS '||AMT);
3384ELSE
3385AMT:=CP-SP;
3386DBMS_OUTPUT.PUT_LINE('LOSS IS '||AMT);
3387END IF;
3388END;
3389/
339055.Writ a program to generate the following series
339153 53 53 53 53
339243 43 43 43
339333 33 33
339423 23
339513
3396DECLARE
3397V VARCHAR2(20);
3398BEGIN
3399FOR I IN REVERSE 1..5
3400LOOP
3401FOR J IN 1..I
3402LOOP
3403V:=V||I||CHR(179);
3404END LOOP;
3405DBMS_OUTPUT.PUT_LINE(V);
3406V:=NULL;
3407END LOOP;
3408END;
3409/
341056.WAP to accept a no in binary format and print it in decimal format
3411DECLARE
3412N VARCHAR2(20):=&N;
3413PRO NUMBER(10,4):=0;
3414L VARCHAR2(10);
3415BEGIN
3416FOR I IN 1..LENGTH(N)
3417LOOP
3418L:=SUBSTR(N,I,1);
3419PRO:=PRO+L*POWER(2,LENGTH(N)-I);
3420END LOOP;
3421DBMS_OUTPUT.PUT_LINE('THE DECIMAL NUMBER IS '||PRO);
3422END;
3423/
342457.WAP to accept two nos and input and find one no is raised to another one (without using any function)
3425DECLARE
3426A NUMBER:=&A;
3427B NUMBER:=&B;
3428R NUMBER:=1;
3429BEGIN
3430FOR I IN 1..B
3431LOOP
3432R:=R*A;
3433END LOOP;
3434DBMS_OUTPUT.PUT_LINE('A RAISED POWER B IS '||R);
3435END;
3436/
343758.WAP to accept a sentence and count the no of chars in that sentence
3438DECLARE
3439STR VARCHAR2(100):='&STR';
3440NO NUMBER(5):=0;
3441I NUMBER;
3442BEGIN
3443I:=INSTR(STR,'.');
3444DBMS_OUTPUT.PUT_LINE('NO OF CHAR IS '||I);
3445END;
3446/
344759.WAP to accept two strings and display the large one among those
3448DECLARE
3449STR1 VARCHAR2(100):='&STR1';
3450STR2 VARCHAR2(100):='&STR2';
3451BEGIN
3452IF LENGTH(STR1) > LENGTH(STR2) THEN
3453DBMS_OUTPUT.PUT_LINE(STR1 ||' IS GREATER');
3454ELSIF LENGTH(STR1) < LENGTH(STR2) THEN
3455DBMS_OUTPUT.PUT_LINE(STR2 ||' IS GREATER');
3456ELSE
3457DBMS_OUTPUT.PUT_LINE('BOTH STRINGS ARE EQUAL');
3458END IF;
3459END;
3460/
346160.WAP to display all the nos whose sum of digits is 9 from 1 to 9999
3462DECLARE
3463N NUMBER;
3464M NUMBER;
3465S NUMBER:=0;
3466BEGIN
3467FOR I IN 1..999
3468LOOP
3469N:=I;
3470WHILE N>0
3471LOOP
3472M:=MOD(N,10);
3473S:=S+M;
3474N:=TRUNC(N/10);
3475END LOOP;
3476IF S=9 THEN
3477DBMS_OUTPUT.PUT_LINE(I||' ');
3478END IF;
3479S:=0;
3480END LOOP;
3481END;
3482/
348361.WAP to accept a no and find the sum in a single digit
3484DECLARE
3485N NUMBER(4):=&N;
3486S NUMBER(10):=0;
3487BEGIN
3488WHILE LENGTH(N)>1
3489LOOP
3490FOR I IN 1..LENGTH(N)
3491LOOP
3492S:=S+SUBSTR(N,I,1);
3493END LOOP;
3494N:=S;
3495S:=0;
3496END LOOP;
3497DBMS_OUTPUT.PUT_LINE('THE SUM IN SINGLE DIGIT IS '||N);
3498END;
3499/
350062.Ente the no of days and find out the no of years and no of days and months
3501DECLARE
3502D NUMBER:=&D;
3503Y NUMBER;
3504M NUMBER;
3505BEGIN
3506Y:=TRUNC(D/365);
3507M:=TRUNC(MOD(D,365)/30);
3508D:=MOD(MOD(D,365),30);
3509DBMS_OUTPUT.PUT_LINE(Y||' YEARS '||M||' MONTHS '||D||' DAYS');
3510END;
3511/
351263.WAP to accept the date and print all the weekdays along with the given date
3513DECLARE
3514D DATE:='&D';
3515V VARCHAR2(20);
3516BEGIN
3517FOR I IN 1..7
3518LOOP
3519V:=TO_CHAR(D,'DAY')||D;
3520DBMS_OUTPUT.PUT_LINE(V);
3521D:=D+1;
3522END LOOP;
3523END;
3524/
352564.WAP while purchasing certain items,discount of each is as follows
3526i) If qty purchased > 1000 discount is 20%
3527ii) If the qty and price per item are i/p then calculate the expenditure
3528DECLARE
3529QTY NUMBER(5):=&QTY;
3530UP NUMBER(6,2):=&UP;
3531DIS NUMBER(6,2):=0;
3532TAMT NUMBER(10,2);
3533BILL NUMBER(10,2);
3534BEGIN
3535BILL:=QTY*UP;
3536IF BILL > 1000 THEN
3537DIS:=BILL*20/1000;
3538END IF;
3539TAMT:=BILL-DIS;
3540DBMS_OUTPUT.PUT_LINE('THE TOTAL AMOUNT IS '||TAMT);
3541END;
3542/
354365.Write a program to accept a string and count the no of individual chars
3544DECLARE
3545V VARCHAR2(100):='&V';
3546V1 VARCHAR2(100);
3547LB NUMBER;
3548LA NUMBER;
3549DIFF NUMBER;
3550C CHAR;
3551N NUMBER(5):=0;
3552BEGIN
3553V1:=V;
3554WHILE LENGTH(V1)>0
3555LOOP
3556C:=SUBSTR(V1,1,1);
3557LB:=LENGTH(V1);
3558V1:=REPLACE(V1,C);
3559LA:=NVL(LENGTH(V1),0);
3560DIFF:=LB-LA;
3561IF ASCII(C)=32 THEN
3562DBMS_OUTPUT.PUT_LINE('SPACE'||' EXISTS '||DIFF||' TIMES');
3563ELSE
3564DBMS_OUTPUT.PUT_LINE(C||' EXISTS '||DIFF||' TIMES');
3565END IF;
3566N:=N+DIFF;
3567END LOOP;
3568DBMS_OUTPUT.PUT_LINE('TOTAL LENGTH OF THE GIVEN STRING '||V||'='||N);
3569END;
3570/
357166.Write a program to display all combination of 1,2,&3
3572BEGIN
3573FOR I IN 1..3
3574LOOP
3575FOR J IN 1..3
3576LOOP
3577FOR K IN 1..3
3578LOOP
3579DBMS_OUTPUT.PUT_LINE(I||J||K);
3580END LOOP;
3581END LOOP;
3582END LOOP;
3583END;
3584/
358567.Write a program to find out the series 12+22+32+42+....++n2
3586DECLARE
3587N NUMBER:=&N;
3588A NUMBER:=1;
3589B NUMBER:=2;
3590C NUMBER:=0;
3591D NUMBER:=0;
3592S NUMBER:=0;
3593BEGIN
3594WHILE A<=N
3595LOOP
3596C:=C+A*A;
3597A:=A+2;
3598END LOOP;
3599WHILE B<=N
3600LOOP
3601D:=D+B*B;
3602B:=B+2;
3603END LOOP;
3604S:=C-D;
3605DBMS_OUTPUT.PUT_LINE('RESULT IS '||S);
3606END;
3607/
360868.Write a program to accep the time in HH & MIN format and find the total senconds
3609DECLARE
3610H NUMBER:=&HOUR;
3611M NUMBER:=&MINUTE;
3612S NUMBER(10):=0;
3613BEGIN
3614S:=(H*60*60)+(M*60);
3615DBMS_OUTPUT.PUT_LINE(H||' HOURS '||M||' MINUTES '||'IS'||S||' SECONDS');
3616END;
3617/
361869.WAP to accept the distance between two cities in km and convert into mts ,cm & ft
3619DECLARE
3620D NUMBER:=&D;
3621M NUMBER:=0;
3622CM NUMBER:=0;
3623FT NUMBER:=0;
3624BEGIN
3625M:=D*1000;
3626CM:=M*100;
3627FT:=ROUND(CM/12.3);
3628DBMS_OUTPUT.PUT_LINE('DISTANCE IN METERS IS '||M);
3629DBMS_OUTPUT.PUT_LINE('DISTANCE IN CENTIMETERS IS '||CM);
3630DBMS_OUTPUT.PUT_LINE('DISTANCE IN FOOT IS '||FT);
3631END;
3632/
363370.Write a program to find the series x+x2/2!+x3/3!+.....+xn/n!
3634DECLARE
3635N NUMBER:=&N;
3636X NUMBER:=&X;
3637S NUMBER:=0;
3638F NUMBER:=1;
3639BEGIN
3640FOR I IN 1..N
3641LOOP
3642FOR J IN 1..I
3643LOOP
3644F:=F*J;
3645END LOOP;
3646S:=ROUND(s+(POWER(X,I)/F),3);
3647F:=1;
3648END LOOP;
3649DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS '||S);
3650END;
3651/
365271.Write a program to accept the population of hyderabad each year the population increases
36532% after 4y what is the population of hyd
3654DECLARE
3655P NUMBER:=&P;
3656L NUMBER;
3657BEGIN
3658FOR J IN 1..4
3659LOOP
3660L:=P*2/100;
3661P:=P+L;
3662END LOOP;
3663DBMS_OUTPUT.PUT_LINE('POPULATION OF HYDERABAD AFTER 4 YEARS IS '||TRUNC(P));
3664END;
3665/
366672.WAP to accept the 3 dates and display the most recently month among 3 dates
3667DECLARE
3668D1 DATE:='&D1';
3669D2 DATE:='&D2';
3670D3 DATE:='&D3';
3671M1 NUMBER;
3672M2 NUMBER;
3673M3 NUMBER;
3674BEGIN
3675M1:=TO_CHAR(D1,'MM');
3676M2:=TO_CHAR(D2,'MM');
3677M3:=TO_CHAR(D3,'MM');
3678IF M1>M2 AND M1>M3 THEN
3679DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'MON')||' IS RECENT MONTH');
3680ELSIF M2>M1 AND M2>M3 THEN
3681DBMS_OUTPUT.PUT_LINE(TO_CHAR(D2,'MON')||' IS RECENT MONTH');
3682ELSE
3683DBMS_OUTPUT.PUT_LINE(TO_CHAR(D3,'MON')||' IS RECENT MONTH');
3684END IF;
3685END;
3686/
368773.Accept a string and print in the following format
3688O
3689OR
3690ORA
3691ORAC
3692ORACL
3693ORACLE
3694DECLARE
3695V VARCHAR2(20):='&V';
3696C VARCHAR(20);
3697BEGIN
3698FOR I IN 1..LENGTH(V)
3699LOOP
3700C:=SUBSTR(V,1,I);
3701DBMS_OUTPUT.PUT_LINE(C);
3702END LOOP;
3703END;
3704/
370574.Write a program to accept the annual income of the emp and find the income tax
3706i) If the annsal > 60000 then tax is 10% of income
3707ii) If the annsal > 100000 then tax is Rs 800+16% of income
3708iii) If the annsal > 140000 then tax is Rs 2500+25% of income
3709DECLARE
3710AI NUMBER(10,2):=&ANNUALINCOME;
3711TAX NUMBER(10,3):=0;
3712BEGIN
3713IF AI BETWEEN 36000 AND 50000 THEN
3714TAX:=AI*10/100;
3715ELSIF AI BETWEEN 50000 AND 100000 THEN
3716TAX:=800+AI*16/100;
3717ELSIF AI > 100000 THEN
3718TAX:=2500+AI*25/100;
3719END IF;
3720DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
3721DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
3722END;
3723/
372475.WAP to accept a year as i/p & find how many even number present in that year
3725DECLARE
3726Y NUMBER:=&YEAR;
3727A VARCHAR2(20);
3728CNT NUMBER(5):=0;
3729BEGIN
3730FOR I IN 1..LENGTH(Y)
3731LOOP
3732A:=SUBSTR(Y,I,1);
3733IF MOD(A,2)=0 THEN
3734CNT:=CNT+1;
3735END IF;
3736END LOOP;
3737DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
3738END;
3739/
374076.WAP to accept a year as i/p & find how many odd number present in that year
3741DECLARE
3742Y NUMBER:=&YEAR;
3743A VARCHAR2(20);
3744CNT NUMBER(5):=0;
3745BEGIN
3746FOR I IN 1..LENGTH(Y)
3747LOOP
3748A:=SUBSTR(Y,I,1);
3749IF MOD(A,2)!=0 THEN
3750CNT:=CNT+1;
3751END IF;
3752END LOOP;
3753DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
3754END;
3755/
375677.WAP to accept a number and calculate the sum of numbers in even places
3757DECLARE
3758N NUMBER:=&NUMBER;
3759A VARCHAR2(10);
3760S NUMBER:=0;
3761BEGIN
3762FOR I IN 1..LENGTH(N)
3763LOOP
3764A:=SUBSTR(N,I,1);
3765IF MOD(I,2)=0 THEN
3766S:=S+A;
3767END IF;
3768END LOOP;
3769DBMS_OUTPUT.PUT_LINE('SUM OF EVEN PLACE IS '||S);
3770END;
3771/
377278.WAP to accept the emp details and calculate the bonus based on the following conditions
3773i) If sal < 500 then bonus is 10% sal
3774ii) If sal > 3500 then bonus is 12% sal
3775iii) If sal > 1000 then bonus is 13.5% sal
3776DECLARE
3777EMPNOV NUMBER:=&EMPNOV;
3778SALV NUMBER;
3779B NUMBER(7,2);
3780BEGIN
3781SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
3782IF SALV BETWEEN 500 AND 3500 THEN
3783B:=SALV*10/100;
3784ELSIF SALV BETWEEN 3500 AND 10000 THEN
3785B:=SALV*12/100;
3786ELSIF SALV>10000 THEN
3787B:=SALV*13.5/100;
3788END IF;
3789DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
3790DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
3791DBMS_OUTPUT.PUT_LINE('BONUS '||B);
3792END;
3793/
379479.WAP to accept the empno and display ename,sal,hiredate and calculate ta,da,hra,lic,gross,exp and
3795print all emp details. ta is 30% of sal,da is 20% of sal,hra is 15% of sal,lic is 5% of sal
3796DECLARE
3797EMPNOV NUMBER:=&EMPNOV;
3798ENAMEV EMP.ENAME%TYPE;
3799SALV EMP.SAL%TYPE;
3800HIREDATEV EMP.HIREDATE%TYPE;
3801EXP NUMBER(7,2);
3802TA NUMBER(7,2);
3803DA NUMBER(7,2);
3804HRA NUMBER(7,2);
3805LIC NUMBER(7,2);
3806GROSS NUMBER(7,2);
3807S NUMBER:=0;
3808BEGIN
3809SELECT ENAME,SAL,HIREDATE INTO ENAMEV,SALV,HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
3810EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
3811TA:=SALV*30/100;
3812DA:=SALV*20/100;
3813HRA:=SALV*15/100;
3814LIC:=SALV*5/100;
3815GROSS:=SALV+TA+DA+HRA-LIC;
3816DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
3817DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
3818DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
3819DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP);
3820DBMS_OUTPUT.PUT_LINE('TA '||TA);
3821DBMS_OUTPUT.PUT_LINE('DA '||DA);
3822DBMS_OUTPUT.PUT_LINE('HRA '||HRA);
3823DBMS_OUTPUT.PUT_LINE('LIC '||LIC);
3824DBMS_OUTPUT.PUT_LINE('GROSS '||GROSS);
3825END;
3826/
382780.WAP to accept the item no ,item name,qty,unit price and calculate the bill
3828If the bill > 500 then give discount 2% of bill amount and display the details
3829DECLARE
3830INO NUMBER:=&INO;
3831INAME VARCHAR2(50):='&INAME';
3832QTY NUMBER(5):=&QTY;
3833UP NUMBER(7,2):=&UP;
3834DIS NUMBER(7,2):=0;
3835BILL NUMBER(7,2);
3836NET NUMBER(7,2);
3837BEGIN
3838BILL:=QTY*UP;
3839IF BILL>500 THEN
3840DIS:=BILL*2/100;
3841END IF;
3842NET:=BILL-DIS;
3843DBMS_OUTPUT.PUT_LINE('ITEM NO '||INO);
3844DBMS_OUTPUT.PUT_LINE('ITEM NAME '||INAME);
3845DBMS_OUTPUT.PUT_LINE('QUANTITY '||QTY);
3846DBMS_OUTPUT.PUT_LINE('UNIT PRICE '||UP);
3847DBMS_OUTPUT.PUT_LINE('BILL AMT '||BILL);
3848DBMS_OUTPUT.PUT_LINE('DISCOUNT '||DIS);
3849DBMS_OUTPUT.PUT_LINE('NET AMT '||NET);
3850END;
3851/
385281.Write a program to generate sequence of numbers horizontally from 1 to 25
3853DECLARE
3854V VARCHAR2(100);
3855BEGIN
3856FOR I IN 1..25
3857LOOP
3858V:=V||' '||I;
3859END LOOP;
3860DBMS_OUTPUT.PUT_LINE(V);
3861END;
3862/
386382.WAP to accept a empno and display empno,name,sal,exp,dname,grade and loc.
3864DECLARE
3865EMPNOV NUMBER:=&EMPNO;
3866ENAMEV EMP.ENAME%TYPE;
3867HIREDATEV DATE;
3868SALV EMP.SAL%TYPE;
3869EXP NUMBER;
3870DNAMEV DEPT.DNAME%TYPE;
3871GRADEV SALGRADE.GRADE%TYPE;
3872BEGIN
3873SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
3874WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
3875EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
3876DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
3877DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
3878DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
3879DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
3880DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
3881DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
3882END;
3883/
388483.WAP to accept a empno and display empno,based on experience calculate the bonus and store it into the bonus table
3885If exp > 5 years then bonus is 1 month salary
3886If exp between 5 and 9 years then bonus is 20% of annual salary
3887If exp more than 9 years then bonus is 1 month sal plus 25% of annual salary
3888DECLARE
3889EMPNOV NUMBER:=&EMPNO;
3890ENAMEV EMP.ENAME%TYPE;
3891HIREDATEV DATE;
3892SALV EMP.SAL%TYPE;
3893EXP NUMBER;
3894DNAMEV DEPT.DNAME%TYPE;
3895GRADEV SALGRADE.GRADE%TYPE;
3896BEGIN
3897SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
3898WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
3899EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
3900DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
3901DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
3902DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
3903DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
3904DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
3905DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
3906END;
3907/
390884.WAP to accept the empno, based upon the dname transfer the emps ie, make the changes in the emp table. Transfer the emps from Accounting dept to Research, Research dept to Operation, Opertion dept to Sales
3909and Sales to Accounting dept
3910DECLARE
3911EMPNOV NUMBER:=&EMPNO;
3912DNAMEV VARCHAR2(20);
3913DNAMEVV VARCHAR2(20);
3914BEGIN
3915SELECT DNAME INTO DNAMEV FROM EMP,DEPT WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO;
3916IF DNAMEV='ACCOUNTING' THEN
3917DNAMEVV:='RESEARCH';
3918ELSIF DNAMEV='RESEARCH' THEN
3919DNAMEVV:='SALES';
3920ELSIF DNAMEV='SALES' THEN
3921DNAMEVV:='OPERATIONS';
3922ELSIF DNAMEV='OPERATIONS' THEN
3923DNAMEVV:='ACCOUNTING';
3924END IF;
3925UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=DNAMEVV) WHERE EMPNO=EMPNOV;
3926END;
3927/
392885.WAP to accept the empno and display all the details of emp. If emp doesnot exist display the appreciate message
3929DECLARE
3930EMPNOV NUMBER:=&EMPNO;
3931EMPV EMP%ROWTYPE;
3932BEGIN
3933SELECT * INTO EMPV FROM EMP WHERE EMPNO=EMPNOV;
3934DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPV.EMPNO);
3935DBMS_OUTPUT.PUT_LINE('ENAME '||EMPV.ENAME);
3936DBMS_OUTPUT.PUT_LINE('JOB '||EMPV.JOB);
3937DBMS_OUTPUT.PUT_LINE('SALARY '||EMPV.SAL);
3938DBMS_OUTPUT.PUT_LINE('HIREDATE '||EMPV.HIREDATE);
3939DBMS_OUTPUT.PUT_LINE('DEPTNO '||EMPV.DEPTNO);
3940DBMS_OUTPUT.PUT_LINE('MGRNO '||EMPV.MGR);
3941DBMS_OUTPUT.PUT_LINE('COMMISSION '||EMPV.COMM);
3942EXCEPTION
3943WHEN NO_DATA_FOUND THEN
3944DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EXIST');
3945END;
3946/
394786.WAP to accept the empno and print all the details of emp,dept and salgrade
3948DECLARE
3949E EMP%ROWTYPE;
3950D DEPT%ROWTYPE;
3951S SALGRADE%ROWTYPE;
3952BEGIN
3953SELECT * INTO E FROM EMP WHERE EMPNO=&EMPNO;
3954SELECT * INTO D FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO;
3955SELECT * INTO S FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
3956DBMS_OUTPUT.PUT_LINE('EMPNO '||E.EMPNO);
3957DBMS_OUTPUT.PUT_LINE('DEPTNO '||D.DEPTNO);
3958DBMS_OUTPUT.PUT_LINE('DNAME '||D.DNAME);
3959DBMS_OUTPUT.PUT_LINE('LOCATION '||D.LOC);
3960DBMS_OUTPUT.PUT_LINE('GRADE '||S.GRADE);
3961DBMS_OUTPUT.PUT_LINE('HISALARY '||S.HISAL);
3962DBMS_OUTPUT.PUT_LINE('LOWSALARY '||S.LOSAL);
3963END;
3964/
396587.WAP to accept the mgrno and display the empno,ename,sal,dname and grade of all emps working under that mgr
3966DECLARE
3967MGRV NUMBER:=&MGRV;
3968CURSOR EMPCUR IS
3969SELECT EMPNO,ENAME,SAL,DEPTNO,GRADE FROM EMP,SALGRADE WHERE MGR=MGRV AND SAL BETWEEN LOSAL AND HISAL;
3970X EMPCUR%ROWTYPE;
3971BEGIN
3972OPEN EMPCUR;
3973LOOP
3974FETCH EMPCUR INTO X;
3975EXIT WHEN EMPCUR%NOTFOUND;
3976DBMS_OUTPUT.PUT_LINE('EMPNO '||X.EMPNO);
3977DBMS_OUTPUT.PUT_LINE('ENAME '||X.ENAME);
3978DBMS_OUTPUT.PUT_LINE('SALARY '||X.SAL);
3979DBMS_OUTPUT.PUT_LINE('DEPTNO '||X.DEPTNO);
3980DBMS_OUTPUT.PUT_LINE('GRADE '||X.GRADE);
3981DBMS_OUTPUT.PUT_LINE('******************');
3982END LOOP;
3983CLOSE EMPCUR;
3984END;
3985/
398688.WAP to accept the empno and display the exp with minimum 3 decimal places
3987DECLARE
3988EMPNOV NUMBER:=&EMPNOV;
3989HIREDATEV DATE;
3990EXPV NUMBER(10,5);
3991BEGIN
3992SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
3993EXPV:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
3994DBMS_OUTPUT.PUT_LINE('EXPERIENCE OF EMP'||EMPNOV||' IS '||EXPV||' YEARS ');
3995END;
3996/
399789.Write a program to print the following series
39981
39991 2
40001 2 3
40011 2 3 4
40021 2 3 4 5
4003DECLARE
4004V VARCHAR2(20);
4005BEGIN
4006FOR I IN 1..5
4007LOOP
4008FOR J IN 1..I
4009LOOP
4010V:=V||' '||J;
4011END LOOP;
4012DBMS_OUTPUT.PUT_LINE(V);
4013V:=NULL;
4014END LOOP;
4015END;
4016/
401790.Write a program to print the following series
40181
40192 1
40203 2 1
40214 3 2 1
40225 4 3 2 1
4023DECLARE
4024V VARCHAR2(20);
4025BEGIN
4026FOR I IN 1..5
4027LOOP
4028FOR J IN REVERSE 1..I
4029LOOP
4030V:=V||' '||J;
4031END LOOP;
4032DBMS_OUTPUT.PUT_LINE(V);
4033V:=NULL;
4034END LOOP;
4035END;
4036/
403791.Write a program to print the following series
40381 2 3 4 5
40391 2 3 4
40401 2 3
40411 2
40421
4043DECLARE
4044V VARCHAR2(20);
4045BEGIN
4046FOR I IN REVERSE 1..5
4047LOOP
4048FOR J IN 1..I
4049LOOP
4050V:=V||' '||J;
4051END LOOP;
4052DBMS_OUTPUT.PUT_LINE(V);
4053V:=NULL;
4054END LOOP;
4055END;
4056/
405792.Write a program to print the following series
40581 1 1 1 1
40592 2 2 2 2
40603 3 3 3 3
40614 4 4 4 4
40625 5 5 5 5
4063DECLARE
4064V VARCHAR2(20);
4065BEGIN
4066FOR I IN 1..5
4067LOOP
4068FOR J IN 1..5
4069LOOP
4070V:=V||' '||I;
4071END LOOP;
4072DBMS_OUTPUT.PUT_LINE(V);
4073V:=NULL;
4074END LOOP;
4075END;
4076/
407793.Write a program to print the following series
40781 2 3 4 5
40791 2 3 4 5
40801 2 3 4 5
40811 2 3 4 5
40821 2 3 4 5
4083DECLARE
4084V VARCHAR2(20);
4085BEGIN
4086FOR I IN 1..5
4087LOOP
4088FOR J IN 1..5
4089LOOP
4090V:=V||' '||J;
4091END LOOP;
4092DBMS_OUTPUT.PUT_LINE(V);
4093V:=NULL;
4094END LOOP;
4095END;
4096/
409794.Write a program to print the following series
40985 4 3 2 1
40995 4 3 2
41005 4 3
41015 4
41025
4103DECLARE
4104V VARCHAR2(20);
4105BEGIN
4106FOR I IN 1..5
4107LOOP
4108FOR J IN REVERSE 1..5
4109LOOP
4110IF I<=J THEN
4111V:=V||' '||J;
4112END IF;
4113END LOOP;
4114DBMS_OUTPUT.PUT_LINE(V);
4115V:=NULL;
4116END LOOP;
4117END;
4118/
411995.Write a program to print the following series
41205 5 5 5 5
41214 4 4 4
41223 3 3
41232 2
41241
4125DECLARE
4126V VARCHAR2(20);
4127BEGIN
4128FOR I IN REVERSE 1..5
4129LOOP
4130FOR J IN 1..5
4131LOOP
4132IF I>=J THEN
4133V:=V||' '||I;
4134END IF;
4135END LOOP;
4136DBMS_OUTPUT.PUT_LINE(V);
4137V:=NULL;
4138END LOOP;
4139END;
4140/
414196.Write a program to print the following series
41421
41432 2
41443 3 3
41454 4 4 4
41465 5 5 5 5
4147DECLARE
4148V VARCHAR2(20);
4149BEGIN
4150FOR I IN 1..5
4151LOOP
4152FOR J IN 1..I
4153LOOP
4154V:=V||' '||I;
4155END LOOP;
4156DBMS_OUTPUT.PUT_LINE(V);
4157V:=NULL;
4158END LOOP;
4159END;
4160/
416197.Write a program to print the following series
41621
41630 1
41641 0 1
41650 1 0 1
41661 0 1 0 1
4167DECLARE
4168A NUMBER:=1;
4169V VARCHAR2(20):=1;
4170BEGIN
4171DBMS_OUTPUT.PUT_LINE(V);
4172FOR I IN 1..4
4173LOOP
4174IF SUBSTR(V,1,1)='1' THEN
4175V:='0'||V;
4176ELSE
4177V:='1'||V;
4178END IF;
4179DBMS_OUTPUT.PUT_LINE(V);
4180END LOOP;
4181END;
4182/
418398.Write a program to print the following series
4184*
4185* *
4186* * *
4187* * * *
4188* * * * *
4189DECLARE
4190V VARCHAR2(20);
4191BEGIN
4192FOR I IN 1..5
4193LOOP
4194FOR J IN 1..I
4195LOOP
4196V:=V||' '||'*';
4197END LOOP;
4198DBMS_OUTPUT.PUT_LINE(V);
4199V:=NULL;
4200END LOOP;
4201END;
4202/
420399.Write a program to print the following series
4204*
4205* *
4206* * *
4207* * * *
4208* * * * *
4209* * * *
4210* * *
4211* *
4212*
4213DECLARE
4214V VARCHAR2(20);
4215BEGIN
4216FOR I IN 1..5
4217LOOP
4218FOR J IN 1..I
4219LOOP
4220V:=V||' '||'*';
4221END LOOP;
4222DBMS_OUTPUT.PUT_LINE(V);
4223V:=NULL;
4224END LOOP;
4225FOR I IN REVERSE 1..5
4226LOOP
4227FOR J IN 2..I
4228LOOP
4229V:=V||' '||'*';
4230END LOOP;
4231DBMS_OUTPUT.PUT_LINE(V);
4232V:=NULL;
4233END LOOP;
4234END;
4235/
4236100.Write a program to print the following series
42371 2 3 4 5
42382 3 4 5
42393 4 5
42404 5
42415
4242DECLARE
4243V VARCHAR2(20);
4244BEGIN
4245FOR I IN 1..5
4246LOOP
4247FOR J IN I..5
4248LOOP
4249V:=V||' '||J;
4250END LOOP;
4251DBMS_OUTPUT.PUT_LINE(V);
4252V:=NULL;
4253END LOOP;
4254END;
4255/
4256101.Write a program to print the following series
42575 4 3 2 1
42584 3 2 1
42593 2 1
42602 1
42611
4262DECLARE
4263V VARCHAR2(20);
4264BEGIN
4265FOR I IN REVERSE 1..5
4266LOOP
4267FOR J IN REVERSE 1..I
4268LOOP
4269V:=V||' '||J;
4270END LOOP;
4271DBMS_OUTPUT.PUT_LINE(V);
4272V:=NULL;
4273END LOOP;
4274END;
4275/
4276102.WAP to accept 2 nos and find the sum and product of the nos and print the output
4277DECLARE
4278A NUMBER:=&A;
4279B NUMBER:=&B;
4280S NUMBER;
4281M NUMBER;
4282BEGIN
4283S:=A+B;
4284M:=A*B;
4285DBMS_OUTPUT.PUT_LINE('SUM OF '||'A'||' AND '||'B'||' IS '||S);
4286DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
4287END;
4288/
4289103.WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function)
4290DECLARE
4291A NUMBER:=&A;
4292B NUMBER:=&B;
4293C NUMBER;
4294M NUMBER;
4295BEGIN
4296C:=TRUNC(A/B);
4297M:=A-C*B;
4298DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
4299END;
4300/
4301104.WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122
4302BEGIN
4303FOR I IN 1..255
4304LOOP
4305DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
4306END LOOP;
4307END;
4308/
4309105.Print the following format
4310ORACLE
4311ORACL
4312ORAC
4313ORA
4314OR
4315O
4316DECLARE
4317STR VARCHAR2(10):='&STR';
4318L VARCHAR2(10);
4319N NUMBER(15);
4320BEGIN
4321N:=LENGTH(STR);
4322WHILE N>=1
4323LOOP
4324L:=SUBSTR(STR,1,N);
4325N:=N-1;
4326DBMS_OUTPUT.PUT_LINE(L);
4327END LOOP;
4328END;
4329/
4330106.WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time
4331DECLARE
4332HH NUMBER;
4333BEGIN
4334HH:=TO_CHAR(SYSDATE,'HH24');
4335IF HH>6 AND HH<12 THEN
4336DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
4337ELSIF HH>=12 AND HH<18 THEN
4338DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
4339ELSIF HH>=18 AND HH<25 THEN
4340DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
4341END IF;
4342END;
4343/
4344107.WAP to accept two strings and concat the two strings
4345DECLARE
4346STR VARCHAR2(20):='&STR';
4347STR1 VARCHAR2(20):='&STR1';
4348V VARCHAR2(40);
4349BEGIN
4350V:=STR||''||STR1;
4351DBMS_OUTPUT.PUT_LINE(V);
4352END;
4353/
4354108.WAP to accept a string and count the no of chars,words in that string
4355DECLARE
4356STR VARCHAR2(20):='&STR';
4357NOC NUMBER(4):=0;
4358NOW NUMBER(4):=1;
4359S CHAR;
4360BEGIN
4361FOR I IN 1..LENGTH(STR)
4362LOOP
4363S:=SUBSTR(STR,I,1);
4364NOC:=NOC+1;
4365IF S=' ' THEN
4366NOW:=NOW+1;
4367END IF;
4368END LOOP;
4369DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
4370DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
4371END;
4372/
4373109.WAP to accept the octal number and print it in decimal format
4374DECLARE
4375N VARCHAR2(20):='&N';
4376A NUMBER;
4377P NUMBER:=0;
4378C CHAR;
4379BEGIN
4380A:=LENGTH(N);
4381FOR I IN 1..A
4382LOOP
4383C:=SUBSTR(N,I,1);
4384P:=P+C*POWER(8,A-I);
4385END LOOP;
4386DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
4387END;
4388/
4389110.WAP to accept the mgr and find how many emps are working under that mgr
4390DECLARE
4391MGRV EMP.MGR%TYPE:=&MGRNO;
4392N NUMBER:=0;
4393BEGIN
4394SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
4395DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
4396END;
4397/
4398111.WAP to accept the empno and update the employee row on the following
4399If sal < 2600 then sal=sal+10% of sal make the changes in the emp table
4400DECLARE
4401EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
4402SALV NUMBER(7,2):=0;
4403BEGIN
4404SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
4405IF SALV < 2600 THEN
4406SALV:=SALV+SALV*(10/100);
4407END IF;
4408UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
4409DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
4410DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
4411END;
4412/
4413112.Write the floyd's triangle
44141
44152 3
44164 5 6
44177 8 9 10
441811 12 13 14 15
441916 17 18 19 20 21
4420...............
442179..............91
4422DECLARE
4423N NUMBER:=1;
4424V VARCHAR2(100);
4425BEGIN
4426FOR I IN 1..92
4427LOOP
4428FOR J IN 1..I
4429LOOP
4430V:=V||' '||N;
4431N:=N+1;
4432EXIT WHEN N=92;
4433END LOOP;
4434DBMS_OUTPUT.PUT_LINE(V);
4435EXIT WHEN N=92;
4436V:=NULL;
4437END LOOP;
4438END;
4439/
4440113.WAP to accept the real value and print integer value only
4441DECLARE
4442N NUMBER(7,3):=&N;
4443A NUMBER(5);
4444BEGIN
4445A:=TRUNC(N);
4446DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
4447END;
4448/
4449114.WAP to calculate the sum of n odd factorials
4450DECLARE
4451N NUMBER:=&N;
4452S NUMBER:=0;
4453F NUMBER:=1;
4454BEGIN
4455FOR I IN 1..N
4456LOOP
4457IF MOD(I,2)!=0 THEN
4458FOR J IN 1..I
4459LOOP
4460F:=F*J;
4461END LOOP;
4462S:=S+F;
4463F:=1;
4464END IF;
4465END LOOP;
4466DBMS_OUTPUT.PUT_LINE('SUM '||S);
4467END;
4468/
4469115.WAP to calculate the sum of n even factorials
4470DECLARE
4471N NUMBER:=&N;
4472S NUMBER:=0;
4473F NUMBER:=1;
4474BEGIN
4475FOR I IN 1..N
4476LOOP
4477IF MOD(I,2)=0 THEN
4478FOR J IN 1..I
4479LOOP
4480F:=F*J;
4481END LOOP;
4482S:=S+F;
4483F:=1;
4484END IF;
4485END LOOP;
4486DBMS_OUTPUT.PUT_LINE('SUM '||S);
4487END;
4488/
4489116.WAP to generate the nos which are prime and odd between 1 and 100
4490DECLARE
4491N NUMBER;
4492CNT NUMBER:=0;
4493BEGIN
4494FOR I IN 1..100
4495LOOP
4496FOR J IN 1..I
4497LOOP
4498IF MOD(I,J)=0 THEN
4499CNT:=CNT+1;
4500END IF;
4501END LOOP;
4502IF CNT <= 2 THEN
4503IF MOD(I,2)!=0 THEN
4504DBMS_OUTPUT.PUT_LINE(I);
4505END IF;
4506END IF;
4507CNT:=0;
4508END LOOP;
4509END;
4510/
4511117.Write a program to generate following series
451212
451312 22
451412 22 32
451512 22 32 42
451612 22 32 42 52
4517DECLARE
4518V VARCHAR2(20);
4519BEGIN
4520FOR I IN 1..5
4521LOOP
4522FOR J IN 1..I
4523LOOP
4524V:=V||' '||J||CHR(178);
4525END LOOP;
4526DBMS_OUTPUT.PUT_LINE(V);
4527V:=NULL;
4528END LOOP;
4529END;
4530/
4531118.Find the roots of a quadratic equation
4532DECLARE
4533A NUMBER(4):=&A;
4534B NUMBER(4):=&B;
4535C NUMBER(4):=&C;
4536D NUMBER(8,2);
4537R1 NUMBER(8,2);
4538R2 NUMBER(8,2);
4539BEGIN
4540D:=POWER(B,2)-4*A*C;
4541IF D = 0 THEN
4542DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
4543ELSIF D > 0 THEN
4544R1:=(-B+SQRT(D))/2*A;
4545R2:=(-B-SQRT(D))/2*A;
4546DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
4547DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
4548ELSE
4549DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
4550END IF;
4551END;
4552/
4553119.WAP to accept the 2 diff nos, assume that first one is smaller and second one is highest value then print the all even nos in between them horizontally
4554DECLARE
4555A NUMBER:=&A;
4556B NUMBER:=&B;
4557V VARCHAR2(100);
4558BEGIN
4559FOR I IN A..B
4560LOOP
4561IF MOD(I,2)=0 THEN
4562V:=V||' '||I;
4563END IF;
4564END LOOP;
4565DBMS_OUTPUT.PUT_LINE(V);
4566END;
4567/
4568120.WAP to accept two diff nos assume that first one is smaller and second one is highest value then print the all odd nos in between them horizontally
4569DECLARE
4570A NUMBER:=&A;
4571B NUMBER:=&B;
4572V VARCHAR2(100);
4573BEGIN
4574FOR I IN A..B
4575LOOP
4576IF MOD(I,2)!=0 THEN
4577V:=V||' '||I;
4578END IF;
4579END LOOP;
4580DBMS_OUTPUT.PUT_LINE(V);
4581END;
4582/
4583121. Write a program to accept a year and display the emps belongs to that year?
4584DECLARE
4585Y NUMBER(4):=&YEAR;
4586CURSOR YEAR IS
4587SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=Y;
4588B YEAR%ROWTYPE;
4589BEGIN
4590OPEN YEAR;
4591LOOP
4592FETCH YEAR INTO B;
4593EXIT WHEN YEAR%NOTFOUND;
4594DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4595DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
4596DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
4597DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4598DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4599DBMS_OUTPUT.PUT_LINE('*************************');
4600END LOOP;
4601CLOSE YEAR;
4602END;
4603/
4604122.Write a program to accept a mgr and display who are working under that mgr?
4605DECLARE
4606MGRV NUMBER(4):=&MGR;
4607CURSOR AMGR IS
4608SELECT * FROM EMP WHERE MGR=MGRV;
4609B AMGR%ROWTYPE;
4610BEGIN
4611OPEN AMGR;
4612LOOP
4613FETCH AMGR INTO B;
4614EXIT WHEN AMGR%NOTFOUND;
4615DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4616DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
4617DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
4618DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4619DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4620DBMS_OUTPUT.PUT_LINE('*************************');
4621END LOOP;
4622CLOSE AMGR;
4623END;
4624/
4625123. Write a program to accept the grade and display emps belongs to that grade?
4626DECLARE
4627GRADEV SALGRADE.GRADE%TYPE:=&GRADE;
4628CURSOR A IS
4629SELECT EMP.*,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
4630B A%ROWTYPE;
4631BEGIN
4632OPEN A;
4633LOOP
4634FETCH A INTO B;
4635EXIT WHEN A%NOTFOUND;
4636DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4637DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4638DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
4639DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
4640DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
4641DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4642DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
4643DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4644DBMS_OUTPUT.PUT_LINE('*************************');
4645END LOOP;
4646CLOSE A;
4647END;
4648/
4649124. Write a program to accept a deptno and display who are working in that dept?
4650DECLARE
4651DEPTV EMP.DEPTNO%TYPE:=&DEPTNO;
4652CURSOR A IS
4653SELECT * FROM EMP WHERE DEPTNO=DEPTV;
4654B A%ROWTYPE;
4655BEGIN
4656OPEN A;
4657LOOP
4658FETCH A INTO B;
4659EXIT WHEN A%NOTFOUND;
4660DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4661DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4662DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
4663DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
4664DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
4665DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4666DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
4667DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4668DBMS_OUTPUT.PUT_LINE('*************************');
4669END LOOP;
4670CLOSE A;
4671END;
4672/
4673125. Write a program to display all the information of emp table?
4674DECLARE
4675CURSOR A IS
4676SELECT * FROM EMP;
4677B A%ROWTYPE;
4678BEGIN
4679OPEN A;
4680LOOP
4681FETCH A INTO B;
4682EXIT WHEN A%NOTFOUND;
4683DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4684DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4685DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
4686DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
4687DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
4688DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4689DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
4690DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4691DBMS_OUTPUT.PUT_LINE('*************************');
4692END LOOP;
4693CLOSE A;
4694END;
4695/
4696126. Write a program to accept the location and display empno, name, sal , date of join and also display the total salary, avg salary and no of emps?
4697DECLARE
4698LOCV DEPT.LOC%TYPE:='&LOC';
4699TOT NUMBER(10,2):=0;
4700ASAL NUMBER(10,2):=0;
4701NOEMPS NUMBER(5):=0;
4702CURSOR A IS
4703SELECT EMP.*,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=LOCV;
4704B A%ROWTYPE;
4705BEGIN
4706OPEN A;
4707LOOP
4708FETCH A INTO B;
4709NOEMPS:=NOEMPS+1;
4710TOT:=TOT+B.SAL;
4711ASAL:=TOT/NOEMPS;
4712EXIT WHEN A%NOTFOUND;
4713DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4714DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4715DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
4716DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4717DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
4718DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4719DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
4720DBMS_OUTPUT.PUT_LINE('TOT IS ' || TOT);
4721DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' || NOEMPS);
4722DBMS_OUTPUT.PUT_LINE('ASAL IS ' || ASAL);
4723DBMS_OUTPUT.PUT_LINE('*************************');
4724END LOOP;
4725CLOSE A;
4726END;
4727/
4728127. Write a program to accept a range of salary (that is lower boundary and higher boundary) and print the details of emps along with loc,grade and exp?
4729DECLARE
4730LOSALV SALGRADE.LOSAL%TYPE:=&LOSAL;
4731HISALV SALGRADE.HISAL%TYPE:=&HISAL;
4732EXP NUMBER(5,2);
4733CURSOR A IS
4734SELECT EMP.*,LOC,GRADE FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO=DEPT.DEPTNO
4735AND SAL BETWEEN LOSALV AND HISALV
4736AND SAL BETWEEN LOSAL AND HISAL;
4737B A%ROWTYPE;
4738BEGIN
4739OPEN A;
4740LOOP
4741FETCH A INTO B;
4742EXIT WHEN A%NOTFOUND;
4743EXP:=MONTHS_BETWEEN(SYSDATE,B.HIREDATE)/12;
4744DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4745DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4746DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4747DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
4748DBMS_OUTPUT.PUT_LINE('EXP IS ' || EXP);
4749DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
4750DBMS_OUTPUT.PUT_LINE('*************************');
4751END LOOP;
4752CLOSE A;
4753END;
4754/
4755128. Write a program to print all the details of emps accepting the job?
4756DECLARE
4757JOBV EMP.JOB%TYPE:='&JOB';
4758CURSOR A IS
4759SELECT * FROM EMP WHERE JOB=JOBV;
4760B A%ROWTYPE;
4761BEGIN
4762OPEN A;
4763LOOP
4764FETCH A INTO B;
4765EXIT WHEN A%NOTFOUND;
4766DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
4767DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4768DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
4769DBMS_OUTPUT.PUT_LINE('*************************');
4770END LOOP;
4771CLOSE A;
4772END;
4773/
4774129. Write a program to display the details of emps year wise?
4775DECLARE
4776CURSOR YEARS IS
4777SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') YEARS1 FROM EMP ORDER BY 1;
4778YEAR YEARS%ROWTYPE;
4779CURSOR A IS
4780SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=YEAR.YEARS1;
4781B A%ROWTYPE;
4782BEGIN
4783DBMS_OUTPUT.ENABLE(10000);
4784OPEN YEARS;
4785DBMS_OUTPUT.PUT_LINE('********************');
4786LOOP
4787FETCH YEARS INTO YEAR;
4788EXIT WHEN YEARS%NOTFOUND;
4789DBMS_OUTPUT.PUT_LINE('YEAR :' || YEAR.YEARS1);
4790DBMS_OUTPUT.PUT_LINE('**********************');
4791OPEN A;
4792LOOP
4793FETCH A INTO B;
4794EXIT WHEN A%NOTFOUND;
4795DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
4796DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4797DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
4798DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
4799DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
4800DBMS_OUTPUT.PUT_LINE('*************************');
4801END LOOP;
4802CLOSE A;
4803END LOOP;
4804CLOSE YEARS;
4805END;
4806/
4807130. Write a program to accept empno and print all the details along with loc and grade?
4808DECLARE
4809EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
4810CURSOR A IS
4811SELECT EMP.*,GRADE,LOC FROM EMP,DEPT,SALGRADE
4812WHERE EMP.DEPTNO=DEPT.DEPTNO
4813AND SAL BETWEEN LOSAL AND HISAL AND EMPNO=EMPNOV;
4814B A%ROWTYPE;
4815BEGIN
4816OPEN A;
4817LOOP
4818FETCH A INTO B;
4819EXIT WHEN A%NOTFOUND;
4820DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
4821DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
4822DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
4823DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
4824DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
4825DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
4826DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
4827DBMS_OUTPUT.PUT_LINE('*************************');
4828END LOOP;
4829CLOSE A;
4830END;
4831/
4832131. Write a procedure to create your own print statement?
4833CREATE OR REPLACE PROCEDURE PRINT(V VARCHAR2)
4834IS
4835BEGIN
4836DBMS_OUTPUT.PUT_LINE(V);
4837END;
4838/
4839132. Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?
4840CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
4841IS
4842CURSOR A IS
4843SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
4844B A%ROWTYPE;
4845NOE NUMBER:=0;
4846TOT NUMBER:=0;
4847AVGS NUMBER(7,2):=0;
4848MAXS NUMBER(7,2):=0;
4849BEGIN
4850OPEN A;
4851LOOP
4852FETCH A INTO B;
4853EXIT WHEN A%NOTFOUND;
4854DBMS_OUTPUT.PUT_LINE('EMPNO :'||B.EMPNO);
4855DBMS_OUTPUT.PUT_LINE('ENAME :'||B.ENAME);
4856DBMS_OUTPUT.PUT_LINE('JOB :'||B.JOB);
4857DBMS_OUTPUT.PUT_LINE('SAL :'||B.SAL);
4858DBMS_OUTPUT.PUT_LINE('HIREDATE :'||B.HIREDATE);
4859DBMS_OUTPUT.PUT_LINE('COMM :'||B.COMM);
4860DBMS_OUTPUT.PUT_LINE('**********************');
4861TOT:=TOT+B.SAL;
4862NOE:=NOE+1;
4863IF B.SAL>MAXS THEN
4864MAXS:=B.SAL;
4865END IF;
4866END LOOP;
4867AVGS:=TOT/NOE;
4868DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEE :'||NOE);
4869DBMS_OUTPUT.PUT_LINE('TOTAL SALARY :'||TOT);
4870DBMS_OUTPUT.PUT_LINE('AVG SALARY :'||AVGS);
4871DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||MAXS);
4872CLOSE A;
4873END;
4874/
4875133. Write a procedure to accept two different numbers and print all odd numbers between the two given numbers?
4876CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER,B NUMBER)
4877IS
4878N NUMBER(4);
4879BEGIN
4880N:=A;
4881WHILE N<B
4882LOOP
4883IF MOD(N,2)!=0 THEN
4884DBMS_OUTPUT.PUT_LINE(N);
4885END IF;
4886N:=N+1;
4887END LOOP;
4888END;
4889/
4890134. Write a procedure to accept two different numbers and print even numbers between the two given numbers?
4891CREATE OR REPLACE PROCEDURE EVENNO(A NUMBER,B NUMBER)
4892IS
4893N NUMBER(4);
4894BEGIN
4895N:=A;
4896WHILE N<B
4897LOOP
4898IF MOD(N,2)=0 THEN
4899DBMS_OUTPUT.PUT_LINE(N);
4900END IF;
4901N:=N+1;
4902END LOOP;
4903END;
4904/
4905135. Write a procedure to accept deptno as input and print the details of emps along with grade?
4906CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNOV NUMBER)
4907IS
4908CURSOR A IS
4909SELECT EMP.*,GRADE FROM EMP,SALGRADE
4910WHERE SAL BETWEEN LOSAL AND HISAL
4911AND DEPTNO=DEPTNOV;
4912B A%ROWTYPE;
4913BEGIN
4914OPEN A;
4915LOOP
4916FETCH A INTO B;
4917EXIT WHEN A%NOTFOUND;
4918DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
4919DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
4920DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
4921DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
4922DBMS_OUTPUT.PUT_LINE('DEPTNO IS '||B.DEPTNO);
4923DBMS_OUTPUT.PUT_LINE('GRADE IS '||B.GRADE);
4924END LOOP;
4925CLOSE A;
4926END;
4927/
4928136. Write a procedure to accept a number as parameter and print its multiplication table?
4929CREATE OR REPLACE PROCEDURE MULT(A NUMBER)
4930IS
4931B NUMBER(2) DEFAULT 1;
4932C NUMBER(3);
4933BEGIN
4934WHILE B<=10
4935LOOP
4936C:=A*B;
4937DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
4938B:=B+1;
4939END LOOP;
4940END;
4941/
4942137. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different horizontal lines?
4943CREATE OR REPLACE PROCEDURE EVENODD(A NUMBER,B NUMBER)
4944IS
4945N NUMBER;
4946EV VARCHAR2(1000);
4947OD VARCHAR2(1000);
4948BEGIN
4949N:=A;
4950WHILE N<B
4951LOOP
4952IF MOD(N,2)!=0 THEN
4953OD:=OD||' '||N;
4954ELSE
4955EV:=EV||' '||N;
4956END IF;
4957N:=N+1;
4958END LOOP;
4959DBMS_OUTPUT.PUT_LINE('THE ODD NOS ARE '||OD);
4960DBMS_OUTPUT.PUT_LINE('THE EVEN NOS ARE '||EV);
4961END;
4962/
4963138. Write a procedure to accept a string and check whether it is palindrome or not?
4964CREATE OR REPLACE PROCEDURE STRPAL(STR VARCHAR2)
4965IS
4966STR1 VARCHAR2(10);
4967S VARCHAR2(10);
4968BEGIN
4969FOR I IN REVERSE 1..LENGTH(STR)
4970LOOP
4971S:=SUBSTR(STR,I,1);
4972STR1:=STR1||S;
4973END LOOP;
4974IF STR1=STR THEN
4975DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME '||STR1);
4976ELSE
4977DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME '||STR1);
4978END IF;
4979END;
4980/
4981139. Write a procedure to accept a string and print it in reverse order?
4982CREATE OR REPLACE PROCEDURE STRREV(STR VARCHAR2)
4983IS
4984STR1 VARCHAR2(10);
4985S VARCHAR2(10);
4986BEGIN
4987FOR I IN REVERSE 1..LENGTH(STR)
4988LOOP
4989S:=SUBSTR(STR,I,1);
4990STR1:=STR1||S;
4991END LOOP;
4992DBMS_OUTPUT.PUT_LINE('ORIGINAL '||STR);
4993DBMS_OUTPUT.PUT_LINE('REVERSE '||STR1);
4994END;
4995/
4996140. Write a procedure to accept the empno and print all the details of emp along with exp, grade and loc?
4997CREATE OR REPLACE PROCEDURE EMP_DET(EMPNOV NUMBER)
4998IS
4999EXP NUMBER(6,2);
5000E EMP%ROWTYPE;
5001GRADEV SALGRADE.GRADE%TYPE;
5002LOCV DEPT.LOC%TYPE;
5003BEGIN
5004SELECT EMP.* INTO E FROM EMP WHERE EMPNO=EMPNOV;
5005SELECT LOC INTO LOCV FROM DEPT WHERE DEPT.DEPTNO=E.DEPTNO;
5006SELECT GRADE INTO GRADEV FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
5007EXP:=MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12;
5008DBMS_OUTPUT.PUT_LINE('EMPNO IS '||E.EMPNO);
5009DBMS_OUTPUT.PUT_LINE('ENAME IS '||E.ENAME);
5010DBMS_OUTPUT.PUT_LINE('SAL IS '||E.SAL);
5011DBMS_OUTPUT.PUT_LINE('JOB IS '||E.JOB);
5012DBMS_OUTPUT.PUT_LINE('LOC IS '||LOCV);
5013DBMS_OUTPUT.PUT_LINE('GRADE IS '||GRADEV);
5014DBMS_OUTPUT.PUT_LINE('EXP IS '||EXP);
5015END;
5016/
5017141. Write a procedure to accept dname irrespective of case and print all the details of emps?
5018CREATE OR REPLACE PROCEDURE DETAILS(DNAMEV VARCHAR2)
5019IS
5020CURSOR A IS
5021SELECT EMP.*,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME=DNAMEV;
5022B A%ROWTYPE;
5023BEGIN
5024OPEN A;
5025LOOP
5026FETCH A INTO B;
5027EXIT WHEN A%NOTFOUND;
5028DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
5029DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
5030DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
5031DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
5032DBMS_OUTPUT.PUT_LINE('DNAME IS '||B.DNAME);
5033DBMS_OUTPUT.PUT_LINE('HIREDATE IS '||B.HIREDATE);
5034END LOOP;
5035END;
5036/
5037142. Write a procedure to accept a string and print it in reverse case?
5038CREATE OR REPLACE PROCEDURE S_R_CASE(STR VARCHAR2)
5039IS
5040S VARCHAR2(10);
5041V VARCHAR2(10);
5042N NUMBER(3);
5043BEGIN
5044FOR I IN 1..LENGTH(STR)
5045LOOP
5046S:=SUBSTR(STR,I,1);
5047N:=ASCII(S);
5048IF N BETWEEN 65 AND 90 THEN
5049V:=V||CHR(N+32);
5050ELSE
5051V:=V||CHR(N-32);
5052END IF;
5053END LOOP;
5054DBMS_OUTPUT.PUT_LINE('STRING IN REVERSE CASE IS '||V);
5055END;
5056/
5057143. Write a function to accept the empno and return exp with minimum 3 decimal?
5058CREATE OR REPLACE FUNCTION E_DETAILS(EMPNOV NUMBER) RETURN NUMBER
5059IS
5060HIREDATEV EMP.HIREDATE%TYPE;
5061EXP NUMBER(6,3);
5062BEGIN
5063SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
5064EXP:=MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12;
5065RETURN EXP;
5066END;
5067/
5068144. Write a function to accept a number and print the factorial of that number?
5069CREATE OR REPLACE FUNCTION FAC(NUM NUMBER) RETURN NUMBER
5070IS
5071FACT NUMBER(4):=1;
5072BEGIN
5073FOR I IN REVERSE 1..NUM
5074LOOP
5075FACT:=FACT*I;
5076END LOOP;
5077RETURN FACT;
5078END;
5079/
5080145. Write a function to accept a grade and return the number of emps belongs to that grade?
5081CREATE OR REPLACE FUNCTION EMPGRADE(GRADEV NUMBER) RETURN VARCHAR2
5082IS
5083N NUMBER(4);
5084BEGIN
5085SELECT COUNT(*) INTO N FROM EMP,SALGRADE
5086WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
5087RETURN 'NO OF EMPS ARE'||N;
5088END;
5089/
5090146. Write a program to accept the mgr number and return no of emp working at that mgr?
5091CREATE OR REPLACE FUNCTION N_EMPS(MGRV NUMBER) RETURN VARCHAR2
5092IS
5093N NUMBER(4);
5094BEGIN
5095SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
5096RETURN 'THE NO OF EMPS ARE WORKING UNDER THIS MGR IS '||N;
5097END;
5098/
5099147. Write a function to accept a character string and print it in reverse case?
5100CREATE OR REPLACE FUNCTION REVERSE(STR VARCHAR2) RETURN VARCHAR2
5101IS
5102STR1 VARCHAR2(20);
5103S VARCHAR2(20);
5104N NUMBER(4);
5105BEGIN
5106FOR I IN 1..LENGTH(STR)
5107LOOP
5108S:=SUBSTR(STR,I,1);
5109N:=ASCII(S);
5110IF N BETWEEN 65 AND 90 THEN
5111STR1:=STR1||CHR(N+32);
5112ELSE
5113STR1:=STR1||CHR(N-32);
5114END IF;
5115END LOOP;
5116RETURN 'THE REVERSE CASE IS '||STR1;
5117END;
5118/
5119148. Write a function to accept a string and check whether it is palindrome or not?
5120CREATE OR REPLACE FUNCTION STRPAL1(STR VARCHAR2) RETURN VARCHAR2
5121IS
5122STR1 VARCHAR2(10);
5123S VARCHAR2(10);
5124BEGIN
5125FOR I IN REVERSE 1..LENGTH(STR)
5126LOOP
5127S:=SUBSTR(STR,I,1);
5128STR1:=STR1||S;
5129END LOOP;
5130IF STR1=STR THEN
5131RETURN 'IT IS PALINDROME '||STR1;
5132ELSE
5133RETURN 'IT IS NOT PALINDROME '||STR1;
5134END IF;
5135END;
5136/
5137149. Write a function to accept the grade and return max, tot, avg salary and number of emps belongs to that grade as script without using any group functions?
5138CREATE OR REPLACE FUNCTION EMP_DETAILS_SCRIPT (GRADEV SALGRADE.GRADE%TYPE) RETURN VARCHAR2
5139IS
5140V VARCHAR2(30000);
5141CURSOR EMP_CUR IS
5142SELECT EMP.*,GRADE,DNAME FROM DEPT,EMP,SALGRADE
5143WHERE GRADE=GRADEV AND EMP.DEPTNO=DEPT.DEPTNO AND
5144SAL BETWEEN LOSAL AND HISAL;
5145EMP_CUR_V EMP_CUR%ROWTYPE;
5146MAXSAL EMP.SAL%TYPE:=0;
5147MINSAL EMP.SAL%TYPE;
5148AVGSAL NUMBER(6,2);
5149SUMSAL NUMBER(10,2):=0;
5150CNT NUMBER:=0;
5151FLAG CHAR:=0;
5152EX EXCEPTION;
5153BEGIN
5154OPEN EMP_CUR;
5155LOOP
5156FETCH EMP_CUR INTO EMP_CUR_V;
5157EXIT WHEN EMP_CUR%NOTFOUND;
5158IF MAXSAL < EMP_CUR_V.SAL THEN
5159MAXSAL:=EMP_CUR_V.SAL;
5160END IF;
5161IF FLAG=0 THEN
5162MINSAL:=EMP_CUR_V.SAL;
5163FLAG:=1;
5164ELSIF FLAG=1 AND MINSAL > EMP_CUR_V.SAL THEN
5165MINSAL:=EMP_CUR_V.SAL;
5166END IF;
5167SUMSAL:=SUMSAL+EMP_CUR_V.SAL;
5168CNT:=CNT+1;
5169ENDLOOP;
5170IF CNT=0 THEN
5171RAISE EX;
5172END IF;
5173AVGSAL:=SUMSAL/CNT;
5174V:='THE MAXIMUM SALARY OF GRADE' ||GRADEV||' IS'||MAXSAL||' MINIMUM SALARY IS'||MINSAL||
5175'AVERAGE SALARY IS'||AVGSAL||' TOTAL EMPS WORKING FOR THIS GRADE ARE'||CNT;
5176CLOSE EMP_CUR;
5177RETURN V;
5178EXCEPTION
5179WHEN EX THEN
5180RETURN 'THERE IS NO EMPLOYEE WORKING FOR THIS GRADE, CHECK AND RE-ENTER THE GRADE....';
5181END;
5182/
5183150. Create a package to store the following procedure for multiplication table,even-odd, function for factorial and function for palindrome?
5184CREATE OR REPLACE PACKAGE DATA
5185IS
5186PROCEDURE MULT(A NUMBER);
5187PROCEDURE EVEN_ODD(N NUMBER);
5188FUNCTION FACT(N NUMBER) RETURN NUMBER;
5189PRAGMA RESTRICT_REFERENCES(FACT,WNDS);
5190FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2;
5191PRAGMA RESTRICT_REFERENCES(PALEN,WNDS);
5192END;
5193/
5194CREATE OR REPLACE PACKAGE BODY DATA
5195IS
5196PROCEDURE MULT(A NUMBER)
5197IS
5198M NUMBER;
5199BEGIN
5200FOR I IN 1..10
5201LOOP
5202M:=A*I;
5203DBMS_OUTPUT.PUT_LINE(A||'*'||I||'='||M);
5204END LOOP;
5205END;
5206PROCEDURE EVEN_ODD(N NUMBER)
5207IS
5208BEGIN
5209IF MOD(N,2)=0 THEN
5210DBMS_OUTPUT.PUT_LINE(N||' IS EVEN NUMBER');
5211ELSE
5212DBMS_OUTPUT.PUT_LINE(N||' IS NOT EVEN NUMBER');
5213END IF;
5214END;
5215FUNCTION FACT(N NUMBER) RETURN NUMBER
5216IS
5217F NUMBER:=1;
5218BEGIN
5219FOR I IN 1..N
5220LOOP
5221F:=F*I;
5222END LOOP;
5223RETURN F;
5224END;
5225FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2
5226IS
5227S CHAR;
5228V VARCHAR2(50);
5229BEGIN
5230FOR I IN REVERSE 1..LENGTH(SRT)
5231LOOP
5232S:=SUBSTR(SRT,I,1);
5233V:=V||S;
5234END LOOP;
5235IF V=SRT THEN
5236RETURN 'PALINDROME';
5237ELSE
5238RETURN 'NOT PALINDROME';
5239END IF;
5240END;
5241END;
5242/
5243151.Write a database trigger halt the transaction on Sunday on EMP table
5244CREATE OR REPLACE TRIGGER SUN_TRI
5245AFTER INSERT OR UPDATE OR DELETE ON EMP
5246DECLARE
5247DY VARCHAR2(200);
5248BEGIN
5249DY:=TO_CHAR(SYSDATE,'DY');
5250IF DY='SUN' THEN
5251RAISE_APPLICATION_ERROR(-20005,'TODAY IS SUNDAY TRANSACTION NOT ALLOWED TODAY');
5252END IF;
5253END;
5254/
5255152.Write a database trigger halt the transaction of USER SCOTT on table EMP
5256CREATE OR REPLACE TRIGGER SCOTT_TRI
5257BEFORE INSERT OR UPDATE OR DELETE ON EMP
5258BEGIN
5259IF USER = 'SCOTT' THEN
5260RAISE_APPLICATION_ERROR(-20006,'TRANSACTION NOT ALLOWED FOR SCOTT');
5261END IF;
5262END;
5263/
5264153.Write a database trigger halt the transaction between the the time 6pm to 10am on table emp
5265CREATE OR REPLACE TRIGGER OVER_TIME_TRI
5266BEFORE INSERT OR DELETE OR UPDATE ON EMP
5267DECLARE
5268T NUMBER;
5269BEGIN
5270T:=TO_CHAR(SYSDATE,'HH24');
5271IF T NOT BETWEEN 10 AND 18 THEN
5272RAISE_APPLICATION_ERROR(-20007,'TIME ALREADY OVER.....TRANSACTION NOT ALLOWED NOW');
5273END IF;
5274END;
5275154.Write a database trigger to halt the transaction for the employee SALESMAN and
5276PRESIDENT
5277CREATE OR REPLACE TRIGGER SALES_PRI
5278BEFORE INSERT OR UPDATE OR DELETE ON EMP
5279FOR EACH ROW
5280WHEN (OLD.JOB IN ('SALESMAN','PRESIDENT') OR
5281NEW.JOB IN ('SALESMAN','PRESIDENT'))
5282BEGIN
5283RAISE_APPLICATION_ERROR(-20008,'TRANSACTION NOT ALLOWED FOR SALESMAN AND PRESIDENT....');
5284END;
5285/
5286155.Write a database trigger stroe the username ,type of transaction ,date of transaction and time of transaction of table emp into the table EMP_LOG
5287CREATE OR REPLACE TRIGGER TRANS_TYPE
5288AFTER INSERT OR UPDATE OR DELETE ON EMP
5289DECLARE
5290V VARCHAR2(50);
5291BEGIN
5292IF INSERTING THEN
5293V:='I';
5294ELSIF UPDATING THEN
5295V:='U';
5296ELSE
5297V:='D';
5298END IF;
5299INSERT INTO EMP_LOG VALUES (USER,V,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
5300END;
5301/
5302156.Write a database trigger store the deleted data of EMP table in EMPDEL table
5303CREATE OR REPLACE TRIGGER DEL_TRI
5304BEFORE DELETE ON EMP
5305FOR EACH ROW
5306BEGIN
5307INSERT INTO EMPDEL
5308VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,
5309:OLD.DEPTNO,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS));
5310END;
5311/
5312157.Write a database trigger display the message when the inserting hiredate is greater than system date
5313CREATE OR REPLACE TRIGGER HIREDATE_OVER
5314AFTER INSERT ON EMP
5315FOR EACH ROW
5316BEGIN
5317IF :NEW.HIREDATE > SYSDATE THEN
5318RAISE_APPLICATION_ERROR(-20009,'INVALID HIREDATE.....');
5319END IF;
5320END;
5321/
5322158.Write a database trigger halt the transaction of EMP table if the deptno is does not exist in the dept table
5323CREATE OR REPLACE TRIGGER DEPT_NO
5324BEFORE INSERT OR UPDATE OR DELETE ON EMP
5325FOR EACH ROW
5326DECLARE
5327DNO NUMBER:=0;
5328BEGIN
5329SELECT COUNT(*) INTO DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;
5330DBMS_OUTPUT.PUT_LINE(DNO);
5331IF DNO=0 THEN
5332RAISE_APPLICATION_ERROR(-20009,'DEPTNO NOT EXIST IN DEPT TABLE....');
5333END IF;
5334END;
5335/
5336159.Write a database trigger add Rs 500 if the inserting salary is less than Rs 1000
5337CREATE OR REPLACE TRIGGER SAL_ADD
5338BEFORE INSERT ON EMP
5339FOR EACH ROW
5340BEGIN
5341IF :NEW.SAL <= 1000 THEN
5342:NEW.SAL:=:NEW.SAL+500;
5343END IF;
5344END;
5345/
5346160.Write a database trigger give the appropriate message if the record exceed more than 100 on EMP table
5347CREATE OR REPLACE TRIGGER EMP_OVER_REC
5348AFTER INSERT ON EMP
5349DECLARE
5350R NUMBER;
5351BEGIN
5352SELECT COUNT(*) INTO R FROM EMP;
5353IF R>=100 THEN
5354RAISE_APPLICATION_ERROR(-20009,'100 RECORD ALLOWED IN EMP TABLE.....');
5355END IF;
5356END;
5357/
5358161.Write a program to month and year and display the Calendar of that month.
5359DECLARE
5360D NUMBER:=1;
5361M VARCHAR2(10):='&MONTH';
5362Y NUMBER:=&YEAR;
5363C CHAR(20);
5364V VARCHAR2(500);
5365N NUMBER;
5366BEGIN
5367N:=TO_CHAR(LAST_DAY(D||'-'||M||'-'||Y),'DD');
5368C:= TO_CHAR(TO_DATE(D||'-'||M||'-'||Y),'DY');
5369dbms_output.put_line('*********************************');
5370dbms_output.put_line('* '||M||'-'||Y||' *');
5371dbms_output.put_line('*SUN MON TUE WED THU FRI SAT *');
5372dbms_output.put_line('**********************************');
5373IF C='MON' THEN
5374V:=' ';
5375ELSIF C='TUE' THEN
5376V:=' ';
5377ELSIF C='WED' THEN
5378V:=' ';
5379ELSIF C='THU' THEN
5380V:=' ';
5381ELSIF C='FRI' THEN
5382V:=' ';
5383ELSIF C='SAT' THEN
5384V:=' ';
5385END IF;
5386FOR I IN 1..N
5387LOOP
5388V:=V||LPAD(I,4);
5389IF LENGTH(V)=28 THEN
5390dbms_output.put_line(LPAD(V,29,'*')||' *');
5391V:=NULL;
5392END IF;
5393END LOOP;
5394dbms_output.put_line('*'||RPAD(V,29)||'*');
5395END;
5396/
5397-------------------------------------
5398
5399
5400PRIME NUMBER
5401
5402Aim: To write a pl/sql program to check weather given number is Prime or not.
5403
5404Procedure:
5405
5406  declare
5407       num number;
5408       i number:=1;
5409       c number:=0;
5410  begin
5411        num:=&num;
5412       for i in 1..num
5413       loop
5414          if((mod(num,i))=0)
5415           then
5416              c:=c+1;
5417         end if;
5418      end loop;
5419     if(c>2)
5420     then
5421         dbms_output.put_line(num||' not a prime');
5422     else
5423        dbms_output.put_line(num||' is prime');
5424     end if;
5425  end;
5426   /
5427
5428Execution:
5429
5430SQL> @e:\plsql\prime.sql
5431Enter value for num: 5
5432old   6:  num:=&num;
5433new   6:  num:=5;
54345 is prime
5435
5436
5437Conclusion:
5438       A pl/sql program is successfully executed to check the given number is prime or not.
5439
5440
5441
5442FACTORIAL OF A NUMBER.
5443
5444Aim: To write a pl/sql program to finding factorial of given number.
5445
5446Procedure:
5447
5448 declare
5449       i number(4):=1;
5450       n number(4):=&n;
5451       f number(4):=1;
5452 begin
5453    for i in 1..n
5454    loop
5455       f:=f*i;
5456     end loop;
5457    Dbms_output.put_line('the factorial of '||n||' is:'||f);
5458    end;
5459   /
5460
5461Output:
5462
5463SQL> @e:\plsql\fact.sql
5464Enter value for n: 5
5465old   3:   n number(4):=&n;
5466new   3:   n number(4):=5;
5467the factorial of 5 is:120
5468
5469PL/SQL procedure successfully completed.
5470
5471
5472Conclusion:
5473       a pl/sql program is successfully executed for finding factorial of a given number.
5474Reverse Of A Number
5475
5476Aim: To write a pl/sql program to generate reverse for given number.
5477
5478Procedure:
5479
5480declare
5481     n number(4):=&n;
5482     s number(4):=0;
5483     r number(4);
5484 begin
5485     while n>0
5486     loop
5487      r:=mod(n,10);
5488      s:=(s*10)+r;
5489      n:=trunc(n/10);
5490     end loop;
5491   dbms_output.put_line(‘the reverse number is:’);   
5492   dbms_output.put_line(s);
5493end;
5494/
5495
5496Execution:
5497
5498SQL> @e:\plsql\rev.sql
5499Enter value for n: 457
5500old   2:   n number(4):=&n;
5501new   2:   n number(4):=457;
5502
5503the reverse number is:
5504754
5505
5506PL/SQL procedure successfully completed.
5507
5508
5509Conclusion:
5510       a pl/sql program is successfully executed to generate reverse number for given number.
5511
5512
5513
5514
5515
5516
5517FIBINOCCI SERIES
5518
5519Aim: To write a pl/sql program to generate fibinocci series.
5520
5521Procedure:
5522   declare
5523      a number(3):=1;
5524      b number(3):=1;
5525      c number(3);
5526      n number(3):=&n;
5527begin
5528  Dbms_output.put_line('the fibinocci series is:'); 
5529  while a<=n
5530    loop
5531        dbms_output.put_line(a);
5532        c:=a+b;
5533        a:=b;
5534        b:=c;
5535     end loop;
5536  end;
5537 /
5538
5539Execution:
5540SQL> @e:\plsql\fibi.sql
5541Enter value for n: 13
5542old   5:   n number(3):=&n;
5543new   5:   n number(3):=13;
5544the fibinocci series is:
55451
55461
55472
55483
55495
55508
555113
5552PL/SQL procedure successfully completed.
5553
5554Conclusion:
5555       a pl/sql program is successfully executed for to generate fibinocci series.
5556
5557
5558EVEN OR ODD
5559
5560Aim: To write a pl/sql program to find given number is even or odd.
5561
5562Procedure:
5563
5564Declare
5565     n number(4):=&n;
5566 Begin
5567     if mod(n,2)=0
5568    then
5569       dbms_output.put_line(n||' even number');
5570    else
5571      dbms_output.put_line(n||' odd number');
5572   end if;
5573end;
5574/
5575
5576Execution:
5577
5578SQL> @e:\plsql\even.sql
5579Enter value for n: 5
5580old   2:  n number(4):=&n;
5581new   2:  n number(4):=5;
5582
55835 odd number
5584
5585PL/SQL procedure successfully completed.
5586
5587Conclusion:
5588       a pl/sql program is successfully executed for finding even or odd.
5589
5590
5591
5592
5593
5594
5595
5596
5597LETTER BY LETTER
5598
5599Aim: To write a pl/sql program to print a string in a letter by letter format.
5600
5601Procedure:
5602
5603  declare
5604      str varchar(10):='&str';
5605      len number(2);
5606      i number(3):=1;
5607  begin
5608      len:=length(str);
5609      while i<=len
5610      loop
5611          Dbms_output.put_line(substr(str,i,1));
5612           i :=i+1;
5613     end loop;
5614   end;
5615  /
5616
5617Output:
5618
5619SQL> @e:\plsql\9a.sql
5620Enter value for str: svcet
5621old   2:   str varchar(10):='&str';
5622new   2:   str varchar(10):='svcet';
5623s
5624v
5625c
5626e
5627t
5628
5629PL/SQL procedure successfully completed.
5630
5631Conclusion:
5632       a pl/sql program is successfully executed for printing a string letter by letter.
5633
5634
5635
5636
5637
5638
5639SPACE AFTER EACH LETTER
5640
5641Aim: To write a pl/sql program to insert a space after each  letter in a  given string.
5642
5643 Procedure:
5644
5645declare
5646     str varchar(10):='&str';
5647     len number(2);
5648      i number(3):=1;
5649      newstr varchar(20);
5650 begin
5651    len:=length(str);
5652     while i<=len
5653     loop
5654        newstr:=newstr||substr(str,i,1)||' ';
5655         i:=i+1;
5656     end loop;
5657      dbms_output.put_line(newstr);
5658    end;
5659     /
5660
5661Execution:
5662
5663SQL> @e:\plsql\9b.sql
5664Enter value for str: svcet
5665old   2:   str varchar(10):='&str';
5666new   2:   str varchar(10):='svcet';
5667
5668s v c e t
5669
5670PL/SQL procedure successfully completed.
5671
5672
5673Conclusion:
5674a pl/sql program is successfully executed for to insert a space after each letter.
5675INSERTING A ROW USING PL/SQL
5676
5677Aim: To write a pl/sql program for inserting a row into vender table .
5678
5679Procedure:
5680      1.Creating vender table.
5681
5682  SQL> create table vendor(
5683   2  v_code number(5) primary key,
5684   3  v_name varchar(10),
5685   4  v_contact varchar(10),
5686   5  v_area varchar(10),
5687   6  v_phone number(10));
5688
5689  Table created.
5690    
5691   2. Inserting row using pl/sql
5692
5693SQL> declare
5694      begin
5695          insert into vendor values(1012,'bajaj','delhi',121,9949481807);
5696      end;
5697     /
5698
5699           PL/SQL procedure successfully completed.
5700
57013. Displaying the row
5702SQL> select *From vendor;
5703
5704    V_CODE    V_NAME     V_CONTACT     V_AREA      V_PHONE
5705--------- ----   -------------   -------------- ---   ------------    --------------
5706         1012            bajaj              delhi               121     9949481807
5707
5708
5709Conclusion:
5710       a pl/sql program is successfully executed for inserting a row into vender table.
5711
5712
5713
5714
5715
5716
5717
5718COUNTING NO OF PRODUCTS
5719
5720Aim:  To write a pl/sql program to print number of products whose
5721    Price between 0 to 50, 50 to 100,100 to 150,150 to 200,
5722    200 to 250.
5723
5724 Procedure:
5725   1.creating product table:
5726
5727SQL> create table product(
5728     2  p_code number(4),
5729     3  p_name varchar(10),
5730     4  p_date date,
5731     5  P_price number(4));
5732
5733    Table created.
5734  
5735  2.Inserting rows into product table:
5736
5737SQL> insert into product values(101,'boost','12-nov-11',190);
57381 row created.
5739SQL> insert into product values(102,'horlicks','12-nov-11',149);
57401 row created.
5741SQL> insert into product values(103,'boornvita','12-nov-11',220);
57421 row created.
5743SQL> insert into product values(104,'complane','12-nov-11',199);
57441 row created.
5745SQL> insert into product values(105,'lux','13-aug-11',18);
57461 row created.
5747SQL> insert into product values(106,'dove','13-aug-11',26);
57481 row created.
5749SQL> insert into product values(107,'axe','13-aug-11',299);
57501 row created.
5751SQL> insert into product values(108,'playboy','18-nov-11',250);
57521 row created.
5753SQL> insert into product values(109,'nike','18-nov-11',240);
57541 row created.
5755SQL> insert into product values(110,'bite','18-nov-11',90);
57561 row created.
5757
5758
5759
57603.Source code:
5761
5762    declare
5763        c number(3):=3;
5764        low number(3):=1;
5765        hi number(3):=50;
5766        i number(3);
5767     begin
5768         while hi<=300
5769         loop
5770             select count(p_price) into c
5771             from product
5772             where p_price between low and hi;
5773             dbms_output.put_line('the no of productos between '||low||'and'||hi||'is'||c) ;
5774             low:=hi;
5775             hi:=hi+50;
5776         end loop;
5777     end;
5778      /
5779
5780 Execution:
5781
5782SQL> @e:\plsql\noitems.sql
5783the no of productos between 1and50is2
5784the no of productos between 50and100is1
5785the no of productos between 100and150is1
5786the no of productos between 150and200is2
5787the no of productos between 200and250is3
5788the no of productos between 250and300is2
5789
5790PL/SQL procedure successfully completed.
5791
5792Conclusion:
5793a pl/sql program is successfully executed for counting no of
5794           products in specified range.
5795
5796
5797
5798USING   %TYPE
5799
5800Aim:  To write a pl/sql program to display the employee details using
5801    %type data type.
5802
5803Procedure:
5804        1.creating emp table:
5805
5806SQL> create table emp(
5807  2  eno number(4) primary key,
5808  3  ename varchar(8),
5809  4  job varchar(8));
5810               Table created.
5811        2.Inserting values into emp table:
5812
5813SQL> insert into emp values(101,'kalam','clerk');
58141 row created.
5815SQL> insert into emp values(100,'john','sales');
58161 row created.
5817SQL> insert into emp values(102,'priya','clerk');
58181 row created.
5819
5820         3. Execution:
5821               SQL> declare
5822       2  Eno emp.eno%type;
5823       3  Ename emp.ename%type;
5824       4  begin
5825       5  select eno,ename into Eno,Ename
5826       6  from emp
5827       7  where eno=100;
5828       8  dbms_output.put_line('eno:'||Eno);
5829       9  dbms_output.put_line('ename:'||Ename);
5830     10  end;
5831     11  /
5832                     eno:100
5833     ename:john
5834                  PL/SQL procedure successfully completed.
5835
5836Conclusion:
5837     a pl/sql program is successfully executed to display  the employee
5838     details using %type data type.
5839
5840USING  %ROWTYPE
5841
5842Aim: To write a pl/sql program  to display the employee details
5843    using %rowtype data type.
5844
5845Procedure:
5846     
5847Execution:
5848
5849SQL> declare
5850  2  E emp%rowtype;
5851  3  begin
5852  4  select * into E
5853  5  from emp
5854  6  where eno=100;
5855  7  dbms_output.put_line('eno:'||E.eno);
5856  8  dbms_output.put_line('ename:'||E.ename);
5857  9  end;
5858 10  /
5859
5860eno:100
5861ename:john
5862
5863            PL/SQL procedure successfully completed.
5864
5865Conclusion:
5866     a pl/sql program is successfully executed to display
5867     the employee details using %type data type.
5868
5869
5870
5871
5872
5873
5874
5875
5876
5877
5878
5879
5880
5881CALCULATE STUDENT GRADE
5882
5883Aim: To Write a pl/sql program to calculate the student grade using case statement.
5884
5885
5886Procedure:
5887   
5888     Display ing student details:
5889        SQL>select *From student where sno=101;
5890          Sno   sname      group  m1  m2  m3   grade
5891         -----   ----------  ---------   ----   ----  ----    -------
5892        100   wasim    mpc        75    76   88         B
5893  
5894Source code:
5895
5896     declare
5897          Grd student.grade%type;
5898     begin
5899        select grade into Grd from student
5900        where sno=100;
5901        dbms_output.put_line('Grade:');
5902       case Grd
5903           when 'A' then Dbms_output.Put_line('Very Good');
5904           when 'B' then Dbms_output.Put_line('Good');
5905           when 'C' then Dbms_output.Put_line('Avrage');
5906          else
5907           Dbms_output.Put_line('Fail');
5908        end case;
5909      end;
5910   /
5911
5912  Output:      
5913
5914     SQL> @e:\plsql\case.sql
5915     Grade:
5916     Good
5917
5918Conclusion: a pl/sql program is successfully executed to calculate the student grade
5919                       using case statement.
5920                                         
5921
5922
5923
5924DISPLAYING EMPLOYEE DETAILS USING CURSORS.
5925Aim: To write a pl/sql program to displaying employee details using cursors.
5926Source code:
5927
5928   declare
5929      no emp.eno%type;
5930      name emp.ename%type;
5931      cursor emp_cur is select eno,ename from emp;
5932    begin
5933       open emp_cur;          
5934       loop
5935         fetch emp_cur into no,name;
5936         exit when emp_cur%notfound;     
5937         DBMS_output.put_line('Employee No:'||no||'Employee Name:'||name);
5938       end loop;
5939      close emp_cur;
5940    end;
5941    /
5942Execution:
5943
5944SQL> @e:\plsql\c1r.sql;
5945Employee No:101 Employee Name:kalam
5946Employee No:100 Employee Name:john
5947Employee No:102 Employee Name:priya
5948Employee No:103 Employee Name:raja
5949Employee No:104 Employee Name:giri
5950Employee No:105 Employee Name:kohili
5951Employee No:106 Employee Name:latha
5952Employee No:107 Employee Name:hari
5953
5954PL/SQL procedure successfully completed.
5955
5956Conclusion:
5957       a pl/sql program is successfully executed for displaying employee details using cursors.
5958
5959
5960TOP 10 EMPLOYEES
5961Aim: To Write a pl/sql program to to displaying top 10 employee details based  on salary using cursors
5962Source code:
5963   declare
5964        i number(2);
5965        E emp%rowtype;
5966        cursor ec is select * from emp  order by salary desc;
5967    begin
5968         Dbms_output.put_line('Eno Ename   Job  Salary');
5969         Dbms_output.put_line('----------------------');
5970        i:=1;
5971       open ec;
5972       loop 
5973         fetch ec into E;
5974            i:=i+1;
5975           Dbms_output.put_line(E.eno||' '||rpad(E.ename,8,' ')||' '|| rpad(E.job,5,' ')||'  '||E.salary);
5976      exit when i>10 or ec%notfound;
5977     end loop;
5978   close ec;
5979  end;
5980   /
5981Execution:
5982SQL> @e:\plsql\c2.sql;
5983Eno      Ename     Job     Salary
5984------------------------------------
5985104     giri            gm      14000
5986106     latha        sales    12800
5987111     kiran         agm    12800
5988112     swathi      clerk   12600
5989113     wasim      agm     12400
5990107     hari          sales      6000
5991105     kohili       clerk       6000
5992108     lakshmi    clerk      5600
5993100     john          sales      4880
5994101     kalam       clerk       4800
5995PL/SQL procedure successfully completed.
5996
5997Conclusion :   a pl/sql program is successfully executed for program to displaying
5998                           top 10   employees.
5999
6000
6001STUDENT MARK LIST
6002Aim: To Write a pl/sql program to print mark list using cursors.
6003Procedure:
6004Creating table:    
6005SQL>create table Student(
6006             sno  number(4),
6007              sname varchar(10),
6008              m1  numbar(3),
6009              m2 numbar(3),
6010              m3 numbar(3));
6011             table created.
6012SQL>insert into student values (500,’wasim’,70,75,89);
60131 row inserted.
6014SQL>insert into student values (500,’siva’,75,69,88);
60151 row inserted.
6016SQL>insert into student values (500,’vani’,84,75,75);
60171 row inserted.
6018SQL>insert into student values (500,’naga’,67,50,33);
60191 row inserted.
6020Source code:
6021declare
6022    stu student%rowtype;
6023    total number(4);
6024    result varchar(4);
6025    cursor c is select * From student;
6026begin
6027  for stu in c
6028    loop
6029         Dbms_output.put_line('STUDENT MARKLIST');
6030         Dbms_output.put_line('----------------------------');
6031         Dbms_output.put_line('sno:'||stu.sno||' sname:'||stu.sname);
6032         total:=stu.m1+stu.m2+stu.m3;
6033         if stu.M1>35 and stu.M2>35 and stu.M3>35
6034         then
6035             result:='pass';
6036         else
6037             result:='fail';
6038         end if;
6039          Dbms_output.put_line('m1:'||stu.M1||' m2:'||stu.M2||' m3:'||stu.M3);
6040          Dbms_output.put_line('total:'||total||' result:'||result);
6041       end loop;
6042end;
6043/
6044 
6045
6046
6047
6048
6049
6050
6051
6052
6053Execution:     
6054SQL> @e:\plsql\c3.sql
6055
6056STUDENT MARKLIST
6057---------------------------
6058sno:500  sname:wasim
6059m1:70 m2:75 m3:89
6060total:234 result:pass
6061
6062STUDENT MARKLIST
6063--------------------------
6064sno:501 sname:siva
6065m1:75 m2:69 m3:88
6066total:232 result:pass
6067
6068STUDENT MARKLIST
6069--------------------------
6070sno:502 sname:vani
6071m1:84 m2:75 m3:75
6072total:234 result:pass
6073
6074STUDENT MARKLIST
6075---------------------------
6076sno:504 sname:naga
6077m1:67 m2:50 m3:33
6078total:150 result:fail
6079
6080         PL/SQL procedure successfully completed.
6081
6082Conclusion:
6083       a pl/sql program is successfully executed for displaying the student mark list using cursors. 
6084PARAMETERIZED CURSOR
6085Aim: To Write a pl/sql program to display employees using parameterized cursor.
6086Source code:
6087              declare
6088     n number;
6089      er emp%rowtype;
6090     cursor c(d number) is
6091     select * from emp where emp.eno=d;
6092begin
6093    n:=&n;
6094    open c(n);
6095    if c%isopen then
6096      loop
6097          fetch c into er;
6098          exit when c%notfound;
6099             Dbms_output.put_line('Employee No:'||er.eno
6100              ||'Employee Name:'||er.ename);
6101          end loop;
6102      else
6103       DBMS_OUTPUT.PUT_LINE('not found');
6104      end if;
6105    close c;
6106end;
6107  /
6108
6109 Execution:    
6110SQL> @e:\plsql\c4.sql
6111Enter value for n: 100
6112old   7:  n:=&n;
6113new   7:  n:=100;
6114Employee No:100  Employee Name:mahesh
6115
6116        PL/SQL procedure successfully completed.
6117
6118Conclusion:
6119       a pl/sql program is successfully executed for displaying  employees using parameterized cursor
6120.
6121
6122INCREMENTING SALARY OF EMPLOYEES.
6123Aim: To Write a pl/sql program to update the commission values for all employees with salary less than 2000 by adding Rs.1000 to existing employees.
6124Source code:
6125  declare
6126       cursor c is select *From emp for update;
6127        emp_rec emp%rowtype;
6128begin
6129     for emp_rec in c
6130     loop
6131          if emp_rec.salary<2000 then
6132             update emp set comm=comm+1000
6133             where current of c;
6134           end if;
6135        end loop;
6136   end;
6137/
6138Displaying rows before execution:
6139    SQL>Select *From employee;
6140          Eno  Ename  Job   Salary comm
6141----------------------------------------------------
6142106     latha        sales      12800      5000
6143111     kiran         agm      12800      6000
6144100     john          sales        1880       500
6145101     kalam       clerk        1800       600
6146105     kohili       clerk         1000       200
6147
6148Execution:
6149        SQL>@E:\plsql\incr.sql ;      
6150           PL/SQL procedure successfully completed.
6151Displaying rows after execution:
6152    SQL>Select *From employee;
6153          Eno  Ename  Job   Salary comm
6154----------------------------------------------------
6155106     latha        sales      12800      5000
6156111     kiran         agm      12800      6000
6157100     john          sales        1880     1500
6158101     kalam       clerk        1800     1600
6159105     kohili       clerk         1000     1200
6160Conclusion:
6161     a pl/sql program is successfully executed for incrementing salary of employees.
6162
6163DELETING EMPLOYEE
6164Aim: To Write a pl/sql program to delete employees whose experience
6165               is less then 2   years.
6166Procedure:
6167Employee table:
6168     Emp(eno,ename,salary,experience)
6169
6170Source code:
6171declare
6172    cursor c is select *From emp for update;
6173     emp_rec emp%rowtype;
6174begin
6175  for emp_rec in c
6176  loop
6177     if emp_rec.experience<2 then
6178       delete emp
6179       where current of c;
6180      end if;
6181   end loop;
6182end;
6183/
6184
6185Execution:
6186
6187  SQL>@del.sql
6188        
6189        PL/SQL procedure successfully completed.
6190
6191Conclusion:  a pl/sql program is successfully executed for to delete employees
6192                          whose experience is less than 2   years.
6193
6194
6195
6196
6197
6198A PREDEFINED EXCEPTION
6199Aim: To Write a pl/sql program to handle a predefined exception.
6200Source code:
6201
6202  declare
6203      n number(4);
6204      d number(4);
6205  begin
6206      n:=&n;
6207      d:=n/0;
6208       EXCEPTION
6209         WHEN ZERO_DIVIDE THEN
6210           DBMS_OUTPUT.PUT_LINE('Divide by error exception is caught');
6211  end;
6212 /
6213
6214Execution:
6215SQL> @e:\plsql\exp.sql
6216Enter value for n: 4
6217old   5: n:=&n;
6218new   5: n:=4;
6219Divide by error exception is caught
6220
6221PL/SQL procedure successfully completed.
6222
6223Conclusion:
6224       a pl/sql program is successfully executed for handle a predefined exception.
6225
6226
6227A USER DEFINED EXCEPTION
6228Aim: To Write a pl/sql program to handle a user defined exception.
6229Source code:
6230
6231declare
6232       others Exception;
6233       n dept.dno%type;
6234       name dept.dname%type;
6235       n1 dept.dno%type;
6236 begin
6237       n1:=&dept_no;
6238       select dno,dname into n,name
6239       from dept
6240        where dno=n1;
6241      if n<>n1 then
6242         raise others;
6243     else
6244        update dept set dname='&dname'
6245         where dno=n;
6246         Dbms_output.put_line('dname updated.');
6247       end if;
6248    Exception
6249       when others then
6250            Dbms_output.put_line('given dno is not found');
6251     end;
6252    /
6253
6254Before  execution:
6255
6256SQL> select *From dept;
6257
6258       DNO DNAME
6259---------- --------
6260       101 SALES
6261       103 ACCOUNTS
6262       104 ADMIN
6263
6264
6265
6266
6267
6268Execution:
6269SQL> @e:\plsql\userexp.sql
6270Enter value for dept_no: 102
6271old   7:    n1:=&dept_no;
6272new   7:    n1:=102;
6273Enter value for dname: marketing
6274old  14:       update dept set dname='&dname'
6275new  14:       update dept set dname='marketing'
6276given dno is not found.
6277PL/SQL procedure successfully completed.
6278
6279SQL> @e:\plsql\userexp.sql
6280Enter value for dept_no: 104
6281old   7:    n1:=&dept_no;
6282new   7:    n1:=104;
6283Enter value for dname: market
6284old  14:       update dept set dname='&dname'
6285new  14:       update dept set dname='market'
6286dname updated.
6287
6288PL/SQL procedure successfully completed.
6289
6290After  execution:
6291
6292SQL> select *From dept;
6293
6294       DNO DNAME
6295---------- --------
6296       101 SALES
6297       103 ACCOUNTS
6298       104 market
62993 rows selected.
6300
6301Conclusion:
6302       a pl/sql program is successfully executed for handle a user defined exception.
6303
6304
6305
6306
6307SUM OF TWO NUMBERS USING PROCEDURES
6308Aim: To Write a pl/sql program for creating a procedure for calculating
6309          sum of two numbers.
6310Source code:
6311create or replace procedure "SUM"(n1 IN NUMBER,n2 IN NUMBER) is
6312total number(6);
6313begin
6314total:=n1+n2;
6315  dbms_output.put_line('the sum is:'||total);
6316end;
6317
6318Execution:
6319     Method1:
6320         SQL> exec sum(10,20);
6321         the sum is:30
6322        
6323           PL/SQL procedure successfully completed.
6324   
6325      Method 2:
6326          SQL> begin
6327  2  sum(12,13);
6328  3  end;
6329  4  /
6330the sum is:25
6331
6332PL/SQL procedure successfully completed.
6333
6334Conclusion:
6335     a pl/sql program is successfully executed for creating a procedure for calculating
6336     sum of two numbers.
6337
6338
6339
6340
6341----------------------------------------------