一道外企SQL面试题，挺考验基本功的 (原)

Your question is : 

Given the following tables:

Refuel
CarID	OdometerReading	LitersGas
	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.

 

Next sql statment is my answer:

create table Refuel
(
 CarID int,
 OdometerReading int,
 LitersGas numeric(4,2)
)
go

create table car
(
 CarID int,
 Color varchar(200),
 Make varchar(200),
 Model varchar(200)
)
go

insert into car values(1,'Blue','Ford','Taurus')
insert into car values(2,'Green','VW','Jetta')
insert into car values(3,'Blue','Acura','1.7EL')

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

select w.odometerreading,nvl(to_char(round(w.litersgas*100/w.kilomete,2)),'n/a') Consumption,w.color,w.make 
from (select r.odometerreading,
r.odometerreading - lag(r.odometerreading,1) over(PARTITION BY r.carid order by r.carid,r.odometerreading) as kilomete
,r.litersgas,c.color,c.make  from Refuel r ,car c where r.carid= c.carid) w

Please pay attention to the nvl function ,the two parameters of this function must have the same datatype .In this question ,the return type of the round(...) function is numeric datatype but i must replace it into a 'n/o' which is a varchar datatype .So i must change the datatype using to_char() function.   

the result of the sql statment is :

 

1245	n/a	Blue	Ford
1457	8	Blue	Ford
1872	8.25	Blue	Ford
2193	7.79	Blue	Ford
2448	8	Blue	Ford
2508	9.2	Blue	Ford
2663	9.13	Blue	Ford
3075	10.15	Blue	Ford
3387	8.9	Blue	Ford
112145	n/a	Green	VW
112972	4.14	Green	VW
113357	7.8	Green	VW
113731	8	Green	VW
114130	9.2	Green	VW
114535	9.13	Green	VW
114943	10.15	Green	VW
18091	n/a	Blue	Acura
18291	8.3	Blue	Acura
18506	8.5	Blue	Acura
18791	8.2	Blue	Acura
19065	8.3	Blue	Acura
19364	9.5	Blue	Acura
19569	9.1	Blue	Acura

 

I surprisingly found that the result is different from the answer the interviewer gave , If someone have any question about that ,Don't hestated to leave message to me ,Chris_Liu was waiting for you.....