(I) Query on Stations

•         1) Find all stations with names including string 'Boston'.
                  SELECT * FROM stations WHERE station LIKE '%Boston%';
•         2) Find all existing stations located in Brookline.
•         3) Return number of stations (including existing and removed stations) for each different municipal.
•         4) Show the largest and smallest latitude and longitude from all stations.

(II) Query on Trips

•         5) Return number of trips with different starting station and ending station.
                  SELECT COUNT(*) AS COUNT From trips WHERE trips.strt_statn != trips.end_statn;
•         6) Find maximal, minimal and average duration for all trips.
•         7) Find top 5 most used bike (Show count and bike number).
•         8) Calculate average number of trips per bike.
•         9) Find top 5 birth year in all trips (Show count and birth year).
•         10) Find number of trips for different starting hours (Show count and starting hours).
•         11) Find number of trips for different ending hours (Show count and ending hours).

(III) Query on both Stations and Trips

•         12) Find top 5 starting stations with most number of trips. (Show count and station name).
•         13) Find top 5 ending stations with most number of trips. (Show count and station name)
•         14) Find top 5 most popular trips. (Show count, starting station name and ending station name)

(IV) Solutions

•         1) SELECT *
              FROM stations
              WHERE station LIKE '%Boston%';


•         2) SELECT *
              FROM stations
              WHERE municipal = 'Brookline' AND status = 'Existing';


•         3) SELECT COUNT(*), municipal
              FROM stations
              GROUP BY municipal;


•         4) SELECT MAX(lat) as MAX_LAT, MIN(lat) as MIN_LAT, MAX(lng) as MAX_LNG, MIN(lng) as MIN_LNG
              FROM stations;


•         5) SELECT COUNT(*) AS COUNT
              From trips
              WHERE trips.strt_statn != trips.end_statn;


•         6) SELECT MAX(duration), MIN(duration), AVG(duration)
              From trips;


•         7) SELECT COUNT(*) AS COUNT, bike_nr
              FROM trips
              WHERE bike_nr != ''
              GROUP BY bike_nr
              ORDER BY COUNT DESC
              LIMIT 5;


•         8) SELECT COUNT(*) AS COUNT
              INTO COUNT_TABLE
              FROM trips
              WHERE bike_nr != ''
              GROUP BY bike_nr;
             
              SELECT AVG(COUNT) FROM COUNT_TABLE;


•         9) SELECT COUNT(*) AS COUNT, birth_date
              FROM trips
              WHERE birth_date > 0
              GROUP BY birth_date
              ORDER BY count DESC
              LIMIT 5;


•         10) SELECT COUNT(*), DATE_PART('hour', start_date)
              FROM trips
              GROUP BY DATE_PART('hour', start_date)
              ORDER BY DATE_PART('hour', start_date) ASC;


•         11) SELECT COUNT(*), DATE_PART('hour', end_date)
              FROM trips
              GROUP BY DATE_PART('hour', end_date)
              ORDER BY DATE_PART('hour', end_date) ASC;


•         12) SELECT COUNT(*) AS COUNT, station
              FROM stations, trips
              WHERE stations.id = trips.strt_statn
              AND trips.strt_statn != trips.end_statn
              GROUP BY station
              ORDER BY COUNT DESC
              LIMIT 5;


•         13) SELECT COUNT(*) AS COUNT, station
              FROM stations, trips
              WHERE stations.id = trips.end_statn
              AND trips.strt_statn != trips.end_statn
              GROUP BY station
              ORDER BY COUNT DESC
              LIMIT 5; )


•         14) SELECT COUNT(*) AS COUNT, S1.station AS START, S2.station AS END
              FROM stations AS S1, stations AS S2, trips
              WHERE S1.id = trips.strt_statn
              AND S2.id = trips.end_statn
              AND trips.strt_statn != trips.end_statn
              GROUP BY S1.station, S2.station
              ORDER BY COUNT DESC
              LIMIT 5;