MS SQL SERVER TRICKY/COMPLEX INTERVIEW QUERIES QUESTIONS ANSWERS : PDF SET-10

MOST COMPLEX/TRICKY MS SQL SERVER (2005-08-12) QUERIES INTERVIEW QUESTIONS SET-10

This set contains most Tricky/complex MS SQL Server queries interview questions for experienced developers. If you think you are expert in sql, then go through this set and check that you are right or not.
This set contains most puzzled/complex interview queries so that this set is for experienced developers, even DBA can also try this set to check their join concept, so if you have more then 5 years of exp. then this is for you. Try it, .PDF will be available soon.

Related Tables :




--100. Write down the query to print first letter of a Name in Upper Case and all other letter in Lower Case.(EmployDetail table)
 ANS:
 SELECT UPPER(SUBSTRING(FirstName,1,1))+LOWER(SUBSTRING(FirstName,2,LEN(FirstName)-1)) AS [FirstName]
Output:-










--101. Write down the query to display all employee name in one cell seprated by ',' ex:-"Vikas, nikita, Ashish, Nikhil , anish"(EmployDetail table)
 ANS:
Solution 1:
 SELECT STUFF(( SELECT  ', ' + E.FirstName FROM [EmployeeDetail] AS E FOR XML PATH('')), 1, 2, '') AS [All Emp Name]
Output:-





Solution 2:


--102. Write down the query to get ProjectName and respective EmployeeName(firstname) which are working on the project,
--if more then one employee working on same project, then it should be in same cell seprated by comma
--for example :- Task Tracker : Vikas, Ashish
 ANS:
SELECT ProjectName, STUFF((SELECT   ', ' +  FirstName FROM EmployeeDetail E1 INNER JOIN [ProjectDetail] P1 ON E1.EmployeeID = P1.EmployeeDetailID
WHERE P1.ProjectName = P2.ProjectName FOR XML PATH('')),1,2,'' ) AS [Employee Name] FROM EmployeeDetail E2
INNER JOIN [ProjectDetail] P2 ON E2.EmployeeID = P2.EmployeeDetailID
GROUP BY ProjectName
Output:- 










AND THE VERY VERY COMPLEX QUERY HERE
--103: You have a table(FuelDetail) with ID, Fuel, And Date columns.
--Fuel column is contain fuel quantity at a particular time when car start traveling. So we need to find out that when the driver fill Petrol in his/her car.
--By FuelDetail Table image on the top of this post, you can understand the query. 
--Car start driving at 10 Am on 25th April with petrol(10 liter)
--at 11 AM Petrol was 9 liters
--at 12 AM petrol was 8 liters
--at 2 PM (14) petrol was 12 liters... 
--This means that he/she fill the petrol at 25th April 2014 at 2PM
--Next time he fill petrol at 7PM 25th April 2014
--and Next time he fill petrol at  11PM  25th April 2014
 ANS: 
Solution 1:
SELECT c1.fuel AS [Fuel quantity Now],c1.[Date],c.fuel AS [Fuel quantity Before],c.[Date]
FROM FuelDetail c
JOIN
FuelDetail c1 ON c1.[Date] =(SELECT MIN([Date]) FROM FuelDetail WHERE [Date]>c.[Date] )

WHERE c1.fuel>c.fuel

Solution 2:(by Eduardo Ramires) see in comment section
Select FD.ID, FD.Fuel, FD.Date,FD1.Fuel [Fuel Quantity Before],FD1.Date
from FuelDetail FD inner join FuelDetail FD1 on FD1.ID = (FD.ID-1)
and FD1.Fuel < FD.Fuel

Output will be:




