SQL INTERVIEW QUESTIONS (SCENARIO BASED) - Part 1

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