Ranking functions

Consider the below product and sub product table,

Product

Sub_Product

Expected Output :

Query:

SELECT
ROW_NUMBER() OVER(ORDER BY P.ID) "NO",
RANK() OVER(PARTITION BY P.ID ORDER BY SP.ID) "ORDER",
P.ID PRD_ID,
SP.ID SUB_PRD_ID,
P.NAME MAIN_PRD_NAME,
SP.NAME SUB_PRD_NAME
FROM
PRODUCT P,
SUB_PRODUCT SP
WHERE P.ID = SP.PID;

------------------------------------Table Creation-----------------------------------------------------------------

CREATE TABLE PRODUCT
(ID NUMBER,
NAME VARCHAR2(255)
);

INSERT INTO PRODUCT VALUES (10,'SHOES');
INSERT INTO PRODUCT VALUES (20,'CLOTHES');
INSERT INTO PRODUCT VALUES (30,'ACCESORIES');
COMMIT;

SELECT * FROM PRODUCT;

CREATE TABLE SUB_PRODUCT
(
ID NUMBER,
PID NUMBER,
NAME VARCHAR2(255)
);

INSERT INTO SUB_PRODUCT VALUES(101,10,'LEATHER');
INSERT INTO SUB_PRODUCT VALUES(102,10,'NORMAL');
INSERT INTO SUB_PRODUCT VALUES(210,20,'REGULAR');
INSERT INTO SUB_PRODUCT VALUES(211,20,'LARGE');
INSERT INTO SUB_PRODUCT VALUES(212,20,'EXTRA LARGE');
INSERT INTO SUB_PRODUCT VALUES(213,20,'MEDIUM');
INSERT INTO SUB_PRODUCT VALUES(301,30,'YES');
COMMIT;

SELECT * FROM SUB_PRODUCT;

--------------------------------------------------------------------------------------------------------------------------
--Used Oracle Livesql EMP and DEPT tables for the queries

Query to find the highest & lowest salary earned by an employee in each department and also the number of employees who earn the highest & lowest  salary?

SELECT D.DEPTNO,
MAX(SAL),
COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SAL) CNT_HIGH_SAL,
MIN(SAL),
COUNT(1) KEEP(DENSE_RANK FIRST ORDER BY SAL) CNT_LOW_SAL
FROM EMP E,DEPT D
WHERE
E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO;

--------------------------------------------------------------------------------------------------------------------------

 Query to get the top 2 employees who are earning the highest salary in each department

WITH Q AS 
(SELECT D.DEPTNO DN,
SAL,
ROW_NUMBER() OVER ( PARTITION BY D.DEPTNO ORDER BY SAL DESC) RN
FROM EMP E,DEPT D
WHERE
E.DEPTNO = D.DEPTNO)
SELECT DN,SAL FROM Q WHERE RN <3

--------------------------------------------------------------------------------------------------------------------------

Query to delete the duplicate records from employees table

DELETE FROM EMP 

WHERE 

ROWID IN (SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO HAVING COUNT(1) >1);

--------------------------------------------------------------------------------------------------------------------------

Query to find the employees who are earning more than the average salary in their department

SELECT * FROM EMP E 

WHERE 

SAL > (SELECT AVG(SAL) FROM EMP E1 WHERE E.DEPTNO = E1.DEPTNO)