SQL and MSBI Practice Questions

Hey Folks! Few days back i attended an interview. I got few interesting questions, I am sharing few which i remembered :

Q1 : Interviewer need each character count in table. { Lets say there is a table with one column varchar data type, You need to find each character count}

Q2 : There is a txt file. It has only one column with some string and some numbers. You need to send string to one table and integer to other table with SSIS.

Q3 : There are two tables. both having one column as id. What could be minimum and maximum number of rows if we use inner join and first table having 2 rows and second table having 3 rows. {data could be anything in table}

Q4 : Write a query to find the name of the bank or banks whose revenue never went less from previous year. {data is shown below}

BankName	Year	Revenue(Cr)
ICICI	2013	515
ICICI	2014	602
BOI	2015	477
BOI	2016	564
Axis	2013	497
Hdfc	2013	503
Hdfc	2014	580
Axis	2016	666
BOI	2013	369
BOI	2014	366
ICICI	2015	715
ICICI	2016	780
Hdfc	2015	740
Hdfc	2016	760
Axis	2014	560
Axis	2015	480

---------------------------------------------------------------------
Ans1 :

CREATE TABLE #t (val NVARCHAR(10))

INSERT INTO #t
VALUES ('vimal')
 ,('kamal')

SELECT *
FROM #t;

WITH cte
AS (
 SELECT Val
  ,len(val) AS lnt
  ,val RemainingStr
  ,1 no
 FROM #t
 
 UNION ALL
 
 SELECT Cast(Right(RemainingStr, 1) AS NVARCHAR(10))
  ,lnt - 1
  ,SUBSTRING(RemainingStr, 1, len(RemainingStr) - 1)
  ,no + 1
 FROM cte
 WHERE lnt > 0
 )
 ,ct
AS (
 SELECT Val
 FROM cte
 WHERE no > 1
 )
SELECT Val
 ,COUNT(Val) Occurance
FROM CT
GROUP BY Val

DROP TABLE #t

Ans2 : 

Ans 3 : 

Minimum : No rows [No join match]

Maximum : 6 [all values are same like 1]

Ans 4 : 

CREATE TABLE Bank (
 BankName VARCHAR(100)
 ,Year INT
 ,Revenue MONEY
 )

INSERT INTO Bank
VALUES (
 'ICICI'
 ,2013
 ,515
 )
 ,(
 'ICICI'
 ,2014
 ,602
 )
 ,(
 'BOI'
 ,2015
 ,477
 )
 ,(
 'BOI'
 ,2016
 ,564
 )
 ,(
 'Axis'
 ,2013
 ,497
 )
 ,(
 'Hdfc'
 ,2013
 ,503
 )
 ,(
 'Hdfc'
 ,2014
 ,580
 )
 ,(
 'Axis'
 ,2016
 ,666
 )
 ,(
 'BOI'
 ,2013
 ,369
 )
 ,(
 'BOI'
 ,2014
 ,366
 )
 ,(
 'ICICI'
 ,2015
 ,715
 )
 ,(
 'ICICI'
 ,2016
 ,780
 )
 ,(
 'Hdfc'
 ,2015
 ,740
 )
 ,(
 'Hdfc'
 ,2016
 ,760
 )
 ,(
 'Axis'
 ,2014
 ,560
 )
 ,(
 'Axis'
 ,2015
 ,480
 )

---------------------------
SELECT *
FROM Bank
ORDER BY BankName
 ,Year ASC;

WITH BankCTE
AS (
 SELECT *
  ,LEAD(Revenue) OVER (
   PARTITION BY BankName ORDER BY Year ASC
   ) NextYearRevenue
 FROM Bank
 )
 ,FindBank
AS (
 SELECT BankName
 FROM BankCTE
 WHERE NextYearRevenue < Revenue
  AND NextYearRevenue IS NOT NULL
 )
SELECT BankName
FROM Bank
GROUP BY BankName

EXCEPT

SELECT BankName
FROM FindBank

SCD Type2 with sql server Merge statement or SSIS package

