Wednesday, 6 August 2014

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:




Visit Our New Site for HR Interview Questions Answers http://www.interviewboat.com/Company/GetCompany
Sponsored Ads

18 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