/*DROP TABLE EMP_CUSTOM_TABLE;
CREATE TABLE EMP_CUSTOM_TABLE (
EMPNO NUMBER NOT NULL CONSTRAINT EMP_CUSTOM_TABLE_PK PRIMARY KEY,
ENAME VARCHAR2(50),
JOB VARCHAR2(50),
MGR VARCHAR2(4000),
HIREDATE VARCHAR2(4000),
EXITDATE VARCHAR2(4000),
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER,
LOCATION VARCHAR2(400) )
;
INSERT INTO EMP_CUSTOM_TABLE VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'), 800,NULL,20, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1600,300,30,'MUMBAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),TO_DATE('19-6-1983','dd-mm-yyyy'),1250,500,30, 'CHENNAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),TO_DATE('27-11-1987','dd-mm-yyyy'),2975,NULL,20, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1250,1400,30, 'BANGALORE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),2850,NULL,30, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),2450,NULL,10, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-87','dd-mm-rr')-85,TO_DATE('7-1-1990','dd-mm-yyyy'),3500,NULL,20, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),5000,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1500,0,30, 'BANGALORE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('13-JUL-87', 'dd-mm-rr')-51,TO_DATE('7-1-1989','dd-mm-yyyy'),1100,NULL,20, 'CHENNAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),950,NULL,30, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1983','dd-mm-yyyy'),3230,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1400,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7999,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3050,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8001 ,'KUMAR','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1600,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8002 ,'SACHIN','MARKETING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3200,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8003,'RAHUL','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1100,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8004,'GAMBHIR','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8005,'SEHWAG','TRAINING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1700,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8006,'YURAJ','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8007,'DHONI','MARKETING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1986','dd-mm-yyyy'),1300,800,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8008,'KOHLI','SALES',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3050,658,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8009,'PANDHYA','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1988','dd-mm-yyyy'),1350,800,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8010,'DHAWAN','ADVRTISING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1989','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8011,'BHUVANESH','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1990','dd-mm-yyyy'),1360,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8012,'KUMBLE','TRAINING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1300,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8013,'KAPIL','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3200,300,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8014,'ROBIN','MARKETING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1986','dd-mm-yyyy'),1700,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8015,'JOSHI','SALES',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3800,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8016,'SHASHTRI','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1988','dd-mm-yyyy'),1000,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8017,'GANGULI','ADVRTISING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1989','dd-mm-yyyy'),3300,600,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8018,'SRINADH','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1990','dd-mm-yyyy'),1800,500,10, 'DELHI') ;
COMMIT ;
Dropping table & Creating the Same table then inserting data into Table, Table name is "EMP_CUSTOM_TABLE"
Inserting data into table using insert statements.
*/
--1)
describe EMP_CUSTOM_TABLE;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM EMP_CUSTOM_TABLE ;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, SAL, DEPTNO,
count(*) OVER () as Numer_of_Records,
SUM(SAL) OVER () as TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME
DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, SAL, DEPTNO,
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL,
SAL + LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_NEXT_SAL,
SAL + LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_PREV_SAL ,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) +
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_PLUS_PREV_SAL
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC
;
SELECT ENAME, SAL, CUMLATIVE_SAL, COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL FROM (SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL ASC ) ;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, DEPTNO,SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM --- CREATING CUMULATIVE SALARIES AT TSBLE, DEPT LEVEL
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LEAD,
MIN(SAL) OVER ( PARTITION BY DEPTNO ) MIN_SAL_DEPT,
MAX(SAL) OVER ( PARTITION BY DEPTNO ) MAX_SAL_DEPT,
ROUND(AVG(SAL) OVER ( PARTITION BY DEPTNO ),3) AS AVG_SAL_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT DEPTNO,
MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL) DEPT_MIN_SAL,
MAX(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL) DEPT_MAX_SAL
, MAX(SAL) MAXSAL
, MIN(SAL) MINSAL
FROM EMP_CUSTOM_TABLE GROUP BY DEPTNO ORDER BY DEPTNO
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) RANKS,
RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPT_RANKS,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LAG
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_DEPT,
SUM(SAL) OVER ( ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_TABLE
FROM (SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC) DENSE_RANKS_DEPT
FROM (SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC
;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC
;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, SAL, DEPTNO,
SUM(SAL) OVER () TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC
;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, SAL, DEPTNO,
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL,
SAL + LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_NEXT_SAL,
SAL + LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_PREV_SAL ,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) +
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_PLUS_PREV_SAL
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC
;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME, SAL, CUMLATIVE_SAL,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL
FROM
(SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL ASC
)
;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, DEPTNO,SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM --- CREATING CUMULATIVE SALARIES AT TABLE, DEPT LEVEL
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LEAD,
MIN(SAL) OVER ( PARTITION BY DEPTNO ) MIN_SAL_DEPT,
MAX(SAL) OVER ( PARTITION BY DEPTNO ) MAX_SAL_DEPT,
ROUND(AVG(SAL) OVER ( PARTITION BY DEPTNO ),3) AS AVG_SAL_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT DEPTNO,
MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL) DEPT_MIN_SAL,
MAX(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL) DEPT_MAX_SAL
, MAX(SAL) MAXSAL
, MIN(SAL) MINSAL
FROM EMP_CUSTOM_TABLE GROUP BY DEPTNO ORDER BY DEPTNO
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) RANKS,
RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPT_RANKS,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LAG
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
---------------------------------------------------------------------------------------------------------------
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_DEPT,
SUM(SAL) OVER ( ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_TABLE
FROM (SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC) DENSE_RANKS_DEPT
FROM (SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC
;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME,JOB,DEPTNO, SAL
, SUM(SAL) OVER() AS TOTAL_SAL_TABLE
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC ) AS TOTAL_SAL_DEPT
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC, ENAME ASC ) AS TOTAL_SAL_DEPT_EMP
, SUM(SAL) OVER(PARTITION BY JOB ORDER BY JOB ASC,SAL DESC) AS TOTAL_SAL_JOB
, SUM(SAL) OVER(PARTITION BY JOB ORDER BY JOB ASC,SAL DESC, ENAME ASC) AS TOTAL_SAL_JOB_ENAME
FROM EMP_CUSTOM_TABLE ORDER BY JOB ASC, SAL DESC ;
---------------------------------------------------------------------------------------------------------------
SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO, JOB , SAL DESC ;
SELECT ENAME,JOB,DEPTNO, SAL FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO, JOB , SAL DESC ;
---------------------------------------------------------------------------------------------------------------
SELECT ENAME,JOB,DEPTNO, SAL
, SUM(SAL) OVER() TOTAL_SAL_TABLE
, SUM(SAL) OVER(PARTITION BY DEPTNO ) AS TOTAL_SAL_DEPT
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC ) AS RUNNING_TOTAL_SAL_DEPT
--, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC, ENAME ASC ) AS TOTAL_SAL_DEPT_ENAME
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC,SAL DESC ;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO,ENAME, SAL, DEPTNO,
SUM(SAL) OVER () TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPTTOTALSAL2,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME
DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC
;
SELECT EMPNO,ENAME, SAL, DEPTNO
, LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL_WITH_IN_DEPT
, LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL_WITH_IN_DEPT
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC;
---
-- HTTPS://SQL4DATAANALYSIS.BLOGSPOT.COM/
/*Analytical Functions in Oracle */
select ('HTTPS://SQL4DATAANALYSIS.BLOGSPOT.COM/') as ORACLE_SQL_Blog from dual;
-- Laxman Kumar Boorugu
-----------------------------------------------------------------------------------------------------------------
1) Show the data that resides in the table
SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
-- 2) Information about this table---- describe
describe EMP_CUSTOM_TABLE;
---------------------------------------------------------------------------------------------------------------
--3) What is total SALARY of each employee including commissoin
SELECT EMPNO, ENAME,SAL , COMM, (SAL+COMM) as TOTAL_SAL FROM EMP_CUSTOM_TABLE;
---------------------------------------------------------------------------------------------------------------
--4) What is total SALARY of each employee including commissoin
-- Numerical Value + (COMM) = NULL
-- we handle it by using nvl(Column,0)
SELECT EMPNO, ENAME,SAL , nvl(COMM,0) as COMM, (SAL+nvl(COMM,0)) asTOTAL_SAL FROM EMP_CUSTOM_TABLE;
---------------------------------------------------------------------------------------------------------------
--5) Total SALARY of entire Table(not include COMM) & DEPT wise Total SALARY
SELECT EMPNO, ENAME,DEPTNO,SAL
, SUM(SAL) OVER () TOTAL_SAL_TABLE
, SUM(SAL) OVER (PARTITION BY DEPTNO ) TOTAL_SAL_DEPTNO
, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ) SEQ_EMP_DEPT
, ROW_NUMBER() OVER (ORDER BY DEPTNO ASC, SAL DESC) SEQ_EMP_TABLE
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
--6) CUMULATIVE SUM OF SALARY of entire Table & CUMULATIVE SUM OF SALARY of DEPT
SELECT EMPNO, ENAME,DEPTNO,SAL
, SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_TABLE
, SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUM_SAL_DEPTNO
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
--7) PREVIOUS & NEXT EMPLOYEE SALARY in entire Table & in DEPT
SELECT EMPNO, ENAME,DEPTNO,SAL
, LAG(SAL,1,0) OVER ( ORDER BY DEPTNO ASC, SAL DESC) PRVS_SAL_TABLE
, LEAD(SAL,1,0) OVER ( ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL_TABLE
, LAG(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ) PRVS_SAL_DEPT
, LEAD(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
--8) MIN & MAX EMPLOYEE SALARY in entire Table & in DEPT
SELECT EMPNO, ENAME,DEPTNO,SAL
, MIN(SAL) OVER () MIN_SAL_TABLE , MAX(SAL) OVER () MAX_SAL_TABLE
, MIN(SAL) OVER (PARTITION BY DEPTNO ) MIN_SAL_DEPT , MAX(SAL) OVER (PARTITION BY DEPTNO) MAX_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO, ENAME,DEPTNO,SAL
, FIRST_VALUE(SAL) IGNORE NULLS OVER () FIRST_VALUE_TABLE
, LAST_VALUE(SAL) RESPECT NULLS OVER () LAST_VALUE_TABLE
, FIRST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) FIRST_VALUE_DEPT
, LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO) LAST_VALUE_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO, ENAME,DEPTNO,SAL
, MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL DESC) OVER () AS MAX_SAL_TABLE
, MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL ASC) OVER () AS MIN_SAL_TABLE
, MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL DESC) OVER (PARTITION BY DEPTNO) AS MAX_SAL_DEPT
, MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL ASC) OVER (PARTITION BY DEPTNO) AS MIN_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO, ENAME,DEPTNO,SAL
, MAX(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL ASC) OVER () AS MIN_SAL_TABLE
, MAX(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL DESC) OVER () AS MAX_SAL_TABLE
, MAX(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL ASC ) OVER (PARTITION BY DEPTNO) AS MIN_SAL_DEPT
, MAX(SAL) KEEP ( DENSE_RANK FIRST ORDER BY SAL DESC) OVER (PARTITION BY DEPTNO) AS MAX_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
SELECT EMPNO, ENAME,DEPTNO,SAL
, MAX(SAL) KEEP ( DENSE_RANK LAST ORDER BY SAL ASC) OVER () AS MAX_SAL_TABLE
, MAX(SAL) KEEP ( DENSE_RANK LAST ORDER BY SAL DESC) OVER () AS MAX_SAL_TABLE
, MAX(SAL) KEEP ( DENSE_RANK LAST ORDER BY SAL ASC ) OVER (PARTITION BY DEPTNO) AS MAX_SAL_DEPT
, MAX(SAL) KEEP ( DENSE_RANK LAST ORDER BY SAL DESC) OVER (PARTITION BY DEPTNO) AS MIN_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC;
---------------------------------------------------------------------------------------------------------------
We can remove duplicates using Analytical functions
-- we are getting duplicates, we can notice it by looking at derived column 'num_emp_Seq'
select deptno, job,
ROW_NUMBER() OVER (PARTITION BY DEPTNO,job ORDER BY DEPTNO ) num_emp_Seq
from EMP_CUSTOM_TABLE
order by deptno, job;
---------------------------------------------------------------------------------------------------------------
--now we are going to removing duplicates using aggregate Function (Common Table Expressions in MS SQL Server)
-- in the query result but not from database.
with data as (
select deptno, job,
ROW_NUMBER() OVER (PARTITION BY DEPTNO,job ORDER BY DEPTNO ) num_emp_Seq
from EMP_CUSTOM_TABLE
order by deptno, job
) --- temp table;
select * from data where num_emp_Seq=1;
select distinct deptno, job from EMP_CUSTOM_TABLE order by deptno, job;
--drop table dept_job;
create or replace view dept_job as select deptno, job from EMP_CUSTOM_TABLE order by deptno, job ;
select * from dept_job;
with data as (
select deptno, job,
ROW_NUMBER() OVER (PARTITION BY DEPTNO,job ORDER BY DEPTNO ) num_emp_Seq
from EMP_CUSTOM_TABLE
order by deptno, job
) --- temp table;
delete from data where num_emp_Seq >1;
No comments:
Post a Comment