RANKING FUNCTIONS WITH CASE STUDIES
----RANKING FUNCTIONS
----LEARN USE OF DERIVED QUERY
----LEARN USE OF COMMON TABLE EXPRESSIONS
----USE OF GROUP BY
----USE OF HAVING CLAUSE
----RANKING FUNCTIONS:
--ROW_NUMBER will always generate unique values without any gaps, even if there are ties.
--RANK can have gaps in its sequence and when values are the same, they get the same rank.
--DENSE_RANK also returns the same rank for ties, but doesn’t have any gaps in the sequence.
-NTILE--------
CREATE TABLE EMP2004
(
EMPID INT,
NAME VARCHAR(10),
SALARY INT,
JOB_ROLE VARCHAR(10),
DEPT_ID INT,
CITY VARCHAR(20)
)
--INSERT INTO EMP2004 VALUES(1,'JANVI',10000000,'HR',1,'BANG')
--INSERT INTO EMP2004 VALUES(2,'NEHA',800000,'HR',1,'RAJKOT')
--INSERT INTO EMP2004 VALUES(3,'NIDHI',100000,'HR',1,'BANG')
--INSERT INTO EMP2004 VALUES(4,'AMBIKA',77000,'HR',1,'HYD')
--INSERT INTO EMP2004 VALUES(5,'HARSHIT',66000,'HR',1,'BHAV')
--INSERT INTO EMP2004 VALUES(6,'DEEKSHA',80000,'Manager',2,'BHAV')
--INSERT INTO EMP2004 VALUES(7,'ASHISH',80000,'JR Manager',2,'AHM')
--INSERT INTO EMP2004 VALUES(8,'KISHAN',100000,'SR Manager',2,'OTTY')
--INSERT INTO EMP2004 VALUES(9,'KAJAL',100000,'Manager',2,'BANG')
--INSERT INTO EMP2004 VALUES(10,'MIRA',66000,'Assistant',2,'AHM')
--INSERT INTO EMP2004 VALUES(11,'SRIJA',88800,'SR ENG',3,'BHAV')
--INSERT INTO EMP2004 VALUES(12,'MOHINI',89100,'ENG',3,'AHM')
--INSERT INTO EMP2004 VALUES(13,'RADHA',50000,'SR ENG',3,'OTTY')
--INSERT INTO EMP2004 VALUES(14,'KAJAL',50000,'Manager',3,'BANG')
--INSERT INTO EMP2004 VALUES(15,'MIRA',50000,'Assistant',3,'AHM')
--INSERT INTO EMP2004 VALUES(16,'SRIJA',18800,'SR ENG',4,'BHAV')
--INSERT INTO EMP2004 VALUES(17,'MOHINI',19100,'ENG',4,'AHM')
--INSERT INTO EMP2004 VALUES(18,'RADHA',52000,'SR ENG',4,'OTTY')
--INSERT INTO EMP2004 VALUES(19,'KAJAL',53000,'Manager',4,'BANG')
--INSERT INTO EMP2004 VALUES(20,'MIRA',54000,'Assistant',4,'AHM')
--INSERT INTO EMP2004 VALUES(21,'SRIJA',84800,'SR ENG',5,'BHAV')
--INSERT INTO EMP2004 VALUES(22,'MOHINI',59100,'ENG',5,'AHM')
--INSERT INTO EMP2004 VALUES(23,'RADHA',55000,'SR ENG',5,'OTTY')
--INSERT INTO EMP2004 VALUES(24,'KAJAL',55000,'Manager',5,'BANG')
--INSERT INTO EMP2004 VALUES(25,'MIRA',5000,'Assistant',5,'AHM')
--INSERT INTO EMP2004 VALUES(26,'HARESH',55000,'Manager',6,'BANG')
--INSERT INTO EMP2004 VALUES(27,'RITU',5000,'Assistant',7,'AHM')
--INSERT INTO EMP2004 VALUES(28,'HEMA',55000,'Manager',8,'BANG')
--INSERT INTO EMP2004 VALUES(29,'SWETA',5000,'Assistant',9,'AHM')
CREATE TABLE DEPARTMENTS2004
(
DEPT_ID INT,
DEPTNAME VARCHAR(10),
LOCATIONID INT
)
--INSERT INTO DEPARTMENTS2004 VALUES(1,'HR',1)
--INSERT INTO DEPARTMENTS2004 VALUES(2,'IT',2)
--INSERT INTO DEPARTMENTS2004 VALUES(3,'ACCOUNTS',3)
--INSERT INTO DEPARTMENTS2004 VALUES(4,'INCOMETAX',4)
--INSERT INTO DEPARTMENTS2004 VALUES(5,'R&D',5)
--INSERT INTO DEPARTMENTS2004 VALUES(6,'XXX',6)
--INSERT INTO DEPARTMENTS2004 VALUES(7,'YYY',7)
--INSERT INTO DEPARTMENTS2004 VALUES(88,'MEDICAL',9)
--INSERT INTO DEPARTMENTS2004 VALUES(99,'SHIP',10)
CREATE TABLE LOCATIONS
(
ID INT,
NAME VARCHAR(10)
)
--INSERT INTO LOCATIONS VALUES(1,'INDIA')
--INSERT INTO LOCATIONS VALUES(2,'US')
--INSERT INTO LOCATIONS VALUES(3,'UK')
--INSERT INTO LOCATIONS VALUES(4,'EUROPE')
--INSERT INTO LOCATIONS VALUES(5,'SRILANKA')
--INSERT INTO LOCATIONS VALUES(6,'CANADA')
--INSERT INTO LOCATIONS VALUES(7,'IRE')
--INSERT INTO LOCATIONS VALUES(8,'AUS')
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
SELECT * FROM EMP2004
SELECT * FROM DEPARTMENTS2004
SELECT * FROM LOCATIONS