Comments

  1. SELECT t.*
    FROM Tabla1 t
    left join (select taus.id+1 as idaux, fuel from tabla1 taus) t2 on t2.idaux = t.id
    where t.fuel > t2.fuel;

    ReplyDelete
  2. Nice to see your comment... keep it up...

    ReplyDelete
  3. I guess in query 102 there was no need of a inner join to outer query thus it can be modified as:

    SELECT
    DISTINCT P1.ProjectName,
    (SELECT FirstName +',' FROM EmployeeDetail WHERE EmployeeID = P1.EmployeeDetailID FOR XML PATH(''))
    FROM ProjectDetail P1

    ReplyDelete
  4. Well, explain MECHANISM by which query answer for Q) 103 executes to produces desired result for us as it really seems solved using very very complex JOINS.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Query 103:

    Select FD.ID,
    FD.Fuel,
    FD.Date,
    FD1.Fuel [Fuel Quantity Before],
    FD1.Date
    from FuelDetail FD
    inner join FuelDetail FD1
    on FD1.ID = (FD.ID-1)
    and FD1.Fuel < FD.Fuel;

    ReplyDelete
    Replies
    1. Richard,

      I don't exactly understand how the query works

      Delete
  7. SELECT F2.Fuel AS 'Fuel quantity Now', F2.Date
    , F1.Fuel AS 'Fuel quantity Before', F2.Date
    FROM FuelDetail F1
    INNER JOIN FuelDetail F2
    ON DATEDIFF(HOUR, F1.Date, F2.Date) = 1
    WHERE F1.Fuel < F2.Fuel

    ReplyDelete
  8. select c.id, c.Fule,c.Dates,d.Fule,d.Dates from fuleDetail c
    inner join (select *
    From FuleDetail a where a.Fule <(select b.fule from FuleDetail b where id=(a.id +1))) d on c.id =d.id +1
    where c.id in(select a.id+1
    From FuleDetail a where a.Fule <(select b.fule from FuleDetail b where id=(a.id +1)))

    ReplyDelete
  9. that fuel question it self i didnt get please make me understand that please

    ReplyDelete
    Replies
    1. Hi Mahesh, We just need to find out when car driver fill the patrol in his car. In fuel table you can see time and fuel quantity. if its decreasing then no fuel filled till now, but at 2PM its increased, its means fuel filled.. go in this way and try to understand, like this there are three time fuel filled.. you can see in the output. and see in the table on the top of this post

      Delete
  10. SELECT
    T1.FUEL AS 'FUEL NOW', T1.DATE AS 'FUEL NOW DATE',
    T3.FUEL 'FUEL BEFORE', T3.DATE AS 'FUEL BEFORE DATE'
    FROM FUELDETAIL AS T1
    CROSS APPLY
    (
    SELECT TOP 1 * FROM FUELDETAIL AS T2 WHERE (T1.FUEL > T2.FUEL AND T1.ID = (T2.ID + 1))
    ) AS T3

    ReplyDelete
  11. SELECT
    T1.FUEL AS 'FUEL NOW', T1.DATE AS 'FUEL NOW DATE',
    T3.FUEL 'FUEL BEFORE', T3.DATE AS 'FUEL BEFORE DATE'
    FROM FUELDETAIL AS T1
    CROSS APPLY
    (
    SELECT TOP 1 * FROM FUELDETAIL AS T2 WHERE (T1.FUEL > T2.FUEL AND T1.ID = (T2.ID + 1))
    ) AS T3

    ReplyDelete
  12. SELECT ProjectName, STUFF((SELECT ', ' + FirstName FROM EmployeeDetail E1 INNER JOIN [ProjectDetail] P1 ON E1.EmployeeID = P1.EmployeeDetailID
    WHERE P1.ProjectName = P2.ProjectName FOR XML PATH('')),1,2,'' ) AS [Employee Name] FROM EmployeeDetail E2
    INNER JOIN [ProjectDetail] P2 ON E2.EmployeeID = P2.EmployeeDetailID
    GROUP BY ProjectName

    ReplyDelete
    Replies
    1. For those who want to copy query..

      Delete
    2. hi vikas i didn't understood how the query works for
      all names in single column i want concept can you please...........
      and if there is any pdf please share @shabashi2482@gmail.com

      Delete
  13. Query 103:

    I think this problem might be solved recursivly as well
    /* initial setup */
    create table #FuelDetail(
    id int,
    fuel int,
    [Date] smalldatetime null
    )
    declare @data as smalldatetime = '2014-04-25 10:00:00'
    insert into #FuelDetail(id, fuel) values
    (1,10),(2,9),(3,8),(4,6),(5,12),(6,11),(7,10),(8,9),(9,8),(10,10),(11,9),(12,8),(13,7),(14,15)
    update #FuelDetail
    set [Date] = dateadd(HH, id-1, @data)

    /* solving problem query */

    ;with baza as (
    SELECT
    ID,
    fuel,
    fuel as usedFuel,
    0 as FuelBefore,
    [Date] as DateNow,
    [Date] as [DateBefore]
    FROM #FuelDetail WHERE ID = 1
    UNION ALL
    SELECT
    s.ID,
    s.fuel,
    s.fuel - b.fuel,
    b.fuel,
    s.Date,
    b.DateNow
    FROM baza b
    INNER JOIN #FuelDetail s on s.id = b.id + 1
    )
    SELECT
    b.ID,
    b.fuel as [Fuel qty Now],
    b.DateNow,
    b.FuelBefore,
    b.DateBefore
    FROM baza b
    WHERE
    b.usedFuel>0
    AND b.ID>1

    I would be grateful for comparing execution plan of given queries in solutions(1 and 2 and mine) and explanation of results. I am not so agile in veryfing which query provide better result

    ReplyDelete
  14. Try below query for the fuel problem. I got required output:
    declare @var1 int=1
    declare @var2 int
    declare @var3 int
    declare @var4 int
    declare @var5 datetime
    declare @var6 datetime
    set @var4= (select count(id) from fuel)
    create table ##fueltemp (idb int,fuelqeb int,dateb datetime,ida int,fuelqea int,datea datetime)

    while @var1 <= @var4
    begin
    set @var2=(select fuelqe from fuel where id=@var1)
    set @var5=(select fueldate from fuel where id=@var1)
    --print @var2
    set @var1=@var1+1
    set @var3=(select fuelqe from fuel where id=@var1)
    set @var6=(select fueldate from fuel where id=@var1)
    if (@var3 > @var2)
    begin
    insert into ##fueltemp (idb,fuelqeb,dateb,ida,fuelqea,datea) values (@var1-1,@Var2,@var5,@var1,@var3,@var6)
    end
    else
    continue
    end

    ReplyDelete
  15. Query for the Fuel output. I was able to generate output as expected.
    declare @var1 int=1
    declare @var2 int
    declare @var3 int
    declare @var4 int
    declare @var5 datetime
    declare @var6 datetime
    set @var4= (select count(id) from fuel)
    create table ##fueltemp (idb int,fuelqeb int,dateb datetime,ida int,fuelqea int,datea datetime)

    while @var1 <= @var4
    begin
    set @var2=(select fuelqe from fuel where id=@var1)
    set @var5=(select fueldate from fuel where id=@var1)
    --print @var2
    set @var1=@var1+1
    set @var3=(select fuelqe from fuel where id=@var1)
    set @var6=(select fueldate from fuel where id=@var1)
    if (@var3 > @var2)
    begin
    insert into ##fueltemp (idb,fuelqeb,dateb,ida,fuelqea,datea) values (@var1-1,@Var2,@var5,@var1,@var3,@var6)
    end
    else
    continue
    end

    ReplyDelete
  16. Hi Guys,

    Last query we can rewrite as below.


    SELECT *FROM (
    SELECT fc.id,fc.fuel,fc.date,
    (CASE WHEN fuel < LAG(fuel) OVER (ORDER BY FC.DATE) THEN 0 ELSE 1 END) fc2
    FROM FuelDetail fc
    ) tbl WHERE tbl.fc2 = 1

    Thanks.

    ReplyDelete
  17. Hi, i am new to sql i am not getting how the query works can any one explain how the min(date) sub query works and rest

    ReplyDelete

Archive

Contact Form

Send