mysql>
mysql>
mysql> CREATE TABLE trip_log
-> (
-> seq INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> city VARCHAR(30) NOT NULL, # location of stop
-> t DATETIME NOT NULL, # time of stop
-> miles INT UNSIGNED NOT NULL, # miles traveled so far
-> fuel DECIMAL(6,3), # gas used between citees
-> PRIMARY KEY (seq)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO trip_log (t, city, miles, fuel)
-> VALUES
-> ('2010-10-23 05:00:00', 'San Antonio, TX', 0, 0),
-> ('2010-10-23 09:10:00', 'Dallas, TX', 263, 11.375),
-> ('2010-10-23 13:40:00', 'Benton, AR', 566, 12.398),
-> ('2010-10-23 16:51:00', 'Memphis, TN', 745, 6.820),
-> ('2010-10-23 19:06:00', 'Portageville, MO', 745+133, 7.007),
-> ('2010-10-23 23:16:00', 'Champaign, IL', 745+419, 11.354),
-> ('2010-10-24 03:27:00', 'Madison, WI', 745+667, 13.016)
-> ;
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM trip_log;
+-----+------------------+---------------------+-------+--------+
| seq | city | t | miles | fuel |
+-----+------------------+---------------------+-------+--------+
| 1 | San Antonio, TX | 2010-10-23 05:00:00 | 0 | 0.000 |
| 2 | Dallas, TX | 2010-10-23 09:10:00 | 263 | 11.375 |
| 3 | Benton, AR | 2010-10-23 13:40:00 | 566 | 12.398 |
| 4 | Memphis, TN | 2010-10-23 16:51:00 | 745 | 6.820 |
| 5 | Portageville, MO | 2010-10-23 19:06:00 | 878 | 7.007 |
| 6 | Champaign, IL | 2010-10-23 23:16:00 | 1164 | 11.354 |
| 7 | Madison, WI | 2010-10-24 03:27:00 | 1412 | 13.016 |
+-----+------------------+---------------------+-------+--------+
7 rows in set (0.00 sec)
mysql>
mysql> SELECT t1.seq AS seq1, t2.seq AS seq2,
-> t1.city AS city1, t2.city AS city2,
-> t1.miles AS miles1, t2.miles AS miles2,
-> t2.miles-t1.miles AS dist
-> FROM trip_log AS t1, trip_log AS t2
-> WHERE t1.seq+1 = t2.seq
-> ORDER BY t1.seq;
+------+------+------------------+------------------+--------+--------+------+
| seq1 | seq2 | city1 | city2 | miles1 | miles2 | dist |
+------+------+------------------+------------------+--------+--------+------+
| 1 | 2 | San Antonio, TX | Dallas, TX | 0 | 263 | 263 |
| 2 | 3 | Dallas, TX | Benton, AR | 263 | 566 | 303 |
| 3 | 4 | Benton, AR | Memphis, TN | 566 | 745 | 179 |
| 4 | 5 | Memphis, TN | Portageville, MO | 745 | 878 | 133 |
| 5 | 6 | Portageville, MO | Champaign, IL | 878 | 1164 | 286 |
| 6 | 7 | Champaign, IL | Madison, WI | 1164 | 1412 | 248 |
+------+------+------------------+------------------+--------+--------+------+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table trip_log;
Query OK, 0 rows affected (0.00 sec)
|