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 :)