Scribd
Upload
7 views16 pages

SQL Server Interview Questions Guide

This document contains 12 SQL Server scenario based interview questions and answers. Each question provides a sample table with data and asks the candidate to write a SQL query to solve the problem. The questions cover topics like PIVOT, UNPIVOT, window functions, ranking functions, deleting duplicates, and more. Sample data and queries are provided as answers for each question.

Uploaded by

Mastan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views16 pages

SQL Server Interview Questions Guide

This document contains 12 SQL Server scenario based interview questions and answers. Each question provides a sample table with data and asks the candidate to write a SQL query to solve the problem. The questions cover topics like PIVOT, UNPIVOT, window functions, ranking functions, deleting duplicates, and more. Sample data and queries are provided as answers for each question.

Uploaded by

Mastan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQLScientist.

com
Pages

 Home
 About Me
 Contact Us
 Portfolio
 SQL Server Interview Questions

Wednesday, March 5, 2014

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 asvarchar) Sa
lary,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 3th and
5th record as below.

Query:
select

*
from
(
select
*,
row_number() over(order by id) rownumber
from @employee
)t
where
[Link] = 3 or [Link] = 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
[Link] = 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
[Link] <> 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
[Link],
b.column_name,
column_value =
case b.column_name
when 'Name' then [Link]
when 'Salary' then [Link]
when 'Age' then [Link]
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 [Link] > all(select [Link] from @employeeE1 where
[Link] <> [Link])

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 [Link] < all(select [Link] from @employee E1 where
[Link] <> [Link])

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 whereProductID = t.P
roductID for xml path('')),2,8000) AmountList
)t1
Posted by Unknown at 5:38 PM
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: SQL Server Scenario Based Interview Question

5 comments:

1.

Sriharirao KMay 12, 2015 at 1:25 PM

it's very useful to me thank very much asif ghanchi

ReplyDelete

Replies

Reply

2.

siva reddyFebruary 28, 2017 at 11:29 PM

nice work

ReplyDelete

Replies

Reply

3.

AnonymousAugust 21, 2017 at 10:18 PM

Hello colleagues, how is everything, and what you want to say concerning this post, in my
view its truly
remarkable in favor of me.

ReplyDelete

Replies

Reply

4.
AnonymousAugust 26, 2017 at 11:54 PM

You actually make it seem so easy with your presentation but


I find this topic to be actually something which I think I would never understand.
It seems too complex and extremely broad for me. I'm looking
forward for your next post, I will try to get the hang
of it!

ReplyDelete

Replies

Reply

5.

AnonymousAugust 27, 2017 at 5:05 PM

Article writing is also a fun, if you be familiar with after that


you can write if not it is difficult to write.

ReplyDelete

Replies

Reply

Add comment
Load more...
Newer Post Older Post Home
Subscribe to: Post Comments (Atom)
Popular Posts

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 ( ...

The OLE DB provider "[Link].12.0" has not been registered.


This is most well known error when we are trying to access excel from SQL Server. To fix this issue, we need to follow below

Auto Maintain Archive, Purge & Purge on Source Table in SQL Server

We are developing many application where we are auditing, archiving and purging data also for many tables. Most of the app

Setup Kerberos Authentication for SQL Server

Below are steps to configure Kerberos Authentication for SQL Server. 1) Open Active Directory 2) Go to the properties of Ser

Get column header list from Excel using Open XML SDK 2.5

There are many applications which are using to insert, update and delete data in database using Excel file. In this type of requ

Auto Audit Column Wise Change Data Capture (CDC)

Change data capture is used to audit insert, update, and delete activity for table in SQL Server table. You can go through belo

Different ways to execute/store SSIS packages

