SQL Refresh

Practice

With a partner, see if you can complete each of the tasks below. After each section we will check in as a group.

SELECT FROM LIMIT

* All of the information on the stations table. 
  * SELECT * FROM stations;
* Max, min, and mean temp from the conditions table. 
  * SELECT max_temperature_f, min_temperature_f, mean_temperature_f FROM conditions;
* id, start_station_id, and duration of five trips.
  * SELECT id, start_station_id, duration FROM trips LIMIT 5;
  
WHERE

* Trips that started at the station with an id of 2.
  * SELECT * FROM trips WHERE start_station_id = 2;
* Stations that have a dock_count of 15.
  * SELECT * FROM stations WHERE dock_count=15;
* id, date, and precipitation for conditions with more than 1 inch of precipitation.
  * SELECT id, date, precipitation_inches FROM conditions WHERE precipitation_inches > 1;

max/min/count/average

* Duration of the longest trip.
  * SELECT MAX(duration) FROM trips;
* Duration of the shortest trip.
  * SELECT MIN(duration) FROM trips;
* Average dock_count at a station.
  * SELECT AVG(dock_count) FROM stations;
* Highest dock_count at a station.
  * SELECT MAX(dock_count) FROM stations;
* Count of days with no rain.
  * SELECT count(id) FROM conditions WHERE precipitation_inches=0;
* Name/dock_count of the station with the most docks.
  * SELECT name, dock_count FROM stations ORDER BY dock_count DESC LIMIT 1;
* Id, start station id, and duration of the longest trip.
  * SELECT id, start_station_id, duration FROM trips ORDER BY duration DESC LIMIT 1;
  * SELECT id, start_station_id, duration FROM trips WHERE duration = (SELECT max(duration) FROM trips);
* Id, start station id, and duration of the shortest trips.
  * SELECT id, start_station_id, duration FROM trips ORDER BY duration ASC LIMIT 1;

JOIN

* Name of the station where the longest trip started.
  * SELECT stations.name FROM stations JOIN trips ON stations.id=trips.start_station_id ORDER BY trips.duration DESC LIMIT 1;
  * SELECT stations.name FROM stations JOIN trips ON stations.id=trips.start_station_id WHERE duration = (SELECT max(duration) FROM trips);
* Name of the stations where the shortest trips started.
  * SELECT stations.name FROM stations JOIN trips ON stations.id=trips.start_station_id WHERE duration = (SELECT min(duration) FROM trips);

GROUP

* Count of trips started at each station.
  * SELECT stations.name, count(trips.id) AS trip_count FROM stations JOIN trips ON stations.id=trips.start_station_id GROUP BY stations.id;
* Count of trips ended at each station.
  * SELECT stations.name, count(trips.id) AS trip_count FROM stations JOIN trips ON stations.id=trips.end_station_id GROUP BY stations.id;
* Count of trips started on days with more than an inch of precipitation.
  * SELECT conditions.date, count(trips.id) AS trip_count FROM conditions JOIN trips ON conditions.id=trips.condition_id WHERE conditions.precipitation_inches > 1 GROUP BY conditions.date;

ORDER

* Top five stations with the most trips started.
* Top five stations with the most trips ended.
* Least popular start station.
* mean_temperature and precipiation on the five dates with the most trips.