Hi folks, Few days back i was working on SCD Type2. SCD is slowly changing dimensions & Type2 is used for managing history. I have implemented it through SSIS package however i am also describing sql merge statement to implement it.
I have few execution segments. I am explaining the order of execution of these segments. It is necessary because i am using some segment code in SSIS as well-

/*Segment1 */

Create table StagingEmployee(EmployeeCode int, EmployeeName varchar(100), Address varchar(100), 
 ChecksumData int Default -1);

Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1001,'Vijay','Noida');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1002,'Manoj','Gurugram');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1003,'Jai','Delhi');

Update StagingEmployee Set ChecksumData=BINARY_CHECKSUM(EmployeeCode,EmployeeName,Address);

Create table dimEmployee(Employeeid int identity(1,1),Employeecode int,EmployeeName varchar(100),
  Address varchar(100),CreatedDate datetime null Default Getdate(),ExpirationDate datetime null,
  CurrentRecord char(1) Default 'Y',DimChecksum int Default -1);

Select * from StagingEmployee
Select * from dimEmployee


/*Segment2 */

Insert into dimEmployee(EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum)
Select EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum
From
(
 Merge into dimEmployee as T
 Using StagingEmployee as S
  ON T.EmployeeCode=S.EmployeeCode
 When Matched and T.DimChecksum<>S.ChecksumData and T.CurrentRecord='Y'
 Then
  Update Set
  ExpirationDate=GetDate()-1,
  CurrentRecord= 'N'
 When Not Matched
 Then 
  Insert(EmployeeCode,EmployeeName,Address,DimChecksum)
  Values(S.EmployeeCode,S.EmployeeName,S.Address,S.ChecksumData)
 Output
  $action,S.EmployeeCode,S.EmployeeName,S.Address,GetDate(),Null,S.ChecksumData
) as Tab 
  (action,EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum)
where action='UPDATE';

Select * from StagingEmployee
Select * from dimEmployee


/*Segment3 */

Truncate table StagingEmployee;
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1001,'Vijay','Pune');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1005,'Varun','Mumbai');
Update StagingEmployee Set ChecksumData=BINARY_CHECKSUM(EmployeeCode,EmployeeName,Address);

/*Segment4 */ -- <----Execute2 code

Select * from StagingEmployee
Select * from dimEmployee
/*
Truncate table StagingEmployee;
Truncate table dimEmployee;
Drop table StagingEmployee;
Drop table dimEmployee;
*/

SQL SERVER :

Execute Segment1 ---> Segment2 --->Segment3 --->Segment2 --->Segment4
Check result after each segment.

Now execute Segment4

SSIS :

Execute Segment1 from above code.
Open BIDS or SSDT ---> Open/Create SSIS project --->Create new project
Create a connection string and drag data flow task and rename it as below :

Click on SCD Type2 (Control flow task) --> drag a source assistant and connect with table StagingEmplohyee and name it StagingEmployee

 

Now drag a slowly changing dimension and connect it from stagingEmployee(Source)
Click on slowly changing dimension and one wizard will open then follow like below :

 

 

Now it will look like this :

Now run the package and try the result in sql server management studio.

 





If you will check the result then DimChecksum value not correct so here either we can remove the column as we don't need it or we can correct it with the help of some package correction.
Also you need to correct some other components as below :

Correction on OLEDB Command as below (in sql query section and no change in mapping)

Correction on Insert destination > Mapping the checksumdata and then execute Segment4

Now Execute Segment1 ---> Run the package see the results

Now Execute Segment3 ---> run the package and check the results and then Execugte Segment4

Powershell to execute sql script on all server

Hi folks, I was working on some maintenance activity where i have to execute one sql script on all sql server in domain and collect the result on csv file.
So I created one txt file in which i kept all server list. I also put sql script on same folder. then i wrote below script on powershell. I hope it will help you too.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$Serverlist= Get-Content "D:\Lab\PowerShellTest\AllServerScript\ServerList.txt"
$SqlScript= "D:\Lab\PowerShellTest\AllServerScript\Script.sql"
$Outputfile= "D:\Lab\PowerShellTest\AllServerScript\Report_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv"
$Logfile= "D:\Lab\PowerShellTest\AllServerScript\Log_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).txt"

