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

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:




Related

SQL SERVER 647466167282953218

Post a comment

  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

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item