SQL Queries Interview Question and Answer Part 1

1. Delete Duplicate records from table
DECLARE @EMP TABLE
(
       ID INT,
       Name VARCHAR(100),
       Course VARCHAR(100)
)

INSERT INTO @EMP
SELECT 1,'S1','MCA' UNION ALL
SELECT 1,'S1','MCA' UNION ALL
SELECT 1,'S1','MCA' UNION ALL
SELECT 1,'S1','MCA' UNION ALL
SELECT 2,'S2','BCA' UNION ALL
SELECT 2,'S2','BCA' UNION ALL
SELECT 3,'S3','BSc'

Solution:

;WITH CTE AS
(
  SELECT ID,Name,Course,ROW_NUMBER() OVER(PARTITION BY Name,Course ORDER BY ID) AS RNo  FROM @EMP
)
DELETE CTE WHERE RNo > 1 -- Delete Duplicate records
--Check Final Result
SELECT * FROM @EMP 

2.Get Nth highest salary from Table
DECLARE @EMP TABLE
(
       ID INT,
       Name VARCHAR(100),
       Salary INT
)

INSERT INTO @EMP
SELECT 1,'Shatrughna',20000 UNION ALL
SELECT 2,'Deepak',30000 UNION ALL
SELECT 3,'Arun',40000 UNION ALL
SELECT 4,'Barik',50000

Solution:

SELECT MIN(Salary) FROM @EMP WHERE Salary IN
(SELECT DISTINCT TOP N Salary FROM @EMP ORDER BY Salary DESC)

3.Get all records where 4th character of value's field lies between 0-4
DECLARE @DATA TABLE
(
       VAL VARCHAR(100)
)


INSERT INTO @DATA
SELECT 'SAT1UTRE1' UNION ALL
SELECT 'DAT2UTRE1' UNION ALL
SELECT 'LAT3UTRE1' UNION ALL
SELECT 'CAT4UTRE1' UNION ALL
SELECT 'UAT5UTRE1' UNION ALL
SELECT 'KAT7UTRE1'

Solution:

SELECT * FROM @DATA
WHERE
VAL LIKE '___[0-4]%'

4.Get all missing numbers between 1-10

DECLARE @MSN TABLE
(VAL INT)

INSERT INTO @MSN
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 10

Solution:

;WITH CTE(SN)
AS
(
SELECT 1 SN
UNION ALL
SELECT SN+1 FROM CTE WHERE SN < 10
)
SELECT CTE.* FROM CTE
LEFT JOIN @MSN T1 ON T1.VAL=CTE.SN
WHERE
T1.VAL IS NULL

5. Find Cumulative Sum

DECLARE @Table TABLE
(ID INT,Amount INT)

INSERT INTO @Table
SELECT 1, 200 UNION ALL
SELECT 2, 200 UNION ALL
SELECT 3, 100 UNION ALL
SELECT 4, 350 UNION ALL
SELECT 5, 100

Solution:

SELECT T1.ID,T1.Amount,SUM(T2.Amount) AS 'Cumulative Sum'
FROM @Table T1 JOIN @Table T2 ON T2.ID <= T1.ID
GROUP BY T1.ID,T1.Amount
ORDER BY ID