#=====================
#Sql script to execute
#=====================
@(
ForEach($ServerName in $Serverlist)
{
try {
write-host $ServerName
Invoke-Sqlcmd -InputFile $SqlScript -serverinstance $ServerName -Database master}
Catch {

$ServerName | Out-File $Logfile -Append
$_ | Out-File $Logfile -Append
}}
) | export-csv -NoTypeInformation -Path $Outputfile


If you want to learn PowerShell, please follow below link-

Learn PowerShell with Vimal Lohani

Interview Question : Adding Each character of a number in a table

CREATE TABLE addnum ( id INT identity(1, 1) ,
                             val INT )
INSERT INTO addnum
SELECT ABS(CAST(NEWID() AS BINARY (6)) % 1000) + 1 randomNumber GO 1000
SELECT *
FROM addnum

;

WITH cte AS
  (SELECT id,
          cast(val AS varchar(10)) val,
          len(val) leng
   FROM addnum),
     cte1 AS
  (SELECT substring(val,1,1) x,
          id,
          1 num
   FROM cte
   UNION ALL SELECT Isnull(substring(cte.val,num+1,1),'0'),
                    cte.id,
                    num+1
   FROM cte1
   JOIN cte ON cte1.id=cte.id
   AND cte1.num<cte.leng)
SELECT cte.val,
       cte.id,
       sum(cast(x AS int)) addition
FROM cte1
JOIN cte ON cte.id=cte1.id
GROUP BY cte.val,
         cte.id
ORDER BY id

Dynamic Restore Script

Hi Folks! Few days back i was working on a project, I was trying to automate few things, I will share most of my work in next blogs, so i have also automate restore process for database having multiple files. It will work well with any number of files. You need to provide database name and backup location.

This script is for Sql Server. 

SET NOCOUNT ON

