Wednesday, March 5, 2014

SQL Server Scenario Based Interview Question

1. There’s a table with below data

clip_image001[5]
Write a query which will give below output.
clip_image002[3]
Query:
declare @input as table
(
       column_name varchar(100)
)


insert into @input values('AAA')
insert into @input values('BBB')
insert into @input values('CCC')


select * from @input


select
       [AAA],min([BBB]),min([CCC])
from
       @input
pivot
(
       max(column_name) for column_name in ([AAA],[BBB],[CCC])
)pv
group by
cube([AAA])

2. There’s a table with below data
clip_image001[6]
Write a query which will give below output.
clip_image003[3] 
Query:
declare @input as table
(
       column_name varchar(100)
)
insert into @input values('AAA')
insert into @input values('BBB')
insert into @input values('CCC')

select * from @input

select
       [AAA] FirstColumn,
       [BBB] SecondColumn,
       [CCC] ThirdColumn
from
       @input
pivot
(
       max(column_name) for column_name in ([AAA],[BBB],[CCC])
)pv
union
select
       null,[AAA],[BBB]
from
       @input
pivot
(
       max(column_name) for column_name in ([AAA],[BBB],[CCC])
)pv
order by [AAA] desc


3. There’s a table with below data
clip_image004[3] 
Write a query which will give below output.
clip_image005[3] 
Query:
declare @table as table(
       Id int,
       Name varchar(40),
       Salary decimal(18,2),
       Age int
)
insert into @table values(1,'asif',12.45,2)


select * from @table


select
       variable,value
from
       (
       select cast(Id as varchar) Id,cast(Name as varchar) as Name,cast(Salary as varchar) Salary,cast(Age as varchar) Age
       from
       @table
       ) t
unpivot(
       value FOR variable IN (Id,Name,Salary,Age)
)as unp;


4. Difference between ISNULL and COALESCE
ISNULL:
Replaces NULL with the specified replacement value.
Syntax: ISNULL ( check_expression , replacement_value )
Example: 
declare
       @var1 varchar(max) = 'val1',
       @var2 varchar(max) = 'val2'
select isnull(@var1,@var2)
Output:
clip_image006[3] 
set @var1 = null
select isnull(@var1,@var2) Output:

clip_image007[3] 

COALESCE: Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Syntax: COALESCE ( expression [ ,...n ] )
Example:
declare
       @var1 varchar(max) = 'val1',
       @var2 varchar(max) = 'val2',
       @var3 varchar(max) = 'val3',
       @var4 varchar(max) = 'val4'

select COALESCE(@var1,@var2,@var3,@var4)
Output:
clip_image008[3]
set @var1 = null


select COALESCE(@var1,@var2,@var3,@var4)
clip_image009[3]
set @var1 = null
set @var2 = null

select COALESCE(@var1,@var2,@var3,@var4)

clip_image010[3]
set @var1 = null
set @var2 = null
set @var3 = null


select COALESCE(@var1,@var2,@var3,@var4)
clip_image011[3]

5. There’s a table with below data. Write query for all rank function based on department_id.
clip_image012[3]
Query:
declare @employee as table(
       id int,
       department_id int,
       name varchar(100),
       salary decimal(18,2)
)


insert into @employee values(1,1,'A',1000)
insert into @employee values(2,1,'B',2000)
insert into @employee values(3,2,'C',3000)
insert into @employee values(4,2,'D',4000)
insert into @employee values(4,2,'E',5000)
insert into @employee values(4,3,'F',6000)


select * from @employee

select
       *,
       row_number() over(order by id) rownumber,
       dense_rank() over(order by id) denserank,
       rank() over(order by id) ranknumber,
       ntile(4) over(order by department_id) ntilenumber
from @employee Output:
clip_image013[3]

6. There’s a same table mentioned in above question. Write a query to get 3th and 5th record as below.
clip_image014[3]
Query:
select

       *
from
(
       select
              *,
              row_number() over(order by id) rownumber
       from @employee
)t
where
       t.rownumber = 3 or t.rownumber = 5


