oracle ques answer

Write a program to generate sequence of numbers horizontally from 1 to 25
DECLARE
V VARCHAR2(100);
BEGIN
FOR I IN 1..25
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/
2.WAP to accept a empno and display empno,name,sal,exp,dname,grade and loc.
DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
/
3.WAP to accept a empno and display empno,based on experience calculate the bonus and store it into the bonus table
If exp > 5 years then bonus is 1 month salary
If exp between 5 and 9 years then bonus is 20% of annual salary
If exp more than 9 years then bonus is 1 month sal plus 25% of annual salary
DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
/
4.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
and Sales to Accounting dept
DECLARE
EMPNOV NUMBER:=&EMPNO;
DNAMEV VARCHAR2(20);
DNAMEVV VARCHAR2(20);
BEGIN
SELECT DNAME INTO DNAMEV FROM EMP,DEPT WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO;
IF DNAMEV='ACCOUNTING' THEN
DNAMEVV:='RESEARCH';
ELSIF DNAMEV='RESEARCH' THEN
DNAMEVV:='SALES';
ELSIF DNAMEV='SALES' THEN
DNAMEVV:='OPERATIONS';
ELSIF DNAMEV='OPERATIONS' THEN
DNAMEVV:='ACCOUNTING';
END IF;
UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=DNAMEVV) WHERE EMPNO=EMPNOV;
END;
/
5.WAP to accept the empno and display all the details of emp. If emp doesnot exist display the appreciate message
DECLARE
EMPNOV NUMBER:=&EMPNO;
EMPV EMP%ROWTYPE;
BEGIN
SELECT * INTO EMPV FROM EMP WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPV.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||EMPV.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB '||EMPV.JOB);
DBMS_OUTPUT.PUT_LINE('SALARY '||EMPV.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE '||EMPV.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||EMPV.DEPTNO);
DBMS_OUTPUT.PUT_LINE('MGRNO '||EMPV.MGR);
DBMS_OUTPUT.PUT_LINE('COMMISSION '||EMPV.COMM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EXIST');
END;
/
6.WAP to accept the empno and print all the details of emp,dept and salgrade
DECLARE
E EMP%ROWTYPE;
D DEPT%ROWTYPE;
S SALGRADE%ROWTYPE;
BEGIN
SELECT * INTO E FROM EMP WHERE EMPNO=&EMPNO;
SELECT * INTO D FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO;
SELECT * INTO S FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
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('HISALARY '||S.HISAL);
DBMS_OUTPUT.PUT_LINE('LOWSALARY '||S.LOSAL);
END;
/
7.WAP to accept the mgrno and display the empno,ename,sal,dname and grade of all emps working under that mgr
DECLARE
MGRV NUMBER:=&MGRV;
CURSOR EMPCUR IS
SELECT EMPNO,ENAME,SAL,DEPTNO,GRADE FROM EMP,SALGRADE WHERE MGR=MGRV AND SAL BETWEEN LOSAL AND HISAL;
X EMPCUR%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO X;
EXIT WHEN EMPCUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO '||X.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||X.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY '||X.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||X.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE '||X.GRADE);
DBMS_OUTPUT.PUT_LINE('******************');
END LOOP;
CLOSE EMPCUR;
END;
/
8.WAP to accept the empno and display the exp with minimum 3 decimal places
DECLARE
EMPNOV NUMBER:=&EMPNOV;
HIREDATEV DATE;
EXPV NUMBER(10,5);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXPV:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE OF EMP'||EMPNOV||' IS '||EXPV||' YEARS ');
END;
/
9.Write a program to print the following series
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
10.Write a program to print the following series
1
2 1
3 2 1
4 3 2 1
5 4 3 2 1
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
11.Write a program to print the following series
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
12.Write a program to print the following series
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
13.Write a program to print the following series
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
14.Write a program to print the following series
5 4 3 2 1
5 4 3 2
5 4 3
5 4
5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..5
LOOP
IF I<=J THEN
V:=V||' '||J;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
15.Write a program to print the following series
5 5 5 5 5
4 4 4 4
3 3 3
2 2
1
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..5
LOOP
IF I>=J THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
16.Write a program to print the following series
1
2 2
3 3 3
4 4 4 4
5 5 5 5 5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
17.Write a program to print the following series
1
0 1
1 0 1
0 1 0 1
1 0 1 0 1
DECLARE
A NUMBER:=1;
V VARCHAR2(20):=1;
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
FOR I IN 1..4
LOOP
IF SUBSTR(V,1,1)='1' THEN
V:='0'||V;
ELSE
V:='1'||V;
END IF;
DBMS_OUTPUT.PUT_LINE(V);
END LOOP;
END;
/
18.Write a program to print the following series
*
* *
* * *
* * * *
* * * * *
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
19.Write a program to print the following series
*
* *
* * *
* * * *
* * * * *
* * * *
* * *
* *
*
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
FOR I IN REVERSE 1..5
LOOP
FOR J IN 2..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
20.Write a program to print the following series
1 2 3 4 5
2 3 4 5
3 4 5
4 5
5
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN I..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
21.Write a program to print the following series
5 4 3 2 1
4 3 2 1
3 2 1
2 1
1
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
22.WAP 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 OF '||'A'||' AND '||'B'||' IS '||S);
DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
END;
/
23.WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function)
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER;
M NUMBER;
BEGIN
C:=TRUNC(A/B);
M:=A-C*B;
DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
END;
/
24.WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122
BEGIN
FOR I IN 1..255
LOOP
DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
END LOOP;
END;
/
25.Print the following format
ORACLE
ORACL
ORAC
ORA
OR
O
DECLARE
STR VARCHAR2(10):='&STR';
L VARCHAR2(10);
N NUMBER(15);
BEGIN
N:=LENGTH(STR);
WHILE N>=1
LOOP
L:=SUBSTR(STR,1,N);
N:=N-1;
DBMS_OUTPUT.PUT_LINE(L);
END LOOP;
END;
/
26.WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time
DECLARE
HH NUMBER;
BEGIN
HH:=TO_CHAR(SYSDATE,'HH24');
IF HH>6 AND HH<12 THEN
DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
ELSIF HH>=12 AND HH<18 THEN
DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
ELSIF HH>=18 AND HH<25 THEN
DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
END IF;
END;
/
27.WAP to accept two strings and concat the two strings
DECLARE
STR VARCHAR2(20):='&STR';
STR1 VARCHAR2(20):='&STR1';
V VARCHAR2(40);
BEGIN
V:=STR||''||STR1;
DBMS_OUTPUT.PUT_LINE(V);
END;
/
28.WAP to accept a string and count the no of chars,words in that string
DECLARE
STR VARCHAR2(20):='&STR';
NOC NUMBER(4):=0;
NOW NUMBER(4):=1;
S CHAR;
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
NOC:=NOC+1;
IF S=' ' THEN
NOW:=NOW+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
END;
/
29.WAP to accept the octal number and print it in decimal format
DECLARE
N VARCHAR2(20):='&N';
A NUMBER;
P NUMBER:=0;
C CHAR;
BEGIN
A:=LENGTH(N);
FOR I IN 1..A
LOOP
C:=SUBSTR(N,I,1);
P:=P+C*POWER(8,A-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
END;
/
30.WAP to accept the mgr and find how many emps are working under that mgr
DECLARE
MGRV EMP.MGR%TYPE:=&MGRNO;
N NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
END;
/
31.WAP to accept the empno and update the employee row on the following
If sal < 2600 then sal=sal+10% of sal make the changes in the emp table
DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
SALV NUMBER(7,2):=0;
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV < 2600 THEN
SALV:=SALV+SALV*(10/100);
END IF;
UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
END;
/
32.Write the floyd's triangle
1
2 3
4 5 6
7 8 9 10
11 12 13 14 15
16 17 18 19 20 21
...............
79..............91
DECLARE
N NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..92
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||N;
N:=N+1;
EXIT WHEN N=92;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
EXIT WHEN N=92;
V:=NULL;
END LOOP;
END;
/
33.WAP to accept the real value and print integer value only
DECLARE
N NUMBER(7,3):=&N;
A NUMBER(5);
BEGIN
A:=TRUNC(N);
DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
END;
/
34.WAP to calculate the sum of n odd factorials
DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)!=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/
35.WAP to calculate the sum of n even factorials
DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/
36.WAP to generate the nos which are prime and odd between 1 and 100
DECLARE
N NUMBER;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
FOR J IN 1..I
LOOP
IF MOD(I,J)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
IF CNT <= 2 THEN
IF MOD(I,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END IF;
CNT:=0;
END LOOP;
END;
/
37.Write a program to generate following series
12
12 22
12 22 32
12 22 32 42
12 22 32 42 52
DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J||CHR(178);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/
38.Find the roots of a quadratic equation
DECLARE
A NUMBER(4):=&A;
B NUMBER(4):=&B;
C NUMBER(4):=&C;
D NUMBER(8,2);
R1 NUMBER(8,2);
R2 NUMBER(8,2);
BEGIN
D:=POWER(B,2)-4*A*C;
IF D = 0 THEN
DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
ELSIF D > 0 THEN
R1:=(-B+SQRT(D))/2*A;
R2:=(-B-SQRT(D))/2*A;
DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
ELSE
DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
END IF;
END;
/
39.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
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/
40.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
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)!=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

PRAKASH MISHRA