Question

Please generate the queries in SQL given the following: Problem: Analyzing Santa’s Bike Service (20%) The Santa Bike Service provides rental bicycles in San Francisco. It distributes these bikes through several completely automated stations, each with a number of bike docks for securely parking a rental bike. Users can rent these bicycles by visiting Santa’s website, choosing their preferred station for pick-up, and paying for their rental. The website will generate a reservation code for their use. They can head to the station, enter the code at a bike’s dock and the bicycle will be released. Once users are done with the bicycle, they can return it to any Santa’s station, as long as there is an empty dock for them to park and lock the bicycle.

Santa captures this data, together with some weather data, in a database using the following 4 relations and associated attributes:

TRIP DATA Stores information about a particular trip taken on a bike.

trip_id : an ID created for each trip

duration: duration of the trip (in seconds)

start_station : the name of the station where the bike was rented

end_station : the name of the station where the bike was returned

bike_number : the ID of the bike that was rented for the trip

subscriber_type : the type of customers (“Subscriber” or “Customer”)

zip_code: the home address zip code of the customer who rented the bike for the trip start_date: the start time and date of the trip

end_date: the end time and date of the trip

DAILY WEATHER

mean_temperature : average temperate in a day, in that area

mean_humidity: average humidity in a day, in that area

mean_visibility : a visible viewing distance in kilometres, in that area

precipitation: a measurement of how much rain fell that day, on a scale of 0-10, in that area

cloud_cover: a measurement of how cloudy it was on a day, on a scale of 0-10, in that area

zip: a zip code for some area of San Francisco

date: the date

STATION STATUS

station_id : an ID for the station

bikes_available : the number of available bikes at a particular time, given by the Time attribute

dock_available : the number of available docks for bikes at a particular time, given by the Time attribute

date_time : a time stamp

STATION INFORMATION

station_id : an ID for the station

name : the name of the station

lat : latitude of the station

long : longitude of the station

dock_count : the total number of docks at this station

landmark: landmark of the station

4 Based on the schema above, what SQL queries would you use to retrieve the following information? Please check the BikeStation database on our MySQL server to see the values of each field. 6. Retrieve all the start times and associated end times for all trips with a duration that is less than 1 hour. (2%) 7. Retrieve the list of station names. (2%) 8. Retrieve the list of trips made in June 2016. (2%) 9. Retrieve the list of trips not made in June 2016. (2%) 10. Retrieve the list of all the stations that have available bikes, but no available docks on 2016-01-29. (2%) 11. Retrieve the list of areas (zip code) where the temperature was 50 or lower in January 2016. (2%) 12. Retrieve the list of trips started by customers from these areas where the temperature was 50 or lower in January 2016. (2%) 13. Retrieve the list of the IDs of stations that have bikes available but no available docks on 2016-01-29 between the 6:00 am and 9:00 am. (2%) 14. Retrieve the list of the names of stations that have bikes available but no available docks on 2016-01-29 between the 6:00 am and 9:00 am. (2%) 15. Retrieve the list of all trips where customers started their rides in stations that have more than 30 available docks. (2%) ***Note i only need 10-15***

Answer 1