FINDING Second Highest Salary in SQL
CREATE TABLE #EMP
(
NAME NVARCHAR(100), SALARY BIGINT)
INSERT INTO #EMP VALUES('One',100)
INSERT INTO #EMP VALUES('Two',200)
INSERT INTO #EMP VALUES('Three',300)
INSERT INTO #EMP VALUES('Four',400)
-- USING CTE
;WITH CTEEmp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS RN,
NAME,
SALARY
FROm #EMP
)
SELECT NAME, SALARY, RN FROM CTEEmp WHERE RN = 2
-- USING CO
RELATED QUERIES
SELECT * FROM #EMP e1
WHERE 2 = (SELECT count(0) FROM #Emp e2 WHERE e1.Salary >= e2.Salary)
FIND THE
Phone number's of the employees having salary > 200
CREATE TABLE #EMP
(
ID INT
IDENTITY(1,1) , NAME NVARCHAR(100), SALARY BIGINT
)
INSERT INTO #EMP VALUES('One',100)
INSERT INTO #EMP VALUES('Two',200)
INSERT INTO #EMP VALUES('Three',300)
INSERT INTO #EMP VALUES('Four',400)
CREATE TABLE #EMPDetails
(
ID INT IDENTITY(1,1) , [Address] NVARCHAR(100), PHONENumber BIGINT
)
INSERT INTO #EMPDetails VALUES('Delhi',12345)
INSERT INTO #EMPDetails VALUES('Noida',54321)
INSERT INTO #EMPDetails VALUES('Mumbai',32145)
INSERT INTO #EMPDetails VALUES('Pune',14532)
-- CTE WAY
;WITH CTE1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY e.ID) AS RN,
e.NAME,
ed.PHONENumber
FROM
#Emp e JOIN #EmpDetails ed ON e.ID = ed.ID
)
SELECT * FROM CTE1 WHERE RN > 2
-- SUB QUERY WAY
SELECT * FROM #EmpDetails WHERE ID IN (SELECT ID FROM #EMP WHERE SALARY > 200)
-- DELETE DUPLICATE RECORDS FROM A TABLE
-- DELETE
DUPLICATE RECORDS FROM A TABLE:
CREATE TABLE #EMP
(
NAME NVARCHAR(100), SALARY BIGINT
)
INSERT INTO #EMP VALUES('One',100)
INSERT INTO #EMP VALUES('Two',200)
INSERT INTO #EMP VALUES('Three',300)
INSERT INTO #EMP VALUES('Three',300)
INSERT INTO #EMP VALUES('Four',400)
SELECT * FROM #EMP
;WITH CTEDup AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY SALARY ORDER BY (SELECT 1)) AS RN,
NAME,
SALARY
FROM #EMP
)
DELETE FROM CTEDUP WHERE RN > 1
SELECT * FROM #EMP
-------------------------------------------------------------------------
-- RANKING
FUNCTIONS
CREATE TABLE #Customer
(
NAME NVARCHAR(100), ProductName NVARCHAR(100), Amount BIGINT, ShopName NVARCHAR(100)
)
INSERT INTO #Customer VALUES('Vijay','Deo', 280, 'ParkAvenue')
INSERT INTO #Customer VALUES('Praveen','Perfume', 1000, 'KS')
INSERT INTO #Customer VALUES('Praveen','Shirt',1700, 'LP')
INSERT INTO #Customer VALUES('Tripuresh','Tee',900,'UCB')
INSERT INTO #Customer VALUES('Manish','Cap', 100, 'UCB')
SELECT * FROM #Customer
SELECT
ROW_NUMBER() OVER(ORDER BY NAME) AS OrderNumber,
NAME,
ProductName, Amount,
ShopName
FROM #Customer
SELECT
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ShopName) AS CustomerNumber,
NAME,
ProductName, Amount,
ShopName
FROM #Customer
SELECT
RANK() OVER(ORDER BY Name) AS CustomerNumber,
NAME,
ProductName, Amount,
ShopName
FROM #Customer
-- RANK will give output as 1,1,3,4,5
SELECT
DENSE_RANK() OVER(ORDER BY Name) AS CustomerNumber,
NAME,
ProductName, Amount,
ShopName
FROM #Customer
-- DENSE_RANK will give output as 1,1,2,3,4
