Use the csv file shown below

Create a database named mileage. Then use the mileage database.

Then use the following to create the appropriate table:

CREATE TABLE fillups (id INT not null PRIMARY KEY AUTO_INCREMENT, date_time datetime, volume DOUBLE, vehicle_id INTEGER, odometer DOUBLE, economy DOUBLE, total_cost DOUBLE, price DOUBLE, is_partial INTEGER, `restart` INTEGER ); 

Now for the fun part. The app stored the second column using milliseconds since the epoch. MySQL is only capable of handling seconds since the epoch. Therefore, we will need to modify the data as it is being loaded (something we have done in the past). To convert, you will use the from_unixtime function. Try the following now, in DBeaver, enter the follow query:

select from_unixtime(1392164473694 / 1000); 

That should give you the following result: 2014-02-11 19:21:14.Now, using that knowledge you can use the following template to load the data into your database:

LOAD DATA LOCAL INFILE 'Your path to snames here/mileage.csv' INTO TABLE fillups FIELDS TERMINATED BY ',' IGNORE 1 LINES (id, @date_time, You fill in the rest of the column names) set date_time=from_unixtime(@date_time / 1000); 

Now have a look at the data. The first thing to notice is that the restart column is useless. Write a SQL statement to remove that column from the table.

The data is largely self-explanatory. The only part that is not obvious is the vehicle_id. All you need to know is that one of the cars is a Prius. It turns out there is one row that has erroneous data. Find that row, and write a SQL statement to modify the erroneous value. Include in that statement a where clause that selects the row by its id. Hint: It is not the economy column. Hint: You may want to try ordering by the various columns to look at the high and low values.

Write a SQL query that will show the minimum, average, and maximum prices of gas.

Now write a SQL query that will show the vehicle_id and the total amount spent for gas for all fillups on each vehicle.

Let's see if we can find out which car is the Prius. Write a SQL statement that will select the vehicle_id and average (fuel-) economy of each vehicle rounded to the nearest whole mile-per-gallon. If on your first attempt you see average (fuel-) economy's of 37 and 25, you're wrong. Go back and look at the data, there are some values that will need to be explicitly excluded in your query. Hint: you may want to order by to check the low and high values in the appropriate column.

Now, modify your query from above to include a CASE statement to display Prius, for vehicle_ids that are the Prius and Other, for other vehicle_ids.

Write a SQL query that will show how many times gas was pumped for any given hour of the day that gas was pumped.

Write a SQL query that will show how many times gas was pumped for each day of the week (displayed as the three letter abbreviation for that day), sorted from most number of fillups to least.

Write a SQL query that shows the vehicle_id, the month and year of the fillup, the number of times the car was filled with gas that month and the total number of gallons of gas for that month sorted in descending order of total number of gallons.

timestamivolume vehicle_ic odometer economy total_cost price is_partial restart 2 1 1.33E+127.697 2 1.33E+127.634 8.62 4 1.33E+127.441 5 1.33E+125.205 61.34[+12 6.434 7 1.34E+128.564 81.34[+12 9.138 9 1.34E+128.268 10 1.34E+12 9.135 11 1.34E+12 9.306 121. 34?+12 8.714 131. 34?+12 9.064 141. 34?+12 6.791 151. 34?+12 9.353 16 1.35E+12 8.602 17 1.35E+126.827 18 1.35E+12 7.758 191. 35?+12 9.087 20 1.35E+128.271 21 1.35E+128.536 9.01 23 1.35?+12 8.905 24 1.35E+127.962 2 17511 2 17777 34.8441230.07033 3.939 2 18115 39.21114 34.126583.959 2 18412 39.91399 29.3101 3.939 2 18593 34.77426 20.81483.999 2 18878 44.29593 24.828813.859 2 19226 40.63522 33.21976 3.879 2 19585 39.2865 34.349743.759 2 19877 35.31688 29.92189 3.619 2 20227 38.31418 32.694173.579 2 20575 37.39523 31.817213.419 2 20911 38.55864 29.27033 3.359 2 21266 39.16593 30.808543.399 2 21533 39.31674 23.76171 3.499 2 21904 39.6664233.6614 3.599 2 22249 40.10695 31.990843.719 2 22524 40.28124 26.208853.839 2 22813 37.25187 30.4036 3.919 2 23169 39.17685 34.703253.819 2 23482 37.84307 31.586953.819 2 23792 36.31678 32.257543.779 2 24128 37.2919 34.228993.799 2 24460 37.28243 32.76153.679 2 24748 36.17182 28.177523.539 0 29.548783.839 3 1.33E+12 2 3 10 14 16 22 1.35E+12 24 timestamivolume vehicle_ic odometer economy total_cost price is_partial restart 2 1 1.33E+127.697 2 1.33E+127.634 8.62 4 1.33E+127.441 5 1.33E+125.205 61.34[+12 6.434 7 1.34E+128.564 81.34[+12 9.138 9 1.34E+128.268 10 1.34E+12 9.135 11 1.34E+12 9.306 121. 34?+12 8.714 131. 34?+12 9.064 141. 34?+12 6.791 151. 34?+12 9.353 16 1.35E+12 8.602 17 1.35E+126.827 18 1.35E+12 7.758 191. 35?+12 9.087 20 1.35E+128.271 21 1.35E+128.536 9.01 23 1.35?+12 8.905 24 1.35E+127.962 2 17511 2 17777 34.8441230.07033 3.939 2 18115 39.21114 34.126583.959 2 18412 39.91399 29.3101 3.939 2 18593 34.77426 20.81483.999 2 18878 44.29593 24.828813.859 2 19226 40.63522 33.21976 3.879 2 19585 39.2865 34.349743.759 2 19877 35.31688 29.92189 3.619 2 20227 38.31418 32.694173.579 2 20575 37.39523 31.817213.419 2 20911 38.55864 29.27033 3.359 2 21266 39.16593 30.808543.399 2 21533 39.31674 23.76171 3.499 2 21904 39.6664233.6614 3.599 2 22249 40.10695 31.990843.719 2 22524 40.28124 26.208853.839 2 22813 37.25187 30.4036 3.919 2 23169 39.17685 34.703253.819 2 23482 37.84307 31.586953.819 2 23792 36.31678 32.257543.779 2 24128 37.2919 34.228993.799 2 24460 37.28243 32.76153.679 2 24748 36.17182 28.177523.539 0 29.548783.839 3 1.33E+12 2 3 10 14 16 22 1.35E+12 24