Sql server General T-SQL Interview questions

How to Delete duplicate Records:

here am showing the examples how to delete duplicate records in sqlserver.

First create the table with sample data

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'

first method.using cte with row_number

;with cte as

(

select ID,name,ROW_NUMBER() over(PARTITION BY NAME ORDER BY ID) as row from #test

)

delete from CTE

where row>1

select * from #test

go

drop table #test

go

second method.using group by clause

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'
go

delete from #test

where id in (select MAX(ID) from #test

group by name

having COUNT(*)>1)

select * from #test

go

drop table #test

Suppose there is no unique key  in table. using the below sample query you can delete the duplicate data.

create table test (name varchar(10))

insert Test

select 'malli'

union all

select 'malli'

union all

select 'ram'

union all

select 'ram'

;with cte as

(

  select row_number() over (partition by name order by name) as row,name from test

)

delete from cte where row>1

select * from Test

drop table Test

2) How to get top n records from a give table

using ranking function.

;with cte

as

(

select id,dense_rank() over ( order by id desc) as row

from systables

)

select id from cte where row between 6 and 10   ---top 6-10 records

go

using top 

declare @date datetime;

set @date=getdate()

select top 5 id

from sysobjects

where id in (select top 10 id

from sysobjects order by id desc

)

order by id asc    ---top 6-10 records

3) Suppose i have 3 tables with same data.First table have clustered index ,second table have non clustered
index and third table have no index. suppose i issue a command

select * from tablename

which will execute faster.

declare @time datetime2;

set @time=GETDATE()

select * from NOIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from CLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from NONCLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

go

4)  Below is the Employee table which has the four feilds that contain the data. Assume ManagerID is Dept ID. You need to get second highest salary for each department.

Table

-----------

Expected output 

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

Using the Ranking functions we can get the desired output. 

1) Row_number provides the 3 entries even if tiep-up with the with same salary

2) Dense_rank and rank will give the match and returns mutiple rows.

WITH CTE
AS (
	SELECT ID
		,name
		,AnnualSalary
		,ManagerID
		,row_number() OVER (
			PARTITION BY ManagerID ORDER BY AnnualSalary DESC
			) AS rnk
	FROM Table1
	)
SELECT ID
	,name
	,AnnualSalary
	,ManagerID
FROM CTE
WHERE rnk = 2;

WITH CTE
AS (
	SELECT ID
		,name
		,AnnualSalary
		,ManagerID
		,dense_rank() OVER (
			PARTITION BY ManagerID ORDER BY AnnualSalary DESC
			) AS rnk
	FROM Table1
	)
SELECT ID
	,name
	,AnnualSalary
	,ManagerID
FROM CTE
WHERE rnk = 2;

CREATE TABLE Table1 (
	ID INT
	,Name VARCHAR(50)
	,AnnualSalary BIGINT
	,ManagerID INT
	)

INSERT INTO Table1
VALUES (
	1
	,'Lisa'
	,150000
	,NULL
	)
	,(
	2
	,'Dan'
	,110000
	,1
	)
	,(
	3
	,'Oliver'
	,180000
	,1
	)
	,(
	4
	,'Dave'
	,100000
	,1
	);

INSERT INTO Table1
VALUES (
	5
	,'Steve'
	,200000
	,NULL
	)
	,(
	6
	,'Alice'
	,2050000
	,5
	)
	,(
	7
	,'Damian'
	,100000
	,5
	)
	,(
	8
	,'Avery'
	,210000
	,5
	)
	,(
	9
	,'Mark'
	,210000
	,5
	);

SELECT *
FROM Table1

5) Returns pages from a cook book alternatevly 

CREATE TABLE [dbo].[recipes] (
	[page_no] [int] NULL
	,[title] [varchar](50) NULL
	) ON [PRIMARY]
GO

DECLARE @table TABLE (
	pageno INT
	,title VARCHAR(30)
	)

INSERT INTO @table
VALUES (
	1
	,'pancake'
	)
	,(
	2
	,'pizza'
	)
	,(
	3
	,'pasta'
	)
	,(
	5
	,'cookie'
	);

WITH cte_pages
AS (
	-- generate page numbers
	SELECT 0 n
		,MAX(pageno) maxpgno
	FROM @table
	
	UNION ALL
	
	SELECT n + 1 n
		,maxpgno
	FROM cte_pages
	WHERE n <= maxpgno
	)
	,cte_left
AS (
	--- even
	SELECT n
		,ROW_NUMBER() OVER (
			ORDER BY n
			) rn
	FROM cte_pages
	WHERE n % 2 = 0
	)
	,cte_right
AS (
	--- odd
	SELECT n
		,ROW_NUMBER() OVER (
			ORDER BY n
			) rn
	FROM cte_pages
	WHERE n % 2 <> 0
	)
SELECT tl.title left_title
	,tr.title right_title
FROM cte_left l
INNER JOIN cte_right r ON l.rn = r.rn
LEFT OUTER JOIN @table tl ON tl.pageno = l.n
LEFT OUTER JOIN @table tr ON tr.pageno = r.n

6)  Last person to get into lift with max weight of 1000

SELECT TOP 1 name
FROM liftline t1
WHERE (
		SELECT SUM(t2.weight)
		FROM liftline t2
		WHERE t2.turn <= t1.turn
		) <= 1000
ORDER BY t1.turn DESC