Sunday, 17 April 2016

Query to display MAXIMUM SALARY of each DEPARTMENT after joining two similar tables

Requirement: Write a query to display MAXIMUM SALARY of each DEPARTMENT after joining two similar tables:

Demo requirement is depicted in the below image:

Solution: For solution of the above requirement, let us create two tables and insert few records in each table as shown below:

First we will create DEPT1 table as shown below:

CREATE TABLE DEPT1 (DEPT_ID NUMBER(10) PRIMARY KEY, SALARY NUMBER(10));

INSERT INTO DEPT1 VALUES (10,1000);
INSERT INTO DEPT1 VALUES (20,2000);
INSERT INTO DEPT1 VALUES (30,3000);

SELECT * FROM DEPT1;



Now we will create DEPT2 table as shown below:

CREATE TABLE DEPT2 (DEPT_ID NUMBER(10) PRIMARY KEY, SALARY NUMBER(10));

INSERT INTO DEPT2 VALUES (10,5000);
INSERT INTO DEPT2 VALUES (20,6000);
INSERT INTO DEPT2 VALUES (30,NULL);

SELECT * FROM DEPT2;


To get the required output, write the query as shown below:

SELECT D1.DEPT_ID , GREATEST(NVL(D1.SALARY, 0), NVL(D2.SALARY, 0)) MAX_SALARY
FROM DEPT1 D1, DEPT2 D2
WHERE D1.DEPT_ID = D2.DEPT_ID;


Hence, the solution to our requirement.

If you like the post please comment, share and like me on Facebook.


Thanks & Regards,
Susanto Paul