The Fifth Lab

Query Statements in PostgreSQL

Octy 2, 2015

(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;