sql server programming:
Given the following tables:

Refuel
CarId OdometerReading LitersGas
1 1245 55.25
1 1457 16.96
1 1872 34.23
1 2193 25
1 2448 20.4
1 2508 5.52
1 2663 14.15
1 3075 41.82
1 3387 27.77
2 112145 36
2 112972 34.24
2 113357 30.03
2 113731 29.92
2 114130 36.7
2 114535 36.98
2 114943 41.41
3 18091 30.25
3 18291 16.6
3 18506 18.27
3 18791 23.37
3 19065 22.74
3 19364 28.40
3 19569 18.65

CAR
CarID Color Make Model
1 Blue Ford Taurus
2 Green VW Jetta
3 Blue Acura 1.7EL

Take it as a given that every time a car is refueled, the tank is
filled.
To determine gas consumption (l/100km), take the LitersGas, divide it by
kilometers traveled, and multiply by 100.
For example:
If the car takes 45 liters of gas, and had traveled 502 km since the
previous time it was refueled, the fuel consumption would be: 45 liters
/ 502 km * 100 = 8.96 l/100km

1. Is it possible to make a single sql statement that would determine
the fuel consumption and return the following table:

OdometerReading Consumption Color Make
1245 n/a Blue Ford
1457 7.54 Blue Ford
1872 8.19 Blue Ford
2193 7.78 Blue Ford
2448 7.84 Blue Ford
2508 8.33 Blue Ford
2663 9.03 Blue Ford
3075 9.95 Blue Ford
3387 8.65 Blue Ford
112145 n/a Green VW
112972 4.11 Green VW
113357 7.79 Green VW
113731 7.75 Green VW
114130 9.02 Green VW
114535 8.88 Green VW
114943 10.04 Green VW
18091 n/a Blue Acura
18291 8 Blue Acura
18506 8.37 Blue Acura
18791 8.07 Blue Acura
19065 8.02 Blue Acura
19364 9.36 Blue Acura
19569 8.78 Blue Acura

2. If you answered yes to question 1, please give the sql statement.
If you answered no, please explain why it isn’t possible, what extra
information is needed and give a sample sql statement that should give
the answer above.

[quoted text, click to view]
Yes

[quoted text, click to view]

select x.OdometerReading, x.Consumption, c.Color, c.Make
from
(
select CarId
, min(OdometerReading) as OdometerReading
, N'N/A' as Consumption
from Refuel as r
group by CarId
union all
select s.CarId
, f.OdometerReading
, cast(cast(f.LitersGas / (f.OdometerReading - s.OdometerReading) * 100 as
decimal(4, 2)) AS nvarchar(8)) as Consumption
from Refuel as s
inner join Refuel as f
on s.CarId = f.CarId
and f.OdometerReading > s.OdometerReading
where f.OdometerReading =
(
select top (1) OdometerReading
from Refuel as x
where (CarId = s.CarId)
and (OdometerReading > s.OdometerReading)
order by OdometerReading
)
) as x
join Car c
on c.carId = x.carId
order by x.carId, x.OdometerReading