PL/SQL sample queries Part - IV

75.WAP to accept a year as i/p & find how many even number present in that year
DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
/
76.WAP to accept a year as i/p & find how many odd number present in that year
DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)!=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
/
77.WAP to accept a number and calculate the sum of numbers in even places
DECLARE
N NUMBER:=&NUMBER;
A VARCHAR2(10);
S NUMBER:=0;
BEGIN
FOR I IN 1..LENGTH(N)
LOOP
A:=SUBSTR(N,I,1);
IF MOD(I,2)=0 THEN
S:=S+A;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF EVEN PLACE IS '||S);
END;
/
78.WAP to accept the emp details and calculate the bonus based on the following conditions
i) If sal < 500 then bonus is 10% sal
ii) If sal > 3500 then bonus is 12% sal
iii) If sal > 1000 then bonus is 13.5% sal
DECLARE
EMPNOV NUMBER:=&EMPNOV;
SALV NUMBER;
B NUMBER(7,2);
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV BETWEEN 500 AND 3500 THEN
B:=SALV*10/100;
ELSIF SALV BETWEEN 3500 AND 10000 THEN
B:=SALV*12/100;
ELSIF SALV>10000 THEN
B:=SALV*13.5/100;
END IF;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('BONUS '||B);
END;
/
79.WAP to accept the empno and display ename,sal,hiredate and calculate ta,da,hra,lic,gross,exp and
print all emp details. ta is 30% of sal,da is 20% of sal,hra is 15% of sal,lic is 5% of sal
DECLARE
EMPNOV NUMBER:=&EMPNOV;
ENAMEV EMP.ENAME%TYPE;
SALV EMP.SAL%TYPE;
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(7,2);
TA NUMBER(7,2);
DA NUMBER(7,2);
HRA NUMBER(7,2);
LIC NUMBER(7,2);
GROSS NUMBER(7,2);
S NUMBER:=0;
BEGIN
SELECT ENAME,SAL,HIREDATE INTO ENAMEV,SALV,HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
TA:=SALV*30/100;
DA:=SALV*20/100;
HRA:=SALV*15/100;
LIC:=SALV*5/100;
GROSS:=SALV+TA+DA+HRA-LIC;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP);
DBMS_OUTPUT.PUT_LINE('TA '||TA);
DBMS_OUTPUT.PUT_LINE('DA '||DA);
DBMS_OUTPUT.PUT_LINE('HRA '||HRA);
DBMS_OUTPUT.PUT_LINE('LIC '||LIC);
DBMS_OUTPUT.PUT_LINE('GROSS '||GROSS);
END;
/
80.WAP to accept the item no ,item name,qty,unit price and calculate the bill
If the bill > 500 then give discount 2% of bill amount and display the details
DECLARE
INO NUMBER:=&INO;
INAME VARCHAR2(50):='&INAME';
QTY NUMBER(5):=&QTY;
UP NUMBER(7,2):=&UP;
DIS NUMBER(7,2):=0;
BILL NUMBER(7,2);
NET NUMBER(7,2);
BEGIN
BILL:=QTY*UP;
IF BILL>500 THEN
DIS:=BILL*2/100;
END IF;
NET:=BILL-DIS;
DBMS_OUTPUT.PUT_LINE('ITEM NO '||INO);
DBMS_OUTPUT.PUT_LINE('ITEM NAME '||INAME);
DBMS_OUTPUT.PUT_LINE('QUANTITY '||QTY);
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;
/
81.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;
/
82.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;
/
83.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;
/
84.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;
/
85.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;
/
86.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;
/
87.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;
/
88.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;
/
89.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;
/
90.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;
/
91.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;
/
92.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;
/
93.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;
/
94.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;
/
95.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;
/
96.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;
/
97.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;
/
98.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;
/
99.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;
/