PLSQL Programmes 1. Wr Write ite a Program Program to print the followi following ng format format 2. WELCOME WELCOME TO TO PL/SQL PL/SQL PROGR PROGRAMMI AMMING NG 3. BEGIN 4. DBMS_OUTPUT.PUT DBMS_OUTPUT.PUT_LINE('W _LINE('WELCOME ELCOME TO TO PL/SQL PL/SQL PROGRAMMING'); PROGRAMMING'); END;
5. Wr Write ite a program program to print print the numbers numbers from 1 to 100 6. DECL ECLARE 7. N NUM NUMBER BER(3 (3)! )!"; "; #. $ $ARC $ARC%AR %AR2( 2("&& "&&&); &); . BEGIN "&. "&. W%I W%ILE N !"&& "&& "". LOOP "2. "2. $!$ $!$ '' '' N; N; "3. N!N*"; "4. END LO LOOP; "+. DBMS_ DBMS_OUT OUTPU PUT.P T.PUT_ UT_LI LINE( NE($) $); ; "6. END;
17. write a program to print print the even numbers from 1 to 100 "#. DECLARE ". ". N NUMB NUMBER ER(3 (3) )!& !&; ; 2&. BEGIN 2". 2". W%I W%ILE N "&& 22. LOOP 23. N!N*2; 24. DBMS_ DBMS_OUT OUTPU PUT.P T.PUT_ UT_LI LINE( NE(N) N); ; 2+. END LO LOOP; END;
26. Write Write a program to print the the odd numbers from 1 to 100 27. DECLARE 2#. 2#. N NUMB NUMBER ER(3 (3) )!" !"; ; 2. BEGIN 3&. 3&. W%I W%ILE N !"&& "&& 3". LOOP 32. N!N*2; 33. DBMS_ DBMS_OUT OUTPU PUT.P T.PUT_ UT_LI LINE( NE(N) N); ; 34. END LO LOOP; END;
35. write write a program program for multiplicati multiplication on table 36. DECLARE 37. 37. A NUMB NUMBER ER(2 (2) )!, !,A; A; 3#. 3#. B NUMB NUMBER ER(2 (2) )!" !"; ;
3. 3. C NU NUMBER BER(3); 4&. BEGIN 4". 4". W%I W%ILE B !"& 42. LOOP 43. C!A-B; 44. DBMS_OUT DBMS_OUTPUT. PUT.PUT_ PUT_LIN LINE(A E(A'-' '-'B B'!' '!'C) C); ; 4+. B!B*"; 46. END LO LOOP; END;
7. write a program to find find the sum of numbers from 1 to 100 4#. DECLARE 4. 4. N NUMB NUMBER ER(3 (3) )!" !"; ; +&. +&. S NUMB NUMBER ER(4 (4) )!& !&; ; +". BEGIN +2. +2. W%I W%ILE N !"&& "&& +3. LOOP +4. S!S*N; ++. N!N*"; +6. END LO LOOP; +7. DBMS_OUT DBMS_OUTPUT. PUT.PUT_ PUT_LIN LINE('T E('T%E %E SUM O O " TO "&& IS 'S) 'S); ; END;
5!. Write Write a program to find the sum sum of all odd numbers from from 1 to 100 +. DECLARE 6&. 6&. N NUMB NUMBER ER(3 (3) )!" !"; ; 6". 6". S NUMB NUMBER ER(4 (4) )!& !&; ; 62. BEGIN 63. 63. W%I W%ILE N !"&& "&& 64. LOOP 6+. S!S*N; 66. N!N*2; 67. END LO LOOP; 6#. DBMS_OUT DBMS_OUTPUT. PUT.PUT_ PUT_LIN LINE('T E('T%E %E SUM O " TO "&& ODD ODD NUMBERS NUMBERS IS 'S); 'S); END;
6". Write Write a program to find the sum sum of all even numbers from from 1 to 100 7&. DECLARE 7". 7". N NUMB NUMBER ER(3 (3) )!& !&; ; 72. 72. S NUMB NUMBER ER(4 (4) )!& !&; ; 73. BEGIN 74. 74. W%I W%ILE N !"&& "&& 7+. LOOP 76. S!S*N; 77. N!N*2; 7#. END LO LOOP; 7. DBMS_OUT DBMS_OUTPUT. PUT.PUT_ PUT_LIN LINE('T E('T%E %E SUM O " TO "&& E$EN E$EN NUMBERS NUMBERS IS IS 'S); 'S); END;
!0. Write Write a program to accept a number and find find how man# digits it contain
#". DECLARE #2. #2. N NUMB NUMBER ER(+ (+) )!, !,N; N; #3. #3. CNT CNT NUMB NUMBER ER! !&; &; #4. #4. R NUMB NUMBER ER(2 (2) )!& !&; ; #+. BEGIN #6. W%ILE N !& #7. LOOP ##. ##. R!M R!MOD OD(N (N0" 0"&) &); ; #. #. CNT CNT!CNT CNT*"; &. &. N!T N!TRU RUNC NC(N (N/" /"&) &); ; ". END LO LOOP; 2. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('N LINE('NUMBER UMBER O DIGITS DIGITS O GI$EN GI$EN NUMBER NUMBER IS 'CNT); 'CNT); END;
"3. Write Write a program to accept a number and find find the sum of the digits digits 4. DECLARE +. +. N NUMB NUMBER ER(+ (+) )!, !,N; N; 6. 6. S NU NUMBER BER!&; 7. 7. R NUMB NUMBER ER(2 (2) )!& !&; ; #. BEGIN . W%ILE N !& "&&. "&&. LOOP LOOP "&". R!MOD(N R!MOD(N0"&) 0"&); ; "&2. "&2. S!S* S!S*R; R; "&3. N!TRUNC N!TRUNC(N/" (N/"&); &); "&4. "&4. END LOOP; LOOP; "&+. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('S LINE('SUM UM O DIGITS O GI$EN NUMBER IS 'S); END;
106. 106.
Write ite a prog progra ram m to to acc accep eptt a numb number er and and pri print nt it in reve revers rsee ord order er
"&7. "&7. DECLA DECLARE RE "&#. N NUMBER(+ NUMBER(+)!, )!,N; N; "&. RE$ NUMBER(+ NUMBER(+)!& )!&; ; ""&. R NUMBER( NUMBER(+)! +)!&; &; """. """. BEGI BEGIN N ""2. ""2. W%ILE W%ILE N !& !& ""3. ""3. LOOP LOOP ""4. R!MOD(N R!MOD(N0"&) 0"&); ; ""+. RE$!RE$ RE$!RE$-"&* -"&*R; R; ""6. N!TRUNC N!TRUNC(N/" (N/"&); &); ""7. ""7. END LOOP; LOOP; ""#. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('T LINE('T%E %E RE$ERSE O A GI$EN NUMBER IS 'RE$); END;
11". 11".
Writ Wr itee a progr program am to accep acceptt a no and and che chec$ c$ whet whether her it is %rms %rmstr tron ong g num number ber or not not
120. 120.
Write ite a por porgr gram am to gene genera rate te all all the the %rmst rmstro rong ng numb number erss fro from m 1 to 1000 1000
121. 121.
Write ite a prog progra ram m to to gen gener erat atee all all prim primee num numbe bers rs betw betwee een n 1 to 100 100
122. 122.
Write ite a pro progr gram am to acee aceept pt a num numbe berr and and che chec$ c$ whe wheth ther er it is prim primee numb number er or not not
123. 123.
Write ite a prog progra ram m to disp displa la# # the the fib fibon onac acci ci seri series es from from 1 to 10
12. 12.
Write ite a prog progra ram m to to ace aceep eptt a numb number er and and pri print nt it in bina binar# r# form format at
125. 125.
Write ite a pro progr gram am to acce accept pt a num numbe berr and and find find the the fac facto tori rial al of the the num numbe berr
126 126.
&in &ind the factorials of num numbers ers from 1 to 10
"27. "27. DECLA DECLARE RE "2#. ACT NUMB NUMBER! ER!"; "; "2. $ $ARC%AR2 $ARC%AR2("&& ("&&); ); "3&. "3&. BEGI BEGIN N "3". OR I IN ".."& ".."& "32. "32. LOOP LOOP "33. OR 1 IN "..I "34. "34. LOOP LOOP "3+. ACT!A ACT!ACT-1 CT-1; ; "36. $!1'$!1'-'$ '$; ; "37. "37. END LOOP; LOOP; "3#. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE(RT LINE(RTRIM($0'RIM($0'-')'!' ')'!'ACT); ACT); "3. "3. ACT ACT!"; !"; "4&. "4&. $!NU $!NULL; LL; "4". "4". END LOOP; LOOP; END;
12. 12.
Write ite a pro progr gram am to acee aceept pt a num numbe berr and and disp displa la# # it it in in the the 'cta 'ctall for forma matt
"43. "43. DECLA DECLARE RE "44. N NUMBER(2 NUMBER(2)!, )!,N; N; "4+. R NUMBE NUMBER(2 R(2); ); "46. $ $ARC%AR2(" $ARC%AR2("&&&) &&&); ; "47. "47. BEGI BEGIN N "4#. "4#. W%ILE W%ILE N& N& "4. "4. LOOP LOOP "+&. R!MOD(N R!MOD(N0#); 0#); "+". "+". $!R $!R$; $; "+2. N!TRUNC N!TRUNC(N/# (N/#); ); "+3. "+3. END LOOP; LOOP; "+4. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('O LINE('OCTAL CTAL O A GI$EN NUMBER IS '$); '$); END;
155. 155. "+6. "+6. "+7. "+#. "+#. "+. "+. "6&. "6". "6". "62. "63. "63. "64. "64. "6+.
Writ Wr itee a progr program am to accep acceptt a numbe numberr and and prin printt the the mult multip ipli licat catio ion n tab table less upt upto o soo soo DECLA DECLARE RE N NUMBER(2 NUMBER(2)!, )!,N; N; M NUMBE NUMBER; R; BEGI BEGIN N OR I IN N..N* N..N*+ + LOOP LOOP OR 1 IN ".."& ".."& LOOP LOOP M!IM!I-1; 1; DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE(I LINE(I'-'1 '-'1'!'M) '!'M); ;
"66. "66. END LOOP; LOOP; "67. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('LINE('-----------------------------------'); ----'); "6#. "6#. END LOOP; LOOP; END;
16". 16". Write ite a pro progr gram am to acce accept pt the the tem temp p in in (en (enti tigr grad adee and and con conve vert rt it into into &ahrenheit)c*&+32,1.!"7&. "7&. DECLA DECLARE RE "7". C NUMBER NUMBER!,C !,C; ; "72. "72. NUMBE NUMBER; R; "73. "73. BEGI BEGIN N "74. !C-".# !C-".#*32; *32; "7+. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('T LINE('T%E %E AREN%ETT O GI$EN OC IS '); END;
176. 176.
Write ite a pro progr gram am to calc calcul ulat atee the the area area of a tri trian angl glee b# acce accept ptin ing g the the 3 side sidess
"77. (!(*5*)/2 (!(*5*)/2 8!9:(-()-( 8!9:(-()-(5)-( 5)-())) ))) "7#. "7#. DECLA DECLARE RE "7. "7. S NUMBE NUMBER; R; "#&. A NUMBER NUMBER!,A !,A; ; "#". B NUMBER NUMBER!,B !,B; ; "#2. C NUMBER NUMBER!,C !,C; ; "#3. AREA NUMBER( NUMBER(702) 702); ; "#4. "#4. BEGI BEGIN N "#+. S!(A*B* S!(A*B*C)/2 C)/2; ; "#6. AREA!SQRT(S-(S AREA!SQRT(S-(SA)-(SB A)-(SB)-(SC)) )-(SC)); ; "#7. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('T LINE('T%E %E AREA O TRIANGLE IS 'AREA); END;
1!!. 1!!. Writ Wr itee a progr program am to calc calcul ulat atee the the area area of a cir circl clee b# accept acceptin ing g the the radi radius us and and unit unit of measurearea*P/r2 "#. "#. DECLA DECLARE RE "&. R NUMBER NUMBER!,R !,R; ; "". AREA NUMBER( NUMBER(702) 702); ; "2. "2. BEGI BEGIN N "3. AREA!(2 AREA!(22/7) 2/7)-R-R -R-R; ; "4. DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('T LINE('T%E %E AREA O CIRCLE IS 'AREA); 'AREA); END;
1"5. 1"5.
Writ Wr itee a progr program am to calc calcul ulat atee the the peri perime mete terr of a circ circle le)p )per erim imet eter er*2 *2/P /P/r /r--
"6. "6. DECLA DECLARE RE "7. R NUMBER NUMBER!,R !,R; ; "#. PERIMETER PERIMETER NUMBER(702); NUMBER(702); ". ". BEGI BEGIN N 2&&. PERIMETER!2-(22 PERIMETER!2-(22/7)-R; /7)-R; 2&". DBMS_OUTPUT.PUT_ DBMS_OUTPUT.PUT_LINE('T LINE('T%E %E PERIMETER O CIRCLE IS 'PERIMETER); END;
202. Write a program to accept the 3 sides of the triangle and displa# the t#pe of triangle 2&3. DECLARE 2&4. A NUMBER(402)!,A; 2&+. B NUMBER(402)!,B; 2&6. C NUMBER(402)!,C; 2&7. PERIMETER NUMBER(702); 2&#. BEGIN 2&. I (A!B AND B!C AND C!A) T%EN 2"&. DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE'); 2"". ELSI A!B OR A!C OR C!B T%EN 2"2. DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE'); 2"3. ELSE 2"4. DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE'); 2"+. END I; END;
216.
Write a program accept the value of %( displa# which is greater
2"7. DECLARE 2"#. A NUMBER(402)!,A; 2". B NUMBER(402)!,B; 22&. C NUMBER(402)!,C; 22". BEGIN 222. I (AB AND AC) T%EN 223. DBMS_OUTPUT.PUT_LINE('A IS GREATER '''A); 224. ELSI BC T%EN 22+. DBMS_OUTPUT.PUT_LINE('B IS GREATE '''B); 226. ELSE 227. DBMS_OUTPUT.PUT_LINE('C IS GREATER '''C); 22#. END I; END;
22". 23&. 23". 232. 233. 234. 23+. 236. 237. 23#. 23. 24&. 24". 242. 243. 244. 24+.
26.
Write a program accept a string and chec$ whether it is palindrome or not DECLARE S $ARC%AR2("&)!',S'; L $ARC%AR2(2&); TEMP $ARC%AR2("&); BEGIN OR I IN RE$ERSE "..LENGT%(S) LOOP L!SUBSTR(S0I0"); TEMP!TEMP''L; END LOOP; I TEMP!S T%EN DBMS_OUTPUT.PUT_LINE(TEMP ''' IS PALINDROME'); ELSE DBMS_OUTPUT.PUT_LINE(TEMP ''' IS NOT PALINDROME'); END I; END;
.Write a program aceepts the value of % and swap the nos and print the values
247. DECLARE 24#. A NUMBER(2)!,A; 24. B NUMBER(2)!,B; 2+&. LAG NUMBER(2); 2+". BEGIN 2+2. LAG!A; 2+3. A!B; 2+4. B!LAG; 2++. DBMS_OUTPUT.PUT_LINE('A ''! 'A' AND ''''B ''! 'B); END;
256.
Write a program to accept the values of % and swap the numbers and print the
2+7. <=>8 2+#. ?@:>: >@ :@ <@5=8 2+. DECLARE 26&. A NUMBER(2)!,A; 26". B NUMBER(2)!,B; 262. LAG NUMBER(2); 263. BEGIN 264. LAG!A; 26+. A!B; 266. B!LAG; 267. DBMS_OUTPUT.PUT_LINE('A ''! 'A' AND ''''B ''! 'B); END;
26!.
Write a program to accept the side of a suare and calculate the area area *a2
26. DECLARE 27&. A NUMBER!,A; 27". AREA NUMBER(+); 272. BEGIN 273. AREA!A-A; 274. DBMS_OUTPUT.PUT_LINE('AREA O A SQUARE IS '''AREA); END;
275. Write a program to accept principle amount ratetime calculate the simple interest si*)p/t/r-,100 276. DECLARE 277. P NUMBER(602)!,P; 27#. R NUMBER(602)!,R; 27. T NUMBER(602)!,T; 2#&. SI NUMBER(602); 2#". BEGIN 2#2. SI!(P-R-T)/"&&; 2#3. DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '''SI); END;
2!.
4rite a program to aceept the principle amountratetime and find the compound
2#+. @:88: 2#6. @!F-("*/"&&) 2#7. DECLARE
2##. P NUMBER(602)!,P; 2#. R NUMBER(602)!,R; 2&. T NUMBER(602)!,T; 2". CI NUMBER(602); 22. BEGIN 23. CI!P-POWER("*(R/"&&)0T); 24. DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS 'CI); END;
2"5.
W%P to calculate the sum of 12......n
26. DECLARE 27. N NUMBER!,N; 2#. S NUMBER!&; 2. NUMBER!"; 3&&. BEGIN 3&". OR I IN "..N 3&2. LOOP 3&3. OR 1 IN "..I 3&4. LOOP 3&+. !-1; 3&6. END LOOP; 3&7. S!S*; 3&#. !"; 3&. END LOOP; 3"&. DBMS_OUTPUT.PUT_LINE('SUM O ACT IS 'S); END;
311.
W%P to calculate the sum of 11,21,3......1,n
3"2. DECLARE 3"3. N NUMBER!,N; 3"4. A NUMBER; 3"+. S NUMBER(602)!&; 3"6. BEGIN 3"7. OR I IN "..N 3"#. LOOP 3". A!"/I; 32&. S!S*A; 32". END LOOP; 322. DBMS_OUTPUT.PUT_LINE('SUM O NO ARE 'S); END;
323. 324. 32+. 326. 327. 32#. 32. 33&. 33". 332. 333.
W%P to calculate the sum of 1,11,2.....1,n DECLARE N NUMBER!,N; S NUMBER(602)!&; NUMBER!"; BEGIN OR I IN "..N LOOP OR 1 IN "..I LOOP !-1;
334. END LOOP; 33+. S!S*("/); 336. END LOOP; 337. DBMS_OUTPUT.PUT_LINE('SUM IS 'S); END;
33!.
W%P to calculate the sum of 1,12,2......n,n
33. DECLARE 34&. N NUMBER(4)!,N; 34". S NUMBER(602)!&; 342. NUMBER(4)!"; 343. BEGIN 344. OR I IN "..N 34+. LOOP 346. OR 1 IN "..I 347. LOOP 34#. !-1; 34. END LOOP; 3+&. S!S*(I/); 3+". END LOOP; 3+2. DBMS_OUTPUT.PUT_LINE('SUM O ACT IS 'S); END;
353.
Write a program to displa# the months between two dates of a #ear
3+4. DECLARE 3++. D DATE!',D'; 3+6. D" DATE!',D"'; 3+7. BEGIN 3+#. W%ILE D D" 3+. LOOP 36&. DBMS_OUTPUT.PUT_LINE(TO_C%AR(D0'MONT%')); 36". D!ADD_MONT%S(D0"); 362. END LOOP; END;
363.
Write a program to accept the date and print the wee$da#s from the given date
364. DECLARE 36+. D DATE!',D'; 366. WD DATE; 367. BEGIN 36#. WD!D*6; 36. W%ILE D ! WD 37&. LOOP 37". DBMS_OUTPUT.PUT_LINE(TO_C%AR(D0'DA')); 372. D!D*"; 373. END LOOP; END;
37. W%P to accept the date and print the wee$da#s from the given date along with date format
37+. DECLARE 376. D DATE!',D'; 377. WD DATE; 37#. BEGIN 37. WD!D*6; 3#&. W%ILE D ! WD 3#". LOOP 3#2. DBMS_OUTPUT.PUT_LINE(TO_C%AR(D0'DA')D); 3#3. D!D*"; 3#4. END LOOP; END;
3!5.
Writa a program to accept a #ear and chec$ whether it is leap #ear or not
3#6.
DECLARE
NUMBER!,; R NUMBER; BEGIN I MOD(04)!& AND MOD(0"&&)!& OR MOD(04&&)!& T%EN DBMS_OUTPUT.PUT_LINE( ' IS A LEAP EAR'); ELSE DBMS_OUTPUT.PUT_LINE( ' IS NOT A LEAP EAR'); END I; END;
3!7.
Write a program to accept a #ear and displa# all sunda#s along with the date
3##. DECLARE 3#. NUMBER(4)!,; 3&. A DATE; 3". B DATE; 32. I NUMBER(2)!"; 33. BEGIN 34. A!TO_DATE('&"1AN'0'DDMON'); 3+. B!LAST_DA(ADD_MONT%S(A0"")); 36. W%ILE A ! B 37. LOOP 3#. I TO_C%AR(A0'D')!" T%EN 3. DBMS_OUTPUT.PUT_LINE(LPAD(I020'&')''UPPER(TO_C%AR(A0'DA'))A); 4&&. I!I*"; 4&". END I; 4&2. A!A*"; 4&3. END LOOP; END;
0.
W%P to accept a string and count how man# vowels present in the string
4&+. DECLARE 4&6. $ $ARC%AR2(3&&)!',$'; 4&7. CNT NUMBER(+)!&;
4&#. C C%AR; 4&. BEGIN 4"&. OR I IN "..LENGT%($) 4"". LOOP 4"2. C!SUBSTR($0I0"); 4"3. I C IN ('A'0'E'0'I'0'O'0'U') T%EN 4"4. CNT!CNT*"; 4"+. END I; 4"6. END LOOP; 4"7. DBMS_OUTPUT.PUT_LINE('NO O $OWELS PRESENT ! 'CNT); END;
1!. Write a program to accept a #ear and chec$ whether it is leap #ear or not . f it is leap #ear then displa# how man# sunda#s present in that #ear 4". DECLARE 42&. D DATE!',EAR'; 42". $ARC%AR2(2&); 422. CNT NUMBER(+)!&; 423. $ $ARC%AR2(2&); 424. BEGIN 42+. !TO_C%AR(D0''); 426. D!TO_DATE('&"1AN'); 427. I MOD(04)!& AND MOD(0"&&)!& OR MOD(04&&)!& T%EN 42#. OR I IN "..366 42. LOOP 43&. $!TO_C%AR(D0'D'); 43". I $!" T%EN 432. CNT!CNT*"; 433. END I; 434. D!D*"; 43+. DBMS_OUTPUT.PUT_LINE('NO O $OWELS PRESENT ! 'CNT); 436. END LOOP; END;
37.
Write a program to accept a char and chec$ it is vowel or consonant
43#. DECLARE 43. C C%AR!',C'; 44&. BEGIN 44". I C!'A' OR C!'E' OR C!'I' OR C!'O' OR C!'U' T%EN 442. DBMS_OUTPUT.PUT_LINE('$OWEL'); 443. ELSE 444. DBMS_OUTPUT.PUT_LINE('CONSONANT'); 44+. END I; END;
6. 447. 44#. 44. 4+&. 4+".
W%P to accept %( chec$ whether it is 8amanu9an number or not DECLARE A NUMBER!,A; B NUMBER!,B; C NUMBER!,C; D NUMBER!,D;
4+2. BEGIN 4+3. I 4+4. POWER(A03)*POWER(B03)!POWER(C03)*POWER(D03) T%EN 4++. DBMS_OUTPUT.PUT_LINE(AC%R("7)'*'BC%R("7)'!'C C%R("7)'*'DC%R("7)); 4+6. ELSE 4+7. DBMS_OUTPUT.PUT_LINE(AC%R("7)'*'BC%R("7)'!'C C%R("7)'*'DC%R("7)); 4+#. END I; END;
5".
W%P to accept the (:8 ;:8 find out the total bill amount i.
0+100 units 8s.50 per unit
ii.
101+200n units 8s.o.25 per unit
iii.
<200 units 8s.1.25 per unit
46&. DECLARE 46". LMR NUMBER(+)!,LMR; 462. CMR NUMBER(+)!,CMR; 463. TOT NUMBER(+)!&; 464. BILL NUMBER(702)!&; 46+. BEGIN 466. TOT!CMRLMR; 467. I TOT ! "&& T%EN 46#. BILL!TOT-.+&; 46. ELSI TOT "&& AND TOT ! 2&& T%EN 47&. BILL!("&&-.+&)*((TOT"&&)-.7+); 47". ELSE 472. BILL!("&&-.+&)*("&&-.7+)*(TOT2&&)-".2+; 473. END I; 474. DBMS_OUTPUT.PUT_LINE('TOTAL UNIT CONSUMED 'TOT); 47+. DBMS_OUTPUT.PUT_LINE('TOTAL BILL AMOUNT 'BILL); END;
76. W%P or accept mar$s of 3 sub9ect as i,p and calculate the total mar$s and division of a student i.
f totmar$<*60 then division is &irst
ii.
f totmar$ =60 and totmar$<*50 then division is second
iii.
f totmar$= 50 and <*35 then division is third
iv.
f totmar$= 35 then fail
477. 47#. 47. 4#&.
DECLARE M" NUMBER(2)!,M"; M2 NUMBER(2)!,M2; M3 NUMBER(2)!,M3;
4#". TOTMARH NUMBER(+02); 4#2. A$E NUMBER(+02)!&; 4#3. BEGIN 4#4. TOTMARH!M"*M2*M3; 4#+. A$E!TOTMARH/3; 4#6. I A$E!6& T%EN 4#7. DBMS_OUTPUT.PUT_LINE('T%E DI$ISION IS IRST 'A$E); 4##. ELSI A$E6& AND A$E!+& T%EN 4#. DBMS_OUTPUT.PUT_LINE('T%E DI$ISION IS SECOND 'A$E); 4&. ELSI A$E+& AND A$E!3+ T%EN 4". DBMS_OUTPUT.PUT_LINE('T%E DI$ISION IS T%IRD 'A$E); 42. ELSE 43. DBMS_OUTPUT.PUT_LINE('AIL 'A$E); 44. END I; END;
"5. 46. 47. 4#. 4. +&&. +&". +&2. +&3. +&4. +&+. +&6. +&7.
50!.
W%P to accept a number and print its multiplication table horin>ontall# DECLARE 1 NUMBER!,1; $ $ARC%AR2("&&&); H NUMBER(3); BEGIN OR I IN ".."& LOOP H!1-I; $!$1'-'I'!'H' '; END LOOP; DBMS_OUTPUT.PUT_LINE($); END;
W%P to accept a string and print it in reverse order
+&. DECLARE +"&. STR $ARC%AR2("&&)!',TR'; +"". STR" $ARC%AR2("&&); +"2. N NUMBER(+); +"3. L $ARC%AR2(2&); +"4. BEGIN +"+. N!LENGT%(STR); +"6. OR I IN "..N +"7. LOOP +"#. L!SUBSTR(STR0I0"); +". STR"!LSTR"; +2&. END LOOP; +2". DBMS_OUTPUT.PUT_LINE(STR"); END;
522. +23. +24. +2+. +26. +27.
Write a program to accept a number and find out the sum of first and last digits DECLARE A NUMBER(4)!,A; B NUMBER(+)!&; C NUMBER(+)!&; S NUMBER(+);
+2#. +2. +3&. +3". +32. +33. +34. +3+. +36. +37.
BEGIN I A T%EN C!SUBSTR(A0"0"); B!SUBSTR(A0LENGT%(A)0"); S!B*C; ELSE S!A; END I; DBMS_OUTPUT.PUT_LINE('SUM O IRST AND LAST DIGIT IS 'S); END;
53!. W%P to accept the basic salar# and find out the tadahralic and gs i-ta 20? of basic da 10? of basic hra 30? of basic lic 5? of basic +3. +4&. +4". +42. +43. +44. +4+. +46. +47. +4#. +4. ++&. ++". ++2. ++3. ++4. +++. ++6. ++7.
55!. ++. +6&. +6". +62. +63. +64. +6+. +66.
DECLARE BS NUMBER(602)!,BS; TA NUMBER(602); DA NUMBER(602); %RA NUMBER(602); GS NUMBER(602); LIC NUMBER(602); NS NUMBER(#02); BEGIN TA!BS-(2&/"&&); %RA!BS-(3&/"&&); DA!BS-("&/"&&); LIC!BS-(+/"&&); GS!TA*%RA*DA; NS!GSLIC; DBMS_OUTPUT.PUT_LINE('EMPLOEE BS IS 'BS); DBMS_OUTPUT.PUT_LINE('GROSS SALAR IS 'GS); DBMS_OUTPUT.PUT_LINE('NET SALAR IS 'NS); END;
W%P to accept the length and breadth of a rectangle and find out the perimeter DECLARE L NUMBER(402)!,L; B NUMBER(402)!,B; A NUMBER(402); BEGIN A!2-(L*B); DBMS_OUTPUT.PUT_LINE('T%E PERIMETER O RECTANGLE IS 'A); END;
567. W%P to accept the cost price and selling price of an item and find the loss or profit +6#. +6. +7&. +7". +72. +73.
DECLARE CP NUMBER(2+02)!,CP; SP NUMBER(2+02)!,SP; AMT NUMBER(702); BEGIN I CP SP T%EN
+74. +7+. +76. +77. +7#. +7. +#&.
AMT!SPCP; DBMS_OUTPUT.PUT_LINE('PROIT IS 'AMT); ELSE AMT!CPSP; DBMS_OUTPUT.PUT_LINE('LOSS IS 'AMT); END I; END;
5!1. Writ a program to generate the following series 53 53 53 53 53 3 3 3 3 33 33 33 23 23 13 +#2. +#3. +#4. +#+. +#6. +#7. +##. +#. +&. +". +2. +3. +4.
5"5. +6. +7. +#. +. 6&&. 6&". 6&2. 6&3. 6&4. 6&+. 6&6. 6&7.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN RE$ERSE "..+ LOOP OR 1 IN "..I LOOP $!$IC%R("7); END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
W%P to accept a no in binar# format and print it in decimal format DECLARE N $ARC%AR2(2&)!,N; PRO NUMBER("&04)!&; L $ARC%AR2("&); BEGIN OR I IN "..LENGT%(N) LOOP L!SUBSTR(N0I0"); PRO!PRO*L-POWER(20LENGT%(N)I); END LOOP; DBMS_OUTPUT.PUT_LINE('T%E DECIMAL NUMBER IS 'PRO); END;
60!. W%P to accept two nos and input and find one no is raised to another one )without using an# function6&. 6"&. 6"". 6"2. 6"3. 6"4. 6"+. 6"6. 6"7. 6"#. 6".
DECLARE A NUMBER!,A; B NUMBER!,B; R NUMBER!"; BEGIN OR I IN "..B LOOP R!R-A; END LOOP; DBMS_OUTPUT.PUT_LINE('A RAISED POWER B IS 'R); END;
620. 62". 622. 623. 624. 62+. 626. 627. 62#.
62". 63&. 63". 632. 633. 634. 63+. 636. 637. 63#. 63. 64&. 64".
62. 643. 644. 64+. 646. 647. 64#. 64. 6+&. 6+". 6+2. 6+3. 6+4. 6++. 6+6. 6+7. 6+#. 6+. 66&. 66". 662.
663.
W%P to accept a sentence and count the no of chars in that sentence DECLARE STR $ARC%AR2("&&)!',STR'; NO NUMBER(+)!&; I NUMBER; BEGIN I!INSTR(STR0'.'); DBMS_OUTPUT.PUT_LINE('NO O C%AR IS 'I); END;
W%P to accept two strings and displa# the large one among those DECLARE STR" $ARC%AR2("&&)!',STR"'; STR2 $ARC%AR2("&&)!',STR2'; BEGIN I LENGT%(STR") LENGT%(STR2) T%EN DBMS_OUTPUT.PUT_LINE(STR" ' IS GREATER'); ELSI LENGT%(STR") LENGT%(STR2) T%EN DBMS_OUTPUT.PUT_LINE(STR2 ' IS GREATER'); ELSE DBMS_OUTPUT.PUT_LINE('BOT% STRINGS ARE EQUAL'); END I; END;
W%P to displa# all the nos whose sum of digits is " from 1 to """" DECLARE N NUMBER; M NUMBER; S NUMBER!&; BEGIN OR I IN ".. LOOP N!I; W%ILE N& LOOP M!MOD(N0"&); S!S*M; N!TRUNC(N/"&); END LOOP; I S! T%EN DBMS_OUTPUT.PUT_LINE(I' '); END I; S!&; END LOOP; END;
W%P to accept a no and find the sum in a single digit
664. DECLARE 66+. N NUMBER(4)!,N; 666. S NUMBER("&)!&;
667. 66#. 66. 67&. 67". 672. 673. 674. 67+. 676. 677. 67#.
67". 6#&. 6#". 6#2. 6#3. 6#4. 6#+. 6#6. 6#7. 6##. 6#.
6"0. 6". 62. 63. 64. 6+. 66. 67. 6#. 6. 7&&. 7&".
BEGIN W%ILE LENGT%(N)" LOOP OR I IN "..LENGT%(N) LOOP S!S*SUBSTR(N0I0"); END LOOP; N!S; S!&; END LOOP; DBMS_OUTPUT.PUT_LINE('T%E SUM IN SINGLE DIGIT IS 'N); END;
4nte the no of da#s and find out the no of #ears and no of da#s and months DECLARE D NUMBER!,D; NUMBER; M NUMBER; BEGIN !TRUNC(D/36+); M!TRUNC(MOD(D036+)/3&); D!MOD(MOD(D036+)03&); DBMS_OUTPUT.PUT_LINE(' EARS 'M' MONT%S 'D' DAS'); END;
W%P to accept the date and print all the wee$da#s along with the given date DECLARE D DATE!',D'; $ $ARC%AR2(2&); BEGIN OR I IN "..7 LOOP $!TO_C%AR(D0'DA')D; DBMS_OUTPUT.PUT_LINE($); D!D*"; END LOOP; END;
702. W%P while purchasing certain itemsdiscount of each is as follows i- f t# purchased <1000 discount is 20? ii- f the t# and price per item are i,p then calculate the e@penditure 7&3. 7&4. 7&+. 7&6. 7&7. 7&#. 7&. 7"&. 7"". 7"2.
DECLARE QT NUMBER(+)!,QT; UP NUMBER(602)!,UP; DIS NUMBER(602)!&; TAMT NUMBER("&02); BILL NUMBER("&02); BEGIN BILL!QT-UP; I BILL "&&& T%EN DIS!BILL-2&/"&&&;
7"3. 7"4. 7"+. 7"6.
717. 7"#. 7". 72&. 72". 722. 723. 724. 72+. 726. 727. 72#. 72. 73&. 73". 732. 733. 734. 73+. 736. 737. 73#. 73. 74&. 74". 742. 743.
7. 74+. 746. 747. 74#. 74. 7+&. 7+". 7+2. 7+3. 7+4. 7++. 7+6.
757. 7+#. 7+. 76&. 76".
END I; TAMT!BILLDIS; DBMS_OUTPUT.PUT_LINE('T%E TOTAL AMOUNT IS 'TAMT); END;
Write a program to accept a string and count the no of individual chars DECLARE $ $ARC%AR2("&&)!',$'; $" $ARC%AR2("&&); LB NUMBER; LA NUMBER; DI NUMBER; C C%AR; N NUMBER(+)!&; BEGIN $"!$; W%ILE LENGT%($")& LOOP C!SUBSTR($"0"0"); LB!LENGT%($"); $"!REPLACE($"0C); LA!N$L(LENGT%($")0&); DI!LBLA; I ASCII(C)!32 T%EN DBMS_OUTPUT.PUT_LINE('SPACE'' EISTS 'DI' TIMES'); ELSE DBMS_OUTPUT.PUT_LINE(C' EISTS 'DI' TIMES'); END I; N!N*DI; END LOOP; DBMS_OUTPUT.PUT_LINE('TOTAL LENGT% O T%E GI$EN STRING '$'!'N); END;
Write a program to displa# all combination of 123 BEGIN OR I IN "..3 LOOP OR 1 IN "..3 LOOP OR H IN "..3 LOOP DBMS_OUTPUT.PUT_LINE(I1H); END LOOP; END LOOP; END LOOP; END;
Write a program to find out the series 1222322....n2 DECLARE N NUMBER!,N; A NUMBER!"; B NUMBER!2;
762. 763. 764. 76+. 766. 767. 76#. 76. 77&. 77". 772. 773. 774. 77+. 776. 777. 77#.
C NUMBER!&; D NUMBER!&; S NUMBER!&; BEGIN W%ILE A!N LOOP C!C*A-A; A!A*2; END LOOP; W%ILE B!N LOOP D!D*B-B; B!B*2; END LOOP; S!CD; DBMS_OUTPUT.PUT_LINE('RESULT IS 'S); END;
77". Write a program to accep the time in AA :B format and find the total senconds 7#&. DECLARE 7#". % NUMBER!,%OUR; 7#2. M NUMBER!,MINUTE; 7#3. S NUMBER("&)!&; 7#4. BEGIN 7#+. S!(%-6&-6&)*(M-6&); 7#6. DBMS_OUTPUT.PUT_LINE(%' %OURS 'M' MINUTES ''IS'S' SECONDS'); 7#7. END;
7!!. ft 7#. 7&. 7". 72. 73. 74. 7+. 76. 77. 7#. 7. #&&. #&".
!02. #&3. #&4. #&+. #&6.
W%P to accept the distance between two cities in $m and convert into mts cm
DECLARE D NUMBER!,D; M NUMBER!&; CM NUMBER!&; T NUMBER!&; BEGIN M!D-"&&&; CM!M-"&&; T!ROUND(CM/"2.3); DBMS_OUTPUT.PUT_LINE('DISTANCE IN METERS IS 'M); DBMS_OUTPUT.PUT_LINE('DISTANCE IN CENTIMETERS IS 'CM); DBMS_OUTPUT.PUT_LINE('DISTANCE IN OOT IS 'T); END;
Write a program to find the series @@2,2@3,3.....@n,n DECLARE N NUMBER!,N; NUMBER!,; S NUMBER!&;
#&7. #&#. #&. #"&. #"". #"2. #"3. #"4. #"+. #"6. #"7. #"#. #".
NUMBER!"; BEGIN OR I IN "..N LOOP OR 1 IN "..I LOOP !-1; END LOOP; S!ROUND(*(POWER(0I)/)03); !"; END LOOP; DBMS_OUTPUT.PUT_LINE('SUM O NUMBER IS 'S); END;
!20. Write a program to accept the population of h#derabad each #ear the population increases 2? after # what is the population of h#d #2". DECLARE #22. P NUMBER!,P; #23. L NUMBER; #24. BEGIN #2+. OR 1 IN "..4 #26. LOOP #27. L!P-2/"&&; #2#. P!P*L; #2. END LOOP; #3&. DBMS_OUTPUT.PUT_LINE('POPULATION O %DERABAD ATER 4 EARS IS ' TRUNC(P)); #3". END;
!32. #33. #34. #3+. #36. #37. #3#. #3. #4&. #4". #42. #43. #44. #4+. #46. #47. #4#. #4. #+&. #+".
W%P to accept the 3 dates and displa# the most recentl# month among 3 dates DECLARE D" DATE!',D"'; D2 DATE!',D2'; D3 DATE!',D3'; M" NUMBER; M2 NUMBER; M3 NUMBER; BEGIN M"!TO_C%AR(D"0'MM'); M2!TO_C%AR(D20'MM'); M3!TO_C%AR(D30'MM'); I M"M2 AND M"M3 T%EN DBMS_OUTPUT.PUT_LINE(TO_C%AR(D"0'MON')' IS RECENT MONT%'); ELSI M2M" AND M2M3 T%EN DBMS_OUTPUT.PUT_LINE(TO_C%AR(D20'MON')' IS RECENT MONT%'); ELSE DBMS_OUTPUT.PUT_LINE(TO_C%AR(D30'MON')' IS RECENT MONT%'); END I; END;
!52. %ccept a string and print in the following format ' '8 '8% '8%( '8%(; '8%(;4
#+3. #+4. #++. #+6. #+7. #+#. #+. #6&. #6". #62.
DECLARE $ $ARC%AR2(2&)!',$'; C $ARC%AR(2&); BEGIN OR I IN "..LENGT%($) LOOP C!SUBSTR($0"0I); DBMS_OUTPUT.PUT_LINE(C); END LOOP; END;
!63. Write a program to accept the annual income of the emp and find the income ta@ i- f the annsal <60000 then ta@ is 10? of income ii- f the annsal <100000 then ta@ is 8s !0016? of income iii- f the annsal <10000 then ta@ is 8s 250025? of income #64. #6+. #66. #67. #6#. #6. #7&. #7". #72. #73. #74. #7+. #76. #77.
!7!. #7. ##&. ##". ##2. ##3. ##4. ##+. ##6. ##7. ###. ##. #&. #". #2.
!"3. #4. #+. #6. #7. ##.
DECLARE AI NUMBER("&02)!,ANNUALINCOME; TA NUMBER("&03)!&; BEGIN I AI BETWEEN 36&&& AND +&&&& T%EN TA!AI-"&/"&&; ELSI AI BETWEEN +&&&& AND "&&&&& T%EN TA!#&&*AI-"6/"&&; ELSI AI "&&&&& T%EN TA!2+&&*AI-2+/"&&; END I; DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME 'AI); DBMS_OUTPUT.PUT_LINE('TA 'TA); END;
W%P to accept a #ear as i,p find how man# even number present in that #ear DECLARE NUMBER!,EAR; A $ARC%AR2(2&); CNT NUMBER(+)!&; BEGIN OR I IN "..LENGT%() LOOP A!SUBSTR(0I0"); I MOD(A02)!& T%EN CNT!CNT*"; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('NUMBER O E$EN DIGIT IS 'CNT); END;
W%P to accept a #ear as i,p find how man# odd number present in that #ear DECLARE NUMBER!,EAR; A $ARC%AR2(2&); CNT NUMBER(+)!&; BEGIN
#. &&. &". &2. &3. &4. &+. &6. &7.
"0!. &. "&. "". "2. "3. "4. "+. "6. "7. "#. ". 2&. 2". 22.
OR I IN "..LENGT%() LOOP A!SUBSTR(0I0"); I MOD(A02)!& T%EN CNT!CNT*"; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('NUMBER O E$EN DIGIT IS 'CNT); END;
W%P to accept a number and calculate the sum of numbers in even places DECLARE N NUMBER!,NUMBER; A $ARC%AR2("&); S NUMBER!&; BEGIN OR I IN "..LENGT%(N) LOOP A!SUBSTR(N0I0"); I MOD(I02)!& T%EN S!S*A; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('SUM O E$EN PLACE IS 'S); END;
"23. W%P to accept the emp details and calculate the bonus based on the following conditions i- f sal = 500 then bonus is 10? sal ii- f sal <3500 then bonus is 12? sal iiif sal <1000 then bonus is 13.5? sal 24. 2+. 26. 27. 2#. 2. 3&. 3". 32. 33. 34. 3+. 36. 37. 3#. 3. 4&.
DECLARE EMPNO$ NUMBER!,EMPNO$; SAL$ NUMBER; B NUMBER(702); BEGIN SELECT SAL INTO SAL$ ROM EMP W%ERE EMPNO!EMPNO$; I SAL$ BETWEEN +&& AND 3+&& T%EN B!SAL$-"&/"&&; ELSI SAL$ BETWEEN 3+&& AND "&&&& T%EN B!SAL$-"2/"&&; ELSI SAL$"&&&& T%EN B!SAL$-"3.+/"&&; END I; DBMS_OUTPUT.PUT_LINE('EMPNO 'EMPNO$); DBMS_OUTPUT.PUT_LINE('SALAR 'SAL$); DBMS_OUTPUT.PUT_LINE('BONUS 'B); END;
"1. W%P to accept the empno and displa# enamesalhiredate and calculate tadahralicgrosse@p and print all emp details. ta is 30? of salda is 20? of salhra is 15? of sallic is 5? of sal 42. DECLARE
43. EMPNO$ NUMBER!,EMPNO$; 44. ENAME$ EMP.ENAMEJTPE; 4+. SAL$ EMP.SALJTPE; 46. %IREDATE$ EMP.%IREDATEJTPE; 47. EP NUMBER(702); 4#. TA NUMBER(702); 4. DA NUMBER(702); +&. %RA NUMBER(702); +". LIC NUMBER(702); +2. GROSS NUMBER(702); +3. S NUMBER!&; +4. BEGIN ++. SELECT ENAME0SAL0%IREDATE INTO ENAME$0SAL$0%IREDATE$ ROM EMP W%ERE EMPNO!EMPNO$; +6. EP!ROUND(MONT%S_BETWEEN(SSDATE0%IREDATE$)/"203); +7. TA!SAL$-3&/"&&; +#. DA!SAL$-2&/"&&; +. %RA!SAL$-"+/"&&; 6&. LIC!SAL$-+/"&&; 6". GROSS!SAL$*TA*DA*%RALIC; 62. DBMS_OUTPUT.PUT_LINE('EMPNO 'EMPNO$); 63. DBMS_OUTPUT.PUT_LINE('ENAME 'ENAME$); 64. DBMS_OUTPUT.PUT_LINE('SALAR 'SAL$); 6+. DBMS_OUTPUT.PUT_LINE('EPERIENCE 'EP); 66. DBMS_OUTPUT.PUT_LINE('TA 'TA); 67. DBMS_OUTPUT.PUT_LINE('DA 'DA); 6#. DBMS_OUTPUT.PUT_LINE('%RA '%RA); 6. DBMS_OUTPUT.PUT_LINE('LIC 'LIC); 7&. DBMS_OUTPUT.PUT_LINE('GROSS 'GROSS); 7". END;
"72. W%P to accept the item no item namet#unit price and calculate the bill f the bill <500 then give discount 2? of bill amount and displa# the details 73. 74. 7+. 76. 77. 7#. 7. #&. #". #2. #3. #4. #+. #6. #7. ##. #. &. ". 2. 3. 4.
DECLARE INO NUMBER!,INO; INAME $ARC%AR2(+&)!',INAME'; QT NUMBER(+)!,QT; UP NUMBER(702)!,UP; DIS NUMBER(702)!&; BILL NUMBER(702); NET NUMBER(702); BEGIN BILL!QT-UP; I BILL+&& T%EN DIS!BILL-2/"&&; END I; NET!BILLDIS; DBMS_OUTPUT.PUT_LINE('ITEM NO 'INO); DBMS_OUTPUT.PUT_LINE('ITEM NAME 'INAME); DBMS_OUTPUT.PUT_LINE('QUANTIT 'QT); DBMS_OUTPUT.PUT_LINE('UNIT PRICE 'UP); DBMS_OUTPUT.PUT_LINE('BILL AMT 'BILL); DBMS_OUTPUT.PUT_LINE('DISCOUNT 'DIS); DBMS_OUTPUT.PUT_LINE('NET AMT 'NET); END;
""5. Write a program to generate seuence of numbers hori>ontall# from 1 to 25 4(;%84 C C%8(A%82)100-D 4EB &'8 B 1..25 ;''P CF*CGGH HGGD 4B ;''PD :IJ'KLPKL.PKLJ;B4)C-D 4BD ""6.
W%P to accept a empno and displa# empnonamesale@pdnamegrade and loc.
7. DECLARE #. EMPNO$ NUMBER!,EMPNO; . ENAME$ EMP.ENAMEJTPE; "&&&. %IREDATE$ DATE; "&&". SAL$ EMP.SALJTPE; "&&2. EP NUMBER; "&&3. DNAME$ DEPT.DNAMEJTPE; "&&4. GRADE$ SALGRADE.GRADEJTPE; "&&+. BEGIN "&&6. SELECT ENAME0SAL0%IREDATE0DNAME0GRADE INTO ENAME$0SAL$0%IREDATE$0DNAME$0GRADE$ ROM EMP0DEPT0SALGRADE "&&7. W%ERE EMPNO!EMPNO$ AND EMP.DEPTNO!DEPT.DEPTNO AND SAL BETWEEN LOSAL AND %ISAL; "&&#. EP!ROUND(MONT%S_BETWEEN(SSDATE0%IREDATE$)/"203); "&&. DBMS_OUTPUT.PUT_LINE('EMPNO 'EMPNO$); "&"&. DBMS_OUTPUT.PUT_LINE('ENAME 'ENAME$); "&"". DBMS_OUTPUT.PUT_LINE('SALAR 'SAL$); "&"2. DBMS_OUTPUT.PUT_LINE('EPERIENCE 'EP' EARS'); "&"3. DBMS_OUTPUT.PUT_LINE('DNAME 'DNAME$); "&"4. DBMS_OUTPUT.PUT_LINE('GRADE 'GRADE$); "&"+. END;
1016. W%P to accept a empno and displa# empnobased on e@perience calculate the bonus and store it into the bonus table f e@p <5 #ears then bonus is 1 month salar# f e@p between 5 and " #ears then bonus is 20? of annual salar# f e@p more than " #ears then bonus is 1 month sal plus 25? of annual salar# "&"7. DECLARE "&"#. EMPNO$ NUMBER!,EMPNO; "&". ENAME$ EMP.ENAMEJTPE; "&2&. %IREDATE$ DATE; "&2". SAL$ EMP.SALJTPE; "&22. EP NUMBER; "&23. DNAME$ DEPT.DNAMEJTPE; "&24. GRADE$ SALGRADE.GRADEJTPE; "&2+. BEGIN "&26. SELECT ENAME0SAL0%IREDATE0DNAME0GRADE INTO ENAME$0SAL$0%IREDATE$0DNAME$0GRADE$ ROM EMP0DEPT0SALGRADE "&27. W%ERE EMPNO!EMPNO$ AND EMP.DEPTNO!DEPT.DEPTNO AND SAL BETWEEN LOSAL AND %ISAL; "&2#. EP!ROUND(MONT%S_BETWEEN(SSDATE0%IREDATE$)/"203); "&2. DBMS_OUTPUT.PUT_LINE('EMPNO 'EMPNO$); "&3&. DBMS_OUTPUT.PUT_LINE('ENAME 'ENAME$); "&3". DBMS_OUTPUT.PUT_LINE('SALAR 'SAL$); "&32. DBMS_OUTPUT.PUT_LINE('EPERIENCE 'EP' EARS'); "&33. DBMS_OUTPUT.PUT_LINE('DNAME 'DNAME$); "&34. DBMS_OUTPUT.PUT_LINE('GRADE 'GRADE$); "&3+. END;
1036. W%P to accept the empno based upon the dname transfer the emps ie ma$e the changes in the emp table. Lransfer the emps from %ccounting dept to 8esearch 8esearch dept to 'peration 'pertion dept to Iales and Iales to %ccounting dept "&37. DECLARE "&3#. EMPNO$ NUMBER!,EMPNO; "&3. DNAME$ $ARC%AR2(2&); "&4&. DNAME$$ $ARC%AR2(2&); "&4". BEGIN "&42. SELECT DNAME INTO DNAME$ ROM EMP0DEPT W%ERE EMPNO!EMPNO$ AND EMP.DEPTNO!DEPT.DEPTNO; "&43. I DNAME$!'ACCOUNTING' T%EN "&44. DNAME$$!'RESEARC%'; "&4+. ELSI DNAME$!'RESEARC%' T%EN "&46. DNAME$$!'SALES'; "&47. ELSI DNAME$!'SALES' T%EN "&4#. DNAME$$!'OPERATIONS'; "&4. ELSI DNAME$!'OPERATIONS' T%EN "&+&. DNAME$$!'ACCOUNTING'; "&+". END I; "&+2. UPDATE EMP SET DEPTNO!(SELECT DEPTNO ROM DEPT W%ERE DNAME!DNAME$$) W%ERE EMPNO!EMPNO$; "&+3. END;
105. W%P to accept the empno and displa# all the details of emp. f emp doesnot e@ist displa# the appreciate message "&++. "&+6. "&+7. "&+#. "&+. "&6&. "&6". "&62. "&63. "&64. "&6+. "&66. "&67. "&6#. "&6. "&7&. "&7".
1072. "&73. "&74. "&7+. "&76. "&77. "&7#. "&7.
DECLARE EMPNO$ NUMBER!,EMPNO; EMP$ EMPJROWTPE; BEGIN SELECT - INTO EMP$ ROM EMP W%ERE EMPNO!EMPNO$; DBMS_OUTPUT.PUT_LINE('EMPNO 'EMP$.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME 'EMP$.ENAME); DBMS_OUTPUT.PUT_LINE('1OB 'EMP$.1OB); DBMS_OUTPUT.PUT_LINE('SALAR 'EMP$.SAL); DBMS_OUTPUT.PUT_LINE('%IREDATE 'EMP$.%IREDATE); DBMS_OUTPUT.PUT_LINE('DEPTNO 'EMP$.DEPTNO); DBMS_OUTPUT.PUT_LINE('MGRNO 'EMP$.MGR); DBMS_OUTPUT.PUT_LINE('COMMISSION 'EMP$.COMM); ECEPTION W%EN NO_DATA_OUND T%EN DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EIST'); END;
W%P to accept the empno and print all the details of empdept and salgrade DECLARE E EMPJROWTPE; D DEPTJROWTPE; S SALGRADEJROWTPE; BEGIN SELECT - INTO E ROM EMP W%ERE EMPNO!,EMPNO; SELECT - INTO D ROM DEPT W%ERE E.DEPTNO!DEPT.DEPTNO;
"&#&. %ISAL; "&#". ". ". ". "&#+. ". ". "&##.
SELECT - INTO S ROM SALGRADE W%ERE E.SAL BETWEEN LOSAL AND DBMS_OUTPUT.PUT_LINE('EMPNO 'E.EMPNO); DBMS_OUTPUT.PUT_LINE('DEPTNO 'D.DEPTNO); DBMS_OUTPUT.PUT_LINE('DNAME 'D.DNAME); DBMS_OUTPUT.PUT_LINE('LOCATION 'D.LOC); DBMS_OUTPUT.PUT_LINE('GRADE 'S.GRADE); DBMS_OUTPUT.PUT_LINE('%ISALAR 'S.%ISAL); DBMS_OUTPUT.PUT_LINE('LOWSALAR 'S.LOSAL); END;
10!". W%P to accept the mgrno and displa# the empnoenamesaldname and grade of all emps wor$ing under that mgr "&&. "&". "&2. "&3. MGR!MGR$ "&4. "&+. "&6. "&7. "&#. "&. ""&&. ""&". ""&2. ""&3. ""&4. ""&+. ""&6. ""&7. ""&#.
110".
DECLARE MGR$ NUMBER!,MGR$; CURSOR EMPCUR IS SELECT EMPNO0ENAME0SAL0DEPTNO0GRADE ROM EMP0SALGRADE W%ERE AND SAL BETWEEN LOSAL AND %ISAL; EMPCURJROWTPE; BEGIN OPEN EMPCUR; LOOP ETC% EMPCUR INTO ; EIT W%EN EMPCURJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMPNO '.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME '.ENAME); DBMS_OUTPUT.PUT_LINE('SALAR '.SAL); DBMS_OUTPUT.PUT_LINE('DEPTNO '.DEPTNO); DBMS_OUTPUT.PUT_LINE('GRADE '.GRADE); DBMS_OUTPUT.PUT_LINE('------------------'); END LOOP; CLOSE EMPCUR; END;
W%P to accept the empno and displa# the e@p with minimum 3 decimal places
"""&. DECLARE """". EMPNO$ NUMBER!,EMPNO$; """2. %IREDATE$ DATE; """3. EP$ NUMBER("&0+); """4. BEGIN """+. SELECT %IREDATE INTO %IREDATE$ ROM EMP W%ERE EMPNO!EMPNO$; """6. EP$!ROUND(MONT%S_BETWEEN(SSDATE0%IREDATE$)/"203); """7. DBMS_OUTPUT.PUT_LINE('EPERIENCE O EMP'EMPNO$' IS ' EP$' EARS '); """#. END; """.
1120. 1 12 123
Write a program to print the following series
123 1235 ""2". ""22. ""23. ""24. ""2+. ""26. ""27. ""2#. ""2. ""3&. ""3". ""32. ""33.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..I LOOP $!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
113. Write a program to print the following series 1 21 321 321 5321 ""3+. ""36. ""37. ""3#. ""3. ""4&. ""4". ""42. ""43. ""44. ""4+. ""46. ""47.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN RE$ERSE "..I LOOP $!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
11!. Write a program to print the following series 1235 123 123 12 1 ""4. ""+&. ""+". ""+2. ""+3. ""+4. ""++.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN RE$ERSE "..+ LOOP OR 1 IN "..I LOOP
""+6. ""+7. ""+#. ""+. ""6&. ""6".
$!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1162. "2.Write a program to print the following series 11111 22222 33333 55555 ""63. ""64. ""6+. ""66. ""67. ""6#. ""6. ""7&. ""7". ""72. ""73. ""74. ""7+.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..+ LOOP $!$' 'I; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1176. Write a program to print the following series 1235 1235 1235 1235 1235 ""77. ""7#. ""7. ""#&. ""#". ""#2. ""#3. ""#4. ""#+. ""#6. ""#7. ""##. ""#.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..+ LOOP $!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
11"0. Write a program to print the following series 5321
532 53 5 5 """. ""2. ""3. ""4. ""+. ""6. ""7. ""#. "". "2&&. "2&". "2&2. "2&3. "2&4. "2&+.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN RE$ERSE "..+ LOOP I I!1 T%EN $!$' '1; END I; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1206. Write a program to print the following series 55555 333 22 1 "2&7. "2&#. "2&. "2"&. "2"". "2"2. "2"3. "2"4. "2"+. "2"6. "2"7. "2"#. "2". "22&. "22". "222.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN RE$ERSE "..+ LOOP OR 1 IN "..+ LOOP I I!1 T%EN $!$' 'I; END I; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1223. Write a program to print the following series 1 22 333 55555
"224. "22+. "226. "227. "22#. "22. "23&. "23". "232. "233. "234. "23+. "236.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..I LOOP $!$' 'I; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1237. Write a program to print the following series 1 01 101 0101 10101 "23#. "23. "24&. "24". "242. "243. "244. "24+. "246. "247. "24#. "24. "2+&. "2+". "2+2.
DECLARE A NUMBER!"; $ $ARC%AR2(2&)!"; BEGIN DBMS_OUTPUT.PUT_LINE($); OR I IN "..4 LOOP I SUBSTR($0"0")!'"' T%EN $!'&'$; ELSE $!'"'$; END I; DBMS_OUTPUT.PUT_LINE($); END LOOP; END;
1253. Write a program to print the following series / // /// //// ///// "2+4. "2++. "2+6. "2+7. "2+#. "2+. "26&. "26". "262.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..I LOOP $!$' ''-'; END LOOP;
"263. "264. "26+. "266.
DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
1267. Write a program to print the following series / // /// //// ///// //// /// // / "26#. "26. "27&. "27". "272. "273. "274. "27+. "276. "277. "27#. "27. "2#&. "2#". "2#2. "2#3. "2#4. "2#+. "2#6. "2#7. "2##. "2#.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..I LOOP $!$' ''-'; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; OR I IN RE$ERSE "..+ LOOP OR 1 IN 2..I LOOP $!$' ''-'; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
12"0. Write a program to print the following series 1235 235 35 5 5 "2". "22. "23. "24. "2+. "26.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN I..+
"27. "2#. "2. "3&&. "3&". "3&2. "3&3.
LOOP $!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
130. Write a program to print the following series 5321 321 321 21 1 "3&+. "3&6. "3&7. "3&#. "3&. "3"&. "3"". "3"2. "3"3. "3"4. "3"+. "3"6. "3"7.
131!. "3". "32&. "32". "322. "323. "324. "32+. "326. "327. "32#. 'M); "32.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN RE$ERSE "..+ LOOP OR 1 IN RE$ERSE "..I LOOP $!$' '1; END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
W%P to accept 2 nos and find the sum and product of the nos and print the output DECLARE A NUMBER!,A; B NUMBER!,B; S NUMBER; M NUMBER; BEGIN S!A*B; M!A-B; DBMS_OUTPUT.PUT_LINE('SUM O ''A'' AND ''B'' IS 'S); DBMS_OUTPUT.PUT_LINE('PRODUCT O ''A'' AND ''B'' IS END;
1330. W%P to accept 2 nos and find the remainder when the first number is divided b# sencond)dont use mod function"33". "332. "333. "334. "33+. "336.
DECLARE A NUMBER!,A; B NUMBER!,B; C NUMBER; M NUMBER; BEGIN
"337. "33#. "33. "34&.
131. "342. "343. "344. "34+. "346. "347.
13!. "34. "3+&. "3+". "3+2. "3+3. "3+4. "3++. "3+6. "3+7. "3+#. "3+. "36&. "36". "362. "363. "364. "36+. "366. "367.
C!TRUNC(A/B); M!AC-B; DBMS_OUTPUT.PUT_LINE('REMAINDER IS 'M); END;
W%P to displa# all the %I( characters 0+"++!+57%+M++65+"0a+>++"7+122 BEGIN OR I IN "..2++ LOOP DBMS_OUTPUT.PUT_LINE(I''C%R(I)); END LOOP; END;
Print the following format ORACLE ORACL ORAC ORA OR O DECLARE STR $ARC%AR2("&)!',STR'; L $ARC%AR2("&); N NUMBER("+); BEGIN N!LENGT%(STR); W%ILE N!" LOOP L!SUBSTR(STR0"0N); N!N"; DBMS_OUTPUT.PUT_LINE(L); END LOOP; END;
136!. W%P to displa# NE'' :'8BBEN or NE'' %&L48B''BN or NE'' BEALN depending upon the current time "36. "37&. "37". "372. "373. "374. "37+. "376. "377. "37#. "37. "3#&.
13!1.
DECLARE %% NUMBER; BEGIN %%!TO_C%AR(SSDATE0'%%24'); I %%6 AND %%"2 T%EN DBMS_OUTPUT.PUT_LINE('GOOD MORNING'); ELSI %%!"2 AND %%"# T%EN DBMS_OUTPUT.PUT_LINE('GOOD ATERNOON'); ELSI %%!"# AND %%2+ T%EN DBMS_OUTPUT.PUT_LINE('GOOD N IG%T'); END I; END;
W%P to accept two strings and concat the two strings
"3#2. "3#3. "3#4. "3#+. "3#6. "3#7. "3##. "3#.
13"0.
DECLARE STR $ARC%AR2(2&)!',STR'; STR" $ARC%AR2(2&)!',STR"'; $ $ARC%AR2(4&); BEGIN $!STR''STR"; DBMS_OUTPUT.PUT_LINE($); END;
W%P to accept a string and count the no of charswords in that string
"3". "32. "33. "34. "3+. "36. "37. "3#. "3. "4&&. "4&". "4&2. "4&3. "4&4. "4&+. "4&6. "4&7.
DECLARE STR $ARC%AR2(2&)!',STR'; NOC NUMBER(4)!&; NOW NUMBER(4)!"; S C%AR; BEGIN OR I IN "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); NOC!NOC*"; I S!' ' T%EN NOW!NOW*"; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('T%E NO. O C%ARS 'NOC); DBMS_OUTPUT.PUT_LINE('T%E NO. O WORDS 'NOW); END;
10!.
W%P to accept the octal number and print it in decimal format
"4&. "4"&. "4"". "4"2. "4"3. "4"4. "4"+. "4"6. "4"7. "4"#. "4". "42&. "42". "422.
123.
DECLARE N $ARC%AR2(2&)!',N'; A NUMBER; P NUMBER!&; C C%AR; BEGIN A!LENGT%(N); OR I IN "..A LOOP C!SUBSTR(N0I0"); P!P*C-POWER(#0AI); END LOOP; DBMS_OUTPUT.PUT_LINE('T%E INTEGER O A GI$EN OCTAL IS 'P); END;
W%P to accept the mgr and find how man# emps are wor$ing under that mgr
"424. DECLARE "42+. MGR$ EMP.MGRJTPE!,MGRNO; "426. N NUMBER!&; "427. BEGIN "42#. SELECT COUNT(-) INTO N ROM EMP W%ERE MGR!MGR$; "42. DBMS_OUTPUT.PUT_LINE('NUMBER O EMPLOEE UNDER T%AT MANAGER ARE 'N);
"43&.
131.
END;
W%P to accept the empno and update the emplo#ee row on the following
"432. IK = 26&& :8 =!=*"&J K = 8 :8 8 @ :8 8F :5=8 "433. DECLARE "434. EMPNO$ EMP.EMPNOJTPE!,EMPNO; "43+. SAL$ NUMBER(702)!&; "436. BEGIN "437. SELECT SAL INTO SAL$ ROM EMP W%ERE EMPNO!EMPNO$; "43#. I SAL$ 26&& T%EN "43. SAL$!SAL$*SAL$-("&/"&&); "44&. END I; "44". UPDATE EMP SET SAL!SAL$ W%ERE EMPNO!EMPNO$; "442. DBMS_OUTPUT.PUT_LINE('EMPNO IS 'EMPNO$); "443. DBMS_OUTPUT.PUT_LINE('SAL IS 'SAL$); "444. END;
15. Write the flo#dHs triangle 1 23 56 7 ! " 10 11 12 13 1 15 16 17 1! 1" 20 21 ............... 7".............."1 "446. "447. "44#. "44. "4+&. "4+". "4+2. "4+3. "4+4. "4++. "4+6. "4+7. "4+#. "4+. "46&. "46". "462.
163. "464. "46+. "466. "467.
DECLARE N NUMBER!"; $ $ARC%AR2("&&); BEGIN OR I IN "..2 LOOP OR 1 IN "..I LOOP $!$' 'N; N!N*"; EIT W%EN N!2; END LOOP; DBMS_OUTPUT.PUT_LINE($); EIT W%EN N!2; $!NULL; END LOOP; END;
W%P to accept the real value and print integer value onl# DECLARE N NUMBER(703)!,N; A NUMBER(+); BEGIN
"46#. "46. "47&.
171. "472. "473. "474. "47+. "476. "477. "47#. "47. "4#&. "4#". "4#2. "4#3. "4#4. "4#+. "4#6. "4#7. "4##. "4#.
1"0. "4". "42. "43. "44. "4+. "46. "47. "4#. "4. "+&&. "+&". "+&2. "+&3. "+&4. "+&+. "+&6. "+&7. "+&#.
150". "+"&. "+"". "+"2. "+"3. "+"4. "+"+. "+"6.
A!TRUNC(N); DBMS_OUTPUT.PUT_LINE('REAL $ALUE IS 'A); END;
W%P to calculate the sum of n odd factorials DECLARE N NUMBER!,N; S NUMBER!&; NUMBER!"; BEGIN OR I IN "..N LOOP I MOD(I02)!& T%EN OR 1 IN "..I LOOP !-1; END LOOP; S!S*; !"; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('SUM 'S); END;
W%P to calculate the sum of n even factorials DECLARE N NUMBER!,N; S NUMBER!&; NUMBER!"; BEGIN OR I IN "..N LOOP I MOD(I02)!& T%EN OR 1 IN "..I LOOP !-1; END LOOP; S!S*; !"; END I; END LOOP; DBMS_OUTPUT.PUT_LINE('SUM 'S); END;
W%P to generate the nos which are prime and odd between 1 and 100 DECLARE N NUMBER; CNT NUMBER!&; BEGIN OR I IN ".."&& LOOP OR 1 IN "..I
"+"7. "+"#. "+". "+2&. "+2". "+22. "+23. "+24. "+2+. "+26. "+27. "+2#. "+2.
LOOP I MOD(I01)!& T%EN CNT!CNT*"; END I; END LOOP; I CNT ! 2 T%EN I MOD(I02)!& T%EN DBMS_OUTPUT.PUT_LINE(I); END I; END I; CNT!&; END LOOP; END;
1530. Write a program to generate following series 12 12 22 12 22 32 12 22 32 2 12 22 32 2 52 "+3". "+32. "+33. "+34. "+3+. "+36. "+37. "+3#. "+3. "+4&. "+4". "+42. "+43.
DECLARE $ $ARC%AR2(2&); BEGIN OR I IN "..+ LOOP OR 1 IN "..I LOOP $!$' '1C%R("7#); END LOOP; DBMS_OUTPUT.PUT_LINE($); $!NULL; END LOOP; END;
15.
&ind the roots of a uadratic euation
"+4+. "+46. "+47. "+4#. "+4. "++&. "++". "++2. "++3. "++4. "+++. "++6. "++7. "++#. "++. "+6&. "+6".
DECLARE A NUMBER(4)!,A; B NUMBER(4)!,B; C NUMBER(4)!,C; D NUMBER(#02); R" NUMBER(#02); R2 NUMBER(#02); BEGIN D!POWER(B02)4-A-C; I D ! & T%EN DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL'); ELSI D & T%EN R"!(B*SQRT(D))/2-A; R2!(BSQRT(D))/2-A; DBMS_OUTPUT.PUT_LINE('IRST ROOT IS 'R"); DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS 'R2); ELSE
"+62. "+63. "+64.
DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINAR'); END I; END;
1565. W%P 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 hori>ontall# "+66. "+67. "+6#. "+6. "+7&. "+7". "+72. "+73. "+74. "+7+. "+76. "+77. "+7#.
DECLARE A NUMBER!,A; B NUMBER!,B; $ $ARC%AR2("&&); BEGIN OR I IN A..B LOOP I MOD(I02)!& T%EN $!$' 'I; END I; END LOOP; DBMS_OUTPUT.PUT_LINE($); END;
157". W%P 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 hori>ontall# "+#&. "+#". "+#2. "+#3. "+#4. "+#+. "+#6. "+#7. "+##. "+#. "+&. "+". "+2.
15"3. "+4. "++. "+6. "+7. "+#. "+. "6&&. "6&". "6&2. "6&3. "6&4. "6&+. "6&6. "6&7.
DECLARE A NUMBER!,A; B NUMBER!,B; $ $ARC%AR2("&&); BEGIN OR I IN A..B LOOP I MOD(I02)!& T%EN $!$' 'I; END I; END LOOP; DBMS_OUTPUT.PUT_LINE($); END;
Write a program to accept a #ear and displa# the emps belongs to that #earO DECLARE NUMBER(4)!,EAR; CURSOR EAR IS SELECT - ROM EMP W%ERE TO_C%AR(%IREDATE0'')!; B EARJROWTPE; BEGIN OPEN EAR; LOOP ETC% EAR INTO B; EIT W%EN EARJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE);
"6&#. "6&. "6"&. "6"". "6"2.
1613. "6"4. "6"+. "6"6. "6"7. "6"#. "6". "62&. "62". "622. "623. "624. "62+. "626. "627. "62#. "62. "63&. "63". "632.
1633.
DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE EAR; END;
Write a program to accept a mgr and displa# who are wor$ing under that mgrO DECLARE MGR$ NUMBER(4)!,MGR; CURSOR AMGR IS SELECT - ROM EMP W%ERE MGR!MGR$; B AMGRJROWTPE; BEGIN OPEN AMGR; LOOP ETC% AMGR INTO B; EIT W%EN AMGRJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE AMGR; END;
Write a program to accept the grade and displa# emps belongs to that gradeO
"634. DECLARE "63+. GRADE$ S ALGRADE.GRADEJTPE!,GRADE; "636. CURSOR A IS "637. SELECT EMP.-0GRADE ROM EMP0SALGRADE W%ERE SAL BETWEEN LOSAL AND %ISAL AND GRADE!GRADE$; "63#. B AJROWTPE; "63. BEGIN "64&. OPEN A; "64". LOOP "642. ETC% A INTO B; "643. EIT W%EN AJNOTOUND; "644. DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); "64+. DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); "646. DBMS_OUTPUT.PUT_LINE('SAL IS ' B.SAL); "647. DBMS_OUTPUT.PUT_LINE('MGR NO IS ' B.MGR); "64#. DBMS_OUTPUT.PUT_LINE('COMM IS ' B.COMM); "64. DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); "6+&. DBMS_OUTPUT.PUT_LINE('GRADE IS ' B.GRADE); "6+". DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); "6+2. DBMS_OUTPUT.PUT_LINE('-------------------------'); "6+3. END LOOP; "6+4. CLOSE A; "6++. END;
1656.
Write a program to accept a deptno and displa# who are wor$ing in that deptO
"6+7. "6+#. "6+. "66&. "66". "662. "663. "664. "66+. "666. "667. "66#. "66. "67&. "67". "672. "673. "674. "67+. "676. "677. "67#.
167". "6#&. "6#". "6#2. "6#3. "6#4. "6#+. "6#6. "6#7. "6##. "6#. "6&. "6". "62. "63. "64. "6+. "66. "67. "6#. "6. "7&&.
DECLARE DEPT$ EMP.DEPTNOJTPE!,DEPTNO; CURSOR A IS SELECT - ROM EMP W%ERE DEPTNO!DEPT$; B AJROWTPE; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('SAL IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('MGR NO IS ' B.MGR); DBMS_OUTPUT.PUT_LINE('COMM IS ' B.COMM); DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' B.DEPTNO); DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE A; END;
Write a program to displa# all the information of emp tableO DECLARE CURSOR A IS SELECT - ROM EMP; B AJROWTPE; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('SAL IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('MGR NO IS ' B.MGR); DBMS_OUTPUT.PUT_LINE('COMM IS ' B.COMM); DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' B.DEPTNO); DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE A; END;
1701. Write a program to accept the location and displa# empno name sal date of 9oin and also displa# the total salar# avg salar# and no of empsO "7&2. "7&3. "7&4. "7&+. "7&6.
DECLARE LOC$ DEPT.LOCJTPE!',LOC'; TOT NUMBER("&02)!&; ASAL NUMBER("&02)!&; NOEMPS NUMBER(+)!&;
"7&7. CURSOR A IS "7&#. SELECT EMP.-0LOC ROM EMP0DEPT W%ERE EMP.DEPTNO!DEPT.DEPTNO AND LOC!LOC$; "7&. B AJROWTPE; "7"&. BEGIN "7"". OPEN A; "7"2. LOOP "7"3. ETC% A INTO B; "7"4. NOEMPS!NOEMPS*"; "7"+. TOT!TOT*B.SAL; "7"6. ASAL!TOT/NOEMPS; "7"7. EIT W%EN AJNOTOUND; "7"#. DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); "7". DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); "72&. DBMS_OUTPUT.PUT_LINE('SAL IS ' B.SAL); "72". DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); "722. DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' B.DEPTNO); "723. DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); "724. DBMS_OUTPUT.PUT_LINE('LOC IS ' B.LOC); "72+. DBMS_OUTPUT.PUT_LINE('TOT IS ' TOT); "726. DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' NOEMPS); "727. DBMS_OUTPUT.PUT_LINE('ASAL IS ' ASAL); "72#. DBMS_OUTPUT.PUT_LINE('-------------------------'); "72. END LOOP; "73&. CLOSE A; "73". END;
1732. Write a program to accept a range of salar# )that is lower boundar# and higher boundar#- and print the details of emps along with locgrade and e@pO "733. DECLARE "734. LOSAL$ S ALGRADE.LOSALJTPE!,LOSAL; "73+. %ISAL$ S ALGRADE.%ISALJTPE!,%ISAL; "736. EP NUMBER(+02); "737. CURSOR A IS "73#. SELECT EMP.-0LOC0GRADE ROM EMP0DEPT0SALGRADE W%ERE EMP.DEPTNO!DEPT.DEPTNO "73. AND SAL BETWEEN LOSAL$ AND %ISAL$ "74&. AND SAL BETWEEN LOSAL AND %ISAL; "74". B AJROWTPE; "742. BEGIN "743. OPEN A; "744. LOOP "74+. ETC% A INTO B; "746. EIT W%EN AJNOTOUND; "747. EP!MONT%S_BETWEEN(SSDATE0B.%IREDATE)/"2; "74#. DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); "74. DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); "7+&. DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); "7+". DBMS_OUTPUT.PUT_LINE('LOC IS ' B.LOC); "7+2. DBMS_OUTPUT.PUT_LINE('EP IS ' EP); "7+3. DBMS_OUTPUT.PUT_LINE('GRADE IS ' B.GRADE); "7+4. DBMS_OUTPUT.PUT_LINE('-------------------------'); "7++. END LOOP; "7+6. CLOSE A; "7+7. END;
175!.
Write a program to print all the details of emps accepting the 9obO
"7+. "76&. "76". "762. "763. "764. "76+. "766. "767. "76#. "76. "77&. "77". "772. "773. "774. "77+.
DECLARE 1OB$ EMP.1OBJTPE!',1OB'; CURSOR A IS SELECT - ROM EMP W%ERE 1OB!1OB$; B AJROWTPE; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMP NO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('EMP 1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE A; END;
1776. "777. "77#. "77. B "; "7#&. "7#". "7#2. "7#3. "7#4. "7#+. "7#6. "7#7. "7##. "7#. "7&. "7". "72. "73. "74. "7+. "76. "77. "7#. "7. "#&&. "#&". "#&2. "#&3. "#&4. "#&+. "#&6. "#&7.
Write a program to displa# the details of emps #ear wiseO DECLARE CURSOR EARS IS SELECT DISTINCT TO_C%AR(%IREDATE0'') EARS" ROM EMP ORDER EAR EARSJROWTPE; CURSOR A IS SELECT - ROM EMP W%ERE TO_C%AR(%IREDATE0'')!EAR.EARS"; B AJROWTPE; BEGIN DBMS_OUTPUT.ENABLE("&&&&); OPEN EARS; DBMS_OUTPUT.PUT_LINE('--------------------'); LOOP ETC% EARS INTO EAR; EIT W%EN EARSJNOTOUND; DBMS_OUTPUT.PUT_LINE('EAR ' EAR.EARS"); DBMS_OUTPUT.PUT_LINE('----------------------'); OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMPNO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('SALAR IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' B.DEPTNO); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE A; END LOOP; CLOSE EARS; END;
1!0!. Write a program to accept empno and print all the details along with loc and gradeO "#&. "#"&. "#"". "#"2. "#"3. "#"4. "#"+. "#"6. "#"7. "#"#. "#". "#2&. "#2". "#22. "#23. "#24. "#2+. "#26. "#27. "#2#. "#2. "#3&. "#3".
1!32. "#33. "#34. "#3+. "#36. "#37.
DECLARE EMPNO$ EMP.EMPNOJTPE!,EMPNO; CURSOR A IS SELECT EMP.-0GRADE0LOC ROM EMP0DEPT0SALGRADE W%ERE EMP.DEPTNO!DEPT.DEPTNO AND SAL BETWEEN LOSAL AND %ISAL AND EMPNO!EMPNO$; B AJROWTPE; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMPNO IS ' B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS ' B.ENAME); DBMS_OUTPUT.PUT_LINE('SALAR IS ' B.SAL); DBMS_OUTPUT.PUT_LINE('1OB IS ' B.1OB); DBMS_OUTPUT.PUT_LINE('%IREDATE IS ' B.%IREDATE); DBMS_OUTPUT.PUT_LINE('LOC IS ' B.LOC); DBMS_OUTPUT.PUT_LINE('GRADE IS ' B.GRADE); DBMS_OUTPUT.PUT_LINE('-------------------------'); END LOOP; CLOSE A; END;
Write a procedure to create #our own print statementO CREATE OR REPLACE PROCEDURE PRINT($ $ARC%AR2) IS BEGIN DBMS_OUTPUT.PUT_LINE($); END;
1!3!. Write a procedure to accept the deptno as parameter and displa# the details of that dept also displa# the total salar# no of emplo#ees ma@ sal and avg salO "#3. "#4&. "#4". "#42. "#43. "#44. "#4+. "#46. "#47. "#4#. "#4. "#+&. "#+". "#+2. "#+3. "#+4.
CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNO$ NUMBER) IS CURSOR A IS SELECT - ROM EMP W%ERE DEPTNO!DEPTNO$; B AJROWTPE; NOE NUMBER!&; TOT NUMBER!&; A$GS NUMBER(702)!&; MAS NUMBER(702)!&; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMPNO 'B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME 'B.ENAME);
"#++. "#+6. "#+7. "#+#. "#+. "#6&. "#6". "#62. "#63. "#64. "#6+. "#66. "#67. "#6#. "#6. "#7&. "#7". "#72.
DBMS_OUTPUT.PUT_LINE('1OB 'B.1OB); DBMS_OUTPUT.PUT_LINE('SAL 'B.SAL); DBMS_OUTPUT.PUT_LINE('%IREDATE 'B.%IREDATE); DBMS_OUTPUT.PUT_LINE('COMM 'B.COMM); DBMS_OUTPUT.PUT_LINE('----------------------'); TOT!TOT*B.SAL; NOE!NOE*"; I B.SALMAS T%EN MAS!B.SAL; END I; END LOOP; A$GS!TOT/NOE; DBMS_OUTPUT.PUT_LINE('NO O EMPLOEE 'NOE); DBMS_OUTPUT.PUT_LINE('TOTAL SALAR 'TOT); DBMS_OUTPUT.PUT_LINE('A$G SALAR 'A$GS); DBMS_OUTPUT.PUT_LINE('MA SALAR 'MAS); CLOSE A; END;
1!73. Write a procedure to accept two different numbers and print all odd numbers between the two given numbersO "#74. "#7+. "#76. "#77. "#7#. "#7. "##&. "##". "##2. "##3. "##4. "##+. "##6.
CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER0B NUMBER) IS N NUMBER(4); BEGIN N!A; W%ILE NB LOOP I MOD(N02)!& T%EN DBMS_OUTPUT.PUT_LINE(N); END I; N!N*"; END LOOP; END;
1!!7. Write a procedure to accept two different numbers and print even numbers between the two given numbersO "###. "##. "#&. "#". "#2. "#3. "#4. "#+. "#6. "#7. "##. "#. "&&.
CREATE OR REPLACE PROCEDURE E$ENNO(A NUMBER0B NUMBER) IS N NUMBER(4); BEGIN N!A; W%ILE NB LOOP I MOD(N02)!& T%EN DBMS_OUTPUT.PUT_LINE(N); END I; N!N*"; END LOOP; END;
1"01. Write a procedure to accept deptno as input and print the details of emps along with gradeO "&2. "&3. "&4. "&+. "&6. "&7. "&#. "&. ""&. """. ""2. ""3. ""4. ""+. ""6. ""7. ""#. "". "2&. "2". "22.
CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNO$ NUMBER) IS CURSOR A IS SELECT EMP.-0GRADE ROM EMP0SALGRADE W%ERE SAL BETWEEN LOSAL AND %ISAL AND DEPTNO!DEPTNO$; B AJROWTPE; BEGIN OPEN A; LOOP ETC% A INTO B; EIT W%EN AJNOTOUND; DBMS_OUTPUT.PUT_LINE('EMPNO IS 'B.EMPNO); DBMS_OUTPUT.PUT_LINE('ENAME IS 'B.ENAME); DBMS_OUTPUT.PUT_LINE('1OB IS 'B.1OB); DBMS_OUTPUT.PUT_LINE('SAL IS 'B.SAL); DBMS_OUTPUT.PUT_LINE('DEPTNO IS 'B.DEPTNO); DBMS_OUTPUT.PUT_LINE('GRADE IS 'B.GRADE); END LOOP; CLOSE A; END;
1"23. Write a procedure to accept a number as parameter and print its multiplication tableO "24. "2+. "26. "27. "2#. "2. "3&. "3". "32. "33. "34. "3+.
CREATE OR REPLACE PROCEDURE MULT(A NUMBER) IS B NUMBER(2) DEAULT "; C NUMBER(3); BEGIN W%ILE B!"& LOOP C!A-B; DBMS_OUTPUT.PUT_LINE(A'-'B'!'C); B!B*"; END LOOP; END;
1"36. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different hori>ontal linesO "37. "3#. "3. "4&. "4". "42. "43. "44. "4+. "46.
CREATE OR REPLACE PROCEDURE E$ENODD(A NUMBER0B NUMBER) IS N NUMBER; E$ $ARC%AR2("&&&); OD $ARC%AR2("&&&); BEGIN N!A; W%ILE NB LOOP
"47. "4#. "4. "+&. "+". "+2. "+3. "+4. "++. "+6.
1"57. "+#. "+. "6&. "6". "62. "63. "64. "6+. "66. "67. "6#. "6. "7&. "7". "72. "73.
1"7. "7+. "76. "77. "7#. "7. "#&. "#". "#2. "#3. "#4. "#+. "#6. "#7.
I MOD(N02)!& T%EN OD!OD' 'N; ELSE E$!E$' 'N; END I; N!N*"; END LOOP; DBMS_OUTPUT.PUT_LINE('T%E ODD NOS ARE 'OD); DBMS_OUTPUT.PUT_LINE('T%E E$EN NOS ARE 'E$); END;
Write a procedure to accept a string and chec$ whether it is palindrome or notO CREATE OR REPLACE PROCEDURE STRPAL(STR $ARC%AR2) IS STR" $ARC%AR2("&); S $ARC%AR2("&); BEGIN OR I IN RE$ERSE "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); STR"!STR"S; END LOOP; I STR"!STR T%EN DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME 'STR"); ELSE DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME 'STR"); END I; END;
Write a procedure to accept a string and print it in reverse orderO CREATE OR REPLACE PROCEDURE STRRE$(STR $ARC%AR2) IS STR" $ARC%AR2("&); S $ARC%AR2("&); BEGIN OR I IN RE$ERSE "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); STR"!STR"S; END LOOP; DBMS_OUTPUT.PUT_LINE('ORIGINAL 'STR); DBMS_OUTPUT.PUT_LINE('RE$ERSE 'STR"); END;
1"!!. Write a procedure to accept the empno and print all the details of emp along with e@p grade and locO "#. "&. "". "2. "3.
CREATE OR REPLACE PROCEDURE EMP_DET(EMPNO$ NUMBER) IS EP NUMBER(602); E EMPJROWTPE; GRADE$ SALGRADE.GRADEJTPE;
"4. LOC$ DEPT.LOCJTPE; "+. BEGIN "6. SELECT EMP.- INTO E ROM EMP W%ERE EMPNO!EMPNO$; "7. SELECT LOC INTO LOC$ ROM DEPT W%ERE DEPT.DEPTNO!E.DEPTNO; "#. SELECT GRADE INTO GRADE$ ROM SALGRADE W%ERE E.SAL BETWEEN LOSAL AND %ISAL; ". EP!MONT%S_BETWEEN(SSDATE0E.%IREDATE)/"2; 2&&&. DBMS_OUTPUT.PUT_LINE('EMPNO IS 'E.EMPNO); 2&&". DBMS_OUTPUT.PUT_LINE('ENAME IS 'E.ENAME); 2&&2. DBMS_OUTPUT.PUT_LINE('SAL IS 'E.SAL); 2&&3. DBMS_OUTPUT.PUT_LINE('1OB IS 'E.1OB); 2&&4. DBMS_OUTPUT.PUT_LINE('LOC IS 'LOC$); 2&&+. DBMS_OUTPUT.PUT_LINE('GRADE IS 'GRADE$); 2&&6. DBMS_OUTPUT.PUT_LINE('EP IS 'EP); 2&&7. END;
200!. Write a procedure to accept dname irrespective of case and print all the details of empsO 2&&. CREATE OR REPLACE PROCEDURE DETAILS(DNAME$ $ARC%AR2) 2&"&. IS 2&"". CURSOR A IS 2&"2. SELECT EMP.-0DNAME ROM EMP0DEPT W%ERE EMP.DEPTNO!DEPT.DEPTNO AND DNAME!DNAME$; 2&"3. B AJROWTPE; 2&"4. BEGIN 2&"+. OPEN A; 2&"6. LOOP 2&"7. ETC% A INTO B; 2&"#. EIT W%EN AJNOTOUND; 2&". DBMS_OUTPUT.PUT_LINE('EMPNO IS 'B.EMPNO); 2&2&. DBMS_OUTPUT.PUT_LINE('ENAME IS 'B.ENAME); 2&2". DBMS_OUTPUT.PUT_LINE('SAL IS 'B.SAL); 2&22. DBMS_OUTPUT.PUT_LINE('1OB IS 'B.1OB); 2&23. DBMS_OUTPUT.PUT_LINE('DNAME IS 'B.DNAME); 2&24. DBMS_OUTPUT.PUT_LINE('%IREDATE IS 'B.%IREDATE); 2&2+. END LOOP; 2&26. END;
2027. 2&2#. 2&2. 2&3&. 2&3". 2&32. 2&33. 2&34. 2&3+. 2&36. 2&37. 2&3#. 2&3. 2&4&. 2&4".
Write a procedure to accept a string and print it in reverse caseO CREATE OR REPLACE PROCEDURE S_R_CASE(STR $ARC%AR2) IS S $ARC%AR2("&); $ $ARC%AR2("&); N NUMBER(3); BEGIN OR I IN "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); N!ASCII(S); I N BETWEEN 6+ AND & T%EN $!$C%R(N*32); ELSE $!$C%R(N32);
2&42. 2&43. 2&44. 2&4+.
206.
END I; END LOOP; DBMS_OUTPUT.PUT_LINE('STRING IN RE$ERSE CASE IS '$); END;
Write a function to accept the empno and return e@p with minimum 3 decimalO
2&47. NUMBER 2&4#. 2&4. 2&+&. 2&+". 2&+2. 2&+3. 2&+4. 2&++.
2056.
CREATE OR REPLACE UNCTION E_DETAILS(EMPNO$ NUMBER) RETURN IS %IREDATE$ EMP.%IREDATEJTPE; EP NUMBER(603); BEGIN SELECT %IREDATE INTO %IREDATE$ ROM EMP W%ERE EMPNO!EMPNO$; EP!MONT%S_BETWEEN(SSDATE0%IREDATE$)/"2; RETURN EP; END;
Write a function to accept a number and print the factorial of that numberO
2&+7. 2&+#. 2&+. 2&6&. 2&6". 2&62. 2&63. 2&64. 2&6+. 2&66.
CREATE OR REPLACE UNCTION AC(NUM NUMBER) RETURN NUMBER IS ACT NUMBER(4)!"; BEGIN OR I IN RE$ERSE "..NUM LOOP ACT!ACT-I; END LOOP; RETURN ACT; END;
2067. Write a function to accept a grade and return the number of emps belongs to that gradeO 2&6#. $ARC%AR2 2&6. 2&7&. 2&7". 2&72. 2&73. 2&74. 2&7+.
2076. mgrO 2&77. 2&7#. 2&7. 2&#&. 2&#". 2.
CREATE OR REPLACE UNCTION EMPGRADE(GRADE$ NUMBER) RETURN IS N NUMBER(4); BEGIN SELECT COUNT(-) INTO N ROM EMP0SALGRADE W%ERE SAL BETWEEN LOSAL AND %ISAL AND GRADE!GRADE$; RETURN 'NO O EMPS ARE'N; END;
Write a program to accept the mgr number and return no of emp wor$ing at that
CREATE OR REPLACE UNCTION N_EMPS(MGR$ NUMBER) RETURN $ARC%AR2 IS N NUMBER(4); BEGIN SELECT COUNT(-) INTO N ROM EMP W%ERE MGR!MGR$; RETURN 'T%E NO O EMPS ARE WORHING UNDER T%IS MGR IS 'N;
2.
20!.
END;
Write a function to accept a character string and print it in reverse caseO
2&#+. $ARC%AR2 2. 2. 2&##. 2&#. 2&&. 2&". 2&2. 2&3. 2&4. 2&+. 2&6. 2&7. 2&#. 2&. 2"&&. 2"&". 2"&2.
2103.
CREATE OR REPLACE UNCTION RE$ERSE(STR $ARC%AR2) RETURN IS STR" $ARC%AR2(2&); S $ARC%AR2(2&); N NUMBER(4); BEGIN OR I IN "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); N!ASCII(S); I N BETWEEN 6+ AND & T%EN STR"!STR"C%R(N*32); ELSE STR"!STR"C%R(N32); END I; END LOOP; RETURN 'T%E RE$ERSE CASE IS 'STR"; END;
Write a function to accept a string and chec$ whether it is palindrome or notO
2"&4. $ARC%AR2 2"&+. 2"&6. 2"&7. 2"&#. 2"&. 2""&. 2""". 2""2. 2""3. 2""4. 2""+. 2""6. 2""7. 2""#. 2"".
CREATE OR REPLACE UNCTION STRPAL"(STR $ARC%AR2) RETURN IS STR" $ARC%AR2("&); S $ARC%AR2("&); BEGIN OR I IN RE$ERSE "..LENGT%(STR) LOOP S!SUBSTR(STR0I0"); STR"!STR"S; END LOOP; I STR"!STR T%EN RETURN 'IT IS PALINDROME 'STR"; ELSE RETURN 'IT IS NOT PALINDROME 'STR"; END I; END;
2120. Write a function to accept the grade and return ma@ tot avg salar# and number of emps belongs to that grade as script without using an# group functionsO 2"2". CREATE OR REPLACE UNCTION EMP_DETAILS_SCRIPT (GRADE$ SALGRADE.GRADEJTPE) RETURN $ARC%AR2 2"22. IS 2"23. $ $ARC%AR2(3&&&&); 2"24. CURSOR EMP_CUR IS 2"2+. SELECT EMP.-0GRADE0DNAME ROM DEPT0EMP0SALGRADE 2"26. W%ERE GRADE!GRADE$ AND EMP.DEPTNO!DEPT.DEPTNO AND
2"27. SAL BETWEEN LOSAL AND %ISAL; 2"2#. EMP_CUR_$ EMP_CURJROWTPE; 2"2. MASAL EMP.SALJTPE!&; 2"3&. MINSAL EMP.SALJTPE; 2"3". A$GSAL NUMBER(602); 2"32. SUMSAL NUMBER("&02)!&; 2"33. CNT NUMBER!&; 2"34. LAG C%AR!&; 2"3+. E ECEPTION; 2"36. BEGIN 2"37. OPEN EMP_CUR; 2"3#. LOOP 2"3. ETC% EMP_CUR INTO EMP_CUR_$; 2"4&. EIT W%EN EMP_CURJNOTOUND; 2"4". I MASAL EMP_CUR_$.SAL T%EN 2"42. MASAL!EMP_CUR_$.SAL; 2"43. END I; 2"44. I LAG!& T%EN 2"4+. MINSAL!EMP_CUR_$.SAL; 2"46. LAG!"; 2"47. ELSI LAG!" AND MINSAL EMP_CUR_$.SAL T%EN 2"4#. MINSAL!EMP_CUR_$.SAL; 2"4. END I; 2"+&. SUMSAL!SUMSAL*EMP_CUR_$.SAL; 2"+". CNT!CNT*"; 2"+2. ENDLOOP; 2"+3. I CNT!& T%EN 2"+4. RAISE E; 2"++. END I; 2"+6. A$GSAL!SUMSAL/CNT; 2"+7. $!'T%E MAIMUM SALAR O GRADE' GRADE$' IS'MASAL' MINIMUM SALAR IS'MINSAL 2"+#. 'A$ERAGE SALAR IS'A$GSAL' TOTAL EMPS WORHING OR T%IS GRADE ARE'CNT; 2"+. CLOSE EMP_CUR; 2"6&. RETURN $; 2"6". ECEPTION 2"62. W%EN E T%EN 2"63. RETURN 'T%ERE IS NO EMPLOEE WORHING OR T%IS GRADE0 C%ECH AND REENTER T%E GRADE....'; 2"64. END;
2165. (reate a pac$age to store the following procedure for multiplication tableeven+ odd function for factorial and function for palindromeO 2"66. 2"67. 2"6#. 2"6. 2"7&. 2"7". 2"72. 2"73. 2"74. 2"7+. 2"76.
CREATE OR REPLACE PACHAGE DATA IS PROCEDURE MULT(A NUMBER); PROCEDURE E$EN_ODD(N NUMBER); UNCTION ACT(N NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REERENCES(ACT0WNDS); UNCTION PALEN(SRT $ARC%AR2) RETURN $ARC%AR2; PRAGMA RESTRICT_REERENCES(PALEN0WNDS); END; CREATE OR REPLACE PACHAGE BOD DATA
2"77. 2"7#. 2"7. 2"#&. 2"#". 2"#2. 2"#3. 2"#4. 2"#+. 2"#6. 2"#7. 2"##. 2"#. 2"&. 2"". 2"2. 2"3. 2"4. 2"+. 2"6. 2"7. 2"#. 2". 22&&. 22&". 22&2. 22&3. 22&4. 22&+. 22&6. 22&7. 22&#. 22&. 22"&. 22"". 22"2. 22"3. 22"4. 22"+. 22"6. 22"7. 22"#. 22". 222&. 222". 2222. 2223.
222. 222+. 2226. 2227. 222#. 222. 223&.
IS PROCEDURE MULT(A NUMBER) IS M NUMBER; BEGIN OR I IN ".."& LOOP M!A-I; DBMS_OUTPUT.PUT_LINE(A'-'I'!'M); END LOOP; END; PROCEDURE E$EN_ODD(N NUMBER) IS BEGIN I MOD(N02)!& T%EN DBMS_OUTPUT.PUT_LINE(N' IS E$EN NUMBER'); ELSE DBMS_OUTPUT.PUT_LINE(N' IS NOT E$EN NUMBER'); END I; END; UNCTION ACT(N NUMBER) RETURN NUMBER IS NUMBER!"; BEGIN OR I IN "..N LOOP !-I; END LOOP; RETURN ; END; UNCTION PALEN(SRT $ARC%AR2) RETURN $ARC%AR2 IS S C%AR; $ $ARC%AR2(+&); BEGIN OR I IN RE$ERSE "..LENGT%(SRT) LOOP S!SUBSTR(SRT0I0"); $!$S; END LOOP; I $!SRT T%EN RETURN 'PALINDROME'; ELSE RETURN 'NOT PALINDROME'; END I; END; END;
Write a database trigger halt the transaction on Iunda# on 4:P table CREATE OR REPLACE TRIGGER SUN_TRI ATER INSERT OR UPDATE OR DELETE ON EMP DECLARE D $ARC%AR2(2&&); BEGIN D!TO_C%AR(SSDATE0'D');
223". I D!'SUN' T%EN 2232. RAISE_APPLICATION_ERROR(2&&&+0'TODA IS SUNDA TRANSACTION NOT ALLOWED TODA'); 2233. END I; 2234. END;
2235.
Write a database trigger halt the transaction of KI48 I('LL on table 4:P
2236. 2237. 223#. 223. 224&. SCOTT'); 224". 2242.
CREATE OR REPLACE TRIGGER SCOTT_TRI BEORE INSERT OR UPDATE OR DELETE ON EMP BEGIN I USER ! 'SCOTT' T%EN RAISE_APPLICATION_ERROR(2&&&60'TRANSACTION NOT ALLOWED OR END I; END;
223. Write a database trigger halt the transaction between the the time 6pm to 10am on table emp 2244. CREATE OR REPLACE TRIGGER O$ER_TIME_TRI 224+. BEORE INSERT OR DELETE OR UPDATE ON EMP 2246. DECLARE 2247. T NUMBER; 224#. BEGIN 224. T!TO_C%AR(SSDATE0'%%24'); 22+&. I T NOT BETWEEN "& AND "# T%EN 22+". RAISE_APPLICATION_ERROR(2&&&70'TIME ALREAD O$ER.....TRANSACTION NOT ALLOWED NOW'); 22+2. END I; 22+3. END; 22+4. "+4.W@:8 :58 :@8 : =: :8 ::@ K :8 8F=88 SALESMAN 22++. PRESIDENT 22+6. CREATE OR REPLACE TRIGGER SALES_PRI 22+7. BEORE INSERT OR UPDATE OR DELETE ON EMP 22+#. OR EAC% ROW 22+. W%EN (OLD.1OB IN ('SALESMAN'0'PRESIDENT') OR 226&. NEW.1OB IN ('SALESMAN'0'PRESIDENT')) 226". BEGIN 2262. RAISE_APPLICATION_ERROR(2&&�'TRANSACTION NOT ALLOWED OR SALESMAN AND PRESIDENT....'); 2263. END;
226. Write a database trigger stroe the username t#pe of transaction date of transaction and time of transaction of table emp into the table 4:PJ;'E 226+. 2266. 2267. 226#. 226. 227&. 227".
CREATE OR REPLACE TRIGGER TRANS_TPE ATER INSERT OR UPDATE OR DELETE ON EMP DECLARE $ $ARC%AR2(+&); BEGIN I INSERTING T%EN $!'I';
2272. ELSI UPDATING T%EN 2273. $!'U'; 2274. ELSE 227+. $!'D'; 2276. END I; 2277. INSERT INTO EMP_LOG $ALUES (USER0$0SSDATE0TO_C%AR(SSDATE0'%%MISS')); 227#. END;
227".
Write a database trigger store the deleted data of 4:P table in 4:P4; table
22#&. CREATE OR REPLACE TRIGGER DEL_TRI 22#". BEORE DELETE ON EMP 22#2. OR EAC% ROW 22#3. BEGIN 22#4. INSERT INTO EMPDEL 22#+. $ALUES (OLD.EMPNO0OLD.ENAME0OLD.1OB0OLD.MGR0OLD.%IREDATE0OLD.SAL0OLD.COM M0 22#6. OLD.DEPTNO0SSDATE0TO_C%AR(SSDATE0'%%MISS)); 22#7. END;
22!!. Write a database trigger displa# the message when the inserting hiredate is greater than s#stem date 22#. 22&. 22". 222. 223. 224. 22+. 226.
CREATE OR REPLACE TRIGGER %IREDATE_O$ER ATER INSERT ON EMP OR EAC% ROW BEGIN I NEW.%IREDATE SSDATE T%EN RAISE_APPLICATION_ERROR(2&&&0'IN$ALID %IREDATE.....'); END I; END;
22"7. Write a database trigger halt the transaction of 4:P table if the deptno is does not e@ist in the dept table 22#. CREATE OR REPLACE TRIGGER DEPT_NO 22. BEORE INSERT OR UPDATE OR DELETE ON EMP 23&&. OR EAC% ROW 23&". DECLARE 23&2. DNO NUMBER!&; 23&3. BEGIN 23&4. SELECT COUNT(-) INTO DNO ROM DEPT W%ERE DEPTNO!NEW.DEPTNO; 23&+. DBMS_OUTPUT.PUT_LINE(DNO); 23&6. I DNO!& T%EN 23&7. RAISE_APPLICATION_ERROR(2&&&0'DEPTNO NOT EIST IN DEPT TABLE....'); 23&#. END I; 23&. END;
2310.
Write a database trigger add 8s 500 if the inserting salar# is less than 8s 1000
23"". 23"2. 23"3. 23"4. 23"+. 23"6. 23"7. 23"#.
CREATE OR REPLACE TRIGGER SAL_ADD BEORE INSERT ON EMP OR EAC% ROW BEGIN I NEW.SAL ! "&&& T%EN NEW.SAL!NEW.SAL*+&&; END I; END;
231". Write a database trigger give the appropriate message if the record e@ceed more than 100 on 4:P table 232&. CREATE OR REPLACE TRIGGER EMP_O$ER_REC 232". ATER INSERT ON EMP 2322. DECLARE 2323. R NUMBER; 2324. BEGIN 232+. SELECT COUNT(-) INTO R ROM EMP; 2326. I R!"&& T%EN 2327. RAISE_APPLICATION_ERROR(2&&&0'"&& RECORD ALLOWED IN EMP TABLE.....'); 232#. END I; 232. END;
2330. 233". 2332. 2333. 2334. 233+. 2336. 2337. 233#. 233. 234&. 234". 2342. 2343. 2344. 234+. 2346. 2347. 234#. 234. 23+&. 23+". 23+2. 23+3. 23+4. 23++. 23+6. 23+7. 23+#. 23+.
Write a program to month and #ear and displa# the (alendar of that month. DECLARE D NUMBER!"; M $ARC%AR2("&)!',MONT%'; NUMBER!,EAR; C C%AR(2&); $ $ARC%AR2(+&&); N NUMBER; BEGIN N!TO_C%AR(LAST_DA(D''M'')0'DD'); C! TO_C%AR(TO_DATE(D''M'')0'D'); 5_>:F>:.F>:_=@8('---------------------------------'); 5_>:F>:.F>:_=@8('- 'M''' -'); 5_>:F>:.F>:_=@8('-SUN MON TUE WED T%U RI SAT -'); 5_>:F>:.F>:_=@8('----------------------------------'); I C!'MON' T%EN $!' '; ELSI C!'TUE' T%EN $!' '; ELSI C!'WED' T%EN $!' '; ELSI C!'T%U' T%EN $!' '; ELSI C!'RI' T%EN $!' '; ELSI C!'SAT' T%EN $!' '; END I; OR I IN "..N LOOP