Before going ahead, we should be aware about how many ways we can store SSIS packages File system SQL Server (Integr

Unzip all *.zip files from folder including child folders


I am writing this blog for one on my colleague Ashish Gupta who wants to do this task for his Production Deployment repeated

Error Logging and Alerts in SQL Server

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily lif

The custom tool 'MSLinqToSQLGenerator' failed. Object reference not set to an instance of an object.

Today, I come to this error in Visual studio 2010 when I tried to place procedures in LINQ. I also tried to run Custom tool by rig

Labels

 .NET (2)
 Algorithm (10)
 [Link] (4)
 Batch File (5)
 C# (14)
 jQuery (1)
 MICROSOFT SQLSERVER (1)
 Oracle (5)
 SQL Server (26)
 SQL Server Interview Question (1)
 SQL Server Scenario Based Interview Question (3)
 SSIS (3)
 Task Scheduler (2)
 TFS (1)
 Windows Azure SQL Database (1)

Blog Archive

 ► 2018 (1)
o ► March (1)

 ► 2016 (10)
o ► March (10)

 ► 2015 (6)
o ► July (2)
o ► March (2)
o ► February (2)

 ▼ 2014 (24)
o ► November (4)
o ► June (1)
o ► April (6)
o ▼ March (11)
 Schedule Data Backup from Production to Developmen...
 Unable to make the session state request to the se...
 How to prefix 0 to make same length string for col...
 SQL Server Theoretical Interview Question
 Script to view Dependencies for Database in SQL Se...
 Validation of viewstate MAC failed. If this applic...
 The custom tool 'MSLinqToSQLGenerator' failed. Obj...
 SQL Server Scenario Based Interview Question
 Auto Audit Column Wise Change Data Capture (CDC)
 Steps to deploy SSIS package into Integration Serv...
 Setup Kerberos Authentication for SQL Server
o ► February (2)

 ► 2013 (15)
o ► September (1)
o ► August (5)
o ► July (6)
o ► June (3)

 ► 2012 (3)
o ► October (3)

 ► 2011 (2)
o ► November (1)
o ► April (1)

 ► 2010 (6)
o ► November (2)
o ► September (2)
o ► June (2)

 ► 2009 (1)
o ► December (1)

Follow by Email

Submit

Followers
Total Pageviews

Ethereal theme. Powered by Blogger.

Common questions

Powered by AI

The UNPIVOT operation in SQL is used to transform columns of a table into rows, which is the reverse of PIVOT. This is useful when you want to normalize data. During this process, NULL values are not typically included by default. For instance, to UNPIVOT columns like Name, Salary, Age in an employee table, you employ: `UNPIVOT (ColumnValue for ColumnName in (Name, Salary, Age))`. To include NULLs, a CROSS JOIN operation can be added alongside case statements to manually insert and manage NULL transformations properly .

To optimize query execution in SQL Server with high data volumes and aggregations, you can use indexing strategies like creating clustered indexes on frequently used columns, employing proper indexing on JOIN and WHERE clause columns, and leveraging covering indexes. Query hints and execution plan analysis assist in detecting bottlenecks. Also, partitioning large tables and reorganizing indexes regularly can drastically improve performance alongside efficient memory usage settings .

SQL Server handles audit, archive, and purge operations by employing triggers and stored procedures that systematically log changes, move data into archival databases, and remove outdated entries. Challenges include maintaining data integrity during live updates, managing large transaction logs, and ensuring minimal interference with ongoing database operations. Ensuring backups, optimizing queries, and employing partitioning are key techniques to mitigate these challenges .

To delete duplicate records in SQL while retaining ones with the minimum salary, you can use the ROW_NUMBER() function. By self-joining using a common key such as 'id', you order the duplicates using `ORDER BY salary ASC` creating a temporary table with row numbers. Then, you delete entries where rownumber is greater than 1, maintaining the row with the minimum salary. This is efficient for managing large datasets .

Change Data Capture in SQL Server allows for tracking column-wise changes by capturing insert, update, and delete transactions. Implemented via CDC, it writes changes to a separate capture instance, recording which columns changed. This method supports compliance and analytics but can increase storage requirements and impact performance due to added data processing requirements. Efficient setup usually involves only critical tables while periodically purging old captured data .

The PIVOT operation in SQL is used to transform a set of rows into columns. This operation is typically used when you want to transform unique values from one column into multiple columns in the output, and perform aggregation on them. For instance, given a table with columns like (column_name), you can pivot such that each distinct entry becomes a new column. Example: Using a table with entries ('AAA', 'BBB', 'CCC'), a PIVOT operation can be used to select [AAA] as the first column, [BBB] as the second, and [CCC] as the third, by using `PIVOT (max(column_name) for column_name in ([AAA],[BBB],[CCC]))`. This operation essentially diversifies the rows into structured columns .

SQL ranking functions such as ROW_NUMBER, RANK, and DENSE_RANK are applied to assign a unique rank to rows within a result set. For example, in an employee salary table partitioned by department_id, these functions can be used to differentiate ranks: ROW_NUMBER assigns a sequential integer to rows within a partition; RANK assigns the same number to identical values causing gaps in rankings; DENSE_RANK assigns consecutive numbers without gaps. Applying them to a table with `department_id` using `ORDER BY salary` provides insights into salary rankings within each department .

Cross joins in SQL create a Cartesian product between two tables, resulting in all possible combinations. This feature can be used, for example, to replicate certain rows for multiple specified column values. A common use case is when combining fixed set labels or categories with a main dataset, such as binding certain properties to every row dynamically. By joining a fixed set of column names with every row and using conditions or computations, you can ensure that information couples with criteria without direct table alterations .

To retrieve employees earning the highest salary within each department in SQL, you utilize the ROW_NUMBER() function with PARTITION. The query partitions by `department_id` and orders by `salary DESC`, then filters with `WHERE rownumber = 1` to select the first record (highest salary) from each partition. This approach effectively isolates top earners per department .

ISNULL and COALESCE are both used to handle NULL values in SQL but differ in function. ISNULL takes two parameters and returns the first parameter if it is not NULL; otherwise, it returns the second parameter. It is limited to one alternate value . COALESCE can evaluate multiple expressions and returns the first non-NULL expression from a list, which provides more flexibility in handling multiple potential NULLs. Hence, COALESCE can be used for a broader range of scenarios .

You might also like