SQL for Buying a New Car

Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I needed a reliable car. A big factor was gas mileage. Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars. So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK. The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL. Here's the result:

COLUMN ppg FORMAT $999.00
COLUMN car_1_gpy FORMAT 99,999
COLUMN car_2_gpy FORMAT 99,999
COLUMN car_1_cpy FORMAT $999,999.00
COLUMN car_2_cpy FORMAT $999,999.00
COLUMN car_1_cpm FORMAT 999.00
COLUMN car_2_cpm FORMAT 999.00

SELECT
  car_1,
  car_2,
  ppg,
  mpy,
  ROUND( ( mpy / car_1 ) ) car_1_gpy,
  ROUND( ( mpy / car_2 ) ) car_2_gpy,
  ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,
  ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,
  ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,
  ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpm
FROM
  (
    SELECT
      rownum car_1,
      LAG( rownum, 5 ) OVER
        ( PARTITION BY NULL
          ORDER BY rownum ) car_2
    FROM dual
      CONNECT BY LEVEL <= 50
  ) car_miles,
  (
    SELECT
      3.99 ppg,
      15000 mpy
    FROM dual
  ) constants
/

     CAR_1      CAR_2 CAR_1_GPY CAR_2_GPY    CAR_1_CPY    CAR_2_CPY CAR_1_CPM CAR_2_CPM
---------- ---------- --------- --------- ------------ ------------ --------- ---------
         1               15,000             $59,850.00                   3.99
         2                7,500             $29,925.00                   2.00
         3                5,000             $19,950.00                   1.33
         4                3,750             $14,962.50                   1.00
         5                3,000             $11,970.00                    .80
         6          1     2,500    15,000    $9,975.00   $59,850.00       .67      3.99
         7          2     2,143     7,500    $8,550.00   $29,925.00       .57      2.00
         8          3     1,875     5,000    $7,481.25   $19,950.00       .50      1.33
         9          4     1,667     3,750    $6,650.00   $14,962.50       .44      1.00
        10          5     1,500     3,000    $5,985.00   $11,970.00       .40       .80
        11          6     1,364     2,500    $5,440.91    $9,975.00       .36       .67
        12          7     1,250     2,143    $4,987.50    $8,550.00       .33       .57
        13          8     1,154     1,875    $4,603.85    $7,481.25       .31       .50
        14          9     1,071     1,667    $4,275.00    $6,650.00       .28       .44
        15         10     1,000     1,500    $3,990.00    $5,985.00       .27       .40
        16         11       938     1,364    $3,740.63    $5,440.91       .25       .36
        17         12       882     1,250    $3,520.59    $4,987.50       .23       .33
        18         13       833     1,154    $3,325.00    $4,603.85       .22       .31
        19         14       789     1,071    $3,150.00    $4,275.00       .21       .28
        20         15       750     1,000    $2,992.50    $3,990.00       .20       .27
        21         16       714       938    $2,850.00    $3,740.63       .19       .25
        22         17       682       882    $2,720.45    $3,520.59       .18       .23
        23         18       652       833    $2,602.17    $3,325.00       .17       .22
        24         19       625       789    $2,493.75    $3,150.00       .17       .21
        25         20       600       750    $2,394.00    $2,992.50       .16       .20
        26         21       577       714    $2,301.92    $2,850.00       .15       .19
        27         22       556       682    $2,216.67    $2,720.45       .15       .18
        28         23       536       652    $2,137.50    $2,602.17       .14       .17
        29         24       517       625    $2,063.79    $2,493.75       .14       .17
        30         25       500       600    $1,995.00    $2,394.00       .13       .16
        31         26       484       577    $1,930.65    $2,301.92       .13       .15
        32         27       469       556    $1,870.31    $2,216.67       .12       .15
        33         28       455       536    $1,813.64    $2,137.50       .12       .14
        34         29       441       517    $1,760.29    $2,063.79       .12       .14
        35         30       429       500    $1,710.00    $1,995.00       .11       .13
        36         31       417       484    $1,662.50    $1,930.65       .11       .13
        37         32       405       469    $1,617.57    $1,870.31       .11       .12
        38         33       395       455    $1,575.00    $1,813.64       .11       .12
        39         34       385       441    $1,534.62    $1,760.29       .10       .12
        40         35       375       429    $1,496.25    $1,710.00       .10       .11
        41         36       366       417    $1,459.76    $1,662.50       .10       .11
        42         37       357       405    $1,425.00    $1,617.57       .09       .11
        43         38       349       395    $1,391.86    $1,575.00       .09       .11
        44         39       341       385    $1,360.23    $1,534.62       .09       .10
        45         40       333       375    $1,330.00    $1,496.25       .09       .10
        46         41       326       366    $1,301.09    $1,459.76       .09       .10
        47         42       319       357    $1,273.40    $1,425.00       .08       .09
        48         43       313       349    $1,246.88    $1,391.86       .08       .09
        49         44       306       341    $1,221.43    $1,360.23       .08       .09
        50         45       300       333    $1,197.00    $1,330.00       .08       .09

Obviously this isn't terribly difficult. You can do lots with the results as well. Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually." I love SQL... *Key: car_1 = miles for car 1 car_2 = miles for car 2 car_1_gpy = gallons per year car 1 car_2_gpy = gallons per year car 2 car_1_cpy = cost per year car 1 (gas) car_2_cpy = cost per year car 2 (gas) car_1_cpm = cost per mile car 1 car_2_cpm = cost per mile car 2