7. There’s a same table mentioned in above question. Write a query to get employee with highest salary for all departments as below.
clip_image015[3]
Query:
select
       *
from
(
       select
              *,
              row_number() over(partition by department_id order by salary desc) rownumber
       from @employee
)t
where
       t.rownumber = 1

8. There’s a same table mentioned in above question. Write a query to delete duplicate records for given column “id” and retain single record with minimum salary.
Query:
delete from T
from
(
       select
              *,
              row_number() over(partition by id order by salary asc) rownumber
       from @employee
)t
where
       t.rownumber <> 1
select * from @employee


9. There’s a table with below data.

Write a query which will give below output.
OR
How to Include NULL using UNPIVOT.
Below is query using UNPIVOT:
declare @employee as table(
       Id int,
       Name varchar(40),
       Salary decimal(18,2),
       Age int,
       InsertedBy bigint,
       UpdatedBy bigint
)

insert into @employee values(1,'A',1000,20,1,null)
insert into @employee values(2,'B',null,20,2,null)

select * from @employee


Select

       Id,

       ColumnName,
       ColumnValue,
       UpdatedBy
From
(
Select
       Id,
       Cast(Name as nvarchar(max)) Name,
       Cast(Salary as nvarchar(max)) Salary,
       Cast(Age as nvarchar(max)) Age,
       isnull(UpdatedBy,InsertedBy) UpdatedBy
From
       @employee
)T
Unpivot
(
       ColumnValue for ColumnName in (Name,Salary, Age)
) as UNP
Output:
Here, we can see that, it's excluding results for NULL. We will see how can we achieve it using CROSS JOIN to include NULL column. Below is query for same.
select
       a.ID,
       b.column_name,
       column_value =
    case b.column_name
      when 'Name' then a.Name
      when 'Salary' then a.Salary
      when 'Age' then a.Age
    end,
       UpdatedBy
from (
       Select
              Id,
              Cast(Name as nvarchar(max)) Name,
              Cast(Salary as nvarchar(max)) Salary,
              Cast(Age as nvarchar(max)) Age,
              isnull(UpdatedBy,InsertedBy) UpdatedBy
       From
              @employee
  ) a
cross join (
  select 'Name' union all
  select 'Salary' union all
  select 'Age'
  ) b (column_name)

10. There’s a table with below data.









Write a query to get highest salary of employee.

Query:
declare @employee as table(
id int,
department_id int,
name varchar(100),
salary decimal(18,2)
)
insert into @employee values(1,1,'A',1000)
insert into @employee values(2,1,'B',2000)
insert into @employee values(3,2,'C',3000)
insert into @employee values(4,2,'D',4000)
insert into @employee values(5,2,'E',5000)
insert into @employee values(6,3,'F',6000)

select * from @employee

select * from @employee E where E.salary > all(select E1.salary from @employee E1 where E.id <> E1.id)



11. There’s a table with below data.









Write a query to get highest salary of employee.

Query:
declare @employee as table(
id int,
department_id int,
name varchar(100),
salary decimal(18,2)
)
insert into @employee values(1,1,'A',1000)
insert into @employee values(2,1,'B',2000)
insert into @employee values(3,2,'C',3000)
insert into @employee values(4,2,'D',4000)
insert into @employee values(5,2,'E',5000)
insert into @employee values(6,3,'F',6000)

select * from @employee

select * from @employee E where E.salary < all(select E1.salary from @employee E1 where E.id <> E1.id)

12. There’s a table with below data










Write a query which will give below output.








Query:

declare @product as table(
ProductID int,
Amount int
)insert into @product
values(1,100),
(1,100),
(2,100),
(2,100),
(3,100),
(3,100),
(3,100)

select * from @product

select distinct ProductID,AmountList from @product t
outer apply
(
select substring((select ',' + cast(Amount as varchar) from @product where ProductID = t.ProductID for xml path('')),2,8000) AmountList
)t1

2 comments: