query to find employee based on salary

View All Scripts Login to Run Script

Script Name query to find employee based on salary
Description query to find employee based on salary
Area SQL General / SQL Query
Contributor query to find employee based on salary
Created Sunday April 29, 2018

Statement 1

select * from employee e, salary s 
where e.emp_id = s.emp_id order by s.salary desc


EMP_ID FIRST_NAME LAST_NAME EMP_ID SALARY
100001 Saroj Mandal 100001 9961381
100012 Vivek Saxena 100012 9772577
100011 Sonu Singh 100011 9519614
100017 Indu Gorai 100017 9032699
100020 Chhanda Mandal 100020 7497479
100014 Neha Setia 100014 7263934
100016 Heena Srivastava 100016 6792038
100015 Pooja Kumari 100015 6770155
100003 Vini Narayanasamy 100003 6529215
100005 Shweta Kumari 100005 5891454
100013 Dheeraj Saxena 100013 5717371
100010 Ashutosh Mahto 100010 5094490
100007 Vijay Kumar 100007 4939209
100021 Asit Mandal 100021 4897700
100018 Inderdeep Seti 100018 3960742
100002 Manoj Mandal 100002 3812871
100019 Sujay TG 100019 2928280
100008 Vimal Kumar 100008 2182157
100009 Jayaprakash E 100009 1761705
100004 Sakshi Srivastava 100004 1297780
100006 Hemanth Neelam 100006 1147428

21 rows selected.

EMP_ID	FIRST_NAME	LAST_NAME	EMP_ID	SALARY
100001	Saroj	Mandal	100001	9961381
100012	Vivek	Saxena	100012	9772577
100011	Sonu	Singh	100011	9519614
100017	Indu	Gorai	100017	9032699
100020	Chhanda	Mandal	100020	7497479
100014	Neha	Setia	100014	7263934
100016	Heena	Srivastava	100016	6792038
100015	Pooja	Kumari	100015	6770155
100003	Vini	Narayanasamy	100003	6529215
100005	Shweta	Kumari	100005	5891454
100013	Dheeraj	Saxena	100013	5717371
100010	Ashutosh	Mahto	100010	5094490
100007	Vijay	Kumar	100007	4939209
100021	Asit	Mandal	100021	4897700
100018	Inderdeep	Seti	100018	3960742
100002	Manoj	Mandal	100002	3812871
100019	Sujay	TG	100019	2928280
100008	Vimal	Kumar	100008	2182157
100009	Jayaprakash	E	100009	1761705
100004	Sakshi	Srivastava	100004	1297780
100006	Hemanth	Neelam	100006	1147428

Statement 2

select * from employee e, salary s 
where e.emp_id = s.emp_id and rownum = 1 order by s.salary desc


EMP_ID FIRST_NAME LAST_NAME EMP_ID SALARY
100001 Saroj Mandal 100001 9961381

EMP_ID	FIRST_NAME	LAST_NAME	EMP_ID	SALARY
100001	Saroj	Mandal	100001	9961381

Statement 3

select * from employee e,   
    (select * from(select * from (select * from salary order by salary desc) where rownum <= 10) order by salary) s 
    where e.emp_id = s.emp_id 
    and rownum = 1


EMP_ID FIRST_NAME LAST_NAME EMP_ID SALARY
100005 Shweta Kumari 100005 5891454

EMP_ID	FIRST_NAME	LAST_NAME	EMP_ID	SALARY
100005	Shweta	Kumari	100005	5891454

Statement 4

select * from employee e, salary s 
    where e.emp_id = s.emp_id order by s.salary desc 
    offset 9 rows fetch next 1 rows only


EMP_ID FIRST_NAME LAST_NAME EMP_ID SALARY
100005 Shweta Kumari 100005 5891454

EMP_ID	FIRST_NAME	LAST_NAME	EMP_ID	SALARY
100005	Shweta	Kumari	100005	5891454

Statement 5

select sum(salary) from(select * from employee e, salary s 
    where e.emp_id = s.emp_id order by s.salary desc) where rownum <=10


SUM(SALARY)
79030546

SUM(SALARY)
79030546

Statement 6

select * from employee e, salary s 
    where  
    e.emp_id = s.emp_id and 
    s.salary in ( 
        select sum( 
            (select sum(salary) from(select * from salary order by salary desc) where rownum <=10) 
            - 
            (select sum(salary) from(select * from salary order by salary desc) where rownum <=9) 
        )  
        from dual 
    )


EMP_ID FIRST_NAME LAST_NAME EMP_ID SALARY
100005 Shweta Kumari 100005 5891454

EMP_ID	FIRST_NAME	LAST_NAME	EMP_ID	SALARY
100005	Shweta	Kumari	100005	5891454

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database