SQL Server Scenario Based Interview Question

1. There’s a table with below data

Write a query which will give below output.

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

Write a query which will give below output.

 

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
 
Write a query which will give below output.
 
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:
 
set @var1 = null
select isnull(@var1,@var2) Output:
 

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:

set @var1 = null

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

set @var1 = null
set @var2 = null

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

set @var1 = null
set @var2 = null
set @var3 = null

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

5. There’s a table with below data. Write query for all rank function based on department_id.

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:

6. There’s a same table mentioned in above question. Write a query to get 3^th and 5^th record as below.

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.

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