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