MS SQL SERVER COMPLEX INTERVIEW QUERY | CAR

Today I came with complex Sql server Query, You can face this query in interview but not in case of fresher sql server interview, It can b...

Today I came with complex Sql server Query, You can face this query in interview but not in case of fresher sql server interview, It can be asked to experienced sql server developer. I think If you write down this query, then you must impress your interviewer, and if you impressed your interviewer then you must cracked your interview.

So now lets talk about the query.
Query : You have a table with ID, Fuel, And Date columns.
Fuel column is contain fuel 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 below image you can understand the query. 
Car start driving at 10 Am on 25th April
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

So we need to find out the above date by query.


Solution of the is (by Deepak):
select c1.fuel,c1.[Date],c.fuel,c.[date]
from car c
join
car c1 on c1.[Date] =(select min([Date]) from car where [Date]>c.[Date] )
where c1.fuel>c.fuel




Second Solution you can try following Long Query (by Vikas):

Select * from
(
SELECT CASE
WHEN [Fuel] < fule1 THEN DATE1
ELSE
NULL
END AS [DATE]
FROM
(

select   A.[ID]
      ,A.[Fuel]
      ,m.[Fuel] as fule1
      ,A.[Date]
      ,M.[Date] AS DATE1
FROM
(SELECT TOP 2000 [ID]
      ,[Fuel]
      ,[Date]
  FROM [Vikas].[dbo].[Car]
  where [ID]%2 = 1
) A
  Join (
 SELECT TOP 2000 [ID]-1 as id
      ,[Fuel]
      ,[Date]
  FROM [Vikas].[dbo].[Car]
  where [ID]%2 = 0)
  M on A.ID = M.id
  ) R

Union all
SELECT CASE
WHEN [Fuel] < fule1 THEN DATE1
ELSE
NULL
END  AS [DATE]
FROM
(

select   A.[ID]
      ,A.[Fuel]
      ,m.[Fuel] as fule1
      ,A.[Date]
      ,M.[Date] AS DATE1
FROM
( SELECT [ID] as [ID]
      ,[Fuel]
      ,[Date]
  FROM (  SELECT TOP 2000 [ID]-1 AS ID
      ,[Fuel]
      ,[Date]
  FROM [Vikas].[dbo].[Car]
  WHERE [ID] NOT IN (1)) T
  where [ID]%2 = 1
) A
  Join (
 SELECT [ID]-1 as id
      ,[Fuel]
      ,[Date]
  FROM (  SELECT TOP 2000 [ID]-1 AS ID
      ,[Fuel]
      ,[Date]
  FROM [Vikas].[dbo].[Car]
  WHERE [ID] NOT IN (1)) T
  where [ID]%2 = 0)
  M on A.ID = M.id
  ) R
  ) Z WHERE DATE IS NOT NULL order by [DATE]


OUTPUT :







If you want to try this at your and then run the following script for create table.


CREATE TABLE [dbo].[Car](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Fuel] [int] NULL,
      [Date] [datetime] NULL,
CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Car] ON
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (1, 10, CAST(0x0000A31800A4CB80 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (2, 9, CAST(0x0000A31800B54640 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (3, 8, CAST(0x0000A31800C5C100 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (4, 6, CAST(0x0000A31800D63BC0 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (5, 12, CAST(0x0000A31800E6B680 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (6, 11, CAST(0x0000A31800F73140 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (7, 10, CAST(0x0000A3180107AC00 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (8, 9, CAST(0x0000A318011826C0 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (9, 8, CAST(0x0000A3180128A180 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (10, 10, CAST(0x0000A31801391C40 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (11, 9, CAST(0x0000A31801499700 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (12, 8, CAST(0x0000A318015A11C0 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (13, 7, CAST(0x0000A318016A8C80 AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (14, 15, CAST(0x0000A318017B0740 AS DateTime))

Related

SQL SERVER 2707230709914580685

Post a comment


  1. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to article very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    DedicatedHosting4u.com

    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