DECLARE @restoreTable TABLE (
 [LogicalName] NVARCHAR(128)
 ,[PhysicalName] NVARCHAR(260)
 ,[Type] CHAR(1)
 ,[FileGroupName] NVARCHAR(128)
 ,[Size] NUMERIC(20, 0)
 ,[MaxSize] NUMERIC(20, 0)
 ,[FileID] BIGINT
 ,[CreateLSN] NUMERIC(25, 0)
 ,[DropLSN] NUMERIC(25, 0)
 ,[UniqueID] UNIQUEIDENTIFIER
 ,[ReadOnlyLSN] NUMERIC(25, 0)
 ,[ReadWriteLSN] NUMERIC(25, 0)
 ,[BackupSizeInBytes] BIGINT
 ,[SourceBlockSize] INT
 ,[FileGroupID] INT
 ,[LogGroupGUID] UNIQUEIDENTIFIER
 ,[DifferentialBaseLSN] NUMERIC(25, 0)
 ,[DifferentialBaseGUID] UNIQUEIDENTIFIER
 ,[IsReadOnly] BIT
 ,[IsPresent] BIT
 ,[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
 )

INSERT INTO @restoreTable
EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @backuppath + '''')

--SELECT * FROM @fileListTable
SET @Query = 'RESTORE DATABASE [' + @Dbname + '] FROM  DISK = N''' + @backuppath + ''' 
WITH  FILE = 1,'

SELECT @Query = @Query + ' 
MOVE N''' + LogicalName + ''' TO N''' + PhysicalName + ''','
FROM @restoreTable

SET @Query = @Query + ' 
NOUNLOAD,  REPLACE,  STATS = 5'

PRINT @Query
--Exec(Query)

Interview Question : Create attendance report through sql server query

Hi Everyone! Few months back, i attended sql server developer interview, My interviewer took me to the system and asked me to create complete script for attendance management monthly report in sql server.
I am sharing this question so that this may help you too :

Lets assume we have swapping card system, so when we swap or punch our employee card, logs are generated and they are saved at ATTEN_inout table in sql server.

CREATE TABLE ATTEN_inout (
 NAME VARCHAR(50)
 ,DATE DATETIME
 ,PRESENT_STATUS VARCHAR(10)
 ,TIME TIME
 ,inout BIT --0=IN 1=OUT
 )

INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-01','PRESENT','09:00',0)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-01','PRESENT','18:00',1)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-01','PRESENT','08:47',0)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-01','PRESENT','14:23',1)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-02','PRESENT','10:12',0)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-02','PRESENT','16:19',1)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-03','PRESENT','11:11',0)
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-03','PRESENT','17:10',1)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-03','PRESENT','09:12',0)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-03','PRESENT','18:12',1)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-13','PRESENT','11:12',0)
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-13','PRESENT','17:30',1)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-25','PRESENT','09:32',0)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-25','PRESENT','18:54',1)  
SELECT * FROM ATTEN_inout

So now we need to find insights from this table and process that data and create monthly attendance report.

declare @startdate datetime ='07/01/2016'
declare @enddate datetime ='07/31/2016'

--Generating complete month dates
;with cte
as 
(select @startdate dt
union all
select dt+1 from cte where dt<@enddate
)
select * into #table from cte
--select * from #table

Declare @col varchar(max)=''
Select @Col=@col+'['+ Cast(Convert(Date,dt) as varchar) +'],' from #table
select @col=SUBSTRING(@col,1,datalength(@col)-1)
--select @col

Declare @query varchar(max)=''
Set @query='
select *  from 
(SELECT a.name,a.present_status,t.dt,cASE WHEN INOUT=0 THEN ''IN'' ELSE ''OUT'' END INOUT,TIME
FROM ATTEN_inout a JOIN #table t on a.Date=t.dt )k
PIVOT
(MIN(TIME) FOR dt IN ('+@col+')
)x'

--print(@query)
exec(@query)

Declare @column varchar(max)=''
Select @column=@column+'[IN '+ Cast(Convert(Date,dt) as varchar) +']  time null,'+'[OUT '+ Cast(Convert(Date,dt) as varchar) +']  time null,' from #table
select @column=SUBSTRING(@column,1,datalength(@column)-1)
--print @column

Declare @newqry nvarchar(max)
set @newqry= 'Create table report(name varchar(100), '+ @column +');';
exec(@newqry)

Declare @inqry varchar(max)=''
Select @inqry=@inqry+'['+name+ '],' from sys.columns where object_id=OBJECT_ID('report') AND name like 'IN%'
set  @inqry=SUBSTRING(@inqry,1,datalength(@inqry)-1 )
--print @inqry

Declare @qin nvarchar(max)
Set @qin='Insert into report (name, '+ SUBSTRING(@inqry,1,datalength(@inqry))+')  Select name, '+ @col +' from ('+@query+ ' Where inout=''in'')y'
print (@qin)
exec (@qin)


Declare @outqry varchar(max)=''
Select @outqry=@outqry+'['+name+ ']=['+SUBSTRING(name,5,DATALENGTH(name))+'],' from sys.columns where object_id=OBJECT_ID('report') AND name like 'Out%'
set  @outqry=SUBSTRING(@outqry,1,datalength(@outqry)-1 )
print @outqry
Set @qin= 'update r set '+@outqry+' from report r join ('+@query +') q on q.name=r.name and q.inout=''out'''
exec (@qin)

SELECT * FROM REPORT
drop table #table
drop table report
Drop table ATTEN_inout

Get time for restore or backup a database

SELECT percent_complete,

       CAST((estimated_completion_time/3600000) AS varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 AS varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 AS varchar) + ' sec' AS est_time_to_go,

       command,

       s.text,

       start_time,

       CAST(((DATEDIFF(s,start_time,GetDate()))/3600) AS varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 AS varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) AS varchar) + ' sec' AS running_time,

       dateadd(SECOND,estimated_completion_time/1000, getdate()) AS est_completion_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command IN ('RESTORE DATABASE',

                    'BACKUP DATABASE',

                    'RESTORE LOG',

                    'BACKUP LOG',

                    'RESTORE HEADERON')