CREATE TABLE "AB_EMPLOYEE"
( "EMP_ID" VARCHAR2(5 BYTE),
"EMP_NAME" VARCHAR2(20 BYTE),
"DEPT_ID" VARCHAR2(5 BYTE),
"EXPERTISE" VARCHAR2(50 BYTE),
"SALARY" NUMBER(10,2),
"RESULTS" VARCHAR2(10 BYTE)
)
Table created.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','SCIENCE','PASS',50000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','ENGLISH','PASS',50000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('5003','ABINASH','1','MATH','PASS',50000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('107','AMARESH','2','MATH','PASS',50000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('107','AMARESH','2','ENGLISH','PASS',50000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('105','JYOTI','3','MATH','FAIL',75000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('105','JYOTI','3','ENGLISH','PASS',75000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('7003','NISHAD','2','ENGLISH','FAIL',70000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('7003','NISHAD','2','MATH','PASS',70000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('6003','RAKESH','2','MATH','PASS',50556)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('6003','RAKESH','2','ENGLISH','FAIL',50556)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('104','RAVI','2','MATH','PASS',70000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('104','RAVI','2','ENGLISH','PASS',70000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('106','REDDY','2','MATH','FAIL',80000)
1 row(s) inserted.
Insert into AB_EMPLOYEE (EMP_ID,EMP_NAME,DEPT_ID,EXPERTISE,RESULTS,SALARY) values ('106','REDDY','2','ENGLISH','PASS',80000)
1 row(s) inserted.
SELECT * FROM AB_EMPLOYEE
EMP_ID | EMP_NAME | DEPT_ID | EXPERTISE | SALARY | RESULTS | 5003 | ABINASH | 1 | SCIENCE | 50000 | PASS | 5003 | ABINASH | 1 | ENGLISH | 50000 | PASS | 5003 | ABINASH | 1 | MATH | 50000 | PASS | 107 | AMARESH | 2 | MATH | 50000 | PASS | 107 | AMARESH | 2 | ENGLISH | 50000 | PASS | 105 | JYOTI | 3 | MATH | 75000 | FAIL | 105 | JYOTI | 3 | ENGLISH | 75000 | PASS | 7003 | NISHAD | 2 | ENGLISH | 70000 | FAIL | 7003 | NISHAD | 2 | MATH | 70000 | PASS | 6003 | RAKESH | 2 | MATH | 50556 | PASS | 6003 | RAKESH | 2 | ENGLISH | 50556 | FAIL | 104 | RAVI | 2 | MATH | 70000 | PASS | 104 | RAVI | 2 | ENGLISH | 70000 | PASS | 106 | REDDY | 2 | MATH | 80000 | FAIL | 106 | REDDY | 2 | ENGLISH | 80000 | PASS |
---|
SELECT EMP_ID, DEPT_ID,EXPERTISE,SALARY,
SUM(SALARY) OVER (PARTITION BY DEPT_ID) AS DEPT_SALARY,
SUM(SALARY) OVER (PARTITION BY EXPERTISE) AS EXPERTISE_SALARY,
ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS ROW_NUM ,
RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS RANK_NUM,
DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC ) AS D_RANK_NUM
FROM AB_EMPLOYEE
ORDER BY DEPT_ID,SALARY DESC
EMP_ID | DEPT_ID | EXPERTISE | SALARY | DEPT_SALARY | EXPERTISE_SALARY | ROW_NUM | RANK_NUM | D_RANK_NUM | 5003 | 1 | SCIENCE | 50000 | 150000 | 50000 | 1 | 1 | 1 | 5003 | 1 | MATH | 50000 | 150000 | 445556 | 2 | 1 | 1 | 5003 | 1 | ENGLISH | 50000 | 150000 | 445556 | 3 | 1 | 1 | 106 | 2 | ENGLISH | 80000 | 641112 | 445556 | 1 | 1 | 1 | 106 | 2 | MATH | 80000 | 641112 | 445556 | 2 | 1 | 1 | 7003 | 2 | ENGLISH | 70000 | 641112 | 445556 | 3 | 3 | 2 | 104 | 2 | ENGLISH | 70000 | 641112 | 445556 | 4 | 3 | 2 | 7003 | 2 | MATH | 70000 | 641112 | 445556 | 5 | 3 | 2 | 104 | 2 | MATH | 70000 | 641112 | 445556 | 6 | 3 | 2 | 6003 | 2 | MATH | 50556 | 641112 | 445556 | 7 | 7 | 3 | 6003 | 2 | ENGLISH | 50556 | 641112 | 445556 | 8 | 7 | 3 | 107 | 2 | MATH | 50000 | 641112 | 445556 | 9 | 9 | 4 | 107 | 2 | ENGLISH | 50000 | 641112 | 445556 | 10 | 9 | 4 | 105 | 3 | MATH | 75000 | 150000 | 445556 | 1 | 1 | 1 | 105 | 3 | ENGLISH | 75000 | 150000 | 445556 | 2 | 1 | 1 |
---|