Salary Related Questions in Oracle

       
 --creating table

 CREATE TABLE employee_data
       (
       eid        NUMBER(4),
       ename      VARCHAR2(30),
       depno      NUMBER(3),
       salary     NUMBER(8)
       );
    


BEGIN      
    --Inserting records
    INSERT INTO employee_data VALUES(1000,'Name01',90,30000);
    INSERT INTO employee_data VALUES(1001,'Name02',90,6000);
    INSERT INTO employee_data VALUES(1002,'Name03',90,23000);
    INSERT INTO employee_data VALUES(1003,'Name04',60,35000);
    INSERT INTO employee_data VALUES(1004,'Name05',60,60000);
    INSERT INTO employee_data VALUES(1005,'Name06',60,30000);
    INSERT INTO employee_data VALUES(1006,'Name07',80,36000);
    INSERT INTO employee_data VALUES(1007,'Name08',80,29000);
    INSERT INTO employee_data VALUES(1008,'Name09',80,37000);
    INSERT INTO employee_data VALUES(1009,'Name10',80,41000);
    COMMIT;
END;
/


SELECT * FROM employee_data
ORDER BY salary DESC;

--query to find maximum salary from employee_data  table

SELECT MAX(salary) FROM employee_data; 

--query to find minimum salary from employee_data  table

SELECT MIN(salary) FROM employee_data;

--query to find second maximum salary from employee_data  table

SELECT MAX(salary)
  FROM employee_data
 WHERE salary NOT IN (SELECT MAX(salary)
                        FROM employee_data);

--query to select 5th maximum salary

SELECT MIN(salary)
  FROM (SELECT salary
          FROM (SELECT salary
                  FROM employee_data
                 ORDER BY salary DESC)
         WHERE rownum <= 5);

--query to select nth maximum salary


SELECT MIN(salary)
  FROM (SELECT salary
          FROM (SELECT salary
                  FROM employee_data
                 ORDER BY salary DESC)
         WHERE rownum <= &n);

--query to select all the details of the employee whose getting nth maximum salary
 

SELECT *
  FROM (SELECT emp.*,
               rownum rn
          FROM (SELECT *
                  FROM employee_data
                 ORDER BY salary DESC) emp)
 WHERE rn = 9;

--same query using row_numbr( ) function

SELECT *
  FROM (SELECT emp.*,
               row_number() over(ORDER BY salary) rank
          FROM employee_data emp)
 WHERE rank = 9;

--same query using rank( ) function

SELECT *
  FROM (SELECT emp.*,
               rank() over(ORDER BY salary DESC) rank
          FROM employee_data emp)
 WHERE rank = 4;

--same query using dense_rank( ) function


SELECT *
  FROM (SELECT emp.*,
               dense_rank() over(ORDER BY salary DESC) rank
          FROM employee_data emp)
 WHERE rank = 4;

--query to display display maximum salary in all the department

SELECT e.depno "Department id",
       MAX(salary) "Maximum Salary"
  FROM employee_data e
 GROUP BY e.depno;

--query to display all the employees who are all getting salary more than average salary of the company

SELECT e.* FROM employee_data e
WHERE salary > (SELECT AVG(salary) FROM employee_data);

--query to display all the employees who are all getting salary more than their department average salary


SELECT employee_id,
       first_name,
       salary
  FROM employees OUT
 WHERE salary > (SELECT AVG(salary)
                   FROM employees
                  WHERE department_id = OUT.department_id);
                               
--query to display all the employees who are all getting salary more than their manager salary

                    
SELECT employee_id,
       first_name,
       salary
  FROM employees OUT
 WHERE salary > (SELECT AVG(salary)
                   FROM employees
                  WHERE employee_id = OUT.manager_id);

Documented By : Murugappan Annamalai

Posted on         : 23-Jan-15 16:34:00 IST.

                               
                                

Hope you have enjoyed this little article

               Feed backs are always welcome :)                  
                               

Why prefer COALESCE over NVL

Documented By : Nimish Garg

 I prefer using "COALESCE" over "NVL" is some of the scenarios. Last week One of my friend asked me what is the advantage of using "COALESCE" where we can simply use "NVL". I simply gave him the reply from Oracle Docs i.e. NVL lets you replace null (returned as a blank) with a string in the results of a query and COALESCE returns the first non-null expr in the expression list. Oracle Database uses short-circuit evaluation with "COALESCE".

He replied that he knows the difference, he knows that "COALESCE" can take multiple arguments and so on. He was more interested in understanding "short-circuit evaluation" of "COALESCE" and the scenarios where I prefer using "COALESCE" over "NVL".

To make him understand I created following function which takes One second in every execution and simply returns '--null--' and executed 2 very similar queries one with NVL and other with COALESCE.
 

SQL> create or replace function f_null return varchar2
  2  is
  3  begin
  4     dbms_lock.sleep(1);
  5     return '--null--';
  6  end;
  7  /

Function created.

 
 Following are the queries which I used to demonstrate that NVL evaluates both arguments even if the second argument is not used and COALESCE uses short-circuit evaluation i.e. it only evaluates the arguments only if they are needed.

SQL> select e.empno, e.ename ename, nvl(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:14.01

SQL> select e.empno, e.ename ename, coalesce(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:01.01

Here we can see easily that the first query with NVL took 14+ seconds, one second for each record even if the "f_null" value was used only in one record. On the contrary as mentioned in Oracle Documentation "COALESCE" uses its "short-circuit evaluation" and "f_null" was called only once and so second query took only One Second. "COALESCE" is certainly use less resources than NVL.