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

No comments:

Post a Comment