. PROGRAM TO CALCULATE MAXIMUM, MINIMUM AND AVERAGE SALARY DEPT WISE AND DISPLAY THE RESULT USING CURSOR IN ORACLE?



SQL> DECLARE
  2  CURSOR INFO_CURSOR IS
  3  SELECT DEPTNO, MAX (SAL), MIN (SAL), AVG (SAL)
      FROM EMP GROUP BY DEPTNO;
  4  DCODE EMP. DEPTNO % TYPE;
  5  MAXSAL EMP.SAL% TYPE;
  6  MINSAL EMP.SAL% TYPE;
  7  AVGSAL EMP.SAL% TYPE;
  8  BEGIN
  9  OPEN INFO_CURSOR;
 10  LOOP
 11  FETCH INFO_CURSOR INTO DCODE, MAXSAL, MINSAL,
       AVGSAL;
 12  EXIT WHEN INFO_CURSOR%NOTFOUND;
 13  DBMS_OUTPUT.PUT_LINE (DCODE || ' '|| MAXSAL || ' '||
       MINSAL || ' ' || AVGSAL );
 14  END LOOP;
 15  CLOSE INFO_CURSOR;
 16  END;
 17  /
10 11450 2262.94 6084.43
20 5470.4 1469.5 3282.81
30 5248.96 1707.54 2483.95

PL/SQL procedure successfully completed.

SQL> @ SALARY.SQL;
10  11450     2262.94   6084.43
20  5470.4   1469.5      3282.81
30  5248.96 1707.54    2483.95

PL/SQL procedure successfully completed.

Comments

Post a Comment

Popular posts from this blog

Write a program to add two number using inline function in C++?

#include<iostream.h> #include<conio.h> class bca {   public:   inline int sum(int x, int y)   {   return(x+y);   } }; int main() { clrscr(); int r,a,b; bca obj; cout<<"enter two integers :"<<endl; cin>>a>>b; r=obj.sum(a,b); cout<<" the sum of two integers is :"<<r<<endl; getch(); return 0; }   OUTPUT:- enter two integers :    3    6 the sum of two integer is :   9  
Read more

Traversing of elements program with algorithm and Flowchart

Image
ALGORITHM VARIABLES: §   i:      loop counter of array. §   A:    array name. §   LB: lower bound. §   UB: upper bound. ALGORITHM: STEP 1:         set i =LB STEP 2:         repeat steps 3 & 4                            While i<=UB STEP 3:         apply process to A[i] STEP 4:         i = i+1            STEP 5:         exit EXPLANATION: §   To traverse the elements of the array, firstly set the loop counter ‘i’ equal to last element of the array called the LOWER BOUND. §   Now, repeat step 3 & 4 for accessing all the elements of the array until loop counter ‘i’ is not less than or equal first element of array. §   Now, after checking the above condition, the whole process will repeat for all the elements of array. §   After applying process upon a[i], increment the loop counter by 1, until the condition is not satisfied. IMPLEMENTATION
Read more