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