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.
--101. Write down the query to display all employee name in one cell seprated by ',' ex:-"Vikas, nikita, Ashish, Nikhil , anish"
Solution 2:
--102. Write down the query to get ProjectName and respective EmployeeName(firstname) which are working on the project,
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:-
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"
ANS:
Solution 1:
SELECT STUFF(( SELECT ', ' + E.FirstName FROM [EmployeeDetail] AS E FOR XML PATH('')), 1, 2, '') AS [All Emp Name]
Output:-
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:
SELECT t.*
ReplyDeleteFROM 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;
Nice to see your comment... keep it up...
ReplyDeleteI guess in query 102 there was no need of a inner join to outer query thus it can be modified as:
ReplyDeleteSELECT
DISTINCT P1.ProjectName,
(SELECT FirstName +',' FROM EmployeeDetail WHERE EmployeeID = P1.EmployeeDetailID FOR XML PATH(''))
FROM ProjectDetail P1
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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteQuery 103:
ReplyDeleteSelect 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;
thanks....
DeleteRichard,
DeleteI don't exactly understand how the query works
SELECT F2.Fuel AS 'Fuel quantity Now', F2.Date
ReplyDelete, 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
its not returning right result
Deleteselect c.id, c.Fule,c.Dates,d.Fule,d.Dates from fuleDetail c
ReplyDeleteinner 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)))
that fuel question it self i didnt get please make me understand that please
ReplyDeleteHi 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
Deletenice topic
ReplyDeleteSELECT
ReplyDeleteT1.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
SELECT
ReplyDeleteT1.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
SELECT ProjectName, STUFF((SELECT ', ' + FirstName FROM EmployeeDetail E1 INNER JOIN [ProjectDetail] P1 ON E1.EmployeeID = P1.EmployeeDetailID
ReplyDeleteWHERE 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
For those who want to copy query..
Deletehi vikas i didn't understood how the query works for
Deleteall names in single column i want concept can you please...........
and if there is any pdf please share @shabashi2482@gmail.com
Query 103:
ReplyDeleteI 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
Thank u mahesh.
ReplyDeleteTry below query for the fuel problem. I got required output:
ReplyDeletedeclare @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
Query for the Fuel output. I was able to generate output as expected.
ReplyDeletedeclare @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
Hi Guys,
ReplyDeleteLast 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.
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