Dropping table & Creating the Same table then inserting data into Table, Table name is "EMP_CUSTOM_TABLE"

Inserting data into table using insert statements.

WE ARE GETTING TOTAL SALARY IN THE TABLE BY USING ANALYTICAL FUNCTION WITOUT PARTITION.

---------------------

HERE WE ARE GETTING DEPT TOTAL SALARY & RUNNUNG TOTAL OF SALRY WITH IN DEPT.

IF WE SPECIFY ORDER BY THEN IT FOLLOWS RUNNING SUM ELSE TOTAL OF DEPT.

FINALLY AT THE END OF THE DEPT WE ARE GETTING SAME TOTAL SALRY OF THE DEPT. ( It is highlighted in the below screen shot.) 

--------------------------------

HIGHLIGHTED ROWS WERE SUMMED UP SINCE BOTH ARE HAVING THE SAME SALARY AND BELONGING TO THE SAME DEPT.

WE ARE APPLYING  PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC;  IT MEANS SALARIES ARE SAME & BELONGONG TO THE SAME DEPT SO VALUES ARE BEING SUMMED UP.

HERE WE ARE SPECIFYING ENAME IN THE ORDER BY CLAUSE SO VALUES ARE BEING SUMMED UP ACCORDINGLY, IT MEANS DEPT IS SAME BUT ENAMES  ARE DIFFERENT.

https://unifiedportal-epfo.epfindia.gov.in/publicPortal/no-auth/misReport/home/loadEstSearchHome

Date Functions in Oracle SQL

 

----------------

-------

--------------

------------------

-------------------------------

--------------

------------------------

------------

https://unifiedportal-epfo.epfindia.gov.in/publicPortal/no-auth/misReport/home/loadEstSearchHome

Usage of Trunc function on dates

 

We can get first day of specified level using "TRUNC" function on Date columns.

 

--------------------- -------------

SQL

       1) Display the details of all employees
       ANS: select * from emp;




       2) Display the dept information from department table
        ANS: select * from dept;



       3) Show the Employee Name, Employee No, Salary
       ANS: select ename, empno,sal from emp;




       4) SHOW EMPLOYEES WISE TOTAL MONTHLY SALARY ( MONTHLY SALARY = SAL                     + COMM)
            FOR SOME EMPLOYEES COMMISSION IS NULL, WE HAVE TO REPLACE THEM                  BY  0(ZERO) TO PERFORM ARITHMETIC CALCULATION
            ANS: SELECT ROWNUM, ENAME, EMPNO,SAL, COMM,SAL+ NVL(COMM,0) AS                  TOTAL_SALARY FROM SCOTT.EMP ;

What is the Difference between Rank and Dense Rank

Dense rank gives the continuous integer values as output ( 12223)
where as Rank skip the numbers ( 12225) if the input value is repeated

The following example gives the clear explanation of this topic.

https://unifiedportal-epfo.epfindia.gov.in/publicPortal/no-auth/misReport/home/loadEstSearchHome

Top 2 salaries department wise

-----------------

List out the employees who are having same amount of salary